Adding a Column with Select Into

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 2000 Forums

Transact-SQL (2000)

Adding a Column with Select Into

Author

Topic

DOlivastro
Starting Member

41 Posts

Posted - 2005-08-04 : 22:51:54

When I do this:

Select *, ' ' as NewCol
into NewTable
from OldTable

... how does SQL Server decide the datatpe of NewCol? Is there some way I can control it? It seems like every number produces int, and every string produce varchar with a length equal to the size of the string. Is there some way of specifying ...

Select *, ' ' as NewCol char (2)

... or am I barking up the wrong tree?

Dom

Hunglech
Starting Member

16 Posts

Posted - 2005-08-05 : 00:01:33

Select *, CAST('' AS CHAR(2)) as NewCol
into NewTable
from OldTable

Kristen
Test

22859 Posts

Posted - 2005-08-05 : 00:44:08

That's how I would do it too, note (in case its not obvious!) that you can also do:

Select *, CAST(NULL AS CHAR(2)) as NewCol
into NewTable
from OldTable

Apart from casual one-off usage I would want to do a CREATE TABLE NewTable so that I could add a Primary Key, force appropriate columns to be NOT NULL, set constraints and all the "Good Stuff" that SQL can then do for me.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-05 : 01:45:17

It is better to create newtable with same structure of oldtable and then insert data from Oldtable

Insert into newtable(columnlist) Select columnlist from oldtable

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources