how to insert temp table using variable

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

how to insert temp table using variable

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-26 : 14:26:51

Hello Everybody,

How to insert data in temp table. If i have fields names [From],[To],[Cc]. Tables field names are store on variables.

Declare @Schema VARCHAR(20)
Declare @TableName VARCHAR(25)
Declare @PkID VARCHAR(25)
Declare @From VARCHAR(25)
Declare @To VARCHAR(25)
Declare @Cc VARCHAR(25)

SET @Schema='DBO'
SET @TableName='tbl1'
SET @PkID='ID'
SET @From='From'
SET @To='To'
SET @Cc='Cc'

SELECT @PkID, [@From],[@To],[@Cc]
INTO #Temp FrOM @Schema+'.'+ @TableName WHERE [@From] IS NOT NULL

SELECT * From #Temp
DROP table #Temp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-26 : 14:44:48

You'd need to use dynamic SQL for that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-26 : 15:40:39

its gonna use different database...we have different Schema, different table name and different fields name. But the no of fields are fixed so.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-26 : 15:51:17

You still have to use dynamic SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-26 : 15:51:47

Even i trie this i can not get result:
I got the error

Declare @Schema VARCHAR(20)
Declare @TableName varchar(25)
Declare @ID VARCHAR(25)
Declare @From VARCHAR(25)

SET @Schema='dbo'
SET @TableName='tbl1'
SET @ID='ID'
SET @From='From'

select @id,[@from] from @Schema+'.'+@TableName

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 16:30:48

You are not using it right. Go thru this article,,
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-26 : 16:31:42

And....you'd still need to use dynamic SQL:

Declare @Schema VARCHAR(20)
Declare @TableName varchar(25)
Declare @ID VARCHAR(25)
Declare @From VARCHAR(25)

SET @Schema='dbo'
SET @TableName='tbl1'
SET @ID='ID'
SET @From='From'

exec('select ' + @id + ',[' + @from + '] from ' + @Schema+ '.' +@TableName)

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-26 : 16:37:13

thanks robvolk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources