question

Bugmesh avatar image
Bugmesh 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
import-datadtsmulti-server
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·

1 Answer

·
Tom Staab avatar image
Tom Staab answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.