InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 10:47:11 GMT20http://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxHi LynnI have added a group by clause on the end like so which seems to workinsert into RefTable(software_name_raw,software_name_amended)select bt.software_name_raw, 'Needs Updating'from BigTable btwhere 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 casethanksAlanSun, 10 Mar 2013 22:37:18 GMTalan_lynchhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxHi LynnYeah 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 thatthe table that I am going to be querying may have duplicate values initegINSERT INTO BigTableVALUES('Microsoft Office 2003'),('Microsoft 2003 Office'),('Microsoft Office 2003 Professional'),('Sun Microsystems'),('Sun Microsystems'),('Test'),('Test'),('Test')Sun, 10 Mar 2013 22:10:11 GMTalan_lynchhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspx[quote][b]alan_lynch (3/10/2013)[/b][hr]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 2Violation 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 btwhere 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_amendedfrom 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[/quote]Looks like you are trying to use the same data as input after you have updated it. What would you expect to happen?Sun, 10 Mar 2013 21:58:02 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxHi LynnThat sort of works but when I run just this part for the second time I get.Msg 2627, Level 14, State 1, Line 2Violation 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 btwhere 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_amendedfrom 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;GOSun, 10 Mar 2013 18:19:13 GMTalan_lynchhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxthis:[code="sql"]if exists(select 1 from sys.tables where object_id = object_id('dbo.BigTable')) drop table BigTable; --Drop SOURCEif exists(select 1 from sys.tables where object_id = object_id('dbo.RefTable ')) drop table RefTable; --Drop Lookup--Create a Source tableCREATE TABLE BigTable(software_name_raw VARCHAR(255))GO--Insert records into Source tableINSERT INTO BigTableVALUES('Microsoft Office 2003'),('Microsoft 2003 Office'),('Microsoft Office 2003 Professional'),('Sun Microsystems')GO--Create LOOKUP tableCREATE TABLE RefTable(software_name_raw VARCHAR(255) PRIMARY KEY,software_name_amended Varchar (255))GO--Insert records into Lookup tableINSERT INTO RefTableVALUES('Microsoft Office 2003', 'Office 2003'),('Microsoft 2003 Office', 'Office 2003'),('Microsoft Office 2003 Professional', 'Office 2003'),('Adobe', 'Adobe Inc')GOSELECT * FROM BigTable as Source_TABLESELECT * FROM RefTable as Lookup_TABLEGOinsert into RefTable(software_name_raw,software_name_amended)select bt.software_name_raw, 'Needs Updating'from BigTable btwhere 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_amendedfrom 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;GOif exists(select 1 from sys.tables where object_id = object_id('dbo.BigTable')) drop table BigTable; --Drop SOURCEif exists(select 1 from sys.tables where object_id = object_id('dbo.RefTable ')) drop table RefTable; --Drop LookupGO[/code]Fri, 08 Mar 2013 07:25:14 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxHi LynnYes thats rightregardsAlanThu, 07 Mar 2013 22:19:59 GMTalan_lynchhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspx[quote][b]alan_lynch (3/7/2013)[/b][hr]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 valueTargetcol1,col2a,'test1'b,'test2'c,'test3'Sourcecol1a,b,c,dinserted expected results in targeta,'test1' -------------------------------------Matched 'a' so update Source with Targets second column valueb,'test2' -------------------------------------Matched 'b' so update Source with Targets second column valuec,'test3' -------------------------------------Matched 'c' so update Source with Targets second column valued,'needs updating' -----------------------------Was NO so Match got insertedexpected results in Source'test1''test2''test3'[/quote]SO you if there is match in Target, change the Source. If there is no match in Target, add source to target. This right??Thu, 07 Mar 2013 21:26:32 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxthat was quick i was still mentioning the last thing lolanyway 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 matchThu, 07 Mar 2013 15:52:01 GMTalan_lynchhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxHi 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 valueTargetcol1,col2a,'test1'b,'test2'c,'test3'Sourcecol1a,b,c,dinserted expected results in targeta,'test1' -------------------------------------Matched 'a' so update Source with Targets second column valueb,'test2' -------------------------------------Matched 'b' so update Source with Targets second column valuec,'test3' -------------------------------------Matched 'c' so update Source with Targets second column valued,'needs updating' -----------------------------Was NO so Match got insertedexpected results in Source'test1''test2''test3'Thu, 07 Mar 2013 15:49:24 GMTalan_lynchhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxTry this:[code="sql"]drop table BigTable --Drop SOURCEdrop table RefTable --Drop Lookup--Create a Source tableCREATE TABLE BigTable(software_name_raw VARCHAR(255))GO--Insert records into Source tableINSERT INTO BigTableVALUES('Microsoft Office 2003'),('Microsoft 2003 Office'),('Microsoft Office 2003 Professional'),('Sun Microsystems')GO--Create LOOKUP tableCREATE TABLE RefTable(software_name_raw VARCHAR(255) PRIMARY KEY,software_name_amended Varchar (255))GO--Insert records into Lookup tableINSERT INTO RefTableVALUES('Microsoft Office 2003', 'Office 2003'),('Microsoft 2003 Office', 'Office 2003'),('Microsoft Office 2003 Professional', 'Office 2003'),('Adobe', 'Adobe Inc')GOSELECT * FROM BigTable as Source_TABLESELECT * FROM RefTable as Lookup_TABLEGOinsert into RefTable(software_name_raw,software_name_amended)select bt.software_name_raw, 'Needs Updating'from BigTable btwhere not exists(select 1 from RefTable rf where rf.software_name_raw = bt.software_name_raw);select * from RefTable;[/code]Thu, 07 Mar 2013 15:35:45 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxLynnI 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 isinsert data that does not already exist in the Target Table and ignore the data that doesthanksThu, 07 Mar 2013 15:29:25 GMTalan_lynchhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspx[quote][b]alan_lynch (3/7/2013)[/b][hr]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 messageMsg 8672, Level 16, State 1, Line 9The 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 lookuptabledrop table BigTable --Drop SOURCEdrop table RefTable --Drop Lookup--Create a Source tableCREATE TABLE BigTable(software_name_raw VARCHAR(255))GO--Insert records into Source tableINSERT INTO BigTableVALUES('Microsoft Office 2003'),('Microsoft 2003 Office'),('Microsoft Office 2003 Professional'),('Sun Microsystems')GO--Create LOOKUP tableCREATE TABLE RefTable(software_name_raw VARCHAR(255) PRIMARY KEY,software_name_amended Varchar (255))GO--Insert records into Lookup tableINSERT INTO RefTableVALUES('Microsoft Office 2003', 'Office 2003'),('Microsoft 2003 Office', 'Office 2003'),('Microsoft Office 2003 Professional', 'Office 2003'),('Adobe', 'Adobe Inc')GOSELECT * FROM BigTable as Source_TABLESELECT * FROM RefTable as Lookup_TABLEGO--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 tableMERGE RefTable AS TARGETUSING BigTable AS SOURCEON (TARGET.software_name_raw = SOURCE.software_name_raw)--When records are matched, update the records if there is any changeWHEN MATCHED AND TARGET.software_name_raw = SOURCE.software_name_raw THENUPDATE SET TARGET.software_name_raw = SOURCE.software_name_raw--When no records are matched, insert the incoming records from source into the lookup tableWHEN NOT MATCHED BY TARGET THENINSERT (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 rowOUTPUT $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;GOselect * from BigTable as source_tableselect * from RefTable as lookuptable[/quote]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.Thu, 07 Mar 2013 05:52:18 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxright 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 messageMsg 8672, Level 16, State 1, Line 9The 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 lookuptabledrop table BigTable --Drop SOURCEdrop table RefTable --Drop Lookup--Create a Source tableCREATE TABLE BigTable(software_name_raw VARCHAR(255))GO--Insert records into Source tableINSERT INTO BigTableVALUES('Microsoft Office 2003'),('Microsoft 2003 Office'),('Microsoft Office 2003 Professional'),('Sun Microsystems')GO--Create LOOKUP tableCREATE TABLE RefTable(software_name_raw VARCHAR(255) PRIMARY KEY,software_name_amended Varchar (255))GO--Insert records into Lookup tableINSERT INTO RefTableVALUES('Microsoft Office 2003', 'Office 2003'),('Microsoft 2003 Office', 'Office 2003'),('Microsoft Office 2003 Professional', 'Office 2003'),('Adobe', 'Adobe Inc')GOSELECT * FROM BigTable as Source_TABLESELECT * FROM RefTable as Lookup_TABLEGO--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 tableMERGE RefTable AS TARGETUSING BigTable AS SOURCEON (TARGET.software_name_raw = SOURCE.software_name_raw)--When records are matched, update the records if there is any changeWHEN MATCHED AND TARGET.software_name_raw = SOURCE.software_name_raw THENUPDATE SET TARGET.software_name_raw = SOURCE.software_name_raw--When no records are matched, insert the incoming records from source into the lookup tableWHEN NOT MATCHED BY TARGET THENINSERT (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 rowOUTPUT $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;GOselect * from BigTable as source_tableselect * from RefTable as lookuptableThu, 07 Mar 2013 05:05:11 GMTalan_lynchhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxHey 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.Wed, 06 Mar 2013 21:30:29 GMTbrad.mason5http://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspx[quote][b]alan_lynch (3/6/2013)[/b][hr]ok Joe no worriesCould you tel lme what SSMS isThanksAlan[/quote]SQL Server Management Studio. Just happens to be the main tool we all use when working with MS SQL Server.Wed, 06 Mar 2013 21:25:50 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxok Joe no worriesCould you tel lme what SSMS isThanksAlanWed, 06 Mar 2013 21:21:05 GMTalan_lynchhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxThe one thing I would suggest reading about is the MERGE statement. This may be what you really need.Start here: [b][url]http://msdn.microsoft.com/en-us/library/bb510625(v=sql.100).aspx[/url][/b].Wed, 06 Mar 2013 21:18:41 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxAlan,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)Wed, 06 Mar 2013 21:18:33 GMTjoeroshanhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspx[quote][b]alan_lynch (3/6/2013)[/b][hr]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[/quote]Take the time to read the article I gave you the link to, it will help with everything I asked you to provide.Wed, 06 Mar 2013 21:12:29 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxOk 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 meanWed, 06 Mar 2013 21:09:31 GMTalan_lynchhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspx[quote][b]alan_lynch (3/6/2013)[/b][hr]Sorry LynnThe BigTable.software_name_raw containsSOFTWARE_NAME_RAWMicrosoft Office 2003 Professional Edition, Office 2003 ProfessionalMicrosoft Office 2003 Professional Enterprise Edition, Office 2003 ProfessionalMicrosoft 2003 Office Professional, Office 2003 Professionaland the RefTable contains a further columnSOFTWARE_NAME_RAW,SOFTWARE_NAME_AMENDEDMicrosoft Office 2003 Professional Edition, Office 2003 ProfessionalMicrosoft Office 2003 Professional Enterprise Edition, Office 2003 ProfessionalMicrosoft 2003 Office Professional, Office 2003 ProfessionalSo if the two columns match then update the BigTable with the software_name_amended column from the RefTableIf there is no initial match then I want to insert into the RefTable a new found referenceEG Microsoft 2003 PRO, 'Needs Updating'Hope this helps[/quote]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.Wed, 06 Mar 2013 21:02:48 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxSorry LynnThe BigTable.software_name_raw containsSOFTWARE_NAME_RAWMicrosoft Office 2003 Professional Edition, Office 2003 ProfessionalMicrosoft Office 2003 Professional Enterprise Edition, Office 2003 ProfessionalMicrosoft 2003 Office Professional, Office 2003 Professionaland the RefTable contains a further columnSOFTWARE_NAME_RAW,SOFTWARE_NAME_AMENDEDMicrosoft Office 2003 Professional Edition, Office 2003 ProfessionalMicrosoft Office 2003 Professional Enterprise Edition, Office 2003 ProfessionalMicrosoft 2003 Office Professional, Office 2003 ProfessionalSo if the two columns match then update the BigTable with the software_name_amended column from the RefTableIf there is no initial match then I want to insert into the RefTable a new found referenceEG Microsoft 2003 PRO, 'Needs Updating'Hope this helpsWed, 06 Mar 2013 20:56:11 GMTalan_lynchhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspx[quote][b]alan_lynch (3/6/2013)[/b][hr]Hi Brad.The software_name_raw on the RefTable is the Primary KeyDoes that help[/quote]For better answers to your questions, read this: [b][url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url][/b].Wed, 06 Mar 2013 20:40:28 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxHi Brad.The software_name_raw on the RefTable is the Primary KeyDoes that helpWed, 06 Mar 2013 20:34:05 GMTalan_lynchhttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxDo 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.Wed, 06 Mar 2013 19:45:04 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxI 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 #SourceSELECT dbsource.ID ,dbsource.software_name_raw ,dbref.software_name_amended ,ToInsert = CASE WHEN dbref.software_name_raw IS NULL THEN 1 ELSE 0 ENDINTO #SourceFROM dbo.BigTable dbsourceLEFT 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_rawUPDATE BigTableSET software_name_raw = src.software_name_amendedFROM #Source src JOIN dbo.BigTable ON BigTable.ID = src.IDWHERE src.ToInsert = 0INSERT INTO dbo.RefTable(software_name_raw,software_name_amended)SELECT software_name_raw ,'Needs Updating'FROM #SourceWHERE ToInsert = 1Wed, 06 Mar 2013 19:32:20 GMTbrad.mason5http://www.sqlservercentral.com/Forums/Topic1427718-1292-1.aspxHi 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;GOUPDATE dbsource SET software_name_raw = dbref.software_name_amended FROM dbo.BigTable dbsourceINNER 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_rawgoCOMMIT 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 thisELSE INSERT INTO RefTable(software_name_raw,software_name_amended)Values BigTable(software_name_raw,’Needs Updating’)How can or can this be amended easily.Wed, 06 Mar 2013 18:57:36 GMTalan_lynch