Use Cursor Value for a Query inside Cursor

Question

I'd like to persist some Views we have on DB Server A into fixed tables in DB Server B. My plan is as follow: I get all Views from DB Server A and write it into a helper-table in DB Server B.

Then I create a Cursor, to execute following query for each cursor value: "select * into DBServerB.dbo.@CursorValue from DBServerA.dbo.@CursorValue"

My current code looks like this:

 declare @tView as nvarchar(128)


 DECLARE CursorViews CURSOR local fast_forward
    for
    select [name] from dbo.TablesforImport


Open CursorViews

fetch next from CursorViews into @tView
while (@@fetch_status = 0)
Begin
'Select * into [DBServerB].[DB].dbo.'+@tView +' FROM [DBServerA].[DB].[dbo].'+ @tView
fetch next from CursorViews into @tView
end;
close CursorViews
deallocate CursorViews

Currently im struggling to get the query work. Any ideas? :)

Answer 1

You can use this:

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT
        name
    FROM
        TablesforImport
    OPEN cur;

    FETCH NEXT FROM cur INTO @name;

WHILE @@FETCH_STATUS = 0
BEGIN

    set @sql= 'SELECT * into [DBServerB].[DB].dbo.[' + @name + '] FROM [DBServerA].[DB].dbo.[' + @name + ']'

    exec sp_executesql @sql

FETCH NEXT FROM cur INTO @name;
end
close cur
deallocate cur