T-SQL cursor for linked server INSERT SELECT

Question

I'm trying to get this cursor loop to work, so I could copy data from linked server to another server. However it seems that the cursor is in a loop and does nothing. What am I doing wrong?

    /* For testing purposes I'm fetching data from
       1 company only. The result should be one row.*/

    DECLARE @tmp_key VARCHAR(14)
     DECLARE @db cursor
     DECLARE @sql NVARCHAR(MAX)

    SET @db = CURSOR FOR  
    SELECT [CompanyId] FROM [Test].[dbo].[Company] WHERE [CompanyId] = '0001'

     SET @sql = N'INSERT INTO [Stagingarea].[dbo].[Cominfo]
    SELECT
            convert(nvarchar(100),[Nro])
            ,convert(nvarchar(100),'+@tmp_key+' )
    FROM [Linked_server_name].TK'+@tmp_key+'.[dbo].[cominfo]
    where [rule1] <> 0 and acc = 1777';  

     OPEN @db
     FETCH NEXT FROM @db INTO @tmp_key
     while (@@fetch_status = 0)
    begin

    EXEC sp_sqlexec @sql 

    end;
    CLOSE  @db
    DEALLOCATE @db

score 1 · Accepted Answer · answered Mar 04 '15 at 08:04

You should put the dynamic SQL inside the WHILE loop. Make sure to call FETCH NEXT inside to avoid infinite loop.

DECLARE @tmp_key VARCHAR(14)
DECLARE @db cursor
DECLARE @sql NVARCHAR(MAX)

SET @db = CURSOR FOR  
    SELECT [CompanyId] FROM [Test].[dbo].[Company] WHERE [CompanyId] = '0001'

OPEN @db
FETCH NEXT FROM @db INTO @tmp_key

WHILE(@@FETCH_STATUS = 0) BEGIN

    SET @sql = N'INSERT INTO [Stagingarea].[dbo].[Cominfo]
    SELECT
        CONVERT(NVARCHAR(100), [Nro])
        ,CONVERT(NVARCHAR(100),' + @tmp_key +')
    FROM [Linked_server_name].TK' + @tmp_key + '.[dbo].[cominfo]
    WHERE [rule1] <> 0 AND acc = 1777';  

    EXEC sp_sqlexec @sql

    FETCH NEXT FROM @db INTO @tmp_key



END;

CLOSE  @db
DEALLOCATE @db

Thank you. It works fine now. I´ll accept your answer in 6 minutes — user1054844, Mar 04 '15 at 08:07

T-SQL cursor for linked server INSERT SELECT

1 Answers1