May 13, 2009 at 8:17 pm
Greetings. I was trying to answer a post in the newbie forum, and I gave a dynamic SQL solution using temp tables. I then thought there might be cause to use table variables instead. I was able to replace two of the three temp tables with table varibles, but I could not get the third to work. The one that is causing trouble is being used in the EXEC(@SQL) part of the solution. I think it all boils down to the below.
DECLARE @table TABLE
(
col1 CHAR(1)
)
DECLARE @sql VARCHAR(200)
SELECT @sql = 'INSERT INTO @table SELECT ''1'''
INSERT INTO @table SELECT '1'
--EXEC(@SQL)
SELECT
*
FROM @table
If I try to uncomment the EXEC line, and comment out the INSERT line, I get an error saying I must declare the variable @table. So, my questions are, 1) is there a way around this, and 2) why does the executing of @sql not recognize @table?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 13, 2009 at 9:27 pm
Your problem is happening because the scope of variables. When you use the EXEC command, the statements within the EXEC cannot "see" the variables.
You could try something like..
DECLARE @table TABLE
(
col1 CHAR(1)
)
DECLARE @sql VARCHAR(200)
SELECT @sql = 'SELECT ''1'''
INSERT INTO @table EXEC(@SQL)
SELECT
*
FROM @table
May 13, 2009 at 10:00 pm
happycat59 (5/13/2009)
INSERT INTO @table EXEC(@SQL)SELECT
*
FROM @table
Hi,
Is this works in the SQL?
ref the post: http://www.sqlservercentral.com/Forums/Topic711409-338-2.aspx
ARUN SAS
May 14, 2009 at 6:49 am
Thanks both, but its back to the drawing board. When I tried your solutions, I got...
Server: Msg 197, Level 15, State 1, Line 7
EXECUTE cannot be used as a source when inserting into a table variable.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 14, 2009 at 6:55 am
are you sure that happycat59's solution above does not work it works for me?
Or you could try this:
DECLARE @sql VARCHAR(200)
SELECT @sql = 'DECLARE @table TABLE
(
col1 CHAR(1)
)
INSERT INTO @table SELECT ''1''
SELECT
*
FROM @table'
EXEC(@SQL)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 14, 2009 at 7:28 am
I think you will find you can't INSERT .... EXEC into a table variable in SQL Server 2000. It has to be SQL Server 2005 or higher.
Why not just use a temporary table instead?
Mike
May 14, 2009 at 7:49 am
Christopher Stobbs (5/14/2009)
are you sure that happycat59's solution above does not work it works for me?Or you could try this:
DECLARE @sql VARCHAR(200)
SELECT @sql = 'DECLARE @table TABLE
(
col1 CHAR(1)
)
INSERT INTO @table SELECT ''1''
SELECT
*
FROM @table'
EXEC(@SQL)
That would work, but in my scenario, the insert into the table is in a nested while loop, so I can not create it every time the inner loop fires. The code in question is here...
http://www.sqlservercentral.com/Forums/Topic713278-169-1.aspx, and is in my second post with code. I am trying to replace all the temp tables with table variables. The one I can't get is #final. I know I can do it by eliminating the EXEC(@SQL) part, but have not yet messed with the syntax.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 14, 2009 at 7:51 am
mdowns (5/14/2009)
I think you will find you can't INSERT .... EXEC into a table variable in SQL Server 2000. It has to be SQL Server 2005 or higher.Why not just use a temporary table instead?
Mike
That seems to be the case. And I have a solution with temp tables, my wanting to use table variables is strictly for the sake of seeing if I can do it. Thank you.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 14, 2009 at 8:16 am
Have you tried using the system tables in SQL?
Here is a dynamic unpivot solution with not temp tables or table variables.
I must admit that I found this code a while and and have customized it abit, but I was not 100% the original author.
SET NOCOUNT ON
--SAMPLE TABLE
CREATE TABLE Test
(
ID INT,
pstt375 INT,
pstt455 INT,
pstt585 INT,
pstt643 INT,
phyl375 INT,
pwml306 INT
)
--SAMPLE DATA
INSERT INTO test
SELECT 1,5,45,0,34,2,1 UNION ALL
SELECT 2,6,46,1,35,3,2 UNION ALL
SELECT 3,7,47,2,36,4,3 UNION ALL
SELECT 4,8,48,3,37,5,4 UNION ALL
SELECT 5,9,49,4,38,6,5 UNION ALL
SELECT 6,10,50,5,39,7,6*/
SELECT * FROM test
DECLARE @TableName sysname
DECLARE @IdFieldName sysname
DECLARE @sql varchar(8000)
SELECT
@TableName = 'test',
@IdFieldName = 'ID'
-- create the schema of the resulting table
SET @sql = 'SELECT TOP 0 CONVERT(int,0) AS [ID], '
+'CAST(0 AS nvarchar(4000)) AS [Col],'
+' CONVERT(sql_variant,N'''') AS [Value] WHERE 1=0 '+CHAR(10)
SELECT @sql = @sql + 'UNION ALL SELECT ' + @IdFieldName + ', N'''
+ COLUMN_NAME + ''',CONVERT(sql_variant, '
+ '[' + COLUMN_NAME + ']) FROM [' + @TableName + '] WHERE [' + COLUMN_NAME + '] IS NOT NULL '
+ CHAR(10)
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMN_NAME @IdFieldName
ORDER BY COLUMN_NAME
EXEC(@sql + ' ORDER BY Id')
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 14, 2009 at 8:17 am
It is a scope issue. A temporary table (#TempTable) created inside of a stored procedure can be seen by other stored procedures invoked inside that stored procedure. This includes the use of dynamic sql like you provided to the OP.
Table variables, however, cannot be seen inside other stored procedures invoked inside of a stored procedure where a table variable is declared. This includes the use of dynamic sql, and is why in the dynamic sql you can not insert data into a table variable declared outside of the exec (@SQL).
May 14, 2009 at 8:36 am
Christopher Stobbs (5/14/2009)
Have you tried using the system tables in SQL?Here is a dynamic unpivot solution with not temp tables or table variables.
I must admit that I found this code a while and and have customized it abit, but I was not 100% the original author.
SET NOCOUNT ON
--SAMPLE TABLE
CREATE TABLE Test
(
ID INT,
pstt375 INT,
pstt455 INT,
pstt585 INT,
pstt643 INT,
phyl375 INT,
pwml306 INT
)
--SAMPLE DATA
INSERT INTO test
SELECT 1,5,45,0,34,2,1 UNION ALL
SELECT 2,6,46,1,35,3,2 UNION ALL
SELECT 3,7,47,2,36,4,3 UNION ALL
SELECT 4,8,48,3,37,5,4 UNION ALL
SELECT 5,9,49,4,38,6,5 UNION ALL
SELECT 6,10,50,5,39,7,6*/
SELECT * FROM test
DECLARE @TableName sysname
DECLARE @IdFieldName sysname
DECLARE @sql varchar(8000)
SELECT
@TableName = 'test',
@IdFieldName = 'ID'
-- create the schema of the resulting table
SET @sql = 'SELECT TOP 0 CONVERT(int,0) AS [ID], '
+'CAST(0 AS nvarchar(4000)) AS [Col],'
+' CONVERT(sql_variant,N'''') AS [Value] WHERE 1=0 '+CHAR(10)
SELECT @sql = @sql + 'UNION ALL SELECT ' + @IdFieldName + ', N'''
+ COLUMN_NAME + ''',CONVERT(sql_variant, '
+ '[' + COLUMN_NAME + ']) FROM [' + @TableName + '] WHERE [' + COLUMN_NAME + '] IS NOT NULL '
+ CHAR(10)
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMN_NAME @IdFieldName
ORDER BY COLUMN_NAME
EXEC(@sql + ' ORDER BY Id')
Thanks for that little gem. I'll have to spend some time understanding it, but it definitely looks much simpler than my solution.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 14, 2009 at 8:38 am
if have questions regarding how it works let me know...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 14, 2009 at 8:39 am
Lynn Pettis (5/14/2009)
It is a scope issue.
Ah ha. Not too familiar with what scope is, or how it affects things. Thanks for the tip.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply