CODEWITHSUNDEEP
sqlsql-server
cg91
cg91

Reputation: 467

Insert into multiple tables at once along with additional records

I have 2 tables 

CREATE TABLE [dbo].[extendable1](
[serialnumber] [int] IDENTITY(1,1) NOT NULL,
[createdby] [nvarchar](36) NOT NULL,
[createddate] [datetime] NOT NULL)

CREATE TABLE [dbo].[extendable1_custom](
[serialnumber] [int] NOT NULL,
[createdby] [nvarchar](36) NOT NULL,
[createddate] [datetime] NOT NULL,
[currencyid] [nvarchar](3) NULL,
[partid] [nvarchar](30) NULL,
[price] [float] NULL)

I need to insert the same values into both tables at the same time for the columns with the same attributes along with additional data to the custom table, however I can't find any examples online to show me how this can be done, I have seen examples using the output clause and have attempted it going down this line to no success.

Just to point out my goal is to extract the indentity ID created in extendable1 and input this value to extendable1_custom

EDIT
An example of values I would like to insert:

insert into extendable1 (createdby, createddate) select 'SO', getDate()

Excluding serialnumber as its an IDENTITY field, these values will be the same in extendable 1 no matter how many records entered

insert into extendable1_custom (createdby, createddate, currencyid, partid, price) select (extendable1.serialnumber, extendable1.createdby, extendable1.createddate, #temp.currencyid, #temp.partid, #temp.price) from #temp

Is there another route I should be going or is this possible?

Thanks

Upvotes: 0

Views: 416

Answers (3)

Ayush Kalia
Ayush Kalia

Reputation: 31

Yes,you can simply use a begin tran and commit with the both statement. Secondly you need to use the error handling along with the same. And using a rollback if the tran count is above 0 in that case rollback the query.

Although Tran work as a same either all will get commit or none.But sometimes we encounter issue of insertion so for best possible result use rollback also.

Upvotes: 0

Saif
Saif

Reputation: 2679

you can insert into both tables at the same time using below query 

START TRANSACTION;
 INSERT INTO extendable1 VALUES (column1, column2, ..);
 INSERT INTO extendable1_custom VALUES(column1, column2, columnx ..);
COMMIT;

Upvotes: 1

ser_nicky
ser_nicky

Reputation: 356

You can't insert same records in one statement, but you can in one transaction as follows:

BEGIN TRAN
INSERT INTO [dbo].[extendable1] VALUES(.....)
INSERT INTO [dbo].[extendable1_custom] (serialnumber,createdby,createddate) VALUES(...,...,...)
COMMIT

This means that this chunk of code will be executed in its entirety (all or nothing) - you can read about Atomicity, which is one of the four characteristics of transactions.

Upvotes: 0

Related Questions