March 6, 2013 at 6:57 pm
Hi Professionals I am running the following query as advised previously which updates the source table based on a column from the reference table matching...
BEGIN TRANSACTION Inner1;
GO
UPDATE dbsource SET software_name_raw = dbref.software_name_amended
FROM dbo.BigTable dbsource
INNER JOIN (
SELECT software_name_raw,software_name_amended
FROM RefTable
GROUP BY software_name_raw,software_name_amended
) dbref
ON dbref.software_name_raw = dbsource.software_name_raw
go
COMMIT TRANSACTION Inner1;
I have run into a problem which is. If they dont match I need to update the reference tables 2 columns with the new unmatched record to reference something like this
ELSE INSERT INTO RefTable(software_name_raw,software_name_amended)
Values BigTable(software_name_raw,’Needs Updating’)
How can or can this be amended easily.
March 6, 2013 at 7:32 pm
I would create temp table or CTE and then use that to do insert update. The "ID" field is your PK of the table.
IF OBJECT_ID('tempdb..#Source','u') IS NOT NULL
DROP TABLE #Source
SELECT dbsource.ID
,dbsource.software_name_raw
,dbref.software_name_amended
,ToInsert = CASE WHEN dbref.software_name_raw IS NULL
THEN 1
ELSE 0
END
INTO #Source
FROM dbo.BigTable dbsource
LEFT JOIN (
SELECT software_name_raw,software_name_amended
FROM RefTable
GROUP BY software_name_raw,software_name_amended
) dbref
ON dbref.software_name_raw = dbsource.software_name_raw
UPDATE BigTable
SET software_name_raw = src.software_name_amended
FROM #Source src
JOIN dbo.BigTable
ON BigTable.ID = src.ID
WHERE src.ToInsert = 0
INSERT INTO dbo.RefTable
(software_name_raw
,software_name_amended
)
SELECT software_name_raw
,'Needs Updating'
FROM #Source
WHERE ToInsert = 1
March 6, 2013 at 7:45 pm
Do you know what would really help, besides direct access to your system? The DDL for the table(s), some sample data for the tables, the expected results of the query you are working on based on the sample data, and all of this in a readily consumable (meaning cut/paste/run in SSMS) format.
It is really hard to provide good answers based on just some code that apparently doesn't really work.
March 6, 2013 at 8:34 pm
Hi Brad.
The software_name_raw on the RefTable is the Primary Key
Does that help
March 6, 2013 at 8:40 pm
alan_lynch (3/6/2013)
Hi Brad.The software_name_raw on the RefTable is the Primary Key
Does that help
For better answers to your questions, read this: http://www.sqlservercentral.com/articles/Best+Practices/61537/.
March 6, 2013 at 8:56 pm
Sorry Lynn
The BigTable.software_name_raw contains
SOFTWARE_NAME_RAW
Microsoft Office 2003 Professional Edition, Office 2003 Professional
Microsoft Office 2003 Professional Enterprise Edition, Office 2003 Professional
Microsoft 2003 Office Professional, Office 2003 Professional
and the RefTable contains a further column
SOFTWARE_NAME_RAW,SOFTWARE_NAME_AMENDED
Microsoft Office 2003 Professional Edition, Office 2003 Professional
Microsoft Office 2003 Professional Enterprise Edition, Office 2003 Professional
Microsoft 2003 Office Professional, Office 2003 Professional
So if the two columns match then update the BigTable with the software_name_amended column from the RefTable
If there is no initial match then I want to insert into the RefTable a new found reference
EG Microsoft 2003 PRO, 'Needs Updating'
Hope this helps
March 6, 2013 at 9:02 pm
alan_lynch (3/6/2013)
Sorry LynnThe BigTable.software_name_raw contains
SOFTWARE_NAME_RAW
Microsoft Office 2003 Professional Edition, Office 2003 Professional
Microsoft Office 2003 Professional Enterprise Edition, Office 2003 Professional
Microsoft 2003 Office Professional, Office 2003 Professional
and the RefTable contains a further column
SOFTWARE_NAME_RAW,SOFTWARE_NAME_AMENDED
Microsoft Office 2003 Professional Edition, Office 2003 Professional
Microsoft Office 2003 Professional Enterprise Edition, Office 2003 Professional
Microsoft 2003 Office Professional, Office 2003 Professional
So if the two columns match then update the BigTable with the software_name_amended column from the RefTable
If there is no initial match then I want to insert into the RefTable a new found reference
EG Microsoft 2003 PRO, 'Needs Updating'
Hope this helps
Nope. I don't see any DDL for the table(s), nor insert statements with sample (not real) data, nor even what the expected results are based on the sample data.
Guess I'll move along and see if there are others who I may be able help.
March 6, 2013 at 9:09 pm
Ok Lynn if thats what you want to do and move on and help someone else then thats fine.
I am new to SqlServer so I dont know how to do the insert statement properly, thats why I asked this in my initial enquiry asking if data can be inserted into the reference table if it does not exist during my update.
I will await Brad's reply he seems to know what I mean
March 6, 2013 at 9:12 pm
alan_lynch (3/6/2013)
Ok Lynn if thats what you want to do and move on and help someone else then thats fine.I am new to SqlServer so I dont know how to do the insert statement properly, thats why I asked this in my initial enquiry asking if data can be inserted into the reference table if it does not exist during my update.
I will await Brad's reply he seems to know what I mean
Take the time to read the article I gave you the link to, it will help with everything I asked you to provide.
March 6, 2013 at 9:18 pm
Alan,
What you need to do is right click the Both tables in SSMS, select Script table as Create , paste the code in the forum. Otherwise whatever suggestions we give will be based on assumptions, that can give you wrong results.
Lynn is willing to help you and he is one of very active members here.
For your question, If software_name_raw is pk for your RefTable, I donot see a point in using group by in your update statement.
Edit (Reffered Lynn with wrong pronoun. Because we only see their valuable posts , rarely know them in person. Corrected with apologies)
March 6, 2013 at 9:18 pm
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.
March 6, 2013 at 9:21 pm
ok Joe no worries
Could you tel lme what SSMS is
Thanks
Alan
March 6, 2013 at 9:25 pm
alan_lynch (3/6/2013)
ok Joe no worriesCould 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.
March 6, 2013 at 9:30 pm
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.
March 7, 2013 at 5:05 am
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
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply