Insert Into SQL Statement

Question

I'm fairly new to SQL, & I need help with an Insert statement.

I have two tables -- Current & Old. The Current table has columns that are new & therefore isn't in Old. One of my users deleted a record from Current, & I'd like to add the record to Current from Old.

I tried a simple Insert statement that "pulls" the data from Old into Current, but I get the following error -- Column name or number of supplied values does not match table definition.

Is there a way for me to create the record & ignore / bypass the new fields in Current?

Answer 1

General syntax will look like this

insert into table <columnlist here>
select <columnlist here>
from table

you can specify the columns you want, however this will only work if the columns are nullable or have defaults defined on them

Answer 2

You need to specify the columns

INSERT INTO table1 (column1, column2, ...)
SELECT column3, column4, ...
FROM table2

Answer 3

Either specify all the columns you'll be inserting to upfront or add placeholders to the select clause:

INSERT INTO Current (ID, Col1, Col2, Col3) SELECT * FROM Old WHERE ID=x

or

INSERT INTO Current SELECT *, 'col4value', 'col5value' FROM Old WHERE ID=x

Of course, you could also do both. That's probably that best way to go:

INSERT INTO Current (ID, Col1, Col2, Col3, Col4, Col5) 
    SELECT *, 'col4value', 'col5value' FROM Old WHERE ID=x

Answer 4

Yes, you can do it like this:

INSERT INTO NewTable (OldColumn1, OldColumn2, NewColumn, OldColumn3)
SELECT OldColumn1, OldCoumn2, NULL /* Or some default value */, OldColumn3
FROM OldTable