IDENTITY_INSERT ON Error Message

[Miked811]

Hi,

I have a HistoryTable that has a Primary Key Field and is set with Identity=1, Identity Seed=1 and Identity Increment=1.

I also have an identical WorkingTable. I need to move the records from this table to the HistoryTable. When I execute this SQL Insert:

INSERT INTO HistoryTable SELECT WorkingTable.* FROM WorkingTable WHERE WorkingTable.Campaign=20

I get this error message:

"An explicit value for the identity column in table 'CompletedRecords' can only be specified when a column list is used and IDENTITY_INSERT is ON."

Is there a way to resolve this problem other than exlicitly identifying each field? The table have 100 fields (designed by the customer). I am going to automate it using VB and creating a RecordSet with thousands of records to move to the HistoryTable from the WorkingTable for a day's transaction seem not feasible.

Thanks

Mike

 

[bborissov]

If this is History Table why you need IDENTITY field at all?
Isn't that field values comes from your main table?
I don't know the exact design of your DB, but for Histyory table I didn't use identity fields, becuase their values comes from table I want to save. If identity field of main table have duplicate values there must be some error. So my suggesion is: change History table field to be just int (not identity) and insert the values with the query you use. If there are duplicate values of PK you must receive errors and you can manage them.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Miked811]

I asked the customer the same question, but insist that the design stays the same since it has been like it since the table was created. And, you are correct, when I tried to change the Identity=0, error came up with regards to primary record duplicates. The way we move the entire records is linking it to access through ODBC and by just doing it manually, copy and paste, which is tedious. Why is it working in Access and not getting the same error?

Thanks

 

[bborissov]

Because SQL Server is more ANSI compatible then Access.
How you do that insert?
In SP or from FrontEnd?
If you di that from FrontEnd you could build a whole SQL INSERT with Field List and send it, if you do that in SP you also can build whole query and use Dynamic SQ to do that insert.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP