0

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?

Improve this question
2
  • 2
    Please post the statement that gives the error Commented Jul 8, 2009 at 15:42
  • Yes, the SQL will help. Sounds like you need to call out your select list from Current explicitly within the statement. Commented Jul 8, 2009 at 15:44

4 Answers 4

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

this is the best example in here so far. it is also important to note that the column lists must match.
Sounds like he's gotten this far already - the problem is that the two tables don't have exactly the same columns.
1

You need to specify the columns

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

Comments

1

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
Improve this answer

Comments

0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.