CREATE TABLE [dbo].[USState] (
    [StateCode]    CHAR(2),
    [StateName]    VARCHAR(20)
)
GO

INSERT INTO [dbo].[USState] 
VALUES ('AK', 'Alaska')

INSERT INTO [dbo].[USState] 
VALUES ('AL', 'Alabama')

INSERT INTO [dbo].[USState] 
VALUES ('AR', 'Arkansas')

INSERT INTO [dbo].[USState] 
VALUES ('AZ', 'Arizona')

INSERT INTO [dbo].[USState] 
VALUES ('CA', 'California')
GO

SELECT * FROM [dbo].[USState]
GO

StateCode   StateName
---------   ------------
AK          Alaska
AL          Alabama
AR          Arkansas
AZ          Arizona
CA          California

The individual INSERT INTO... VALUES statement can now be simplified with the row constructor.  The script above will now look as follows:

CREATE TABLE [dbo].[USState] (
    [StateCode]    CHAR(2),
    [StateName]    VARCHAR(20)
)
GO

INSERT INTO [dbo].[USState]
VALUES ('AK', 'Alaska'),
       ('AL', 'Alabama'),
       ('AR', 'Arkansas'),
       ('AZ', 'Arizona'),
       ('CA', 'California')
GO

SELECT * FROM [dbo].[USState]
GO

StateCode   StateName
---------   ------------
AK          Alaska
AL          Alabama
AR          Arkansas
AZ          Arizona
CA          California

Combining SELECT and VALUES Clauses in the INSERT Statement Row Constructor

With the new row constructor which uses the VALUES clause of the INSERT statement, will it allow inserting values into a table records from another table?  In other words, will it allow a combination of specifying a list of values using the VALUES clause and getting data from another table using the SELECT statement.

To find out, let's say you have a table of super heroes called [dbo].[SuperHero] and you want to populate it with a few records using the INSERT INTO ... VALUES statement.  Aside from the list of values, you also have a table of X-Men super heroes that you want to insert into the master super heroes table.  Here's a script that illustrates the scenario:

CREATE TABLE [dbo].[SuperHero] ( 
    [SuperHeroName]     VARCHAR(50),
    [RealIdentity]      VARCHAR(50)
)
GO

CREATE TABLE [dbo].[X-Men] (
    [CodeName]          VARCHAR(50),
    [RealIdentity]      VARCHAR(50),
    [GoodGuy]           BIT
)
GO

INSERT INTO [dbo].[X-Men]
VALUES ('Wolverine', 'Logan', 1),
       ('Cyclops', 'Scott Summers', 1)
GO

INSERT INTO [dbo].[SuperHero]
VALUES ('Superman', 'Clark Kent'),
       ('Batman', 'Bruce Wayne'),
       ('Spiderman', 'Peter Parker'),
       (SELECT [CodeName], [RealIdentity] FROM [dbo].[X-Men] WHERE [GoodGuy] = 1)
GO

Unfortunately, executing this script will generate the following error:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

This is an expected error because the row construcor only allows single scalar values inside the VALUES list for each column.  Subqueries are allowed as long as only a single column and a single row is returned by the subquery.  To overcome the error above, the query can be re-written as follows:

INSERT INTO [dbo].[SuperHero]
VALUES ('Superman', 'Clark Kent'),
       ('Batman', 'Bruce Wayne'),
       ('Spiderman', 'Peter Parker'),
      ((SELECT [CodeName] FROM [dbo].[X-Men] WHERE [CodeName] = 'Wolverine'),
       (SELECT [RealIdentity] FROM [dbo].[X-Men] WHERE [CodeName] = 'Wolverine'))
GO

As mentioned earlier, subqueries are allowed as long as the subquery returns only a single column and a single row.  If the subquery returns a single column but more than a single row, you will get the following error message:

INSERT INTO [dbo].[SuperHero]
VALUES ('Superman', 'Clark Kent'),
       ('Batman', 'Bruce Wayne'),
       ('Spiderman', 'Peter Parker'),
      ((SELECT [CodeName] FROM [dbo].[X-Men] WHERE [GoodGuy] = 1),
       (SELECT [RealIdentity] FROM [dbo].[X-Men] WHERE [GoodGuy] = 1))
GO
       

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. 
This is not permitted when the subquery follows =, !=, <, <= , >, >= or 
when the subquery is used as an expression.
The statement has been terminated.

As can be seen from the script, only one record from the [dbo].[X-Men] can be retrieved and inserted at a time.  If you will be inserting multiple rows from one table to another, if you use the row constructor it's going to be a long script.  In this case, it is suggested that you separate the VALUES list from the subquery and issue 2 separate INSERT commands, as follows:

INSERT INTO [dbo].[SuperHero]
VALUES ('Superman', 'Clark Kent'),
       ('Batman', 'Bruce Wayne'),
       ('Spiderman', 'Peter Parker')
GO

INSERT INTO [dbo].[SuperHero]
SELECT [CodeName], [RealIdentity]
FROM [dbo].[X-Men]
WHERE [GoodGuy] = 1
GO

One limitation of the row constructor is the maximum number of rows that can be inserted, which is 1000 rows.  If you try to use the row constructor with more than 1000 rows, you will get the following error message:

Msg 10738, Level 15, State 1, Line 1002
The number of row value expressions in the INSERT statement
exceeds the maximum allowed number of 1000 row values.

To overcome this limitation, simply break the INSERT statement into multiple INSERT statements with each INSERT statement containing only 1000 rows to insert.

• Row Constructors (or Table-Valued Constructors) as Derived Table
• Table-Valued Parameters