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"]
INSERT INTO DBO.[Audit]
(AuditDate, AuditText)
VALUES
(GETDATE(), 'The sky is blue')

GO

sql1

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




[![sql2](http://pmichaels.net/wp-content/uploads/2016/07/sql2.png)](http://pmichaels.net/wp-content/uploads/2016/07/sql2.png)

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
(TextLookup)
VALUES
('red'), ('blue'), ('green'), ('orange'), ('purple')

SELECT @lookupTableSize = COUNT(1) FROM @lookupTable

INSERT INTO dbo.[Audit]
(AuditDate, AuditText)
VALUES
(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


SELECT \*
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
Twitter

© Paul Michaels 2022