2

Is there a method or syntax that would allow a table that just has a rowversion/timestamp column to be filled with data?

CREATE TABLE [dbo].[TimeStamp_Test]([row_version] [timestamp] NOT NULL)
4

I'm not sure why you would need this but yes it is possible.

Insert into [dbo].[TimeStamp_Test] default values;

Or

Insert into [dbo].[TimeStamp_Test] values (default);

To do multiple rows you can use merge.

4
  • Thank you. I had tried inserting default, but not "default values".
    – John
    Aug 11 '16 at 20:08
  • @john Insert into [dbo].[TimeStamp_Test] values (default) should also work. Online demo of both rextester.com/QQGX73516 Aug 11 '16 at 20:10
  • I just have to say, yikes. I don't know what I did wrong. I've lost the original insert query, but yes, that too works just fine. Thanks a bunch.
    – John
    Aug 11 '16 at 20:15
  • 1
    "I'm not sure why you would need this but yes it is possible." - Just fooling around with the data type, and created a simple setup to look at a working timestamp, but then had trouble filling it... Thanks again.
    – John
    Aug 11 '16 at 20:21
1

You can also poppulate default values from a resultset, which is quicker for generating a lot of values. For rowversion (aka timestamp) you can select a null into it. I often use this technique for springing up test rigs quickly.

USE tempdb
GO

SET NOCOUNT ON

-- Create a large table
IF OBJECT_ID('dbo.largeTable') IS NOT NULL DROP TABLE dbo.largeTable
CREATE TABLE dbo.largeTable ( rowId INT IDENTITY PRIMARY KEY, someData UNIQUEIDENTIFIER DEFAULT NEWID(), dateAdded DATETIME DEFAULT GETDATE(), addedBy VARCHAR(30) DEFAULT SUSER_NAME(), ts ROWVERSION )
GO

-- Populate table: this took just under a min on my laptop
BEGIN TRAN
GO
INSERT INTO dbo.largeTable DEFAULT VALUES
GO 1000000
COMMIT TRAN


-- This took < 2 seconds
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.largeTable ( ts )
SELECT NULL
FROM cte
2
  • BTW: I just happened to notice that Ability to insert null values into timestamp columns is deprecated Aug 21 '16 at 19:21
  • Good spot @MartinSmith. Using it fires a deprecation announcement: "The ability to INSERT NULL values into TIMESTAMP columns will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. Use DEFAULT instead."
    – wBob
    Aug 21 '16 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.