creating multiple tables?

Hello,
I need to create around 1500 similar tables.

Does anyone know how to create them all at once instead of one-by-one?


thanks

If these tables currently reside in another RDBMS then you MAY be able to generate sql for them. I am a pure MS SQL SERVER geek so I am unsure of, but regardless you would have to convert the scripts to use TSQL's create table statement.

If your tables are similiar enough and you must create these fresh you do some thing like the following (use dynamic sql):

declare @.SQL nvarchar(1000)

declare @.i int

select @.i = 0, @.SQL = ''

while @.i <= 1499

begin

set @.SQL = 'CREATE TABLE ' + [table name algorithm goes here] + [table definition goes here

exec sp_executesql @.SQL]

set @.i = @.i + 1

end

TSQL Create Table Syntax:

CREATE TABLE table_name

( { < column_definition > | < table_constraint > } [ ,...n ]

)

< column_definition > ::=

{ column_name data_type }

[ { DEFAULT constant_expression

| [ IDENTITY [ ( seed , increment ) ]

]

} ]

[ ROWGUIDCOL ]

[ < column_constraint > [ ...n ] ]

< column_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ NULL | NOT NULL ]

| [ PRIMARY KEY | UNIQUE ]

| REFERENCES ref_table [ ( ref_column ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

}

< table_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ { PRIMARY KEY | UNIQUE }

{ ( column [ ,...n ] ) }

]

| FOREIGN KEY

( column [ ,...n ] )

REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

}|||thank you very much

I will post again in this thread if I have any troubles|||

Hi I'm having some trouble, I made this query:

declare @.SQL nvarchar(1000)

declare @.i int

SELECT @.i = 0, @.SQL =

WHILE @.i <= 32228

begin

set @.SQL = 'CREATE TABLE' + tbl_i_quotes + (

QuoteDate nchar(20),

QuoteTime nchar(20),

BidPrice float,

AskPrice float,

BidSize float,

AskSize float)

exec sp_executesql @.SQL

set @.i = @.i + 1

end

and i got this as an error message:

Msg 156, Level 15, State 1, Line 7

Incorrect syntax near the keyword 'WHILE'.

Msg 102, Level 15, State 1, Line 12

Incorrect syntax near 'nchar'.

thanks again

|||

this works:

declare @.SQL nvarchar(1000)

declare @.i int

SELECT @.i = 0, @.SQL = ''

WHILE @.i <= 32228

BEGIN

set @.SQL = 'CREATE TABLE' + '[tbl_' + CONVERT(nvarchar(5),@.i) + '_quotes] (

QuoteDate nchar(20),

QuoteTime nchar(20),

BidPrice float,

AskPrice float,

BidSize float,

AskSize float)'

exec sp_executesql @.SQL

set @.i = @.i + 1

end

|||it works! thank you so much!


I'd hate to bother you more, but you seem so informative...

do you know much about bulk inserting a folder full of flat files into select tables?
maybe using integration services? i've tried BCP and have had pretty negative results.


thanks again, yer a life saver.|||your welcome :) glad it helped. I used DTS in sql2000 ALOT, but have had almost 0 exp. with SSIS in 2005 as of yet. However, SSIS (like DTS) has always been a great tool for importing and exporting data and I would look at leveraging it for loading a flat file. BCP/BULK INSERT is good to.