Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

BULK INSERT (TIMESTAMP COLUMN)

Status
Not open for further replies.

JD1866DSC

Technical User
Sep 4, 2006
49
GB
Hi, I BCP'd some data out from a table into a ~ delimited text file.
I now want to Bulk Insert the data back into the original table structure.

BULK INSERT amastact FROM 'D:\Downloads\AssetTables\amastact.txt' WITH (FIELDTERMINATOR = '"~"')

However the first column in the table is a timestamp
and I get this error

OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.
Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.


Is this due to the first column being a timestamp column? and any ideas how I make it work?
 
you can't insert into a timestamp column, a timestamp column is basically just a rowversion and will change everytime something happens to the row

From BOL

Books On Line said:
A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value, thereby changing the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
Thanks SQLDenis, you've confirmed my suspicions.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top