Database
Merging two tables in a non-conventional way
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.
Creating a new Journaler database
1. Under Server Configuration, create a new Journaler Storage mailbox database
2. Create a new user and point it to the new Journaler Storage mailbox
3. Make sure mailbox quoate is set correctly for the user
4. In Organization Configuration > Hub Transport > Journaling, create a new Journal Rule and select the new user in “Send Journal Report to email address”. Select proper Scope
Forcing Exchange mailbox cleanup
One of the reasons why we need to force a database cleanup in exchange is when we wanted the deleted users objects list updated.
First, you have to get the mailbox name,
Get-mailboxdatabase
Next, is to run the cleanup command against the mailbox
Clean-mailboxdatabase “<database name>”
WIKIPinoy 