SELECT INTO - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT INTO

Author

Topic

lkozhush
Starting Member

6 Posts

Posted - 2003-10-08 : 18:55:14

Hi,

I am trying to execute the following code

declare @tblname varchar (30)
select @tblname = 'WorkingData' + LTRIM(STR(YEAR(getdate()))) + LTRIM(STR(MONTH(getdate()))) + LTRIM(STR(DAY(getdate())))
select * into @tblname
from database.dbo.workingdata

and get the following error

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '@tblname

Any suggestions would be much appreciated.

lkozhush

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-08 : 18:58:16

You can not do that. But it can be done using dynamic sql, however dynamic sql is not recommended due to poor performance and poor security.

Do you really need to have your query be dynamic?

Tara

lkozhush
Starting Member

6 Posts

Posted - 2003-10-08 : 19:13:41

Dont think it really matters, the query will only be executed once daily. Whatever will make it work, that is, append a datestamp to the tablename.

lkozhush

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-08 : 19:17:09

Well here it is then:



DECLARE @SQL VARCHAR(8000)
DECLARE @tblname VARCHAR(30)

SELECT @tblname = 'WorkingData' + LTRIM(STR(YEAR(getdate()))) + LTRIM(STR(MONTH(getdate()))) + LTRIM(STR(DAY(getdate())))

SELECT @SQL = 'SELECT * INTO ' + @tblname + ' FROM database.dbo.workingdata'

EXEC (@SQL)

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-08 : 19:23:57

Be careful - a select into in dynamic sql will (supposedly) lock the system tables for the duration so very little else can happen on the server.

It's the way a select into used to work and crash servers.
Haven't tested it but you would be better off creating the table then doing an insert or selecting into a table with a fixed name then doing a rename.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lkozhush
Starting Member

6 Posts

Posted - 2003-10-08 : 20:01:36

Tara, thank you for your help, works really well.

lkozhush

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources