How to copy one table to another

Question

I want to copy the contents of table1 into table2, but it's not a straight copy, as table2 contains more columns than table 1. The structure is similar to this:

Table1 { column2 column4 column6 }

Table2 { column1 column2 column3 column4 column5 column6 }

What I want to do is add every row from table1 to table2, and set default values for the missing columns. Any help would be appreciated.

Answer 1

You can just do an

INSERT INTO xxx
SELECT yyy

And in the select clause, put default values.

INSERT INTO Table2(column1, column2, column3, column4, column5, column6)
SELECT 'horse', column2, 2, column4, 'what ever I want', column6 
FROM Table1

So int Table2, all column1 will have 'horse' value. All column3 will have 2. Etc.

Answer 2

use INSERT..INTO SELECT statement

INSERT INTO table2 (column2, column4, column6)
SELECT column2, column4, column6
FROM table1

so in this case, columns: column1, column2, column3 will have null values. or whatever default you have set.

Answer 3

Not great at SQL but something like this:

INSERT INTO [Table1] (column2, column4, column6) SELECT (column1, column2, column3) FROM [Table2]

Hope this helps. Link that may be useful, http://www.blackwasp.co.uk/SQLSelectInsert.aspx

vote me I need points :P

Answer 4

Please try

INSERT INTO 
    Table2  (
    column1,
    column2,
    column3,
    column4,
    column5,
    column6,
        )
SELECT 
    'StaticValue1',
    column2,
    'StaticValue2'
    column4,
    'StaticValue3'
    column6,
FROM 
    Table1

Answer 5

To copy a full table into a new table:

SELECT * INTO table2 FROM table1;

http://www.w3schools.com/sql/sql_select_into.asp

To copy a table into an existing table:

INSERT INTO table2
SELECT * FROM table1;

http://www.w3schools.com/sql/sql_insert_into_select.asp

Answer 6

insert into TABLE2
select null colum1, column2,null colum3,column4,null colum5,column6
from((
Select  TABLE1.column2, TABLE1.column4, TABLE1.column6
from TABLE1, TABLE2
where TABLE1.primarykey=TABLE2.primarykey))