using an index variable to create tables

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 2005 Forums

Transact-SQL (2005)

using an index variable to create tables

Author

Topic

willben
Starting Member

1 Post

Posted - 2007-11-09 : 07:39:26

Hi,

I am hoping someone can assist me with the following problem I have.

I would like to create 10 tables in a while loop, the query looks like this;

DECLARE @I INT
SET @I = 1

WHILE( @I < 6 )
BEGIN

SELECT *
INTO #AT_NEW@I
FROM
(SELECT * FROM MELON_AUDIT WHERE PROC_ID = @I
UNION
SELECT * FROM LITCHI_AUDIT WHERE PROC_ID = @I
) AS A

SET @I = @I + 1
END

I thought that this might work but I keep getting an "incorrect syntax error near '@I'"

Is there anybody that can help?

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 07:48:15

You have to use dynamic sql.
http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources