SQL Server: INSERT INTO with SubQuery

Introduction

This is a short tutorial on how to insert data with a subquery on SQL Server databases.
The reason I am writing about it is that the required syntax is not that obvious!

Sample tables and data

Let's create two simple tables for this tutorial with the names 'Customers' and 'Orders'.

view source

1	CREATE TABLE [dbo].[Customers](

2	[id] [int] IDENTITY(1,1) NOT NULL,

3	[name] [varchar](100) NOT NULL,

4	[username] [varchar](20) NOT NULL

5

)

view source

1	CREATE TABLE [dbo].[Orders](

2	[order_id] [int] IDENTITY(1,1) NOT NULL,

3	[customer_id] [int] NOT NULL,

4	[order_date] [datetime] NOT NULL

5

)

We are also going to insert a new row on table 'Customers':

view source

1	INSERT INTO Customers

2	VALUES ('John Smith','john.smith')

Inserting data with a subquery

The obvious syntax to insert data into the orders table with a subquery would be:

view source

1	INSERT INTO Orders

2	VALUES ((SELECT id FROM Customers WHERE username = 'john.smith'), GETDATE())

However if you try the above query, you would end up with the following message:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

The correct syntax to accomplish our task is:

view source

1	INSERT INTO Orders

2	SELECT id, GETDATE() FROM Customers

3	WHERE username = 'john.smith'

or if you would like to specify the columns:

view source

1	INSERT INTO Orders (customer_id, order_date)

2	SELECT id, GETDATE() FROM Customers

3	WHERE username = 'john.smith'

Conclusion

Hope you find it useful, especially if you are getting the "Subqueries are not allowed …" error.