Fastest way to create tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Fastest way to create tables

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-10-22 : 05:12:38

Hi,

I need to create a list of new tables from the existing tables, with some additional columns added to the new tables. Anyway to simplify this?

existing tables: tbl_A, tbl_B, tbl_C, tbl_D, etc..
next tables: tbl_A_temp, tbl_B_temp, tbl_C_temp, tbl_D_temp, etc ...

additional columns : Logon_User varchar(50), Updated_Date_Tm datetime

Thanks.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-10-22 : 05:17:49

Right click on the tables that you need and under Script Table As select create table.

From there just change the table name and add the extra column.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-22 : 05:18:39

If you dont worry about indices

1 Run this

select 'select *,cast('' as varchar(50)) as Logon_User , cast(0 as datetime) as Updated_Date_Tm into '+table_name+'_temp from '+table_name
from information_schema.tables

2 Copy the result to QA

3 Run them again

Madhivanan

Failing to plan is Planning to fail

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-10-22 : 05:44:46

Thanks Madhivanan, I get it.

quote:
Originally posted by madhivanan

If you dont worry about indices

1 Run this

select 'select *,cast('' as varchar(50)) as Logon_User , cast(0 as datetime) as Updated_Date_Tm into '+table_name+'_temp from '+table_name
from information_schema.tables

2 Copy the result to QA

3 Run them again

Madhivanan

Failing to plan is Planning to fail

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-10-22 : 05:51:05

I got a problem when tried to put the addtional columns first. Any idea?

select 'select cast('' as varchar(50)) as Logon_User , cast(0 as datetime) as Updated_Date_Tm, top 0 * into '+table_name+'_temp from '+table_name
from information_schema.tables

quote:
Originally posted by madhivanan

If you dont worry about indices

1 Run this

select 'select *,cast('' as varchar(50)) as Logon_User , cast(0 as datetime) as Updated_Date_Tm into '+table_name+'_temp from '+table_name
from information_schema.tables

2 Copy the result to QA

3 Run them again

Madhivanan

Failing to plan is Planning to fail

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-10-22 : 06:03:41

Hi,

It does not work in dinamic sql....Please advise.

Thanks.

quote:
Originally posted by juicyapple

I got a problem when tried to put the addtional columns first. Any idea?

select 'select cast('' as varchar(50)) as Logon_User , cast(0 as datetime) as Updated_Date_Tm, top 0 * into '+table_name+'_temp from '+table_name
from information_schema.tables

quote:
Originally posted by madhivanan

If you dont worry about indices

1 Run this

select 'select *,cast('' as varchar(50)) as Logon_User , cast(0 as datetime) as Updated_Date_Tm into '+table_name+'_temp from '+table_name
from information_schema.tables

2 Copy the result to QA

3 Run them again

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-22 : 06:04:46

select 'select top 0 cast('''' as varchar(50)) as Logon_User , cast(0 as datetime) as Updated_Date_Tm, * into '+table_name+'_temp from '+table_name
from information_schema.tables

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-22 : 06:05:43

quote:
Originally posted by juicyapple

Hi,

It does not work in dinamic sql....Please advise.

Thanks.

quote:
Originally posted by juicyapple

I got a problem when tried to put the addtional columns first. Any idea?

select 'select cast('' as varchar(50)) as Logon_User , cast(0 as datetime) as Updated_Date_Tm, top 0 * into '+table_name+'_temp from '+table_name
from information_schema.tables

quote:
Originally posted by madhivanan

If you dont worry about indices

1 Run this

select 'select *,cast('' as varchar(50)) as Logon_User , cast(0 as datetime) as Updated_Date_Tm into '+table_name+'_temp from '+table_name
from information_schema.tables

2 Copy the result to QA

3 Run them again

Madhivanan

Failing to plan is Planning to fail

Post the dynamic code you used

Madhivanan

Failing to plan is Planning to fail

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-10-22 : 06:39:17

No offence but that approach seems like trying to over complicate something that SSMS can create for you in the first place in less click and typing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources