How to insert multiple records on Transaction

Question

I have a question about SQL Server transactions

I want to insert datas into Table_A and Table_B. Table_B has a Table_A's key. Table_B's records size (has Table_A's key) is dynamic.

[Table_A]
id: ,
title:

[Table_B]
id: ,
tableA_id:,
title:

My code work only the case insert data is static size. like this

var tableBtitles = ['abc','def','ghi'] //this size is dynamic
const transaction = new sql.Transaction()
  transaction.begin(err => {

  const request = new sql.Request(transaction)
  request.query("insert into tableA (title) output Inseted.Id values('a')" , (err, result) => {
    const request = new sql.Request(transaction)
    request.input('tableA_id',mssql.NVarChar,
      result['recordset'][0]['id']);  
    request.input('title1',mssql.NVarChar,
      tableBtitles[0]); 
    request.input('title2',mssql.NVarChar,
      tableBtitles[1]); 
    request.input('title3',mssql.NVarChar,
      tableBtitles[2]); 
    request.query('insert into tableB (tableA_id,title) values(@tableA_id,@title1),(@tableA_id,@title2),(@tableA_id,@title2)), (err, result) => {
      transaction.commit(err => {

      })
  })
})

Please tell me how to do it.

This *is* a SQL question as much as it is a *Javascript* question. It doesn't matter where the database is hosted. The product and language are the same. Although this looks more like a 'How do I write this in Javascript` question. If you want to use SQL statements, you'll have to use a loop to INSERT the parent records, retrieve the new ID and then insert the child records — Panagiotis Kanavos, Sep 08 '17 at 09:48
To avoid all those round trips you can *batch* the items and generate a multi-row INSERT, eg `INSERT INTO... OUTPUT Inserted.Title, Inserted.ID VALUES ('a'), ('B'),...`. You need to return the title and ID otherwise you won't know which ID matches which title — Panagiotis Kanavos, Sep 08 '17 at 09:50
*You can avoid this though* if you use a different strategy to generate new IDs on the client. Once you have that, you can just insert the rows, even using `bulk`. — Panagiotis Kanavos, Sep 08 '17 at 09:52

score 1 · Answer 1 · answered Sep 08 '17 at 06:42

1

try this syntax

insert into [Table_A] ([data]) 
output inserted.id, inserted.data into [Table_B]

--check here https://stackoverflow.com/a/3712735/8543453

answered Sep 08 '17 at 06:42

The beginner

624
4
17

How to insert multiple records on Transaction

1 Answers1