December 13, 2011 at 3:46 am
I am looking at incremental loading and currently use this as my method of copying data from database to database, but this is a little on the slow side when I am doing this for around 40 tables every hour. I have been reading about MERGE INTO, but not quite sure as to how my existing queries would fit into how MERGE INTO works...
Could someone give me an idea of how I can do this?
[Code]
UPDATE d
SET d.id = s.id
,d.class = s.class
,d.vstp = s.vstp
,d.rie = s.rie
,d.sys_creation_datetime = s.sys_creation_datetime
,d.sys_lastupdate_datetime = s.sys_lastupdate_datetime
,d.sys_creation_user = s.sys_creation_user
,d.sys_lastupdate_user = s.sys_lastupdate_user
,d.patient = s.patient
,d.icabreferr = s.icabreferr
,d.referralde = s.referralde
,d.dos = s.dos
,d.currentsta = s.currentsta
,d.carecontex = s.carecontex
,d.consultati = s.consultati
,d.hasinvesti = s.hasinvesti
,d.hasappoint = s.hasappoint
,d.hasdocumen = s.hasdocumen
,d.hasreferra = s.hasreferra
,d.iscab = s.iscab
,d.lkp_additional = s.lkp_additional
,d.rejectrefe = s.rejectrefe
,d.providerca = s.providerca
,d.currentrev = s.currentrev
,d.hasrejecte = s.hasrejecte
,d.isflaggedf = s.isflaggedf
,d.lkp_onwardrefe = s.lkp_onwardrefe
,d.uniqueline = s.uniqueline
,d.hasconsult = s.hasconsult
,d.lkp_isfinalrep = s.lkp_isfinalrep
,d.discharged = s.discharged
,d.triagedate = s.triagedate
,d.lastapptar = s.lastapptar
,d.hastltstar = s.hastltstar
,d.issuitable = s.issuitable
,d.isfitforsu = s.isfitforsu
,d.hastheatre = s.hastheatre
,d.isaccepted = s.isaccepted
,d.isreferral = s.isreferral
,d.waspatient = s.waspatient
,d.waspatien2 = s.waspatien2
,d.requiresdi = s.requiresdi
,d.hasdnaappt = s.hasdnaappt
,d.hascancell = s.hascancell
,d.lkp_issecondar = s.lkp_issecondar
,d.wasfurther = s.wasfurther
,d.iscurrentl = s.iscurrentl
,d.lkp_onwardref2 = s.lkp_onwardref2
,d.wasoutputt = s.wasoutputt
,d.wasoutput2 = s.wasoutput2
,d.lkp_discharget = s.lkp_discharget
,d.c_contra = s.c_contra
,d.awaitingcl = s.awaitingcl
,d.isawaiting = s.isawaiting
,d.isactivemo = s.isactivemo
,d.catsreport = s.catsreport
FROM MyServer.MyDB.dbo.MyTable d
INNER JOIN MyOtherServer.MyOtherDB.MyOtherTable s ON s.id = d.id
WHERE
(
(d.id !=s.id)
OR (d.class COLLATE DATABASE_DEFAULT !=s.class COLLATE DATABASE_DEFAULT)
OR (d.vstp !=s.vstp)
OR (d.rie !=s.rie)
OR (d.sys_creation_datetime !=s.sys_creation_datetime)
OR (d.sys_lastupdate_datetime !=s.sys_lastupdate_datetime)
OR (d.sys_creation_user COLLATE DATABASE_DEFAULT !=s.sys_creation_user COLLATE DATABASE_DEFAULT)
OR (d.sys_lastupdate_user COLLATE DATABASE_DEFAULT !=s.sys_lastupdate_user COLLATE DATABASE_DEFAULT)
OR (d.patient !=s.patient)
OR (d.icabreferr !=s.icabreferr)
OR (d.referralde !=s.referralde)
OR (d.dos !=s.dos)
OR (d.currentsta !=s.currentsta)
OR (d.carecontex !=s.carecontex)
OR (d.consultati !=s.consultati)
OR (d.hasinvesti !=s.hasinvesti)
OR (d.hasappoint !=s.hasappoint)
OR (d.hasdocumen !=s.hasdocumen)
OR (d.hasreferra !=s.hasreferra)
OR (d.iscab !=s.iscab)
OR (d.lkp_additional !=s.lkp_additional)
OR (d.rejectrefe !=s.rejectrefe)
OR (d.providerca !=s.providerca)
OR (d.currentrev !=s.currentrev)
OR (d.hasrejecte !=s.hasrejecte)
OR (d.isflaggedf !=s.isflaggedf)
OR (d.lkp_onwardrefe !=s.lkp_onwardrefe)
OR (d.uniqueline COLLATE DATABASE_DEFAULT !=s.uniqueline COLLATE DATABASE_DEFAULT)
OR (d.hasconsult !=s.hasconsult)
OR (d.lkp_isfinalrep !=s.lkp_isfinalrep)
OR (d.discharged !=s.discharged)
OR (d.triagedate !=s.triagedate)
OR (d.lastapptar !=s.lastapptar)
OR (d.hastltstar !=s.hastltstar)
OR (d.issuitable !=s.issuitable)
OR (d.isfitforsu !=s.isfitforsu)
OR (d.hastheatre !=s.hastheatre)
OR (d.isaccepted !=s.isaccepted)
OR (d.isreferral !=s.isreferral)
OR (d.waspatient !=s.waspatient)
OR (d.waspatien2 !=s.waspatien2)
OR (d.requiresdi !=s.requiresdi)
OR (d.hasdnaappt !=s.hasdnaappt)
OR (d.hascancell !=s.hascancell)
OR (d.lkp_issecondar !=s.lkp_issecondar)
OR (d.wasfurther !=s.wasfurther)
OR (d.iscurrentl !=s.iscurrentl)
OR (d.lkp_onwardref2 !=s.lkp_onwardref2)
OR (d.wasoutputt !=s.wasoutputt)
OR (d.wasoutput2 !=s.wasoutput2)
OR (d.lkp_discharget !=s.lkp_discharget)
OR (d.c_contra !=s.c_contra)
OR (d.awaitingcl !=s.awaitingcl)
OR (d.isawaiting !=s.isawaiting)
OR (d.isactivemo !=s.isactivemo)
OR (d.catsreport !=s.catsreport)
)
[/code]
INSERT INTO MyServer.MyDB.dbo.MyTable
([id]
,[class]
,[vstp]
,[rie]
,[sys_creation_datetime]
,[sys_lastupdate_datetime]
,[sys_creation_user]
,[sys_lastupdate_user]
,[patient]
,[icabreferr]
,[referralde]
,[dos]
,[currentsta]
,[carecontex]
,[consultati]
,[hasinvesti]
,[hasappoint]
,[hasdocumen]
,[hasreferra]
,[iscab]
,[lkp_additional]
,[rejectrefe]
,[providerca]
,[currentrev]
,[hasrejecte]
,[isflaggedf]
,[lkp_onwardrefe]
,[uniqueline]
,[hasconsult]
,[lkp_isfinalrep]
,[discharged]
,[triagedate]
,[lastapptar]
,[hastltstar]
,[issuitable]
,[isfitforsu]
,[hastheatre]
,[isaccepted]
,[isreferral]
,[waspatient]
,[waspatien2]
,[requiresdi]
,[hasdnaappt]
,[hascancell]
,[lkp_issecondar]
,[wasfurther]
,[iscurrentl]
,[lkp_onwardref2]
,[wasoutputt]
,[wasoutput2]
,[lkp_discharget]
,[c_contra]
,[awaitingcl]
,[isawaiting]
,[isactivemo]
,[catsreport])
SELECT s.id
,s.class
,s.vstp
,s.rie
,s.sys_creation_datetime
,s.sys_lastupdate_datetime
,s.sys_creation_user
,s.sys_lastupdate_user
,s.patient
,s.icabreferr
,s.referralde
,s.dos
,s.currentsta
,s.carecontex
,s.consultati
,s.hasinvesti
,s.hasappoint
,s.hasdocumen
,s.hasreferra
,s.iscab
,s.lkp_additional
,s.rejectrefe
,s.providerca
,s.currentrev
,s.hasrejecte
,s.isflaggedf
,s.lkp_onwardrefe
,s.uniqueline
,s.hasconsult
,s.lkp_isfinalrep
,s.discharged
,s.triagedate
,s.lastapptar
,s.hastltstar
,s.issuitable
,s.isfitforsu
,s.hastheatre
,s.isaccepted
,s.isreferral
,s.waspatient
,s.waspatien2
,s.requiresdi
,s.hasdnaappt
,s.hascancell
,s.lkp_issecondar
,s.wasfurther
,s.iscurrentl
,s.lkp_onwardref2
,s.wasoutputt
,s.wasoutput2
,s.lkp_discharget
,s.c_contra
,s.awaitingcl
,s.isawaiting
,s.isactivemo
,s.catsreport
FROM MyOtherServer.MyOtherDB.MyOtherTable s
LEFT OUTER JOIN MyServer.MyDB.dbo.MyTable d ON d.id = s.id
WHERE d.id IS NULL
December 13, 2011 at 4:39 am
January 9, 2012 at 8:51 am
R.P.Rozema (12/13/2011)
http://www.sqlservercentral.com/Forums/Topic1168980-392-1.aspx#bm1169083
I have been on the link and come up with this, my problem is that I get an error which I dont understand would affect things...
Error
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__care_catsreferra__693CA210'. Cannot insert duplicate key in object 'dbo.care_catsreferral'.
The statement has been terminated.
MERGE MyServer.MyDB.dbo.MyTable d
USING MyOtherServer.MyOtherDB.MyOtherTable s
ON
(
d.id = s.id
AND d.class = s.class
AND d.vstp = s.vstp
AND d.rie = s.rie
AND d.sys_creation_datetime = s.sys_creation_datetime
AND d.sys_lastupdate_datetime = s.sys_lastupdate_datetime
AND d.sys_creation_user = s.sys_creation_user
AND d.sys_lastupdate_user = s.sys_lastupdate_user
AND d.patient = s.patient
AND d.icabreferr = s.icabreferr
AND d.referralde = s.referralde
AND d.dos = s.dos
AND d.currentsta = s.currentsta
AND d.catsreport = s.catsreport
)
WHEN NOT MATCHED BY TARGET
THEN
INSERT (id, class, vstp, rie, sys_creation_datetime, sys_lastupdate_datetime, sys_creation_user, sys_lastupdate_user, patient,
icabreferr, referralde, dos, currentsta, catsreport)
VALUES (s.id, s.class, s.vstp, s.rie, s.sys_creation_datetime, s.sys_lastupdate_datetime, s.sys_creation_user, s.sys_lastupdate_user,
s.patient, s.icabreferr, s.referralde, s.dos, s.currentsta, s.catsreport)
WHEN MATCHED
THEN
UPDATE SET d.id = s.id
,d.class = s.class
,d.vstp = s.vstp
,d.rie = s.rie
,d.sys_creation_datetime = s.sys_creation_datetime
,d.sys_lastupdate_datetime = s.sys_lastupdate_datetime
,d.sys_creation_user = s.sys_creation_user
,d.sys_lastupdate_user = s.sys_lastupdate_user
,d.patient = s.patient
,d.icabreferr = s.icabreferr
,d.referralde = s.referralde
,d.dos = s.dos
,d.currentsta = s.currentsta
,d.catsreport = s.catsreport
;
This is my table structure for both the source and destination
CREATE TABLE [dbo].[MyTable](
[id] [numeric](10, 0) NOT NULL,
[class] [varchar](4) NULL,
[vstp] [int] NOT NULL,
[rie] [tinyint] NULL,
[sys_creation_datetime] [datetime] NULL,
[sys_lastupdate_datetime] [datetime] NULL,
[sys_creation_user] [varchar](30) NULL,
[sys_lastupdate_user] [varchar](30) NULL,
[patient] [numeric](10, 0) NOT NULL,
[icabreferr] [numeric](10, 0) NULL,
[referralde] [numeric](10, 0) NULL,
[dos] [numeric](10, 0) NULL,
[currentsta] [numeric](10, 0) NULL,
[catsreport] [datetime] NULL,
CONSTRAINT [PK__MyTable__693CA210] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Where have I gone wrong?
I dont want to insert any duplicates, as there wouldnt be any, does it say this error when its updating records?
January 9, 2012 at 10:45 am
The ON clause is used to specify conditions to determine if source and target rows join or not. In your case, it seems the ON clause should contain only (d.id = s.id). No match will trigger the INSERT; the other filtering conditions (column has changed) should go in the WHEN MATCHED THEN UPDATE part, that is: WHEN MATCHED AND {filtering conditions} THEN UPDATE SET...
More information and details at http://technet.microsoft.com/en-us/library/cc879317.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 10, 2012 at 3:08 am
SQL Kiwi (1/9/2012)
The ON clause is used to specify conditions to determine if source and target rows join or not. In your case, it seems the ON clause should contain only (d.id = s.id). No match will trigger the INSERT; the other filtering conditions (column has changed) should go in the WHEN MATCHED THEN UPDATE part, that is: WHEN MATCHED AND {filtering conditions} THEN UPDATE SET...More information and details at http://technet.microsoft.com/en-us/library/cc879317.aspx
Excellecnt thanks, works on my test environment. Now for my big test to try it with live data 🙂
Thanks.
January 13, 2012 at 7:16 am
The MERGE has been working very well now, which is great 🙂
One thing I do have a question on is this
I get this error below
Msg 8672, Level 16, State 1, Line 2
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
MERGE MyServer.MyDB.dbo.MyTable d
USING MyOtherServer.MyOtherDB.MyOtherTable s
ON (d.id = s.id)
WHEN MATCHED THEN
UPDATE SET
d.lkp_ordinvstat = s.lkp_ordinvstat
,d.changedate = s.changedate
,d.changeuser = s.changeuser
,d.statusreas = s.statusreas
,d.idx = s.idx
,d.lkp_statuschan = s.lkp_statuschan
WHEN NOT MATCHED BY TARGET THEN
INSERT (id, lkp_ordinvstat, changedate, changeuser, statusreas, idx, lkp_statuschan)
VALUES (s.id, s.lkp_ordinvstat, s.changedate, s.changeuser, s.statusreas, s.idx, s.lkp_statuschan)
;
GO
The id field has multiple id when input as this is a History table, how can I get around the MERGE when a table is like this?
January 13, 2012 at 7:39 am
jez.lisle (1/13/2012)
I get this error belowMsg 8672, Level 16, State 1, Line 2
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
The id field has multiple id when input as this is a History table, how can I get around the MERGE when a table is like this?
The MERGE statement is helping you. Your code is attempting to update the same target row more than once (perhaps with different values):
USE Sandpit
DECLARE @Target TABLE
(
pk integer NOT NULL,
val integer NOT NULL
);
DECLARE @Source TABLE
(
pk integer NOT NULL,
val integer NOT NULL
);
INSERT @Target VALUES (1, 100);
INSERT @Source VALUES (1, 200), (1, 300);
-- Uncomment the next section to see the error.
-- Should the row be updated to 200 or 300?
/*
MERGE @Target AS t
USING @Source AS s ON
s.pk = t.pk
WHEN MATCHED THEN UPDATE
SET val = s.val;
*/
-- Success
MERGE @Target AS t
USING
(
SELECT s.pk, MAX(s.val) AS max_val
FROM @Source AS s
GROUP BY s.pk
) AS s ON
s.pk = t.pk
WHEN MATCHED THEN UPDATE
SET val = s.max_val;
-- Updated to 300
SELECT * FROM @Target AS t
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 13, 2012 at 8:00 am
jez.lisle (1/13/2012)
The MERGE has been working very well now, which is great 🙂One thing I do have a question on is this
I get this error below
Msg 8672, Level 16, State 1, Line 2
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
MERGE MyServer.MyDB.dbo.MyTable d
USING MyOtherServer.MyOtherDB.MyOtherTable s
ON (d.id = s.id)
WHEN MATCHED THEN
UPDATE SET
d.lkp_ordinvstat = s.lkp_ordinvstat
,d.changedate = s.changedate
,d.changeuser = s.changeuser
,d.statusreas = s.statusreas
,d.idx = s.idx
,d.lkp_statuschan = s.lkp_statuschan
WHEN NOT MATCHED BY TARGET THEN
INSERT (id, lkp_ordinvstat, changedate, changeuser, statusreas, idx, lkp_statuschan)
VALUES (s.id, s.lkp_ordinvstat, s.changedate, s.changeuser, s.statusreas, s.idx, s.lkp_statuschan)
;
GO
The id field has multiple id when input as this is a History table, how can I get around the MERGE when a table is like this?
I guess the question on it is, what do you want it to do when there are two rows in the source and one in the target, matched by your On criteria.
Do you want it to pick the data from the last row (per ID or a datetime value?), or pick one at random, or abort the update?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2012 at 8:15 am
I need to be able to Insert or Update the fields depending on what the source table has done.
January 13, 2012 at 8:18 am
jez.lisle (1/13/2012)
I need to be able to Insert or Update the fields depending on what the source table has done.
Yes, but if there are two (or more) rows in the source table, which one do you want to use?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2012 at 7:29 pm
GSquared (1/13/2012)
jez.lisle (1/13/2012)
I need to be able to Insert or Update the fields depending on what the source table has done.Yes, but if there are two (or more) rows in the source table, which one do you want to use?
So, let's assume MyTable contains a row with id = 1, and MyOtherTable contains two rows with id = 1. I would imagine that the two rows in MyOtherTable would have different values for the ChangedDate or ChangeUser columns (and so on). In this case, which value do you want MyTable to contain after the MERGE?
MyTable:
id = 1, ChangeDate = '2010-07-15', ChangeUser = 'Bob', OrdInVStat = 'O'
MyOtherTable:
id = 1, ChangeDate = '2010-07-22', ChangeUser = 'Mary', OrdInVStat = 'P'
id = 1, ChangeDate = '2010-07-24', ChangeUser = 'Jez', OrdInVStat = 'C'
My guess is that you would want to choose the row with the latest ChangeDate per id, but you need to confirm that.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply