March 24, 2010 at 6:42 pm
Hello ... I'm using the statement
INSERT INTO INTERSTATE.DBO.ARINVOICE SELECT * FROM INTERSTATERECURRING.DBO.ARINVOICE
to copy the data from ARINVOICE in db INTERSTATE to ARINVOICE in db INTERSTATERECURRING
The message I get is Msg 273, Level 16, State 1, Line 1
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
which I'm sure is a good explanation but it's beyond my limited capabilities. Is there an easy way to alter this query to "insert a DEFAULT" ?
thx in advance ... DMiller
March 24, 2010 at 7:14 pm
Yes, there is an easy way to do this. Rewrite your insert statement to include all of the columns being inserted - without the timestamp column. For example:
Current:
INSERT INTO dbo.Table1 SELECT * FROM db1.dbo.Table2; -- fails
Change it to:
INSERT INTO dbo.Table1 (col1, col2, col3, col4, ...) -- without the timestamp column
SELECT col1, col2, col3, col4, ... FROM db1.dbo.Table2; -- leave off the timestamp column
That should do it...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 24, 2010 at 7:32 pm
Thank you Jeffrey I appreciate the reply ... there are three tables though, and probably about 100 or so columns between them, so I didn't want to go that route. I have been using the Import/Export "wizard" which transfers the data just fine (after I rename the destination table); trouble is, then I have to go and reset the key/Identity information in the destination tables because the table structure doesn't copy.
March 24, 2010 at 7:43 pm
I don't know what is so hard about it - right click on the table in the other database and script it to a select statement. Find the timestamp column and remove it, add the INSERT INTO dbo.Table1 () and then copy/paste the column list from the select statement inside the parens and done.
Now, all you need to do is format it the way you want.
Or, you can do the following:
1) Right-click the table in db2 - script table as insert to new query window
2) Right-click the table in db1 - script table as select to clipboard
3) Paste the select over the VALUES statements included in step 1
4) Remove timestamp columns
Done...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply