Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SergioM
Posting Yak Master
170 Posts |
 Posted - 2012-09-20 : 16:11:02
|
I'm pretty sure that I'm doing something inefficiently, so really, I'm looking to see if anyone has advice.I have 10, identically structured tables. (1) is my final table with all data aggregated & of the other (9) each one represents one distributor. Each of the tables has (20) columns.I wanted to create something that would fill in the blanks. That is, if the final table has a NULL field and one of the source tables has the data, fill in the blanks. So I created the following MERGE statement.MERGE INTO [MyDB].[Final].[Inventory] AS targetDBUSING [MyDB].[inbound].[Distributor] AS sourceDBON sourceDB.[UPC] = targetDB.[UPC]WHEN MATCHED THENUPDATE SET [Manufacturer] = CASE WHEN (targetDB.[Manufacturer] IS NULL AND sourceDB.[Manufacturer] IS NOT NULL) THEN sourceDB.[Manufacturer] ELSE targetDB.[Manufacturer] END; It works well. However, I have to do the same thing for each column in the table and repeat that for each table in the database! Most of these lines of code appear 200x each! Is there a better way to do this? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-20 : 16:25:00
|
If I understood your problem description correctly, you should be able to do a single update like shown below:UPDATE i SET i.Manufacturer = COALESCE (d1.Manufacturer, d2.Manufacturer, d3.Manufacturer)FROM [MyDB].[Final].[Inventory] i LEFT JOIN [MyDB].[inbound].[Distributor1] d1 ON d1.UPC = i.UPC LEFT JOIN [MyDB].[inbound].[Distributor2] d2 ON d2.UPC = i.UPC LEFT JOIN [MyDB].[inbound].[Distributor3] d3 ON d3.UPC = i.UPCWHERE i.Manufacturer IS NOT NULL; |
 |
|
|
|
|
|
More efficient merge?