CODEWITHSUNDEEP
sqloracle-database
bill89654
bill89654

Reputation: 1

How to copy one table to another

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.

Upvotes: 0

Views: 407

Answers (6)

user1167155
user1167155

Reputation: 1

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))

Upvotes: 0

bblokland
bblokland

Reputation: 11

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

Upvotes: 1

TechDo
TechDo

Reputation: 18659

Please try

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

Upvotes: 1

Dr Schizo
Dr Schizo

Reputation: 4386

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

Upvotes: 1

John Woo
John Woo

Reputation: 263893

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.

Upvotes: 1

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

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.

Upvotes: 3

Related Questions