SQL Server: INSERT INTO with SubQuery

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] [intNOT 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'
KategorilerGenel

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir