Note: This is a post from the past being updated. The original post is here: 2008: Initializing Table Data with Row Constructors.. The code used here is the code I reference in this post on Redgate’s Product learning site: SQL Prompt AI Versus Smart Quotes, since the original post is rife with smart quotes!
Row Creators were introduced in SQL Server 2008, and allow you to create multiple rows in a single INSERT statement by using the VALUES clause. In this blog, I will demonstrate a few ways that we have created data in tables, and then show how you can do this with row constructors.
Set up the example:
For this example, let’s create a simple table called Movie Rating in an Inventory schema.
CREATE SCHEMA Inventory; GO CREATE TABLE Inventory.MovieRating ( MovieRatingId int NOT NULL CONSTRAINT PKMovieRating PRIMARY KEY, Code varchar(20) NOT NULL CONSTRAINT AKMovieRating UNIQUE, Description varchar(200) NOT NULL, AllowYouthRentalFlag bit NOT NULL );
Nothing special, just a table that we want to create some data in. In the following sections, I will show a few ways to do this.
Using multiple INSERT statements
And you want to insert a couple of rows into it. One way of doing this is to use completely different statements:
INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (0, 'UR','Unrated',1); INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (1, 'G','General Audiences',1); INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (2, 'PG','Parental Guidance',1); INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (3, 'PG-13','Parental Guidance for Children Under 13',1); INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (4, 'R','Restricted, No Children Under 17 without Parent',0);
The one major concern with this method (which can also be a value), is that if you want, it can keep going after an error:
DELETE FROM Inventory.MovieRating; INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (0, 'UR','Unrated',1); --duplicate rows INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (0, 'UR','Unrated',1); INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (0, 'UR','Unrated',1); INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (1, 'G','General Audiences',1); INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (2, 'PG','Parental Guidance',1); INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (3, 'PG-13','Parental Guidance for Children Under 13',1); INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (4, 'R','Restricted, No Children Under 17 without Parent',0);
The output of this batch will be two errors (certainly with the normal settings that most of us will use in SSMS) that look like this:
Msg 2627, Level 14, State 1, Line 46 Violation of PRIMARY KEY constraint 'PKMovieRating'. Cannot insert duplicate key in object 'Inventory.MovieRating'. The duplicate key value is (0). The statement has been terminated.
But since there are two errors? Did the other INSERT statements work?
SELECT * FROM Inventory.MovieRating;
They sure did:
MovieRatingId Code Description AllowYouthRentalFlag ------------- ------- -------------------------------------------------- -------------------- 0 UR Unrated 1 1 G General Audiences 1 2 PG Parental Guidance 1 3 PG-13 Parental Guidance for Children Under 13 1 4 R Restricted, No Children Under 17 without Parent 0
If this is what you want, then this is the best way to do this sort of operation. (If you are scripting this out and there are a LOT of rows, you might want to script out some error handlers so you know which row failed because not every error message is quite so clear on what happened.)
All or nothing method – UNION ALL
A method pretty much everyone has used is to use a bunch of SELECT statements tied together with a UNION ALL. Really, none of these few methods really are that much better than the others, depending on your need.
So to create the data in Inventory.MovieRating, you can do:
DELETE FROM Inventory.MovieRating; INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) SELECT 0, 'UR','Unrated',1 UNION ALL SELECT 1, 'G','General Audiences',1 UNION ALL SELECT 2, 'PG','Parental Guidance',1 UNION ALL SELECT 3, 'PG-13','Parental Guidance for Children Under 13',1 UNION ALL SELECT 4, 'R','Restricted, No Children Under 17 without Parent',0;
However, this would not insert any data:
INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) SELECT 0, 'UR','Unrated',1 UNION ALL SELECT 0, 'UR','Unrated',1 UNION ALL SELECT 1, 'G','General Audiences',1 UNION ALL SELECT 2, 'PG','Parental Guidance',1 UNION ALL SELECT 3, 'PG-13','Parental Guidance for Children Under 13',1 UNION ALL SELECT 4, 'R','Restricted, No Children Under 17 without Parent',0;
You will also only get a single error message, and the entire statement fails.
All or nothing method – Row Creators in VALUES clause
If it ever hit you that the name of the VALUES clause is plural, and not singular, you’re onto something. In fact, back in SQL Server 2008 (yeah, 18 years ago!), they added the ability to specify multiple sets of values in text, comma-delimited:
DELETE FROM Inventory.MovieRating; INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (0, 'UR','Unrated',1), (1, 'G','General Audiences',1), (2, 'PG','Parental Guidance',1), (3, 'PG-13','Parental Guidance for Children Under 13',1), (4, 'R','Restricted, No Children Under 17 without Parent',0);
This method is much cleaner than the UNION ALL method, and also pulls all operations into a single transaction.
All or nothing method – SELECT FROM (VALUES)
Note that row constructors in SQL Server, at least, are always part of a VALUES clause, but not every VALUES clause needs to be a part of an INSERT statement. So you can also do something like this:
DELETE FROM Inventory.MovieRating;
INSERT INTO Inventory.MovieRating
(MovieRatingId, Code, Description, AllowYouthRentalFlag)
SELECT MovieRatings.MovieRatingId,
MovieRatings.Code,
MovieRatings.Description,
MovieRatings.AllowYouthRentalFlag
FROM
( VALUES
(0, 'UR', 'Unrated', 1),
(1, 'G', 'General Audiences', 1),
(2, 'PG', 'Parental Guidance', 1),
(3, 'PG-13', 'Parental Guidance for Children Under 13', 1),
(4, 'R', 'Restricted, No Children Under 17 without Parent', 0)
) AS MovieRatings (MovieRatingId, Code, Description, AllowYouthRentalFlag);
This method isn’t really better for the simple insert case, but it is definitely better if you need to add data to some other data. You can see that in the query that the VALUES clause is part of a derived table:
( VALUES
(0, 'UR', 'Unrated', 1),
(1, 'G', 'General Audiences', 1),
(2, 'PG', 'Parental Guidance', 1),
(3, 'PG-13', 'Parental Guidance for Children Under 13', 1),
(4, 'R', 'Restricted, No Children Under 17 without Parent', 0)
) AS MovieRatings (MovieRatingId, Code, Description, AllowYouthRentalFlag);
This can be used to join to other sets of data when needed. So if you are doing something like translating 1 to True and 0 to False, you could do something like this:
SELECT TOP 3 name, so.is_ms_shipped, Translation
FROM sys.objects so
JOIN
( VALUES
(0, 'Falue'),
(1, 'True')
) AS TruthTranslator (BitValue, Translation)
ON so.is_ms_shipped = TruthTranslator.BitValue;
This now returns:
name is_ms_shipped Translation --------------- ------------- ----------- sysrscols 1 True sysrowsets 1 True sysclones 1 True
And if you want to use this set more than a few times.
WITH TruthTranslatorCTE AS
(
SELECT TruthTranslator.BitValue,
TruthTranslator.Translation
FROM (VALUES
(0, 'Falue'),
(1, 'True')
) AS TruthTranslator (BitValue, Translation)
)
SELECT TOP 3 so.name,
so.is_ms_shipped,
so.is_published,
ttc_shipped.Translation AS is_ms_shipped_desc,
ttc_published.Translation AS is_published_desc
FROM sys.objects so
JOIN TruthTranslatorCTE ttc_shipped
ON so.is_ms_shipped = ttc_shipped.BitValue
JOIN TruthTranslatorCTE ttc_published
ON so.is_published = ttc_published.BitValue;
Which outputs:
name is_ms_shipped is_published is_ms_shipped_desc is_published_desc -------------- ------------- ------------ ------------------ ----------------- sysrscols 1 0 True Falue sysrowsets 1 0 True Falue sysclones 1 0 True Falue
Summary
In this blog, I looked at a few ways to script out rows of data to insert into a table. The first uses a script, the second uses the T-SQL INSERT statement with a SELECT based on using UNION ALL, the other suing row constructors in a INSERT statement with the VALUES clause.
For many, this might be elementary stuff (and I hope so!), but I wouldn’t be surprised if there weren’t many many people out there writing T-SQL every day that don’t realize that row constructors exist.
BONUS – Let’s get ridiculous and check boundaries
First up, let’s try the row construct method. How many rows can I create using the row constructor approach? So I decided to give it a try. This next rig will delete rows from the Inventory.MovieRating table, and then build up an INSERT statement using a loop to add 5 rows of test data.
--***
DELETE FROM Inventory.MovieRating;
DECLARE @SQLStatement NVARCHAR(MAX) =
'INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description,
AllowYouthRentalFlag)
VALUES (0,''Test0'',''Test Description 0'',1),';
DECLARE @i INT = 1;
WHILE @i < 5 --Set number of rows to insert here
BEGIN
SET @SQLStatement = @SQLStatement + CHAR(13) + CHAR(10) +
'(' + CAST(@i AS VARCHAR(4)) + ',''' + 'Test' +
CAST(@i AS VARCHAR(4)) + ''',''' + 'Test Description ' +
CAST(@i AS VARCHAR(4)) + ''',' + CAST(1 AS VARCHAR(1)) + '),';
SET @i = @i + 1;
END
SET @SQLStatement = LEFT(@SQLStatement, LEN(@SQLStatement) - 1);
SELECT @SQLStatement;
EXEC (@SQLStatement);
--***
The query that is output for 5 rows is:
INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) VALUES (0,'Test0','Test Description 0',1), (1,'Test1','Test Description 1',1), (2,'Test2','Test Description 2',1), (3,'Test3','Test Description 3',1), (4,'Test4','Test Description 4',1)
And you can see the number of rows created:
SELECT * FROM Inventory.MovieRating;
The output is:
MovieRatingId Code Description AllowYouthRentalFlag ------------- ------- --------------------- -------------------- 0 Test0 Test Description 0 1 1 Test1 Test Description 1 1 2 Test2 Test Description 2 1 3 Test3 Test Description 3 1 4 Test4 Test Description 4 1
So next, crank it up to 11. But to be honest, 11 wasn’t that impressive and ran is far less than a second. How about 1100? Nope. 1100 caused this error to occur when doing the insert:
Msg 10738, Level 15, State 1, Line 1050 The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.
So yeah, I could have looked it up, but that was more fun (and thankfully SQLPrompt AI actually wrote some of the tedious code for me.) But hey, I am here with the basic setup created, what about UNION ALL? So I will alter the script to use UNION ALL instead of row constructors like this:
--*** DELETE FROM Inventory.MovieRating; DECLARE @SQLStatement NVARCHAR(MAX) = 'INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) SELECT 0,''Test0'',''Test Description 0'',1'; DECLARE @i INT = 1; WHILE @i < 5 BEGIN SET @SQLStatement = @SQLStatement + CHAR(13) + CHAR(10) + 'UNION ALL' + CHAR(13) + CHAR(10) + 'SELECT ' + CAST(@i AS VARCHAR(4)) + ',''' + 'Test' + CAST(@i AS VARCHAR(4)) + ''',''' + 'Test Description ' + CAST(@i AS VARCHAR(4)) + ''',' + CAST(1 AS VARCHAR(1)) + ''; <pre><code>SET @i = @i + 1;</code></pre> END; SELECT @SQLStatement; EXEC (@SQLStatement); --***
The statement that this output:
INSERT INTO Inventory.MovieRating (MovieRatingId, Code, Description, AllowYouthRentalFlag) SELECT 0,'Test0','Test Description 0',1 UNION ALL SELECT 1,'Test1','Test Description 1',1 UNION ALL SELECT 2,'Test2','Test Description 2',1 UNION ALL SELECT 3,'Test3','Test Description 3',1 UNION ALL SELECT 4,'Test4','Test Description 4',1
And the data looks the same.
Cranking it up to 1100 using the UNION ALL approach and see how many rows I can insert was actually interesting. I start with 1100. Not a problem. 2000? You could feel the time was longer, but still done in 5 seconds. 4000 took 32 seconds, 5000 took 62 seconds. I am doing one last test with 10,000 rows now, but from all I can read, there is no “hard” limit, just practical limitations on resources and time. 10000 rows took 5 minutes to complete.
Well, it took some time to execute in this loop, but it will work. Probably more of a limitation of memory and time rather than a hard limit on the number of rows, but SQL Server and SSMS combined is using less RAM than my Edge Browser, so I am happy with the result.
Hope that final side trip was interesting to you. If not, it was to me 🙂
Drsql's Database Musings 
Leave a comment