I am trying to copy data from one table to another and I get this error...I have a field named [deaID] which is in both tables. The table I am inserting to has this as an Identity field. But I don't see a way to change the IDENTITY_INSERT setting.

Cannot insert explicit value for identity column in table 'dealerships' when IDENTITY_INSERT is set to OFF.

Also will this effect how my identity field is used...meaning will my identity field start to reuse numbers or will it keep the numbers going in order? I want it to keep the [deaID] of the data coming in the same as the table the data is coming from and I know the numbers won't conflict...ie trying to insert a duplicate [deaID]

I just don't want it to effect any new data inserted into this table from another asp page.

Is this SQL Server? If so you just need to incorporate this statement in your stored procedure, or SQL, before you do the copy.
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } ON

Don't forget to turn it back off again after!

SQL Server will automatically update its generated IDs to ensure you don't get duplicates after the insert.

hth
Phil

Here is my SQL Statement on my asp page can you help me set it up correctly...Yes it is SQL Server by the way.


sql3 = "INSERT INTO dealerships
(deaID,latitude,longitude,mediaURL,ccemail,newused ,dealername,dealphone,dealaddress,dealcity,dealst, dealzipcode,dealfax,dealwebsite,dateentered,dateac tive,dactive,makerID,GraphicsLogo,GraphicsBanner,d ivisionID)
SELECT deaID,latitude,longitude,mediaURL,ccemail,newused, dealername,dealphone,dealaddress,dealcity,dealst,d ealzipcode,dealfax,dealwebsite,dateentered,dateact ive,dactive,makerID,GraphicsLogo,GraphicsBanner,di visionID

FROM inactivedealerships WHERE dactive=1"