Click here to Skip to main content
15,169,069 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I have a stored procedure where i am using a Insert Select Statement to insert specific records.

Insert into Test.dbo.G_Record 
Select * from Test.dbo.InformationCurrent c
INNER JOIN Test.dbo.VendorInformation pr  (NOLOCK) ON c.MarketNumber = pr.MarketNumber AND c.FeeSchedule = pr.VendorFeeSchedule AND c.Specialty = pr.Specialty	
INNER JOIN Test.dbo.Translation s  (NOLOCK) ON c.Specialty = s.NMDB_Specialty
(c.ClientID = '287' AND pr.VendorProductIndicator IN ('SX','SY','SZ'))
OR  ( c.ClientID = '559' AND pr.VendorProductIndicator = 'D7' ) 

Now i have a new table Temp(ClientID,ProductIndicator) with data as follows

ClientID ProductIndicator
288 SX
288 SI
256 SJ
256 SY

Now i have to add these values to the above insert select statement's where condition like below statements.
(clientID='288' AND ProductIndicator IN('SX','SI')) OR
(clientID='256' AND ProductIndicator IN('SJ','SY'))

As similar to above lines have to be added to the exisiting Insert select query for any number of records in the new Temp table automatically without hard coding.

Thanks in advance...

1 solution

I would pass up an XML such as:

  <c clientid="287">
    <vpi product="SX" />
    <vpi product="SY" />
    <vpi product="SZ" />
  <c clientid="559">
    <vpi product="D7" />
  <c clientid="288">
    <vpi product="SX" />
    <vpi product="SI" />


You can then select against this structure to product a table representing these values:

He're an example on selecting a data set from XML

Select values from XML field in SQL Server 2008[^]

Client Product
287    SX
287    SY
287    SZ
559    D7
288    SX
287    SI

You then INNER JOIN this to your product database to filter out the desired products.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900