CODEWITHSUNDEEP
sql
SaberTooth
SaberTooth

Reputation: 167

Copying Data from one table into another and simultaneously add another column

I need to copy 3 columns(with values) from one table to another table(this table will be created as new) and also need to add an extra column in the new formed table. Is it possible only in one sql query?

I thought this might be an answer - 

CREATE TABLE NEW_TBL(Col1,Col2,Col3,New_Col) AS SELECT Col1,Col2,Col3       FROM OLD_TBL

But the confusion is how to assign the data type of that new column? Is it possible with only one single SQL statement?

Upvotes: 7

Views: 6653

Answers (3)

valex
valex

Reputation: 24144

MS SQL

You didn't mention your database.

Here is an example for MS SQL

SELECT Col1,Col2,Col3,'text field' as NEW_COL INTO NEW_TBL FROM OLD_TBL;

This query will create new table NEW_TBL and add all data from OLD_TBL with additional text field NEW_COL. The type of the new field depends on constant type for example if you need INT type you can use 0 instead of 'text field' constant.

SQLFiddle demo

Upvotes: 2

neoneye
neoneye

Reputation: 52201

PostgreSQL

Today I needed to extend a product table with a new NOT NULL uuid column with foreign key contraint. So I had to specify an uuid of an existing image, for the new column. I did like this:

INSERT INTO product 
(  
    SELECT 
        product_id,
        product_name,
        '32b295g3-1337-4792-b1c4-9e3412b07982' as product_image_id 
    FROM oldproduct
);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Here is an example using create table as syntax:

CREATE TABLE NEW_TBL AS
    SELECT Col1, Col2, Col3, 'Newcol' as Col4
    FROM OLD_TBL;

To assign a data type, use cast() or convert() to get the type you want:

CREATE TABLE NEW_TBL AS
    SELECT Col1, Col2, Col3, cast('Newcol' as varchar(255) as Col4,
           cast(123 as decimal(18, 2)) as col4
    FROM OLD_TBL;

By the way, you can also add the column directly to the old table:

alter table old_tbl add col4 varchar(255);

You can then update the value there, if you wish.

Upvotes: 3

Related Questions