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

Question

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?

Answer 1

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.

Answer 2

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