SQL Server quick insert test data

July 10, 2016

I recently came across a little known feature of SSMS that made me think that, for small amounts of data, it may be possible to create a procedure to insert test data into a table. Let’s start with the table:

[code lang=“SQL”] CREATE TABLE [dbo].[Audit]( [AuditDate] [datetime] NOT NULL, [AuditText] varchar NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

So, let's create a statement that will inset data into this table:

[code lang="SQL"]
(AuditDate, AuditText)
(GETDATE(), 'The sky is blue')



Okay, so here’s the thing; try this:

[code lang=“SQL”] INSERT INTO DBO.[Audit] (AuditDate, AuditText) VALUES (GETDATE(), ‘The sky is blue’)

GO 10


So, that's good, but what if you want some actual kosher data?

Randomising a date is relatively easy, using a combination of the RAND() function and the DATEADD() functions, you can do this in a single line.  However, randomising text is more complex.  If you just want random strings of letters, then you could try something like [this](http://stackoverflow.com/questions/1324063/generating-random-strings-with-t-sql).  However, if you want something more sensible, then you can use a lookup table:

[code lang="SQL"]
DECLARE @lookupTable TABLE (TextLookup VARCHAR(255))
DECLARE @lookupTableSize INT

INSERT INTO @lookupTable
('red'), ('blue'), ('green'), ('orange'), ('purple')

SELECT @lookupTableSize = COUNT(1) FROM @lookupTable

INSERT INTO dbo.[Audit]
(AuditDate, AuditText)
(DATEADD(day, -ABS(CHECKSUM(NewId())) % 30000, GETDATE()),
(SELECT TOP 1 TextLookup FROM (
    SELECT TextLookup, ROW\_NUMBER() OVER (ORDER BY TextLookup) AS RowNum
    FROM @lookupTable
) AS DerivedAudit
WHERE DerivedAudit.RowNum >= (ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % @lookupTableSize) + 1))

GO 100

FROM dbo.Audit

It’s worth noting that GO in this case executes the entire script, not just the bit you want; however, I’m not proposing you use this for any serious volume - just for add a quick few records.

Profile picture

A blog about one man's journey through code… and some pictures of the Peak District

© Paul Michaels 2022