sql query for inserts records in multiple tables

Posted by Anant under Sql Server on 2/19/2014 | Points: 10 | Views : 1721 | Status : [Member] | Replies : 4
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)




Responses

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

Up
0
Down
-- 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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response