July 7, 2009 at 6:37 am
Hi all,
I have a data set (4 colums) and I insert column 1 and 2 in to table A.
I then get @@identify
So I can insert col3 and 4 into table B and link it to Table A primary.
Please see my example code this is easily done in a cursor. But is it possible to do this all at once instead of row by row?
Many thanks
--test tables
create table testProb1(
PROB1_REFNO numeric (10, 0) IDENTITY (1, 1) NOT NULL ,
col1 varchar(10),
col2 varchar(10))
create table testProb2(
PROB2_REFNO numeric (10, 0) IDENTITY (1, 1) NOT NULL ,
PROB1_REFNO numeric (10, 0),
col1 varchar(10),
col2 varchar(10))
create table testprodD(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10))
--testdata
insert testprodD
select 'a','b','c','d' union
select 'e','f','g','h' union
select 'i','j','k','l' union
select 'm','n','o','p'
--THIS IS HOW I WOULD DO IT A LINE AT A TIME
--BUT WHAT IF I WANTED TO DO IT ALL AT ONCE
declare @new_ID int
--imagine a cursor
insert testProb1
select top 1 col1,col2 from testprodD
set @new_ID = @@identity
insert testProb2
select top 1 @new_ID,col3,col3 from testprodD
select * from testProb1
select * from testProb2
July 7, 2009 at 8:53 am
I'd do something like this:
INSERT INTO testProb1 (
col1,
col2
)
SELECT
TD.col1,
TD.col2
FROM
testprodD AS TD LEFT JOIN
testProb1 AS TP ON TD.col1 = TP.col1 AND TD.col2 = TP.col2
WHERE
TP.PROB1_REFNO IS NULL
INSERT INTO testProb2 (
PROB1_REFNO,
col1,
col2
)
SELECT
A.PROB1_REFNO,
TD.col3,
TD.col4
FROM
testProb1 AS A JOIN
testprodD AS TD ON A.col1 = TD.col1 AND A.col2 = TD.col2 LEFT JOIN
testProb2 AS TP ON A.PROB1_REFNO = TP.PROB1_REFNO
WHERE
TP.PROB2_REFNO IS NULL
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply