[?]TSQL SELECT INTO with 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

SQL Server 2012 Forums

Transact-SQL (2012)

[?]TSQL SELECT INTO with variable

Author

Topic

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2014-05-01 : 09:58:56

Question: How do I use a variable in a SELECT INTO stmt?

I want to create a new table using SELECT INTO and append the date as it prints in @TSQL

print @TSQL
tbl_BACKUP_2014-05-01

TSQL Script


USE DBSQL
DECLARE @DIEM CHAR(10)
DECLARE @TSQL CHAR(100)
SET @DIEM = Convert(date,GETDATE())
SET @TSQL = 'tbl_BACKUP_'+@DIEM

PRINT @TSQL

SELECT * INTO @TSQL FROM tbl;

I want the date to be appended to the new table name. But when I run it this is what I get

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '@TSQL'.

Any suggestions on what I am missing? Thank you

epoh

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-05-01 : 10:12:41

You have to use dynamic SQL:


USE DBSQL;
GO
DECLARE @TSQL varchar(800) =
	'SELECT *' + CHAR(13) + CHAR(10)
	+ 'INTO tbl_BACKUP_' + CONVERT(char(8), CURRENT_TIMESTAMP, 112) + CHAR(13) + CHAR(10)
	+ 'FROM tbl;';
--print @TSQL;
EXEC (@TSQL);

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2014-05-01 : 10:23:32

Whoa! Thank you. A very simple solution!

epoh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources