sql server programming: question on inserting a record on sql server with identity column as key -- sql server programming

question on inserting a record on sql server with identity column as key

Hongyu Sun
1/12/2006 11:55:51 PM

sql server programming:

Hi, All:

Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).

A table on SQL server has an identity column as the key.

We have trouble on adding records to this table using the following SQL.

strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL

The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.

When we try to run the query, it gives errors indicating the key is violated
or missing.

Should I figure out the autonumber for it first and then add to the SQL
server table?

Many thanks,

HS

Re: question on inserting a record on sql server with identity column as key

Jonathan Chong
1/13/2006 3:31:22 PM

There are two options depending your answer to this question:
Do you want you myTableOnSQLServer table to have the same value of ID from
myTableonAccessLocal.
1. If NO. Then don't specify the ID column in your INSERT INTO statement.
2. If YES. Use SET IDENTITY_INSERT command to allows explicit values to be
inserted into the identity column of a table. Like this:

SET IDENTITY_INSERT myTableOnSQLServer ON
insert into .....
SET IDENTITY_INSERT myTableOnSQLServer OFF

Hope it helps.

[quoted text, click to view]