sql query for inserts records in multiple tables

Posted by Anant under Sql Server on 2/19/2014 | Points: 10 | Views : 1876 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Assuming table A has ID(int) as PK, i want to write a script which inserts all rows from table B to table A(both have matching columns) and puts IDs of inserted rows in table C (just keeps IDs). Feel free to design columns as required for A, B, C (go with minimum number of columns)

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 2/19/2014 [Member] [MVP] Platinum | Points: 25

0	-- Sample Script --Table creation A, B and C ; insert sample data into Table A create table A ( ID INT PRIMARY KEY, name varchar(10)) INSERT A values(1, 'AAA'), (2, 'BBB'), (3, 'CCC') create table B ( ID INT PRIMARY KEY, name varchar(10)) create table C ( ID INT ) -- Decalre Temparary table for holding newly inserted IDs of Table B DECLARE @tempC TABLE (ID INT) -- Insert values into table B from table A INSERT B(ID, name) OUTPUT INSERTED.ID INTO @tempC -- here insert temp IDs into temporary table (@tempC) SELECT ID, Name FROM A -- Insert New IDs into Table C INSERT C SELECT * FROM @tempC --Verify Values SELECT * FROM A SELECT * FROM B SELECT * FROM C DROP TABLE A DROP TABLE B DROP TABLE C Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Anant, if this helps please login to Mark As Answer. \| Alert Moderator

-- Sample Script


--Table creation A, B and C ; insert sample data into Table A
create table A ( ID INT PRIMARY KEY, name varchar(10))
INSERT A values(1, 'AAA'), (2, 'BBB'), (3, 'CCC')

create table B ( ID INT PRIMARY KEY, name varchar(10))
create table C ( ID INT )

-- Decalre Temparary table for holding newly inserted IDs of Table B
DECLARE @tempC TABLE (ID INT)
-- Insert values into table B from table A 
INSERT B(ID, name) 
OUTPUT INSERTED.ID INTO @tempC  -- here insert temp IDs into temporary table (@tempC)
SELECT ID, Name FROM A

-- Insert New IDs into Table C
INSERT C SELECT * FROM @tempC

--Verify Values
SELECT * FROM A
SELECT * FROM B
SELECT * FROM C

DROP TABLE A
DROP TABLE B
DROP TABLE C

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Anant, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 2/19/2014 [Member] [MVP] Platinum | Points: 25

0	Refer http://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Anant, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Rajesh_Kumar on: 2/19/2014 [Member] [MVP] Silver | Points: 25

0	Use below code --Test1 table has ID,Name and Address columns INSERT INTO Test1(Name,Address) OUTPUT INSERTED.Id,INSERTED.Name,INSERTED.Address Into Test2 VALUES ('Test3','Pune') INSERT INTO Test3(Id,Name,Address) Select * From Test2 select * from test1; select * from test2; select * from test3; Rejesh Kumar Anant, if this helps please login to Mark As Answer. \| Alert Moderator

Use below code
--Test1 table has ID,Name and Address columns

INSERT INTO Test1(Name,Address)
OUTPUT INSERTED.Id,INSERTED.Name,INSERTED.Address Into Test2
VALUES ('Test3','Pune')

INSERT INTO Test3(Id,Name,Address)
Select * From Test2

select * from test1;
select * from test2;
select * from test3;

Rejesh Kumar

Anant, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Snaveen on: 2/24/2014 [Member] Starter | Points: 25

0	Hi, I can't get your exact requirement, but based on your question i assume onething and prepare script to resolve this issue. [code] insert into table1 ( col..) values( @col1,@col2), (@col1,@col2) [/code] Try something like above to achieve your goal... Anant, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts