CODEWITHSUNDEEP
sql-serverstored-proceduresbulkinsertidentity-insert
Mindan
Mindan

Reputation: 1019

Bulk insert from txt. file with primary key and Timestamp values

I have a table called Address which has an AddressID as the primary key (auto-incremented value), it also has a tmsp column which is of type TimeStamp.

I created a stored procedure that deletes records from the table but before deleting, I save the records to a .txt file. And in the process of saving the records into the text file, I convert the timestamp to a Varbinary value. Meaning that tmsp column is saved as Varbinary in the text file.

Now I create a new stored procedure to insert the deleted records back into the database the code as below:

DECLARE @SQL NVARCHAR(MAX) = ''
BEGIN
    SET IDENTITY_INSERT dbo.Address ON
    SET @SQL = N'BULK INSERT Address
                 FROM ''' + @Address + '''
                 WITH (
                        FIRSTROW = 2,
                        FIELDTERMINATOR = '','',
                        ROWTERMINATOR = ''\n''
                      )'

    EXEC sp_executesql @SQL

    SET IDENTITY_INSERT dbo.Address OFF
 END

I get the following error:

Msg 273, Level 16, State 1, Procedure CleanUp_Address, Line 43 [Batch Start Line 2]
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

When I remove the IDENTITY_INSERT it inserts the data into the table as new value so the AddressID is overwritten, but that is not what I want. I need to keep the AddressID.

Any ideas?

Upvotes: 0

Views: 439

Answers (0)

Related Questions