SQL Construct
-
HI ALL - first time post - I appreciate any suggestions and/or help
I have 2 tables, 1 CurrentData, 1 ArchivedData. I need to Union these together to see an entire Case population.
With the Unioned dataset I need to test another table, and insert any mis-matches into an 'Unresolved Case' table.
The union is simple, only 1 field. The test only checks the one field.
How do I code:
Select CaseNbr From Table 1
UNION
Select CaseNbr from Table 2
Then have it compare to Table 3 Inserting
CaseNbr into 'Unresolved' where
WHERE CaseNbr in UnionTable IS NULL
Thanks in advance
-
try:
INSERT INTO UNRESOLVED (CaseNbr)
SELECT
T3.CaseNbr
FROM
Table3 T3
LEFT OUTER JOIN
(Select CaseNbr From Table 1
UNION
Select CaseNbr from Table 2
 
QON T3.CaseNbr =Q.CaseNbr
WHERE
Q.CaseNbr is Null
* Noel -
If you run the code presented as is, you will insert only null values into the unresolved table. I assume you are also going to include some other information and you are only using CaseNbr as the key for inserting?
-
The Code Posted is Correct.
if exists (select * from dbo.sysobjects where id = object_id(N'[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Table1]
GO
CREATE TABLE Table1 (CaseNbr int)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Table2]
GO
CREATE TABLE Table2 (CaseNbr int)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[Table3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Table3]
GO
CREATE TABLE Table3 (CaseNbr int)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[Unresolved]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Unresolved]
GO
CREATE TABLE Unresolved (CaseNbr int)
GO
SET NOCOUNT ON
Insert into Table1 values(1)
Insert into Table2 values(2)
Insert into Table3 values(1)
Insert into Table3 values(2)
Insert into Table3 values(3)
INSERT INTO UNRESOLVED (CaseNbr)
SELECT
T3.CaseNbr
FROM
Table3 T3
LEFT OUTER JOIN
(Select CaseNbr From Table1
UNION
Select CaseNbr from Table2
) Q
ON T3.CaseNbr =Q.CaseNbr
WHERE
Q.CaseNbr is Null
SELECT * FROM Unresolved
CaseNbr
-----------
3
qed
* Noel -
Whoops, missed that key left join peice. Heh, Guess it's time for new glasses.
-
Hi Noel and Cliff,
I do appreciate your responses - thank you both.
The code Noel posted works fine - thank you!
I have one remaining objective: I need to record the missing record once.
I accrue the missing cases over the course of a month so I only need to record the first instance of mismatch. The Unresolved table has an GetDate auto dating routine so I will know the 'as of' date.
So my question is: How do I check against the Unresolved table to make sure I don't already have the CaseNbr on file?
Here's the actual code I'm using...
INSERT INTO dbo.FoldingTable (Orig_Value)
SELECT
dbo.CountyParent.CaseNbr
FROM
dbo.CountyParent
LEFT OUTER JOIN
(Select CaseNbr From dbo.CDS_Case WHERE CaseNbr is not null
UNION
Select CaseNbr from dbo.CDS_Case_Archive WHERE CaseNbr is not null
) Q
ON dbo.CountyParent.CaseNbr =Q.CaseNbr
WHERE
Q.CaseNbr is Null AND CountyParent.CaseNbr is not null
Thanks again, your support is great!
-
I think this is what you are after:
INSERT INTO dbo.FoldingTable (Orig_Value)
SELECT
dbo.CountyParent.CaseNbr
FROM
dbo.CountyParent
LEFT OUTER JOIN
(Select CaseNbr From dbo.CDS_Case WHERE CaseNbr is not null
UNION
Select CaseNbr from dbo.CDS_Case_Archive WHERE CaseNbr is not null
) Q
ON dbo.CountyParent.CaseNbr =Q.CaseNbr
WHERE
Q.CaseNbr is Null AND CountyParent.CaseNbr is not null
AND Not Exists (Select 1 From FoldingTable T2 Where T2.Orig_Value = CountyParent.CaseNbr )
* Noel -
That was exactly what I needed! I'm having some teething problems with SQL Server but what I've seen so far is very impressive and you have helped me greatly - Thanks much !
-
You're welcome
* Noel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply