Create multiple SQL tables with script

scfits
scfits used Ask the Experts™
on
I have a text file with one column of table names with a carriage return as a delimiter:

Table1
Table2
Table3

A simple CREATE TABLE script would have a variable @TableName and the script would look something like this.  I will not be creating tables in the databases at the moment.

DECLARE @TableName varchar(20)
@TableName = c:\TextFile.txt (something like this, I don't know how to work out this logic)

CREATE DATABASE [@TableName] ON  PRIMARY
( NAME = N'@TableName', FILENAME = N'E:\SQL_DBs\@TableName.mdf' )
 LOG ON
( NAME = N'@TableName_log', FILENAME = N'F:\SQL_Logs\@TableName_log.ldf' )
GO

Any help would be appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
this is a case where a cursor will work


import the file into a table called tableNames

then use this code


DECLARE @Table varchar(255)


DECLARE Get_Table_names
CURSOR FOR SELECT tablename FROM ListOFtables

OPEN Get_Table_names

FETCH NEXT FROM Get_Table_names INTO @Table

WHILE @@FETCH_STATUS = 0
BEGIN


YOUR LOGIC HERE


--CREATE DATABASE [@TableName] ON  PRIMARY
--( NAME = N'@TableName', FILENAME = N'E:\SQL_DBs\@TableName.mdf' )
 --LOG ON
--( NAME = N'@TableName_log', FILENAME = N'F:\SQL_Logs\@TableName_log.ldf' )

FETCH NEXT FROM Get_Table_names INTO @Table
END

CLOSE Get_Table_names
DEALLOCATE Get_Table_names

Author

Commented:
I got it somewhat working - I don't know how to get the MDF & LDF file names created with the @TBLName variable.  What is the syntax to deal with variables within text strings?

DECLARE @Table varchar(255)

DECLARE Get_Table_Names CURSOR FOR
SELECT Name FROM dbo.dblist

OPEN Get_Table_Names

FETCH NEXT FROM Get_Table_Names INTO @Table

WHILE @@FETCH_STATUS = 0
BEGIN


DECLARE @TBLName varchar(255)
Set @TBLName = @Table

CREATE DATABASE[@Table] ON PRIMARY
( NAME = N'@TblName', FILENAME = N'E:\SQL_DBs\@TblName.mdf' )
LOG ON
( NAME = N'@TblName + "_log.ldf"', FILENAME = N'F:\SQL_Logs\@TblName + "_log.ldf')

FETCH NEXT FROM Get_Table_Names INTO @Table
END



The error is:
CREATE FILE encountered operating system error 123(The filename, directory name, or volume label syntax is incorrect.) while attempting to open or create the physical file 'F:\SQL_Logs\@TblName + "_log.ldf'.
Commented:
make it an exec(sql)

declare @sql varchar(max)

so you have
set @sql = ' create database '+ @table + ' On primary ' +  .....

then exec(@sql)

Author

Commented:
Ok.. I shortened the query even more as the file names are created in the default locations... BUT.. it only created the last database at the bottom of the list, so if get_table_names had 75 rows and the first name in the list was ALPHABASE and the last was ZETABASE, then the ZETABASE database was created.  Thanks for your help, I'm learning a ton here.

DECLARE @Table varchar(255)

DECLARE Get_Table_Names CURSOR FOR
SELECT Name FROM dbo.dblist

OPEN Get_Table_Names

FETCH NEXT FROM Get_Table_Names INTO @Table

WHILE @@FETCH_STATUS = 0
BEGIN


DECLARE @TBLName varchar(255)
Set @TBLName = @Table

DECLARE @SQL varchar(max) =

'CREATE DATABASE ' + @TblName

FETCH NEXT FROM Get_Table_Names INTO @Table
END

EXEC (@SQL)

CLOSE Get_Table_names
DEALLOCATE Get_Table_names

Author

Commented:
I figured it out - I put the EXEC @(SQL) before the END statement - awarding points!

Author

Commented:
I could have used specifics but he/she pointed me in the right direction

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial