Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-12-23 : 18:13:34
|
| Yong writes "hello there, this is Yong from malaysia. i have difficulty in transferring data using Query Analyzer from a table from another Server to the dxesignated one. well, i have created a linked server in MSSQL, and there are tables that exactly same settings (with identity insert). so i was trying very hard to transfer from 1 table from another table in the Linked server, but it failed by giving "An explicit value for the identity column in table 'xxxtable' can only be specified when a column list is used and IDENTITY_INSERT is ON." error message. i was thinking to drop the identity in the designated table, and then transfer it using Inser into statements. well, i just can't figure out how to drop that identity settings.. please advice. best regards,yong" |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2002-12-23 : 20:21:08
|
| Identity insert allows you to "suspend" the normal behavior of an identity column. All you should need to do is put this at the top of your sql statement:set identity insert onAnd then end with this:set identity insert offthat should allow you to insert the values from your other table into the new one without tripping over the identity column. However, MAKE SURE that your tables are identitcal.Mike"oh, that monkey is going to pay" |
 |
|
|
|
|
|
Dropping Indentity Insert