|
Home > Archive > MS SQL Server ODBC > January 2006 > help on insert a record on sql server with identity column as key
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
help on insert a record on sql server with identity column as key
|
|
| Hongyu Sun 2006-01-11, 8:23 pm |
| 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
| |
| RDBSAdmin 2006-01-20, 3:23 am |
|
"Hongyu Sun" wrote:
> 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
As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:
SET IDENTITY_INSERT myTableOnSQLServer ON
After the insert has been completed issue the following statement:
SET IDENTITY_INSERT myTableOnSQLServer OFF
Good luck
|
|
|
|
|