[JBurgett]

How can I place the results of a dynamically generated select statement into a (#) temp table?

This has me stumped, any help would be greatly appreciated.

(The names used in the following aren't the actual names, just using the name of what that position represents for readability)

The following query is what I want to use, which works as desired:

SELECT * INTO #TMP FROM [LinkedServer].[DBName].Schema.Table EXCEPT
SELECT * FROM Schema.Table

However, the Schema/Table wont be static (including not knowing the # of columns in the table). I'm trying to place this into a SP where I pass the Schema name and Table name as parameters so I can construct something like the following:

DECLARE @TableName varchar(100)
SET @TableName = 'MyTable'
DECLARE @SchemaName varchar(100)
SET @SchemaName = 'MySchema'

DECLARE @SQL varchar(100)

SET @SQL = ('SELECT * INTO #TMP FROM [LinkedServer].[DBName].' + @SchemaName + '.' + @TableName + ' EXCEPT SELECT * FROM ' + @TableName)

EXEC (@SQL)

Then the results would be placed into the temporary table #TMP.

The above reports an error that #TMP doesn't exist. I can't manually create the temp table as the number of columns vary for each passed table.

Again it works as desired as long as I supply it with the actual values.

The results are being placed into a CURSOR so I can iterate thru each result.

Surely there has to be a way I can do this.

Joey

[ck9663]

It has something to do with the instance of temp table. It is only visible within the same instance or any instance created within. An EXEC creates a separate so the #temp table created inside the EXEC is not visible outside.

Create your temp table before the EXEC and use INSERT INTO instead of SELECT INTO

Happy coding.

-- CK

[JBurgett]

Originally Posted by ck9663

It has something to do with the instance of temp table. It is only visible within the same instance or any instance created within. An EXEC creates a separate so the #temp table created inside the EXEC is not visible outside.

Create your temp table before the EXEC and use INSERT INTO instead of SELECT INTO

Happy coding.

-- CK

Thanks for the response.

Is there a way I can create a global temp table w/o having to provide it with fields?

I can use: SELECT * INTO ##TMP FROM SomeTable

Then I have a global temp table with all the columns from that table.

I'm passing the table name into this procedure so the select statement has to be dynamic. 'SELECT * INTO ##TMP FROM ' + @SomeTableName

And it says invalid object name '##TMP'.

[JBurgett]

I was able to accomplish what I needed and thought I would share it in case someone else was curious:

declare @Result varchar(200)
declare @TableName varchar(100)
set @TableName = 'My_TestTable'

declare @SQLCommand varchar(200)
set @SQLCommand = ('SELECT * FROM ' + @TableName + ' EXCEPT SELECT * FROM ' + @TableName)

/* Create link to self */
exec sp_addlinkedserver N'LocalSQL', ' ', N'SQLNCLI', N'.\SQLEXPRESS', '','', N'DBNameHere'

declare @EXECString varchar(300)
set @EXECString = ('SELECT * INTO #TMP FROM OPENQUERY(' + LocalSQL + ',''' + @SQLCommand + ''')')

exec(@EXECString)

declare @ChangedRecords CURSOR
SET @ChangedRecords = CURSOR FOR
SELECT Field1 FROM #TMP

open @ChangedRecords
FETCH NEXT FROM @ChangedRecords INTO @Result

while 1=1
begin
if @@FETCH_STATUS <> 0
break

begin
exec('DELETE FROM ' + @TableName + ' WHERE Field1 = ' + @Result)
end

FETCH NEXT FROM @ChangedRecords INTO @Result
end
close @ChangedRecords
deallocate @ChangedRecords
go
drop table #tmp