Insert the Dynamic SQL into a Table

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)

Insert the Dynamic SQL into a Table

Author

Topic

whiteshadow
Starting Member

15 Posts

Posted - 2005-04-14 : 05:28:59

Hi,
I would have this error 'Server: Msg 197, Level 15, State 1, Line 8
EXECUTE cannot be used as a source when inserting into a table variable.' upon executing this code.

DECLARE @S VARCHAR(100)
DECLARE @T TABLE(
AU_ID INT,
AU_LNAME VARCHAR(40),
AU_FNAME VARCHAR(20)
)
SET @S = 'SELECT AU_ID, AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_LNAME LIKE ''W%'' '
INSERT INTO @T EXEC(@S)

How do i insert the resulting data of my dynamic SQL into a temporary table.
Thanks a lot in advance for your time and effort.

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-14 : 05:40:49

Why are you doing this with dynamic SQL ?

You wont be able to use that with your table variable, because the scope of the variable will be within the dynamic sql execute itself, so even if you used the INSERT within your dynamic sql it would still break.

-------
Moo. :)

whiteshadow
Starting Member

15 Posts

Posted - 2005-04-14 : 05:57:44

Hi mr_mist,
Thanks for your reply. Your actually correct, I should have not used a variable table instead I should have used local temporary table. This is code now works fine.
DECLARE @S VARCHAR(100)
CREATE TABLE #T (
AU_ID VARCHAR(20),
AU_LNAME VARCHAR(40),
AU_FNAME VARCHAR(20)
)
SET @S = 'SELECT AU_ID, AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_LNAME LIKE ''W%'' '
INSERT INTO #T EXEC(@S)
SELECT * FROM #T
DROP TABLE #T

Thanks a lot for your time and effort and more power to you all!!! :D

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-14 : 10:41:39

CREATE TABLE #T (
AU_ID VARCHAR(20),
AU_LNAME VARCHAR(40),
AU_FNAME VARCHAR(20)
)

INSERT INTO #T SELECT AU_ID, AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_LNAME LIKE 'W%'
SELECT * FROM #T
DROP TABLE #T

This works just fine, without the dynamic SQL. I am trying to figure out why you dont just do the select?

SELECT AU_ID, AU_LNAME, AU_FNAME FROM AUTHORS WHERE AU_LNAME LIKE 'W%'

whiteshadow
Starting Member

15 Posts

Posted - 2005-04-15 : 06:44:19

Hi DonAtWork,
Thanks for your reply. It was just a sample code, I have the same scenario in the project that im working right now. In my project I have to use a dynamic SQL to insert in my temporary table.
Thnaks for your time and effort and more power to you.. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources