March 7, 2013 at 5:52 am
alan_lynch (3/7/2013)
right Lynn I will try again.I have had a look at your merge statement which has been useful the only thing is i have one error from the query below.
Please bare in mind it works fine as it is, but when I add a duplicate row to the Source(BigTable) for my test data-- like so ('Microsoft Office 2003 Professional'),
I get the error message
Msg 8672, Level 16, State 1, Line 9
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.
in my production data I will have many many rows the same from the source table but they are to be unique in the lookuptable
drop table BigTable --Drop SOURCE
drop table RefTable --Drop Lookup
--Create a Source table
CREATE TABLE BigTable
(
software_name_raw VARCHAR(255)
)
GO
--Insert records into Source table
INSERT INTO BigTable
VALUES
('Microsoft Office 2003'),
('Microsoft 2003 Office'),
('Microsoft Office 2003 Professional'),
('Sun Microsystems')
GO
--Create LOOKUP table
CREATE TABLE RefTable
(
software_name_raw VARCHAR(255) PRIMARY KEY,
software_name_amended Varchar (255)
)
GO
--Insert records into Lookup table
INSERT INTO RefTable
VALUES
('Microsoft Office 2003', 'Office 2003'),
('Microsoft 2003 Office', 'Office 2003'),
('Microsoft Office 2003 Professional', 'Office 2003'),
('Adobe', 'Adobe Inc')
GO
SELECT * FROM BigTable as Source_TABLE
SELECT * FROM RefTable as Lookup_TABLE
GO
--Next I will use the MERGE SQL command to synchronize the target table
--with the refreshed data coming from the LOOKUP table.
--MERGE SQL statement – Part 2
--Synchronize the lookup table with
--refreshed data from source table
MERGE RefTable AS TARGET
USING BigTable AS SOURCE
ON (TARGET.software_name_raw = SOURCE.software_name_raw)
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.software_name_raw = SOURCE.software_name_raw THEN
UPDATE SET TARGET.software_name_raw = SOURCE.software_name_raw
--When no records are matched, insert the incoming records from source into the lookup table
WHEN NOT MATCHED BY TARGET THEN
INSERT (software_name_raw, software_name_amended)
VALUES (SOURCE.software_name_raw, 'Needs Updating')
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: ‘INSERT’, ‘UPDATE’, or ‘DELETE’,
--according to the action that was performed on that row
OUTPUT $action,
DELETED.software_name_raw AS Lookupsoftware_name_raw,
DELETED.software_name_amended AS Lookupoftware_name_amended,
INSERTED.software_name_raw AS Sourcesoftware_name_raw,
INSERTED.software_name_amended AS Sourcesoftware_name_amended;
GO
select * from BigTable as source_table
select * from RefTable as lookuptable
I am still at a slight loss here. Based on the limited sample data, that obviously does not reflect your problem domain as it doesn't appear to reflect the error you get, I am trying to figure out why you would want to update values that match. Why update 'Microsoft Office 2003' to 'Microsoft Office 2003' just because it is in the list? Now that you provided a bit more detail, it looks to me like what you really need to do is insert data that does not already exist in the Target Table and ignore the data that does, unless there is more going on than you are showing here.
March 7, 2013 at 3:29 pm
Lynn
I just downloaded this sample code from a website and amended accordingly and your right I do not want to update data that already exists so thanks for pointing that out to me.
Finally yes as you say all I want to do is
insert data that does not already exist in the Target Table and ignore the data that does
thanks
March 7, 2013 at 3:35 pm
Try this:
drop table BigTable --Drop SOURCE
drop table RefTable --Drop Lookup
--Create a Source table
CREATE TABLE BigTable
(
software_name_raw VARCHAR(255)
)
GO
--Insert records into Source table
INSERT INTO BigTable
VALUES
('Microsoft Office 2003'),
('Microsoft 2003 Office'),
('Microsoft Office 2003 Professional'),
('Sun Microsystems')
GO
--Create LOOKUP table
CREATE TABLE RefTable
(
software_name_raw VARCHAR(255) PRIMARY KEY,
software_name_amended Varchar (255)
)
GO
--Insert records into Lookup table
INSERT INTO RefTable
VALUES
('Microsoft Office 2003', 'Office 2003'),
('Microsoft 2003 Office', 'Office 2003'),
('Microsoft Office 2003 Professional', 'Office 2003'),
('Adobe', 'Adobe Inc')
GO
SELECT * FROM BigTable as Source_TABLE
SELECT * FROM RefTable as Lookup_TABLE
GO
insert into RefTable(software_name_raw,software_name_amended)
select
bt.software_name_raw,
'Needs Updating'
from
BigTable bt
where
not exists(select 1 from RefTable rf where rf.software_name_raw = bt.software_name_raw);
select * from RefTable;
March 7, 2013 at 3:49 pm
Hi Lynn
just me again I forgot to mention that yes I want to insert data that does not already exist in the Target Table but if it does exist then I want to update the source table value with col2 value
Target
col1,col2
a,'test1'
b,'test2'
c,'test3'
Source
col1
a,
b,
c,
d
inserted expected results in target
a,'test1' -------------------------------------Matched 'a' so update Source with Targets second column value
b,'test2' -------------------------------------Matched 'b' so update Source with Targets second column value
c,'test3' -------------------------------------Matched 'c' so update Source with Targets second column value
d,'needs updating' -----------------------------Was NO so Match got inserted
expected results in Source
'test1'
'test2'
'test3'
March 7, 2013 at 3:52 pm
that was quick i was still mentioning the last thing lol
anyway the code you have just provided is perfect but I still wish to overwrite the first column in the other table with 'Office 2003 professional' etc when they do match
March 7, 2013 at 9:26 pm
alan_lynch (3/7/2013)
Hi Lynnjust me again I forgot to mention that yes I want to insert data that does not already exist in the Target Table but if it does exist then I want to update the source table value with col2 value
Target
col1,col2
a,'test1'
b,'test2'
c,'test3'
Source
col1
a,
b,
c,
d
inserted expected results in target
a,'test1' -------------------------------------Matched 'a' so update Source with Targets second column value
b,'test2' -------------------------------------Matched 'b' so update Source with Targets second column value
c,'test3' -------------------------------------Matched 'c' so update Source with Targets second column value
d,'needs updating' -----------------------------Was NO so Match got inserted
expected results in Source
'test1'
'test2'
'test3'
SO you if there is match in Target, change the Source. If there is no match in Target, add source to target. This right??
March 7, 2013 at 10:19 pm
Hi Lynn
Yes thats right
regards
Alan
March 8, 2013 at 7:25 am
this:
if exists(select 1 from sys.tables where object_id = object_id('dbo.BigTable'))
drop table BigTable; --Drop SOURCE
if exists(select 1 from sys.tables where object_id = object_id('dbo.RefTable '))
drop table RefTable; --Drop Lookup
--Create a Source table
CREATE TABLE BigTable
(
software_name_raw VARCHAR(255)
)
GO
--Insert records into Source table
INSERT INTO BigTable
VALUES
('Microsoft Office 2003'),
('Microsoft 2003 Office'),
('Microsoft Office 2003 Professional'),
('Sun Microsystems')
GO
--Create LOOKUP table
CREATE TABLE RefTable
(
software_name_raw VARCHAR(255) PRIMARY KEY,
software_name_amended Varchar (255)
)
GO
--Insert records into Lookup table
INSERT INTO RefTable
VALUES
('Microsoft Office 2003', 'Office 2003'),
('Microsoft 2003 Office', 'Office 2003'),
('Microsoft Office 2003 Professional', 'Office 2003'),
('Adobe', 'Adobe Inc')
GO
SELECT * FROM BigTable as Source_TABLE
SELECT * FROM RefTable as Lookup_TABLE
GO
insert into RefTable(software_name_raw,software_name_amended)
select
bt.software_name_raw,
'Needs Updating'
from
BigTable bt
where
not exists(select 1 from RefTable rf where rf.software_name_raw = bt.software_name_raw);
select * from RefTable;
update bt set
software_name_raw = rt.software_name_amended
from
BigTable bt
inner join RefTable rt
on (bt.software_name_raw = rt.software_name_raw)
where
rt.software_name_amended <> 'Needs Updating';
select * from BigTable;
GO
if exists(select 1 from sys.tables where object_id = object_id('dbo.BigTable'))
drop table BigTable; --Drop SOURCE
if exists(select 1 from sys.tables where object_id = object_id('dbo.RefTable '))
drop table RefTable; --Drop Lookup
GO
March 10, 2013 at 6:19 pm
Hi Lynn
That sort of works but when I run just this part for the second time I get.
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__RefTable__6897329A11007AA7'. Cannot insert duplicate key in object 'dbo.RefTable'.
The statement has been terminated.
insert into RefTable(software_name_raw,software_name_amended)
select
bt.software_name_raw,
'Needs Updating'
from
BigTable bt
where
not exists(select 1 from RefTable rf where rf.software_name_raw = bt.software_name_raw);
select * from RefTable;
update bt set
software_name_raw = rt.software_name_amended
from
BigTable bt
inner join RefTable rt
on (bt.software_name_raw = rt.software_name_raw)
where
rt.software_name_amended <> 'Needs Updating';
select * from BigTable;
GO
March 10, 2013 at 9:58 pm
alan_lynch (3/10/2013)
Hi LynnThat sort of works but when I run just this part for the second time I get.
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__RefTable__6897329A11007AA7'. Cannot insert duplicate key in object 'dbo.RefTable'.
The statement has been terminated.
insert into RefTable(software_name_raw,software_name_amended)
select
bt.software_name_raw,
'Needs Updating'
from
BigTable bt
where
not exists(select 1 from RefTable rf where rf.software_name_raw = bt.software_name_raw);
select * from RefTable;
update bt set
software_name_raw = rt.software_name_amended
from
BigTable bt
inner join RefTable rt
on (bt.software_name_raw = rt.software_name_raw)
where
rt.software_name_amended <> 'Needs Updating';
select * from BigTable;
GO
Looks like you are trying to use the same data as input after you have updated it. What would you expect to happen?
March 10, 2013 at 10:10 pm
Hi Lynn
Yeah very close to the expected result but the RefTable has a primary key on it. so if it encounters a duplicate record from the other table it also tries to insert that
the table that I am going to be querying may have duplicate values init
eg
INSERT INTO BigTable
VALUES
('Microsoft Office 2003'),
('Microsoft 2003 Office'),
('Microsoft Office 2003 Professional'),
('Sun Microsystems'),
('Sun Microsystems'),
('Test'),
('Test'),
('Test')
March 10, 2013 at 10:37 pm
Hi Lynn
I have added a group by clause on the end like so which seems to work
insert into RefTable(software_name_raw,software_name_amended)
select
bt.software_name_raw,
'Needs Updating'
from
BigTable bt
where
not exists (select 1 from RefTable rf where rf.software_name_raw = bt.software_name_raw)
group by bt.software_name_raw;
would you agree that this works and we can close the case
thanks
Alan
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply