Merging two tables in a non-conventional way

Posted on Updated on

I found this an easy way to merge two tables together without using UNION, UNION ALL.  Here is a summary that one of our admins are having that led me into creating this script:

We are using a hardware asset manager software, and the vendor did all the data migration from the old version to the new version which also included a migration of data to a new database with different table schemas.  The migration went fine, but our admin noticed some values that were not migrated correctly.  So on the new database, there are some missing data.

Instead of using the UNION or UNION ALL to merge the old and new database.  I decided to create my own script.  Which also made more sense for us, since we only wanted to update the data that are on the new database and not add junk to it.  The old DB has 10,000 records and the new DB has only 7,000.  The old DB included the retired assets that we no longer want.

So here is how I did it in a script: (I will leave the table and database name as is)

You may have to fix the single and double quotes after pasting it to SMC as wordpress formatted the characters differently

use vam_master

DECLARE @T TABLE (SQL varchar(1000))

INSERT INTO @T

SELECT ‘UPDATE dbo.assets SET AssetSerialNo=”’ + [assets/serial number] + ”’ WHERE Barcode=”’ + CAST([general/barcode] as varchar(10)) + ””

FROM dbo.assets

where [general/barcode] is not NULL

INSERT INTO @T

SELECT ‘UPDATE dbo.assets SET PurchaseOrderNo=”’ + [general/purchase order] + ”’ WHERE Barcode=”’ + CAST([general/barcode] as varchar(10)) + ””

FROM dbo.assets

where [general/barcode] is not NULL

INSERT INTO @T

SELECT ‘UPDATE dbo.assets SET Model=”’ + [assets/equipment/model] + ”’ WHERE Barcode=”’+ CAST([general/barcode] as varchar(10)) + ””

FROM dbo.assets

where [general/barcode] is not NULL

INSERT INTO @T

SELECT ‘UPDATE dbo.assets SET mfg=”’ + [assets/mfg] + ”’ WHERE Barcode=”’+ CAST([general/barcode] as varchar(10)) + ””

FROM dbo.assets

where [general/barcode] is not NULL

select * from @T

Running this script will output results like ‘UPDATE dbo.assets SET AssetSerialNo=’blah’ WHERE Barcode=’blah’.  So you can then run this against the new DB and it will update the table with missing values for what you have selected and just ignore those entries that do not exist on the new DB.

I hope this helps, if you are on the same situation as me.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s