Help in Complex query and Insert data

Hi, I have three tables and all three are linked. Please help for the query so that I can get the desired result. Thanks.

Notes for Table_A and Table_B:
-----------------------------------
ROW Data are given in the variables to insert the Row data. If these Row data are already exist with the exactly same sequence in the row of table then don't need to INSERT data again.
If these variable date doen't exist then need to add this row.

Notes for Table_C:
-----------------------------------

Seq_id_Table_A is a Seq_id of #table_A. Seq_id_Table_B is a Seq_id of #table_B.

--Table_A----------------------Variables for Table_A--------------------
Declare @table_A_Y char(4)='TRC'
Declare @table_A_Y1 char(2)='1'
Declare @table_A_Y2 char(1)='D'

Declare @table_A_Z char(4)='XRC'
Declare @table_A_Z1 char(2)='2'
Declare @table_A_Z2 char(1)='E'

Create table #table_A (Seq_id numeric, Y char(4),Y1 char(2), Y2 char(1), Z char(4),Z1 char(2), Z2 char(1))
insert into #table_A values (1,'TRC','2','F','XRC','3','G')
insert into #table_A values (2,'TRC','3','F','XRC','4','G')


--Table_B----------------------Variables for Table_B--------------------
Declare @table_B_N char(10)='Papering'
Declare @table_B_M char(10)='Scanning'


Create table #table_B (Seq_id numeric, N char(10),M char(10))
insert into #table_B values (1,'Papering','Scanning')


--Table_C---------------------------------------------------------------

Create table #table_C (Seq_id_Table_A numeric, Seq_id_Table_B numeric)
insert into #table_C values (2,1)


Desired Result:

Select * from #table_A
Seq_id Y Y1 Y2 Z    Z1 Z2
1    TRC  2  F XRC  3  G
2    TRC  3  F XRC  4  G
3    TRC  1  D XRC  2  E   ---This data was in variable and this exact row was not already exist so need to be added and store seq_id for #table_C. Tricky part is that if this row was already exist then don't need to add this row but store seq_id for #table_c.

Select * from #table_B
Seq_id    N               M
1           Papering     Scanning       ---This data was in variable to insert the Row data and this exact row was already exist so don't need to be added but need to store seq_id for #table_C.

Select * from #table_C
Seq_id_Table_A             Seq_id_Table_B
2                                    1
3                                    1                  --- Added new row. Linked seq_id of table_A and table_B

August 31st, 2015 10:27pm

Like other values, i am assuming you get the sequence id values as input as well:
declare @seq_id_A numeric=5
declare @seq_id_B numeric=6
--other values here
declare @Seq_id_Table_A numeric= NULL
declare @Seq_id_Table_B numeric= NULL

If Not Exists(Select 1 from #table_A where Seq_id=@seq_id_A)
Begin
INSERT INTO #table_A values(..)
Set @Seq_id_Table_A=@seq_id_A
END
ELSE
BEGIN
Set @Seq_id_Table_A=@seq_id_A
END

--..Do the same for @seq_id_B


If (@Seq_id_Table_A is Not NULL) and (@Seq_id_Table_B is not Null)
Begin
insert into #table_C values (@Seq_id_Table_A,Seq_id_Table_B)
end
Use this code as an idea. Hope this helps


Free Windows Admin Tool Kit Click here and download it now
August 31st, 2015 11:10pm

Hi, seq_id in table_A and table_b is MAX(seq_id)+1

August 31st, 2015 11:18pm

Hi Kevin,

If I understand your requirement correctly, to achieve your goal, you can refer to the below sample.
Create table #table_A (Seq_id numeric, Y char(4),Y1 char(2), Y2 char(1), Z char(4),Z1 char(2), Z2 char(1))
insert into #table_A values (1,'TRC','2','F','XRC','3','G')
insert into #table_A values (2,'TRC','3','F','XRC','4','G')

Create table #table_B (Seq_id numeric, N char(10),M char(10))
insert into #table_B values (1,'Papering','Scanning')

Create table #table_C (Seq_id_Table_A numeric, Seq_id_Table_B numeric)
insert into #table_C values (2,1)

Declare @table_A_Y char(4)='TRC'
Declare @table_A_Y1 char(2)='1'
Declare @table_A_Y2 char(1)='D'

Declare @table_A_Z char(4)='XRC'
Declare @table_A_Z1 char(2)='2'
Declare @table_A_Z2 char(1)='E'

Declare @table_B_N char(10)='Papering'
Declare @table_B_M char(10)='Scanning'

DECLARE @Seq_id_Table_A INT, @Seq_id_Table_B INT
SELECT @Seq_id_Table_A=MAX(Seq_id_Table_A)+1,@Seq_id_Table_b=MAX(Seq_id_Table_B)+1 FROM #table_C

DECLARE @isInsertedIntoTableA BIT =0
DECLARE @isInsertedIntoTableB BIT =0

--if not exists in #table_A, insert into #table_A
IF NOT EXISTS(SELECT 1 FROM #table_A WHERE Y=@table_A_Y AND Y1=@table_A_Y1 AND Y2=@table_A_Y2 AND Z=@table_A_Z AND Z1=@table_A_Z1 AND Z2=@table_A_Z2)
	BEGIN
	INSERT INTO #table_A(Seq_id , Y ,Y1 , Y2 , Z ,Z1 , Z2 ) VALUES(@Seq_id_Table_A,@table_A_Y,@table_A_Y1,@table_A_Y2,@table_A_Z,@table_A_Z1,@table_A_Z2);
	SET @isInsertedIntoTableA=1;
	END
ELSE 
	SET @Seq_id_Table_A=@Seq_id_Table_A-1

--if not exists in #table_B, insert into #table_B
IF NOT EXISTS(SELECT 1 FROM #table_B WHERE N=@table_B_N AND M=@table_B_M)
	BEGIN
	INSERT INTO #table_A(Seq_id , N,M) VALUES(@Seq_id_Table_B,@table_B_N,@table_B_M);
	SET @isInsertedIntoTableB=1;
	END
ELSE
--else
	SET @Seq_id_Table_B=@Seq_id_Table_B-1

--if either table got inserted then insert into #table_C
IF(@isInsertedIntoTableA=1 OR @isInsertedIntoTableB=1)
	INSERT INTO #table_C(Seq_id_Table_A,Seq_id_Table_B) VALUES(@Seq_id_Table_A,@Seq_id_Table_B);

SELECT * FROM #table_A
SELECT * FROM #table_B
SELECT * FROM #table_C

DROP TABLE #table_A,#table_B,#table_C

	

Have no idea on the purpose on keeping track of the seq_id that way, for me it looks not like a good practice. You could rely on IDENTITY or SEQUENCE(for SQL Server 2012+).

If you have any question, feel free to let me
Free Windows Admin Tool Kit Click here and download it now
September 1st, 2015 2:20am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics