CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008t-sqltimestamp
user3505708
user3505708

Reputation: 64

How do I preserve timestamp values when altering a table in SQL Server (T-SQL)?

Or: how to copy timestamp data from one table to another?

Using SQL Server 2008 and having old design documents which requires a table to has the columns ordered in a certain way (with timestamp column last, something I guess comes from the time when Excel was used instead of an SQL database) I need to add a column in the middle of a table, keeping the timestamp data intact...

Do you know how to instruct SQL Server to do this?

Example T-SQL code:

-- In the beginning...
CREATE TABLE TestTableA
(
    [TestTableAId] [int] IDENTITY(1,1) NOT NULL,
    [TestTableAText] varchar(max) NOT NULL,
    [TestTableATimeStamp] [timestamp] NOT NULL
)

INSERT INTO TestTableA (TestTableAText) VALUES ('TEST')

-- Many years pass...

-- Now we need to add a column to this table, but preserve all data, including timestamp data. -- Additional requirement: We want SQL Server to keep the TimeStamp last of the column.

CREATE TABLE TestTableB
(
    [TestTableBId] [int] IDENTITY(1,1) NOT NULL,
    [TestTableBText] varchar(max) NOT NULL,
    [TestTableBInt] [int] NULL,
    [TestTableBTimeStamp] [timestamp] NOT NULL
)

-- How do we copy the timestamp data from TestTableATimestamp to `TestTableBTimestamp`?

SET IDENTITY_INSERT [TestTableB] ON

-- Next line will produce errormessage:
-- 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 INTO [TestTableB] (TestTableBId, TestTableBText, TestTableBTimeStamp) 
    SELECT TestTableAId, TestTableAText, TestTableATimestamp 
    FROM TestTableA

SET IDENTITY_INSERT [TestTableB] OFF

GO

Suggestions?

Upvotes: 1

Views: 1340

Answers (2)

Dzmitry Paliakou
Dzmitry Paliakou

Reputation: 1627

Drop table TestTableB first and then run a query:

SELECT 
    TestTableAId AS TestTableBId, 
    TestTableAText AS TestTableBText, 
    cast(null as int) as TestTableBInt,
    TestTableATimestamp AS TestTableBTimeStamp
INTO TestTableB
FROM TestTableA

Upvotes: 3

Ben
Ben

Reputation: 35663

First check requirements: It depends if you need to preserve the timestamps. You actually may not, since they are just ROWVERSION values and don't actually encode the time in any way. So check that.

Why you might not want to preserve them: The only purpose of TIMESTAMP or ROWVERSION is to determine if the row has changed since last being read. If you are adding a column, you may want this to be seen as a change, particularly if the default is non-null.

If you DO need to preserve the timestamps see Dmitry's answer.

Upvotes: 0

Related Questions