Do an Insert For Each ID in a table

jnavarro.ph · September 4, 2015, 1:15pm

I am doing a test script where in I will insert invoice numbers and some information with a batch id into a table. After inserting it, I will select those informations and insert it again into different tables. please see below sql.

DECLARE @TB_CQT_MAIN TABLE(CQT_ID INT IDENTITY(1,1) NOT NULL, CQT_TYPE VARCHAR(1), SALES_ORG VARCHAR(5), DATE_RECEIVED DATETIME, BATCH_ID VARCHAR(20))

DECLARE @TB_CQT_WCSS TABLE (CQT_ID INT NOT NULL, INVOICE_NUMBER VARCHAR(20), INVOICE_TYPE VARCHAR(5))
DECLARE @TB_CQT_OTHER TABLE (PRODUCING_PLANT_CODE VARCHAR(4) NULL, CQT_ID INT, INVOICE_NUMBER VARCHAR(20) NULL, INVOICE_TYPE VARCHAR(5) NULL)

DECLARE @BWCSS BIT
SET @BWCSS = 'TRUE'

--Insert information first in the main table
-- no problem here
INSERT INTO @TB_CQT_MAIN (CQT_TYPE, SALES_ORG, DATE_RECEIVED, BATCH_ID)
SELECT CQT_TYPE, SALES_ORG, GETDATE(), BATCH_ID FROM TB_CQT_MAIN_TEMP WHERE BATCH_ID = 'B-1121214'

IF @BWCSS = 'TRUE'
-- here i have to get the cqt_ids from the previous table using the batch id
-- and insert it into another table with the invoice number and the invoice type.
-- this is where i got stuck because the it causes duplicates

BEGIN
INSERT INTO @TB_CQT_WCSS (CQT_ID, INVOICE_NUMBER, INVOICE_TYPE)
SELECT A.CQT_ID, B.INVOICE_NUMBER, B.INVOICE_TYPE
FROM @TB_CQT_MAIN A LEFT JOIN TB_CQT_MAIN_TEMP B ON A.BATCH_ID = B.BATCH_ID
WHERE A.BATCH_ID = 'B-1121214'
END

the result of the second insert looks like this.

CQT_ID INVOICE_NUMBER INVOICE_TYPE

1 VR79349 SAP
1 VR76568 SAP
1 VR76420 SAP
1 VR74416 SAP
1 VR74255 SAP
1 VR73787 SAP
1 VR71796 SAP
1 VR71620 SAP
1 VR71497 SAP
1 VR71486 SAP
2 VR79349 SAP
2 VR76568 SAP
2 VR76420 SAP
2 VR74416 SAP
2 VR74255 SAP
2 VR73787 SAP
2 VR71796 SAP
2 VR71620 SAP
2 VR71497 SAP
2 VR71486 SAP
3 VR79349 SAP
3 VR76568 SAP
3 VR76420 SAP
3 VR74416 SAP
3 VR74255 SAP
3 VR73787 SAP
3 VR71796 SAP
3 VR71620 SAP
3 VR71497 SAP
3 VR71486 SAP
4 VR79349 SAP
4 VR76568 SAP
4 VR76420 SAP
4 VR74416 SAP
4 VR74255 SAP
4 VR73787 SAP
4 VR71796 SAP
4 VR71620 SAP
4 VR71497 SAP
4 VR71486 SAP
5 VR79349 SAP
5 VR76568 SAP
5 VR76420 SAP
5 VR74416 SAP
5 VR74255 SAP
5 VR73787 SAP
5 VR71796 SAP
5 VR71620 SAP
5 VR71497 SAP
5 VR71486 SAP
6 VR79349 SAP
6 VR76568 SAP
6 VR76420 SAP
6 VR74416 SAP
6 VR74255 SAP
6 VR73787 SAP
6 VR71796 SAP
6 VR71620 SAP
6 VR71497 SAP
6 VR71486 SAP
7 VR79349 SAP
7 VR76568 SAP
7 VR76420 SAP
7 VR74416 SAP
7 VR74255 SAP
7 VR73787 SAP
7 VR71796 SAP
7 VR71620 SAP
7 VR71497 SAP
7 VR71486 SAP
8 VR79349 SAP
8 VR76568 SAP
8 VR76420 SAP
8 VR74416 SAP
8 VR74255 SAP
8 VR73787 SAP
8 VR71796 SAP
8 VR71620 SAP
8 VR71497 SAP
8 VR71486 SAP
9 VR79349 SAP
9 VR76568 SAP
9 VR76420 SAP
9 VR74416 SAP
9 VR74255 SAP
9 VR73787 SAP
9 VR71796 SAP
9 VR71620 SAP
9 VR71497 SAP
9 VR71486 SAP
10 VR79349 SAP
10 VR76568 SAP
10 VR76420 SAP
10 VR74416 SAP
10 VR74255 SAP
10 VR73787 SAP
10 VR71796 SAP
10 VR71620 SAP
10 VR71497 SAP
10 VR71486 SAP

Where in it only should look lilke

CQT_ID INVOICE_NUMBER INVOICE_TYPE

1 VR79349 SAP
2 VR76568 SAP
3 VR76420 SAP
4 VR74416 SAP
5 VR74255 SAP
6 VR73787 SAP
7 VR71796 SAP
8 VR71620 SAP
9 VR71497 SAP
10 VR71486 SAP

Thanks in advance SQL Team!

gbritton · September 4, 2015, 1:20pm

I suppose that the join is returning more than you think it should. Kind of hard to debug without seeing a sample of the data in the source tables

jnavarro.ph · September 4, 2015, 1:22pm

hi @gbritton! thanks for the quick look.

here is the data. from the source table.

CQT_TYPE SALES_ORG BATCH_ID INVOICE_NUMBER INVOICE_TYPE

E US01 B-1121214 VR79349 SAP
E US01 B-1121214 VR76568 SAP
E US01 B-1121214 VR76420 SAP
E US01 B-1121214 VR74416 SAP
E US01 B-1121214 VR74255 SAP
E US01 B-1121214 VR73787 SAP
E US01 B-1121214 VR71796 SAP
E US01 B-1121214 VR71620 SAP
E US01 B-1121214 VR71497 SAP
E US01 B-1121214 VR71486 SAP

gbritton · September 4, 2015, 1:25pm

OK -- well since both table variables contain the same batch, the effect of your Left join is actually a CROSS JOIN (cartesian product) since all rows have the same batch number

jnavarro.ph · September 4, 2015, 1:28pm

So how do i go about it? How can I not make it a cross join? Is it something wrong with the data?

Thanks so much!

gbritton · September 4, 2015, 1:32pm

try including the invoice number in the join predicate

jnavarro.ph · September 7, 2015, 7:52am

Hi @gbritton Sorry for the late response. I think that will only happen if I add another column (INVOICE_NUMBER) @TB_CQT_MAIN. My problem is on the real table, it doesn't have an invoice number on the main table.

Ifor · September 7, 2015, 8:39pm

Your problem is that the combination of CQT_TYPE,SALES_ORG,BATCH_ID is not unique.
If you made it unique on the first insert it would be fine:

DECLARE @BWCSS bit = 1;

--Insert information first in the main table
-- no problem here
INSERT INTO @TB_CQT_MAIN (CQT_TYPE, SALES_ORG, DATE_RECEIVED, BATCH_ID)
SELECT DISTINCT CQT_TYPE, SALES_ORG, CURRENT_TIMESTAMP, BATCH_ID
FROM TB_CQT_MAIN_TEMP
WHERE BATCH_ID = 'B-1121214';

IF @BWCSS = 1
-- here i have to get the cqt_ids from the previous table using the batch id
-- and insert it into another table with the invoice number and the invoice type.
-- this is where i got stuck because the it causes duplicates
BEGIN
INSERT INTO @TB_CQT_WCSS (CQT_ID, INVOICE_NUMBER, INVOICE_TYPE)
SELECT A.CQT_ID, B.INVOICE_NUMBER, B.INVOICE_TYPE
FROM @TB_CQT_MAIN A
	JOIN TB_CQT_MAIN_TEMP B
	ON A.BATCH_ID = B.BATCH_ID
	AND A.SALES_ORG = B.SALES_ORG
	AND A.CQT_TYPE = B.CQT_TYPE
WHERE A.BATCH_ID = 'B-1121214';
END

jnavarro.ph · September 8, 2015, 2:27pm

Hi Guys,

Thank you for all your contributions. I was able to solve the problem. I used a CURSOR for this.

Something like this...

-- declare cursor
DECLARE BATCHCURSOR CURSOR FOR
SELECT CQT_TYPE, SALES_ORG,BATCH_ID, INVOICE_NUMBER, INVOICE_TYPE FROM TB_CQT_MAIN_TEMP
WHERE BATCH_ID = @sBatchID
ORDER BY INVOICE_NUMBER;

OPEN BATCHCURSOR;

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM BATCHCURSOR
INTO @CQT_TYPE, @SALES_ORG, @BATCH_ID, @INVOICE_NUMBER, @INVOICE_TYPE;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

-- Insert batch from Temp table to CQT Main Table
INSERT INTO @TB_CQT_MAIN (CQT_TYPE, SALES_ORG, DATE_RECEIVED, INITIATED_BY, BATCH_ID)
SELECT @CQT_TYPE, @SALES_ORG, GETDATE(), @InitBy, @BATCH_ID

--get the cqt ID
SELECT @CQTID = IDENT_CURRENT('TB_CQT_MAIN')

IF @bWCSS = 'TRUE'

-- WCSS Table
BEGIN
INSERT INTO @TB_CQT_WCSS (CQT_ID, INVOICE_NUMBER, INVOICE_TYPE)
VALUES (@CQTID, @INVOICE_NUMBER, @INVOICE_TYPE)
END
ELSE

-- Other Table
BEGIN
INSERT INTO @TB_CQT_OTHER (CQT_ID, INVOICE_NUMBER, INVOICE_TYPE, PRODUCING_PLANT_CODE)
VALUES (@CQTID, @INVOICE_NUMBER, @INVOICE_TYPE, @PRODUCING_PLANT_CODE)
END

-- Financial Table    
INSERT INTO @TB_CQT_FIN (CQT_ID) VALUES (@CQTID)  
  
-- This is executed as long as the previous fetch succeeds.  
FETCH NEXT FROM BATCHCURSOR  
INTO @CQT_TYPE, @SALES_ORG, @BATCH_ID, @INVOICE_NUMBER, @INVOICE_TYPE;

END

CLOSE BATCHCURSOR;
DEALLOCATE BATCHCURSOR;

Thank you for all your inputs!

gbritton · September 8, 2015, 3:25pm

glad you got it to work. Can't see why you need a cursor though. Hope the rowsets are small enough that ther performance hit is negligible