Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

SQL Syntax Populate all sites all items

I have a situation where I am trying to protect users from themselves. In this case there are three tables involved:
1) Site table - PK - LOCNCODE
2) Item table - PK - ITEMNMBR
3) Item/Site table - PK - ITEMNMBR,LOCNCODE

I am looking to convert my old script that uses cursors to one that does not. I need to schedule a script to run every hour that insures that the Item/Site table always has a record for every item at every site. So let's assume my site table is called SITEMSTR, the item table is called ITEMMSTR and the item/Site table is called ITEMSITE.

The outer loop is a select for every item
The inner loop is a select for every site
Inside the loop it has to look for the item/site combination in the item/site table and if a record does not exist, create it using the current values for item and site.
0
rwheeler23
Asked:
rwheeler23
  • 4
1 Solution
 
rwheeler23Author Commented:
I forgot to mention we are currently using SQL Server 2008 R2.
0
 
Anthony PerkinsCommented:
In a word:  Why?

Why are you relying on an hourly job?  Why not fix your code so that it does not happen?  If you cannot find a way then add a TRIGGER on the last table to be added to check for this situation and INSERT if necessary.  If you are not sure which is the last table add a TRIGGER on both.
0
 
Surendra NathTechnology LeadCommented:
you can do that with the below code

;WITH C AS
(
SELECT distinct I.ITEMNMBR,S.LOCNCODE
FROM  ITEMMSTR  I
CROSS JOIN SITEMSTR S
)
INSERT INTO ITEMSITE
SELECT * FROM C
WHERE NOT EXISTS ( SELECT 1 FROM ITEMSITE IS WHERE IS.ITEMNMBR = C.ITEMNMBR and IS.LOCNCODE = C.LOCNCODE )

Open in new window

0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
rwheeler23Author Commented:
The reason why is that I have no control over when new sites or items are being added and I have grown tired of getting the frantic call that a part could not be ordered for a site because the item/site combination does not exist. The application is an ERP solution and we are not allowed to make any alternations to any table. Not to mention I would need two triggers. One when a new item is added and another when a new site is added. These would be two different tables. The snippet of code provided by Surendra gives me the idea of the cross join which will provide the solution.
0
 
rwheeler23Author Commented:
I should also add this company is using this ERP solution in a non-standard way. This ERP solution is from Microsoft and we have no access to the code. If I did then I would fix the code or at the very least add the triggers. Both of these were good suggestions.
0
 
rwheeler23Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now