CODEWITHSUNDEEP
sqlsql-server
Ankit Mahajan
Ankit Mahajan

Reputation: 576

Create table from another table.(Sql Server)

I want to create a new table same as some existing table plus some additional columns. In simple case we will do as:

 Select * INTO table2 from table1 where 1=0

But I also want to add some new columns. Can we do it in one step i.e. without using ALTER TABLE as next step?

Upvotes: 2

Views: 441

Answers (5)

Peter Tirrell
Peter Tirrell

Reputation: 3003

Can you explicitly list the new columns in the SELECT statement? Do they need to be populated or can you just have empty fields? Something like

Select *, cast(NULL as type) as NewColumn1, cast(NULL as type) as NewColumn2... INTO table2 from table1 where 1=0

Although you might have to list all of the individual columns from the original table rather than *, too.

Upvotes: 1

benjamin moskovits
benjamin moskovits

Reputation: 5458

Yes you can do this

Select *, 'abc' as city into dbo.newtable from dbo.oldtable where 1=0

Upvotes: 0

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

Yes, but in order to specify the data types, you should cast null as the desired type.

SELECT *
, CAST(NULL as NVARCHAR(100)) as NewColumn1
, CAST(NULL as INT) as NewColumn2

INTO B
FROM A
WHERE 1=0

Column constraints/defaults will need to be added separately via an UPDATE TABLE statement, but they may not be necessary for what you are trying to do.

The column types certainly will be.

Upvotes: 3

Praveen
Praveen

Reputation: 9335

If you want to add more columns add those in the select statement: eg:

select 
  t.*, 
  12 as ID, 
  'ABCD' as Name 
INTO table2 
from table1 as t 
where 1 = 0

After creation do a execute sp_columns 'table2' to see the definition of the table as you expected.
Here Name column will be of type varchar of length 4.

Upvotes: 0

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

You can do It in following:

SELECT *, 
       '' AS Col2, -- Here you can specify you new columns
       '' AS Col3  ---------------------------------------
INTO table2 
FROM table1

Upvotes: 0

Related Questions