Sergey to the rescue yet again. Thanks!


>Alan,
>
>Try to add KEEPIDENTITY to the BULK INSERT.
>
>>Hi,
>>
>>For one of my apps, users can e-mail me an encrypted file which I in turn decrypt and do Bulk Inserts into tables in a SQL Server 2000 database (for troubleshooting purposes).
>>
>>With the following code, I am trying to do a bulk insert into a table named 'ReportControl'. The file that I'm doing the bulk insert with includes the identity column (and it's values). When I do the bulk insert with this code, the data gets inserted, but the identity column incriments - but I don't want that to happen - I want the value in the bulk insert text file to be inserted into the table.
>>
>>Here is a snippet of the code (for table ReportControl):
>>
>>

>>
>>Print 'Disabling all Constraints'
>>GO
>>sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"
>>GO
>>
>>--/////////////////////////////////////////////
>>
>>PRINT 'Deleting from table: [dbo].[ReportControl]'
>>DELETE FROM [dbo].[ReportControl]
>>GO
>>
>>SET IDENTITY_INSERT [ReportControl] ON
>>GO
>>
>>PRINT 'Bulk Insert into table: [dbo].[ReportControl]'
>>BULK INSERT ATMPOS.dbo.[ReportControl]
>>From 'F:\Import\ReportControl.lds'
>>WITH (FIELDTERMINATOR = '|',KEEPNULLS)
>>
>>GO
>>
>>SET IDENTITY_INSERT [ReportControl] OFF
>>GO
>>
>>Print ''
>>
>>--///////////////////////////////////////////////
>>
>>Print 'Enabling all Constraints'
>>GO
>>sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT ALL"
>>GO
>>
>>

>>
>>How can I do a bulk insert into a table with an identity column and have the data in the bulk insert text file for this field get inserted into the table - rather than having the identity field continuing to incriment?