Dynamic SQL - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Dynamic SQL

Author

Topic

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2005-06-06 : 10:19:18

Hello,I am trying to the result of a 'dynamic' Qry into a result Table. I should use an 'insert into ....values ....'
But the number of colums on the qry are different, and I don't know how to get this done..

Any Help is welcome...

**-------Start of code--------------**
DECLARE @TblName as varchar (50),
@Cmd as varchar(200),
@Row as integer

-- This tablename should com as parameter to a SP
SET @TblName = 'IF_INITIATESSCC'

DECLARE Transfer_Cursor CURSOR FOR
SELECT ROW_NBR FROM TRANSFER_TABLE
WHERE TABLENAME = @TblName
AND CREATE_DATE > GETDATE() - 1
ORDER BY CREATE_DATE DESC

OPEN Transfer_Cursor
FETCH NEXT FROM Transfer_Cursor
INTO @Row

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Cmd = 'SELECT * FROM ' + @TblName + ' WHERE ROW_NBR = ' + CONVERT(varchar(20), @ROW)
print @Cmd -- debug only
EXEC (@Cmd)
FETCH NEXT FROM Transfer_Cursor
INTO @Row
END

CLOSE Transfer_Cursor
DEALLOCATE Transfer_Cursor

**--------End of code -----------**

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-06 : 10:48:44

Dont use cursor. See if this helps you


DECLARE @TblName as varchar (50),@Cmd as varchar(2000)
SET @TblName = 'IF_INITIATESSCC'
SELECT @cmd = 'Select * into ResultTable from ( SELECT * FROM ' + @TblName + ' WHERE ROW_NBR in (SELECT ROW_NBR FROM TRANSFER_TABLE
WHERE TABLENAME = '''+@TblName+''' AND CREATE_DATE > (GETDATE() - 1))) T ORDER BY CREATE_DATE DESC)'
print @cmd -- Check whether the query is correct
Exec(@cmd)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources