asked
Script to create 150 different table names with the same columns
Howdy from cold..then hot...then cold Oklahoma. I have been tasked with taking a COBOL copy book and create table structures to support the import of data from our mainframe to be used at an offsite function. I have my table definitions complete, but a couple of the copy books include "Occurs" statements in them (to the tune of 150 times). I figured the best way to handle the import was to break these out into individual tables. Below is an example of one of the tables: CREATE TABLE ENDORESEMENT_UNIT_TABLE (ENDORSMENT_UNIT CHAR(3), ENDORSMENT_NUMBER VARCHAR(9), ENDORSMENT_LIMIT VARCHAR(7), END_PREM DECIMAL(7,2)) Now I just need 150 of these tables cloned but with a different table name for each. I was hoping someone could help me with a process that could do this in an automated way as opposed to a lot of cutting and pasting. Alternatively, if someone has a better way of doing this entirely, then that would be greatly appreciated. We are currently running under SQL Server 2000 on a Virtual Machine running on Windows Server 2003. Thank you again for your anticipated assistance
Assuming you have some pattern to what the tables need to be called, you could use a script to create the CREATE TABLE scripts. At it's simplest, create a temp table with the names in one column, then use a select statement to put together a CREATE TABLE statement with the column name instead of the table name, and run. You should then get all your create table scripts.
First, as Dave already mentioned in his comment, you could script the table create statements. Here's an example of that approach: WITH BaseNumbers AS ( SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)) Numbers(Number) ) , Numbers (Number) AS ( SELECT ROW_NUMBER() OVER (ORDER BY a.Number) FROM BaseNumbers a CROSS JOIN BaseNumbers b ) SELECT 'CREATE TABLE ENDORESEMENT_UNIT_TABLE_' + CONVERT(varchar(3), n.Number) + ' (ENDORSMENT_UNIT CHAR(3), ENDORSMENT_NUMBER VARCHAR(9), ENDORSMENT_LIMIT VARCHAR(7), END_PREM DECIMAL(7,2)) ;' FROM Numbers n WHERE n.Number
16 People are following this question.
