Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Stuck on new fault with my update Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 9:18 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:55 PM
Points: 23,043, Visits: 31,567
The one thing I would suggest reading about is the MERGE statement. This may be what you really need.

Start here: http://msdn.microsoft.com/en-us/library/bb510625(v=sql.100).aspx.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1427745
Posted Wednesday, March 6, 2013 9:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
ok Joe no worries

Could you tel lme what SSMS is


Thanks
Alan
Post #1427746
Posted Wednesday, March 6, 2013 9:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:55 PM
Points: 23,043, Visits: 31,567
alan_lynch (3/6/2013)
ok Joe no worries

Could you tel lme what SSMS is


Thanks
Alan


SQL Server Management Studio. Just happens to be the main tool we all use when working with MS SQL Server.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1427747
Posted Wednesday, March 6, 2013 9:30 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 961, Visits: 943
Hey Alan,

I am basically guessing here what data looks like. If you could provide more detail you would be able to get additional expert advice.

If your PK ID is software_name_raw then replace "ID" with "software_name_raw"

Also in the sub query (LEFT JOIN (sub query)) uses group by for name and amended name. If the values are unique in the table for name and amended name then this is not required and you can do a straight LEFT JOIN to the table.

The concept is I propose is to use temp table to gather all data and mark which rows should be inserted or updated (ToInsert). Then use this data to do Insert and Update.

If this were the same table and you are on SQL Server 2008 or greater you could use MERGE which I have been using a lot for my projects.
Post #1427750
Posted Thursday, March 7, 2013 5:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
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
Post #1427902
Posted Thursday, March 7, 2013 5:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:55 PM
Points: 23,043, Visits: 31,567
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1427921
Posted Thursday, March 7, 2013 3:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
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
Post #1428298
Posted Thursday, March 7, 2013 3:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:55 PM
Points: 23,043, Visits: 31,567
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1428302
Posted Thursday, March 7, 2013 3:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
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'



Post #1428308
Posted Thursday, March 7, 2013 3:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
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
Post #1428309
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse