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’.

CREATE TABLE [dbo].[Customers](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](100) NOT NULL,
	[username] [varchar](20) NOT NULL
)

CREATE TABLE [dbo].[Orders](
	[order_id] [int] IDENTITY(1,1) NOT NULL,
	[customer_id] [int] NOT NULL,
	[order_date] [datetime] NOT NULL
)

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

INSERT INTO Customers
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:

INSERT INTO Orders
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:

INSERT INTO Orders
SELECT id, GETDATE() FROM Customers
WHERE username = 'john.smith'

or if you would like to specify the columns:

INSERT INTO Orders (customer_id, order_date)
SELECT id, GETDATE() FROM Customers
WHERE username = 'john.smith'

Conclusion

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

Read Full Post »