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
Okay, so here’s the thing; try this:
[code lang=“SQL”] INSERT INTO DBO.[Audit] (AuditDate, AuditText) VALUES (GETDATE(), ‘The sky is blue’)
[![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.