March 12, 2012 at 10:40 pm
Dear all;
Table having 1 million record we have to write the quries like this .
the number of union all to write and finally insert the new table. in case more then 500 union all sql stmt writing and execute . but it take an more time to perform this task .
any one help this case..
Example
SELECT @pubID,
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '608' AND entity_id = @progID),
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '11606' AND entity_id = @orgID),
GETDATE(),@userId,@relSupp,1,'Fuel cooled oil cooler'
UNION ALL
SELECT @pubID,
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '12569' AND entity_id = @orgID ),
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '608' AND entity_id = @progID ),
GETDATE(),@userId,@relSuppFor,1,'Placards; ID plated markings'
UNION ALL
SELECT @pubID,
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '608' AND entity_id = @progID),
(SELECT entity_item_id from #TMPTABLE WHERE acc_no = '12569' AND entity_id = @orgID),
GETDATE(),@userId,@relSupp,1,'Placards; ID plated markings'
....etc
Thanks
March 12, 2012 at 10:51 pm
Can you provide some more details on what you are trying to acheive? It seems to be easily doable with 1 or 2 select statements
March 13, 2012 at 3:56 am
Maybe this can help you:
-- SETUP
IF OBJECT_ID('tempdb..#TMPTABLE') IS NOT NULL
DROP TABLE #TMPTABLE;
CREATE TABLE #TMPTABLE (
entity_item_id int PRIMARY KEY CLUSTERED,
entity_id int,
acc_no varchar(10)
);
DECLARE @pubID int,
@progId int,
@orgId int,
@userid int,
@relSuppFor int,
@relSupp int;
SELECT @pubID = 1,
@progId = 2,
@orgId = 3,
@userid = 4,
@relSuppFor = 5,
@relSupp = 6;
INSERT INTO #TMPTABLE VALUES (100, @progId, '608');
INSERT INTO #TMPTABLE VALUES (200, @orgId, '11606');
INSERT INTO #TMPTABLE VALUES (300, @orgId, '12569');
-- SOLUTION
WITH inputData AS (
SELECT v.row, v.col, v2.rel, v2.description, tmp.entity_item_id
FROM (
-- break the original rows into a row/column inline table
VALUES (1,1,'608', @progId),
(1,2,'11606',@orgId ),
(2,1,'12569',@orgId ),
(2,2,'608' ,@progId),
(3,1,'608' ,@progId),
(3,2,'12569',@orgId )
) AS v (row, col, acc_no, entity_id)
INNER JOIN (
-- keep the unchanging columns of the original rows here
VALUES (1, @relSupp, 'Fuel cooled oil cooler'),
(2, @relSuppFor,'Placards; ID plated markings'),
(3, @relSupp, 'Placards; ID plated markings')
) AS v2 (row, rel, description)
ON v.row = v2.row
INNER JOIN #TMPTABLE AS tmp
ON v.acc_no = tmp.acc_no
AND v.entity_id = tmp.entity_id
)
SELECT @pubID AS pubId,
[1] AS entity_item_id_1,
[2] AS entity_item_id_2,
GETDATE() AS dt,
@userid AS userId,
rel,
1 AS one,
description
FROM inputData AS data
-- pivot the results to rebuild the original rows
PIVOT( MIN(entity_item_id) FOR col IN([1],[2]) ) AS p
ORDER BY row;
-- Gianluca Sartori
March 14, 2012 at 7:23 am
Your procedure design is completely flawed. Do not build large "UNION ALL", but instead put required data into (temp) tables,
and then use one simple INSERT command that joins your prepared tables.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply