January 12, 2012 at 10:59 am
I'm trying to export data using Import/Export Wizard. I'm getting error when importing into timestamp column. What is the work around?
January 12, 2012 at 11:10 am
"TIMESTAMP" is a very misleading name...it's actual datatype is "ROWVERSION", which gives you an incrementing value to let you know which row was last edited...
if your source is a datetime, then you need to change the datatype of your column to datetime instead.
a ROWVERSION column's data value is auto generated and maintained by the server.
Lowell
January 12, 2012 at 11:10 am
Don't import the timestamp column. You can't insert into one, it's just a row version indicating which rows were latest modified, nothing more. It's not a time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2012 at 11:15 am
Thank you all .My source column has timestamp data type, should I create rowversion, date time data type to hold the same value as in source
January 12, 2012 at 11:19 am
svakka (1/12/2012)
Thank you all .My source column has timestamp data type, should I create rowversion, date time data type to hold the same value as in source
I don't think You quite understand. The source is probably not a "timestamp data tyoe" but a timestamp. In SQL Server, a timestamp IS a datetime data type. So, the column you are importing into for that must be datetime data type. Does that make sense?
Jared
CE - Microsoft
January 12, 2012 at 11:21 am
svakka (1/12/2012)
Thank you all .My source column has timestamp data type, should I create rowversion, date time data type to hold the same value as in source
Would you mind in posting the schema of your table?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 12, 2012 at 11:24 am
svakka (1/12/2012)
I'm trying to export data using Import/Export Wizard. I'm getting error when importing into timestamp column. What is the work around?
Just read this again... Are you exporting or importing data? From what format/platform to what format/platform?
Jared
CE - Microsoft
January 12, 2012 at 11:30 am
SQLKnowItAll (1/12/2012)
In SQL Server, a timestamp IS a datetime data type. So, the column you are importing into for that must be datetime data type. Does that make sense?
No, it's not. Timestamp is NOT a date or a time of any form. It's a binary number that gets incremented by SQL whenever the row is updated. It's a row version, not a date. It cannot go into a datetime column meaningfully.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2012 at 11:31 am
svakka (1/12/2012)
Thank you all .My source column has timestamp data type, should I create rowversion, date time data type to hold the same value as in source
You can create a timestamp or rowversion column (rowversion preferably, the alias 'timestamp' is deprecated iirc) in your destination table, just exclude the timestamp column from the import/export.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2012 at 11:35 am
svakka (1/12/2012)
Thank you all .My source column has timestamp data type, should I create rowversion, date time data type to hold the same value as in source
This script demonstrates the concepts:
CREATE TABLE dbo.FromTable
(
col1 integer NULL,
col2 integer NULL,
col3 timestamp NOT NULL
)
-- SQL Server provides the value for col3
INSERT dbo.FromTable
(col1, col2)
VALUES
(1, 100)
-- This also works, and again,
-- SQL Server provides the value for col3
INSERT dbo.FromTable
(col1, col2, col3)
VALUES
(2, 200, DEFAULT)
-- Col3 now contains a rowversion
SELECT *
FROM dbo.FromTable AS ft
-- Rowversion is the new name for timestamp
-- It is just a different name for the same thing
CREATE TABLE dbo.ToTable
(
col1 integer NULL,
col2 integer NULL,
col3 rowversion NOT NULL
)
-- Error:
--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.
INSERT dbo.ToTable
(col1, col2, col3)
SELECT
ft.col1,
ft.col2,
ft.col3
FROM dbo.FromTable AS ft
-- Exclude the column = success
-- SQL Server provides the values for col3
INSERT dbo.ToTable
(col1, col2)
SELECT
ft.col1,
ft.col2
FROM dbo.FromTable AS ft
Books Online reference: rowversion (Transact-SQL)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 12, 2012 at 11:36 am
GilaMonster (1/12/2012)
SQLKnowItAll (1/12/2012)
In SQL Server, a timestamp IS a datetime data type. So, the column you are importing into for that must be datetime data type. Does that make sense?No, it's not. Timestamp is NOT a date or a time of any form. It's a binary number that gets incremented by SQL whenever the row is updated. It's a row version, not a date. It cannot go into a datetime column meaningfully.
Sorry, I was trying to portray that a timestamp (not the datatype but an actual stamp of the time) is datetime in SQL Server. i.e. real-world timestamp <> SQL data type timestamp.
Jared
CE - Microsoft
January 12, 2012 at 12:37 pm
Thank you all. Probably an example will help !
Source Table --- SQL Server 2000
Table A
Col1 nvarchar(100)
Col2 datetime
Col3 timestamp
Source Table A Data
Col1 Col2 Col3
'Test1' 07/12/11 00000000000232AE
'Test2' 08/12/11 00000000000232AF
'Test3' 09/10/11 00000000000232AA
I'm required to export A structure and data from SS2000 to SS2008.
I created the same table structure in destination and received an error when importing the data(col3-timestamp). If timestamp is read only field , what should the destination datatype be changed to in order to retain col3 value.
January 12, 2012 at 12:41 pm
Typically you wouldn't retain it, it's not meaningful in the majority of cases (it's just an indication of a row being modified). Typically you'd put a timestamp/rowversion column in the destination table and exclude that column from the data flow.
What would you do with the timestamp column on the destination?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2012 at 12:43 pm
You could cast the column into another data type if you needed to preserve it, but I would question the use. If there is no need for the column for applications or anything else, why migrate that as well? If you need a versioning column in the 2008 table, then add that and let it populate itself while you move data.
Jared
CE - Microsoft
January 12, 2012 at 12:53 pm
Some of the batch jobs at the destination can use this column to compare what changed in the source and update the changed rows in destination table.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply