Have you ever worked with a table that has default values accepted for each column? I hope not, but if so, this article is for you.
I was recently working with a table like this for some testing. There wasn’t a “great” way to load thousands of rows into this table without using a loop.
SQL Server 2008 introduced the following optional argument to assist with this, appropriately named:
It’s demo time.
I’m going to create a table that will work for only allowing DEFAULT VALUES inserts.
CREATE TABLE dbo.Defaults ( Id int NOT NULL IDENTITY CONSTRAINT Defaults_PK PRIMARY KEY CLUSTERED, String nvarchar (32) NOT NULL CONSTRAINT DF_Defaults_String DEFAULT '', CreationDate datetime NOT NULL CONSTRAINT DF_Defaults_CreationDate DEFAULT CURRENT_TIMESTAMP );
Our table has three columns, each of which does not require specified preset values.
I can use the following statement to add a single row.
INSERT INTO dbo.Defaults DEFAULT VALUES;
This will give us the following result (based on the time of execution).
What if I wanted to do this 100,000 times? I could easily just adjust that previous statement to follow with a GO 100000. Like so.
INSERT INTO dbo.Defaults DEFAULT VALUES; GO 100000
That finished on my machine in about 23 seconds. Using GO in that way requires an execution loop, which means it will go row by row. I think we can do better.
We could also use a MERGE statement to complete this task. It’s absolutely not the way the statement was intended to be used, but it works, nonetheless.
WITH CTE AS ( SELECT TOP (100000) NULL AS Id FROM sys.objects AS O CROSS JOIN sys.objects AS O2 CROSS JOIN sys.objects AS O3 ) MERGE INTO dbo.Defaults USING CTE AS Src ON dbo.Defaults.Id = Src.Id WHEN MATCHED THEN UPDATE SET Defaults.String = '' WHEN NOT MATCHED THEN INSERT DEFAULT VALUES;
In this example, we’re using some CROSS JOINs to the
sys.objects view to generate some rows. I normally wouldn’t recommend hitting these system views to perform row generation, but it works in a time crunch. We’re merging our results into our
dbo.Defaults table based on a match on the “Id” column. Since our “Id” column would never be NULL, we’ll always hit the “NOT MATCHED” portion of our MERGE statement.
In that example, I was able to populate 100,000 rows in less than one second.
I’m not really sure if this would ever serve a real “need” in a normal environment. I did, however, think it was interesting regardless. Hopefully if you’re doing some testing you’re able to use it too!
Thanks for reading!