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'
Hope you find it useful, especially if you are getting the “Subqueries are not allowed …” error.