Query Help - Mulitple inserts

Question

Query Help - Mulitple inserts

Gloriann

Ten Centuries

Points: 1192
More actions
August 29, 2005 at 3:01 pm

#165368

I have multiple insert statements that need to be executed. Each insert statement is exactly the same except one value; is there any way I can do this more efficiently? (I know what the value is going to be each time and it is not dependant on anything.)

Example:
INSERT INTO CQT_ORGANIZATION_PRODUCT(ORP_ORG_ID, ORP_PRD_ID)
SELECT Org_ID, '86'
FROM CQT_ORGANIZATION (nolock)
WHERE Org_ID not in (Select Orp_Org_ID From CQT_ORGANIZATION_PRODUCT (nolock))

INSERT INTO CQT_ORGANIZATION_PRODUCT(ORP_ORG_ID, ORP_PRD_ID)
SELECT Org_ID, '88'
FROM CQT_ORGANIZATION (nolock)
WHERE Org_ID not in (Select Orp_Org_ID From CQT_ORGANIZATION_PRODUCT (nolock))

INSERT INTO CQT_ORGANIZATION_PRODUCT(ORP_ORG_ID, ORP_PRD_ID)
SELECT Org_ID, ‘92’
FROM CQT_ORGANIZATION (nolock)
WHERE Org_ID not in (Select Orp_Org_ID From CQT_ORGANIZATION_PRODUCT (nolock))

There just has to be a way to combine these??…. Any suggestions you could give me I’d be greatly appreciated!!!

ps - I need a row inserted for each value so in the end it would look similiar to this:
ORP_ORG_ID    ORP_PRD_ID
1299               86
1299               88
1299               92 and so on......

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Glorianni...so your ORP_ORG_ID is not unique ?!?!

How do you decide which number goes into the ORP_PRD_ID column - I know you said it is not dependent on anything...but I'm curious about how you decide which ORP_PRD_ID goes against which ORP_ORG_ID...also, do the #s have any sort of a pattern at all - ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 2

Seems like a slightly odd request, but here goes:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #prds
(prd_id CHAR (2) NOT NULL)
INSERT INTO #prds (prd_id) VALUES ('86')
INSERT INTO #prds (prd_id) VALUES ('88')
INSERT INTO #prds (prd_id) VALUES ('92')
INSERT INTO CQT_ORGANIZATION_PRODUCT (ORP_ORG_ID, ORP_PRD_ID)
SELECT c.Org_ID
       , p.prd_id
FROM CQT_ORGANIZATION c
    CROSS JOIN #prds    p
WHERE c.Org_ID not in (Select Orp_Org_ID
                         From CQT_ORGANIZATION_PRODUCT)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

You have the option of eschewing the temp table and just making a SELECT ... UNION ALL SELECT ... list. You also have the option of looping, but as per your specification, I don't see the need.

The SET statement obviates the need for NOLOCK on each table.

Gloriann Ten Centuries Points: 1192 More actions · Answer 3

Thanks Lee! I think your idea is great... is there any way to make it even more simplified?

INSERT INTO #prds (prd_id) VALUES ('86')

INSERT INTO #prds (prd_id) VALUES ('88')

INSERT INTO #prds (prd_id) VALUES ('92')

Can we shorten that part of the script? I have about 256 seperate values...

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Where are the values coming from? If they are stored in a text document or spreadsheet you could use DTS to import the values into a table and then join the table you created.

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

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Well, if you have 256 separate values that have to be inserted always (is that so? For each Org_ID you insert 256 rows into the table with always the same values of prd_id?) then I would recommend that you create a permanent table with all these values instead of #prds. Then just modify the above SQL (by Lee Dise) to work with that table.

Your original method - from first post - would IMHO not work the way you described, because once you insert the first row with '86', no other rows will be inserted for the same organization, because the where condition would not be met (there already is a row with this Org_ID in the table, i.e. the one you currently inserted). That is, you would not achieve the desired result described in the same post.

Could you please describe a little more of the background - what does the table mean and why do you need to fill it this way? Maybe there is some other way to solve it....

HTH, Vladan

David Durkalski SSC Enthusiast Points: 102 More actions · Answer 6

If I'm working with one-time chunks of data that need to be inserted (or other commands such as update or delete), I often use an Excel spreadsheet to build the command strings for me. An Excel formula such as:

="insert into table1(column1, column2) values (" & A1 & "," & A2 & ")"

will take the values in a1 and a2 and put them into the SQL string. Then you can propagate that formula all the way down the column for as many values as you have.

When you're done, just copy that column with the formulas and paste into Query Analyzer and run the statements. The paste operation should paste the values that the formulas generate rather than the formulas themselves.

Sometimes it's just easier to work in Excel because it's copy-and-paste operations are so flexible and cooperative with other programs.

Cheers,

d.