0

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

cg91
  • 467
  • 3
  • 15

3 Answers3

1

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;
Saif
  • 2,611
  • 3
  • 17
  • 37
  • i need to get the same serial number from extendable1 into extendable1_custom will this method extract from the table and insert into the custom table? Also will this work for multiple records coming from a temp table? – cg91 Feb 01 '17 at 10:50
  • you just need to make `serialnumber` auto increment in both tables. Try this first, if that still does not work, then will show how to extract `serialnumber` from table1 and insert into table2 – Saif Feb 01 '17 at 10:57
  • Due to the constraints of the system I am using I am not able to edit the table, therefore I need to be able to extract the data from table1, I would very much appreciate if you could show me how to do this for serialnumber, createdby etc – cg91 Feb 01 '17 at 11:00
0

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.

ser_nicky
  • 326
  • 1
  • 5
  • i need to get the same serial number from extendable1 into extendable1_custom will this method extract from the table and insert into the custom table? Also will this work for multiple records coming from a temp table? – cg91 Feb 01 '17 at 10:51
  • Oh, then you should maybe look at this http://stackoverflow.com/questions/175066/sql-server-is-it-possible-to-insert-into-two-tables-at-the-same-time[/link] – ser_nicky Feb 01 '17 at 11:03
0

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.

Ayush Kalia
  • 31
  • 1
  • 7