Load data and exclude What Already exists
-
--- l have this script that l wrote but l'm having problems trying
-- to get it to work. l want to populate tbDevBalances1 with records
-- from tbDevBalances but only records that do not already exist in tbDevBalances1
-- second question is how l want to ignore all records with errors and write them out to
-- an exceptions table AND FLAG THE CUSTCODE AND THE ERROR TEXT Need some advice and guidance
----Populate the Balances Table
DECLARE @Rows INT
DECLARE @ErrorText Varchar(300)
DECLARE @TableName VARCHAR(20)
----Set the Variable Of the Object
SET @TableName = 'tbDevBalances'
---Insert Today's Download
INSERT INTO tbDevBalances1
(
RecordType
, CustCode
, BalanceOutstanding
, PastDue
, CurrentDue
, RepayAmount
, SettlementAmount
, DateApproved
, LastPmtDate
, LastPmtAmount
 
---Insert records from tbDevD2TBalances into that do not exist in table tbDevD2TBalances1
EXECUTE ('SELECT DISTINCT
a.RecordType
, a.CustCode
, a.BalanceOutstanding
, a.PastDue
, a.CurrentDue
, a.RepayAmount
, a.SettlementAmount
, a.DateApproved
, a.LastPmtDate
, a.LastPmtAmount
FROM tbDevBalances1 a
WHERE
NOT EXISTS
(
SELECT
RecordType
, CustCode
, BalanceOutstanding
, PastDue
, CurrentDue
, RepayAmount
, SettlementAmount
, DateApproved
, LastPmtDate
, LastPmtAmount
FROM tbDevBalances
)')
-- SET @ErrorText = ('An error has occured and this record will not be loaded' +''+ Convert(varchar(8),'@Rows')
--
-- IF @@Error <> 0
SET @Rows = @@ROWCOUNT
ELSE
INSERT INTO tbLoadAudit
SELECT
@TableName
,CASE
WHEN CONVERT(INT,@Rows) > 0
THEN CONVERT(Varchar(30),@Rows) +''+ ' Records Were Inserted Successfully'
ELSE Convert(Varchar(30),@Rows) +''+ ' No Records Were Inserted'
END
,GETDATE()
Select * from tbLoadAudit
--Create Table DBO.tbLoadAudit (TableName Varchar(30),Records Varchar(60),RunDate Datetime,RunId INT IDENTITY(1,1))
Ranga
-
When I do this I usually load the new data into a staging table, I never load data directly to online tables, and then merge the old and new using an outer join based on the PK to insert the new records only.
If you need an example script let me know.
-
I guess I'm probably underthinking this, because I'm no S.Q.L. expert but here's how I would approach this (the first part):
Insert Into [Table1] (field1,field2.....) select distinct (field1,field2....) from [Table2] where (field1 Not In (select field1 from Table1 where....))
-
l would like an axample script.Thx in advance
Ranga
-
INSERT INTO Exceptions
(
RecordType,
CustCode,
BalanceOutstanding,
PastDue,
CurrentDue,
RepayAmount,
SettlementAmount,
DateApproved,
LastPmtDate,
LastPmtAmount
)
SELECT
a.RecordType
a.CustCode
a.BalanceOutstanding
a.PastDue
a.CurrentDue
a.RepayAmount
a.SettlementAmount
a.DateApproved
a.LastPmtDate
a.LastPmtAmount
FROM tbDevBalances1 a
INNER JOIN tbDevBalances b
ON b.RecordType = a.RecordType
AND b.CustCode = a.CustCode
AND b.BalanceOutstanding = a.BalanceOutstanding
AND b.PastDue = a.PastDue
AND b.CurrentDue = a.CurrentDue
AND b.RepayAmount = a.RepayAmount
AND b.SettlementAmount = a.SettlementAmount
AND b.DateApproved = a.DateApproved
AND b.LastPmtDate = a.LastPmtDate
AND b.LastPmtAmount = a.LastPmtAmount
INSERT INTO tbDevBalances
(
RecordType,
CustCode,
BalanceOutstanding,
PastDue,
CurrentDue,
RepayAmount,
SettlementAmount,
DateApproved,
LastPmtDate,
LastPmtAmount
)
SELECT
a.RecordType
a.CustCode
a.BalanceOutstanding
a.PastDue
a.CurrentDue
a.RepayAmount
a.SettlementAmount
a.DateApproved
a.LastPmtDate
a.LastPmtAmount
FROM tbDevBalances1 a
LEFT OUTER JOIN tbDevBalances b
ON b.RecordType = a.RecordType
AND b.CustCode = a.CustCode
AND b.BalanceOutstanding = a.BalanceOutstanding
AND b.PastDue = a.PastDue
AND b.CurrentDue = a.CurrentDue
AND b.RepayAmount = a.RepayAmount
AND b.SettlementAmount = a.SettlementAmount
AND b.DateApproved = a.DateApproved
AND b.LastPmtDate = a.LastPmtDate
AND b.LastPmtAmount = a.LastPmtAmount
WHERE b.RecordType IS NULL
SET @Rows = @@ROWCOUNT
INSERT INTO tbLoadAudit
(
TableName,
Records,
RunDate,
)
SELECT
@TableName,
CASE
WHEN CONVERT(INT,@Rows) > 0
THEN CONVERT(Varchar(30),@Rows) +''+ ' Records Were Inserted Successfully'
ELSE Convert(Varchar(30),@Rows) +''+ ' No Records Were Inserted'
END,
GETDATE()
Far away is close at hand in the images of elsewhere.
Anon. -
Dave,
just a note on this
LEFT OUTER JOIN
..
WHERE ... IS NULL
While this solution surely works, you'll find the same rewritten to use (NOT) EXISTS to perform generally better. Try it out
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Generally I agree with you, the only difference is in the Merge Join, either a 'Right Outer Join' or 'Right Anti Semi Join'.
It all depends on volume, the higher the hit rate the slower the NOT EXISTS performance.
So if the hit rate is always guaranteed to be small then the NOT EXISTS would have the better performance.
Far away is close at hand in the images of elsewhere.
Anon. -
Sorry, but I have never seen a Merge Join when you use NOT EXISTS. You should always get Nested Loops.
From my observations I have seen NOT EXISTS performing better (and it's more intuitive, to me anyway) than OUTER JOIN. But the only way to find this out is to test it in the specific environment.
FWIW, here a link to a good summary by Itzik Ben-Gan on the MS newsgroups on this topic:
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply