Creating Multiple Tables with the reference of existing tables but with different names

Question

I have a requirement where I need to create multiple tables with the help of some existing tables but the new tables would have some different names.

For example:

Existing Table Names:

INT_Employees,
INT_HumanResource,
INT_Payroll,
INT_Contacts etc..

New Tables should have prefix STG so it should look like:

STG_Employees,
STG_HumanResource,
STG_Payroll,
STG_Contacts etc..

Note: There are around 127 tables and we need to create new 127 tables with prefix STG_.

Thanks in Advance.

Do you need to copy the data, or just the structure of the table? — pmbAustin
– pmbAustin, Commented Feb 2, 2017 at 17:46

Sean Lange · Accepted Answer · 2017-02-02 20:18:45Z

1

If you are just trying to copy the table structure you can use select into with a where predicate that will prevent any rows from being returned.

select * into STG_Employees from INT_Employees where 1 = 0

Just repeat this for each table.

--EDIT--

Here is an example of you can leverage dynamic sql to do this task instead of resorting to loops. We simply use sys.tables to help us generate our sql statement.

declare @sql nvarchar(max) = ''

select @sql = @sql + 'select * into ' + stuff(name, 1, 4, 'STG_') + ' from ' + name + ' where 1 = 0;'
from sys.tables 
where name like 'INT[_]%'

select @sql --once your comfortable the sql being generated is correct simply uncomment the next line
--exec sp_executesql @sql

Please note that if you want primary keys, indexes, foreign keys, computed columns etc you will need to generate those scripts another way. This will only generate the table structures.

edited Feb 2, 2017 at 20:18

answered Feb 2, 2017 at 17:51

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Kishanu Bhattacharya Over a year ago

Is there a way we can use a looping concept for all these tables ?

Sean Lange Over a year ago

Why use looping? Do you have a list of all the tables?

Kishanu Bhattacharya Over a year ago

Yeah, we can get it from sys.tables by using the Object_ID

Gareth Lyons Over a year ago

Be aware this method won't create any constraints, keys, indexes, identity property, or computed column definitions present in the source tables. Another option would be to script all the tables & run a find-replace on the script.

Sean Lange Over a year ago

@GarethLyons it will pick up identity but you are spot on that the rest of these potentially very important pieces will be skipped.

|

Collectives™ on Stack Overflow

Creating Multiple Tables with the reference of existing tables but with different names

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related