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 |
) |
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’:
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:
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:
1 |
INSERT INTO Orders |
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' |