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 |
|
Jarid.Lawson
Starting Member
7 Posts |
 Posted - 2009-12-02 : 11:38:42
|
I am loading a flat file into a table for various updates (setting name fields into Last, First M format from Last First M format, etc.). Most of the time I do not have a distinct data field, so I have thought that Identiy_Insert should work for this. I have never used Identity_Insert before, so I am not sure where my logic is failing.I load the data into DBO.RAW_RECORDS via Tasks>Import Data. From there I use the following script:Create Table dbo.ALL_RECORDS ( records_id Numeric(18,0) Identity(1,1), monarch_accountno Varchar(250), monarch_originalname Varchar(250)... )Set Identity_Insert DBO.ALL_RECORDS OnInsert Into DBO.ALL_RECORDS ( monarch_accountno, monarch_originalname... )Select monarch_accountno, monarch_originalname...From DBO.RAW_RECORDSSet Identity_Insert DBO.ALL_RECORDS Off When I run this script I receive the following error: quote:
Explicit value must be specified for identity column in table 'ALL_RECORDS' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
Any thoughts?Jarid Lawson"A little knowledge is dangerous. So is a lot." - Einstein |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Jarid.Lawson
Starting Member
7 Posts |
Posted - 2009-12-02 : 12:06:33
|
| I must be a little thick headed, because I am not getting part of your answer. You said I can either set an expilicit value (i.e. 1) during my insert statement, or I can leave identity insert off. If I used Insert Into DBO.ALL_RECORDS(records_id,column2...) Select(1,coulmn2...) I thought SQL will fail since it is looking at column names. Am I getting this wrong? Also, if I leave Identity_Insert off then are you saying since there is records_id Int Identity(1,1) that it will auto account for this without me manually telling it to auto sequence?Sorry if I am over asking the same question, but I am still a little knew with some of these SQL features.Jarid Lawson"A little knowledge is dangerous. So is a lot." - Einstein |
 |
|
|
Jarid.Lawson
Starting Member
7 Posts |
Posted - 2009-12-02 : 12:13:56
|
| Ok, I tried Insert Into DBO.ALL_RECORDS(records_id,column2...) Select(1,coulmn2...) with Identity_Insert On, and now all of the records are set to 1 in that field. I'm guessing that was not the way to do it correctly. I also tried Insert Into DBO.ALL_RECORDS(records_id,column2...) Select(records_id,coulmn2...) with Identity Insert off, but it failed saying there is no column called records_id on Raw_Records.Jarid Lawson"A little knowledge is dangerous. So is a lot." - Einstein |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-12-02 : 13:20:26
|
If you are going to supply the records_id field from your flat file, turn identity insert ON. This will insert the value for the record_id from your flat file. (i.e. Insert into [Table] (record_id,column2, column3, ect)) select record_id,column2,column3,etc.) If you want brand new, server managed IDs generated for your records, turn Identity insert OFF, and change your script to EXCLUDE the record_id column (i.e. Insert into [Table] (column2, column3, ect)) select column2,column3,etc.) http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Jarid.Lawson
Starting Member
7 Posts |
Posted - 2009-12-02 : 13:27:37
|
| That worked perfectly!!! Thank you!!!Jarid Lawson"A little knowledge is dangerous. So is a lot." - Einstein |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
First Attempt With Identity_Insert