CODEWITHSUNDEEP
sqlsql-serversql-insert
Jessica Maya
Jessica Maya

Reputation: 3

how to insert into in 2 table?

"Make a query that having all orders by employees from employeeUK in the orderUK table"

my table of employeeUK:

select *
into employeeUK
from Employees
where Country = 'UK'

then i want to make the orderUK table:

select *
into orderUKcoba
from Orders o join employeeUK e
where (e.EmployeeID = o.EmployeeID)

but what i got is

Incorrect syntax near the keyword 'where'.

how to correct that?

Upvotes: 0

Views: 51

Answers (4)

Ilyes
Ilyes

Reputation: 14928

How to insert into in 2 table?

You could do that by using OUTPUT Clause as the following:

INSERT INTO TargetTable1(Column1, ...)
OUTPUT INSERTED.Column1, ... INTO TargetTable2
SELECT Column1, ...
FROM SourceTable;

Here is a simple example on db<>fiddle

Upvotes: 1

Jim Macaulay
Jim Macaulay

Reputation: 5141

You have to ON clause instead of WHERE clause while joining the table, please use below query

select *
into orderUKcoba
from Orders o join employeeUK e
on (e.EmployeeID = o.EmployeeID);

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

JOIN requires ON clause :

select o.col, . . . , e.col, . . . -- Qualify all column names explicitly 
into orderUKcoba
from Orders o join 
     employeeUK e
     on e.EmployeeID = o.EmployeeID;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The correct syntax uses ON:

select *
into orderUKcoba
from Orders o join
     employeeUK e
     on e.EmployeeID = o.EmployeeID;

This will return an error, because at least one column is duplicated (EmployeeId) and tables cannot have duplicate column names. But that is not the question you asked.

You should be listing out the columns explicitly for a new table.

Upvotes: 0

Related Questions