CODEWITHSUNDEEP
sqlsql-servert-sql
Chris Wallis
Chris Wallis

Reputation: 1273

INSERT embedded in INSERT INTO SELECT

I am trying to do an INSERT INTO SELECT statement, with a further INSERT embedded in the SELECT. The reason is that I need to make a copy of a foreign key and use the new row id in the outside insert. Something like this:

INSERT INTO Contracts ( 
  JobId, 
  InvoiceNumber
  ExpensesId)
SELECT 
  j.Id,
  j.InvoiceNumber, 
  (INSERT INTO Expenses (Bus, Toll) SELECT Bus, Toll FROM Expenses WHERE Id = j.ExpensesId)
FROM Jobs j

This SQL does not run, as the INSERT does not return anything, but is there a way to do this, or a better approach?

Upvotes: 0

Views: 312

Answers (2)

podiluska
podiluska

Reputation: 51494

You can't do it quite like that.

I think this may be what you're trying to do?

INSERT INTO Contracts ( 
  JobId, 
  InvoiceNumber
  ExpensesId)
SELECT 
  j.Id,
  j.InvoiceNumber, 
  j.ExpensesID
FROM Expenses 
   INNER JOIN Jobs j ON expenses.Id = j.ExpensesId

If you need to copy the expenses row, you need to do that first, and use SCOPE_IDENTITY to get the new ID. 

declare @expenseid int = -- your expense ID
declare @newid int

INSERT Expenses (Bus, Toll)
SELECT Bus, Toll FROM Expenses WHERE Id = @expenseID
SELECT @newID = SCOPE_IDENTITY()

INSERT INTO Contracts ( 
  JobId, 
  InvoiceNumber
  ExpensesId)
SELECT 
  j.Id,
  j.InvoiceNumber, 
  @newID
FROM Expenses 
WHERE Id = @expenseID

If you want to do a batch

DECLARE @inserts table(expenseid int, jobid int)

INSERT Expenses (Bus, Toll)
OUTPUT inserted.id, Jobs.ID INTO @inserts
SELECT Bus, Toll FROM Expenses 
       INNER JOIN Jobs ON Jobs.ExpenseID = Expenses.ID

INSERT Contracts (JobID, InvoiceNumber, ExpensesID)
SELECT j.ID, j.InvoiceNumber, i.ExpenseID
FROM Jobs j
     INNER JOIN @inserts I on j.ID = i.jobid

... or something like that - not quite sure I compeletely understand your data structures.

Upvotes: 3

Ali Asad
Ali Asad

Reputation: 157

You can't write insert in select statement. It does not return a column field or object

Upvotes: 0

Related Questions