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'.
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 |
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 |
We are also going to insert a new row on table '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:
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:
2 | SELECT id, GETDATE() FROM Customers |
3 | WHERE username = 'john.smith' |
or if you would like to specify the columns:
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.