0

I am inserting only new records that do not exist in a live table from a "dump" table. My issue is there is an identity column that I don't want to insert into the live, I want the live tables identity column to take care of incrementing the value but I am getting an insert error "Insert Error: Column name or number of supplied values does not match table definition." Is there a way around this or is the only fix to remove the identity column all together?

Thanks, Sam

Sam Cromer
  • 687
  • 3
  • 12
  • 31

3 Answers3

1

You need to list of all the needed columns in your query, excluding the identity column.

One more reason why you should never use SELECT *.

INSERT liveTable
(col1, col2, col3)
SELECT col1, col2, col3
FROM dumpTable dt
WHERE NOT EXISTS
(
  SELECT 1 
  FROM liveTable lt
  WHERE lt.Id == dt.Id
)

Pro tip: You can also achieve the above by using an OUTER JOIN between the dump and live tables and using WHERE liveTable.col1 = NULL (you will probably need to qualify the column names selected with the dump table alias).

Oded
  • 489,969
  • 99
  • 883
  • 1,009
1

I figured out the issue.... my live table didn't have the ID field set as an identity, somehow when I created it that field wasn't set up correctly.

Sam Cromer
  • 687
  • 3
  • 12
  • 31
0

you can leave that column in your insert statment like this

insert into destination (col2, col3, col4)
select col2, col3 col4 from source

Don't do just 

insert into destination
select * from source
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Thanks, What is weird is I am not doing a select * I am not selecting the ID field either. I am doing something similar in another query without the error. – Sam Cromer Aug 08 '12 at 19:05
  • Hard to tell what your problem is. Could you provide an example on [SQLFiddle.com](http://sqlfiddle.com)? – juergen d Aug 08 '12 at 19:09