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

General SQL Server Forums

New to SQL Server Programming

Dynamic sql

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2010-02-26 : 14:27:11

Hi,
I am creating a table variable using dynamic sql and i am also having another dynamic sql to select values from a table.

For example in the below example @dml has the table variable created and @dynamicselect has the sql statement:

DECLARE @dml nvarchar(max), @dynamicSelect nvarchar(max)

SELECT @dml = (SELECT 'DECLARE @@TempTbl TABLE (col1 varchar(255), col2 varchar(255))')
SELECT @dynamicSelect = 'SELECT id, name from contact'
EXEC sp_executesql @dynamicSelect

I want to insert the values that are the result of @dynamicSelect into the table variable @@tempTbl.

How can i do i do this. Can someone please guide me.

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-26 : 14:35:31

Why are you using dynamic sql for this?

Just this wud do....no?

DECLARE @TempTbl TABLE (col1 varchar(255), col2 varchar(255))
insert @TempTbl
SELECT id, name from contact

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 14:38:36

First of all Y u wanna use dynamic sql?


DECLARE @dml nvarchar(max), @dynamicSelect nvarchar(max)

DECLARE @TempTbl as TABLE (col1 varchar(255), col2 varchar(255))
SELECT @dynamicSelect = 'SELECT id,name from contact'
INSERT INTO @TempTbl EXEC sp_executesql @dynamicSelect 
SELECT * FROM @TempTbl

PBUH

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2010-02-26 : 15:26:37

Hi, I think i should have give the context in which i had this question. I am using dynamic sql because i am converting a table that has rows to cols using the PIVOT function dynamically.

DECLARE @colnames nvarchar(max), @dml varchar(max) ,@dynamicSelect nvarchar(max), @tblvar nvarchar(max)

--GET ROWS TO COLS FOR PIVOT
SELECT @colnames = STUFF((SELECT ', ' + QUOTENAME(desc1)
FROM (SELECT DISTINCT desc1 from rwtocol) x
FOR XML PATH ('')), 1, 1, '')

--GET ROWS TO COLS FOR TABLE VARIABLE
SELECT @tblvar = (SELECT QUOTENAME(desc1) + ' VARCHAR(255),'
FROM (SELECT DISTINCT desc1 from rwtocol) x
FOR XML PATH (''))

--remove the last character ','
SELECT @tblvar = LEFT(@tblvar, LEN(@@tblvar)-1)

SELECT @dml = ( SELECT 'DECLARE @@tempTbl TABLE ( maincol1, maincol2' + @tblvar +')')

SET @dynamicSelect = 'SELECT * FROM (SELECT ID, name, desc1, v FROM rwtocol) AS t
PIVOT (MAX(v) FOR desc1 IN(' + @colnames + N')) AS d'

Since i don't know howmany cols will be created, i am creating it dynamically.
Now i want to move the results of @dynamicselect to the @@tempTbl. Any suggestions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 23:26:22

if you're not certain on number of columns returned how can you insert them to already created table. then you must be creating table on the fly using SELECT ... INTO Table option

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources