asked
Use Cursors to copy tables
Hi All, I have to design a archiving solution: source table1YYYYMMDD table2YYYYMMDD table3YYYYMMDD destination table1_master all the tables from source will be appended in the destination master table..this has to be done for many tables of similar types. I plan to use CURSORS to capture the names of the tables. in the code below: DECLARE @a varchar(100) DECLARE cur_table_copy CURSOR FOR -- Cursor declared SELECT name FROM test1.sys.tables where type = 'u' and name like 'table1%[0-9]' OPEN cur_table_copy --Opening Cursor FETCH NEXT FROM cur_table_copy INTO @a --Put values to variable WHILE @@FETCH_STATUS = 0 -- Faching is success BEGIN insert into [DestinationInstance].DBname.dbo.table1_master select * from @ END CLOSE cur_table_copy -- Closing cursor DEALLOCATE cur_table_copy -- De-allocating I am able to capture the table names in cursor, but the select part does not work.. If I try to use something like: set @temp = 1 if @temp <= @count set @count = (select COUNT(*) FROM test1.sys.tables where type = 'u' and name like table1%[0-9]') set @temp = @temp + 1 the WHILE loop becomes infinite, what is it that I am doing wrong?
DECLARE @SQL VARCHAR(MAX) DECLARE @TableName SYSNAME DECLARE cur_table_copy CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' OPEN cur_table_copy FETCH NEXT FROM cur_table_copy INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'insert into [DestinationInstance].DBname.dbo.table1_master select * from ' + @TableName EXEC (@Sql) FETCH NEXT FROM cur_table_copy INTO @TableName END CLOSE cur_table_copy DEALLOCATE cur_table_copy
13 People are following this question.
