Problem placing results of generated select statem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem placing results of generated select statem

Author

Topic

jburgett
Starting Member

3 Posts

Posted - 2008-10-04 : 14:40:42

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 Burgett

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 14:45:42

#TMP will be out of scope once the execution of dynamic string is over. so use global temporary table ##Temp instead of #Temp.
Make sure this procedure wont be run by users simultaneously as it will cause problems executing code using global temp table by various users simultaneously.

jburgett
Starting Member

3 Posts

Posted - 2008-10-05 : 14:08:50

quote:
Originally posted by visakh16

#TMP will be out of scope once the execution of dynamic string is over. so use global temporary table ##Temp instead of #Temp.
Make sure this procedure wont be run by users simultaneously as it will cause problems executing code using global temp table by various users simultaneously.

Still says ##TEMP object doesn't exist.

I'm passing the table name as a parameter in to this SP so I cant do a CREATE TABLE #TMP (fields... ), since fields will vary from table to table.

Is there a way to create a table w/o having to specify the columns?

jburgett
Starting Member

3 Posts

Posted - 2008-10-05 : 16:43:33

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources