August 4, 2003 at 7:23 am
All,
I am having some trouble figuring out how to populate a table with missing records. The table in question looks like this:
CREATE TABLE dbo.DIVISION_LOOKUP
(
SYSTEM_CD varchar(2) NOT NULL,
DIVISION_CD varchar(3) NOT NULL,
GROUP_TXT varchar(60) NULL,
BATCH_ID bigint NULL,
CONSTRAINT XPKDIVISION_LOOKUP
PRIMARY KEY CLUSTERED (SYSTEM_CD,DIVISION_CD) WITH FILLFACTOR=90
ON [PRIMARY]
)
In this table there are three values for the SYSTEM_CD column: '60', '70', '80'. Currently, there are DIVISION_CD values that may or may not exist for all three SYSTEM_CD values. What I need to do is make sure that all SYSTEM_CD have the same DIVISION_CD and corresponding GROUP_TXT (description of the DIVISION_CD).
One way to approach this would be to read all the records for System Code 60 and check for the existence of that Division Code for 70 and 80. If the Division Code does not exist for 70 and/or 80, insert the record using the same Group Txt as the 60 record and using current date in YYYYMMDD format in the Batch ID. Then repeat this logic for System Code 70 and then for 80. I've racked my brain and can't, for the life of me, figured out how to accomplish this with T-SQL. Can anyone help me?
TIA,
Darias Brown
August 4, 2003 at 7:40 am
To get a list of all records that do not have matching records, use the following query:
SELECT dlo.DIVISON_CD, dlo.GROUP_TXT,
SC60.DIVISON_CD AS SYS60,
SC70.DIVISON_CD AS SYS70,
SC80.DIVISON_CD AS SYS80
FROM DIVISON_LOOKUP dlo
LEFT OUTER JOIN
(SELECT DIVISON_CD
FROM DIVISON_LOOKUP
WHERE SYSTEM_CD = '60') SC60
ON dlo.DIVISION_CD = SC60.DIVISION_CD
LEFT OUTER JOIN
(SELECT DIVISON_CD
FROM DIVISON_LOOKUP
WHERE SYSTEM_CD = '70') SC70
ON dlo.DIVISION_CD = SC70.DIVISION_CD
LEFT OUTER JOIN
(SELECT DIVISON_CD
FROM DIVISON_LOOKUP
WHERE SYSTEM_CD = '80') SC80
ON dlo.DIVISION_CD = SC80.DIVISION_CD
WHERE SC60.DIVISON_CD = NULL
OR SC70.DIVISON_CD = NULL
OR SC80.DIVISON_CD = NULL
August 4, 2003 at 3:13 pm
Thanks for the query. It works perfectly.
Darias
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply