Create multiple table - 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

SQL Server Development (2000)

Create multiple table

Author

Topic

M2
Starting Member

22 Posts

Posted - 2003-08-26 : 21:06:23

I want to write a script to create multiple table with different table name into a database without using "exec sp_executesql".
However, I have error as following:

Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near '@SQLString'.

it's not allow to assign @SQLString as Table Name.

Therefore, could anyone please help me on this.

====================================================================
Declare @num int
Declare @count varchar(10)
Declare @SQLString nvarchar(50)

Set @num = 1

While @num <> '5'
Begin
set @count = @num
Set @SQLString = 'Person' + @Count
CREATE TABLE @SQLString
(ID INT PRIMARY KEY,
Name varchar(50),
Address1 varchar(50))
Set @num = @num + 1
End
=====================================================================

Thanks ! M2

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-26 : 21:10:28

You can't

DavidM

"SQL-3 is an abomination.."

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-08-27 : 05:57:53

Try

EXEC (' CREATE TABLE ' + @SQLString + ' (ID........')

Dynamic SQL is my nature (I have a very bad nature

) . Does this help you?

Henri

~~~
Success is the ability to go from one failure to another with no loss of enthusiasm

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-27 : 06:22:36

Reconsider what you are trying to achieve and whether creating multiple dynamically named tables is the best way to go about it. If you need help post the business problem here.

-------
Moo. :)

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-27 : 07:43:22

quote:

EXEC (' CREATE TABLE ' + @SQLString + ' (ID........')

Dynamic SQL is my nature (I have a very bad nature ) . Does this help you?

pretty much the same as sp_executesql but with a smaller likelihood of (re)using execution plans.

Cheers,
Frank

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources