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:
CREATE TABLE [dbo].[Audit](
[AuditDate] [datetime] NOT NULL,
[AuditText] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE\_ON [PRIMARY]
So, let’s create a statement that will inset data into this table:
INSERT INTO DBO.[Audit]
(AuditDate, AuditText)
VALUES
(GETDATE(), 'The sky is blue')
GO
Okay, so here’s the thing; try this:
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. However, if you want something more sensible, then you can use a lookup table:
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.