SQL for Migrations

May 15, 2010

If you don’t have a fancy tool like Scribe lying around, or if you need to work around its shortcomings/bugs/features, or if you simply need to import into a single table and need more flexibility than the Import Wizard gives you, you can do that in straight SQL, through WG Admin.

First, in Database Manager, set the primary key of the table you’ll be importing into, to be auto-increment. (Right-click on the field, select Properties…, and check the Field is Auto Increment checkbox.)

Then, use SQL like the following to build a result set that contains the SQL INSERT strings (this is for transferring all of the Account and Contact Address records from one SLX database to another, but the concept would obviously be the same for any other sources or tables):

SELECT 'INSERT INTO Address (ENTITYID, TYPE, DESCRIPTION, ADDRESS1, ADDRESS2, CITY, STATE, POSTALCODE, COUNTY, COUNTRY, ISPRIMARY, ISMAILING, SALUTATION, CREATEDATE, CREATEUSER, MODIFYDATE, MODIFYUSER, ROUTING, ADDRESS3, ADDRESS4, TIMEZONE, OLDADDRESSID) VALUES ('
+ '''' + CASE WHEN SUBSTRING(ENTITYID, 1, 1) = 'C' THEN ISNULL((SELECT TargetKey FROM sysdba.XREFCONTACT WHERE SourceKey = sysdba.Address.EntityID), '') WHEN SUBSTRING(ENTITYID, 1, 1) = 'A' THEN ISNULL((SELECT TargetKey FROM sysdba.XREFACCOUNT WHERE SourceKey = sysdba.Address.EntityID), '') END + ''', '
+ CASE WHEN TYPE IS NULL THEN 'NULL' ELSE '''' + REPLACE(TYPE, '''', '''''') + '''' END + ', '
+ CASE WHEN DESCRIPTION IS NULL THEN 'NULL' ELSE '''' + REPLACE(DESCRIPTION, '''', '''''') + '''' END + ', '
+ CASE WHEN ADDRESS1 IS NULL THEN 'NULL' ELSE '''' + REPLACE(ADDRESS1, '''', '''''') + '''' END + ', '
+ CASE WHEN ADDRESS2 IS NULL THEN 'NULL' ELSE '''' + REPLACE(ADDRESS2, '''', '''''') + '''' END + ', '
+ CASE WHEN CITY IS NULL THEN 'NULL' ELSE '''' + REPLACE(CITY, '''', '''''') + '''' END + ', '
+ CASE WHEN STATE IS NULL THEN 'NULL' ELSE '''' + REPLACE(STATE, '''', '''''') + '''' END + ', '
+ CASE WHEN POSTALCODE IS NULL THEN 'NULL' ELSE '''' + REPLACE(POSTALCODE, '''', '''''') + '''' END + ', '
+ CASE WHEN COUNTY IS NULL THEN 'NULL' ELSE '''' + REPLACE(COUNTY, '''', '''''') + '''' END + ', '
+ CASE WHEN COUNTRY IS NULL THEN 'NULL' ELSE '''' + REPLACE(COUNTRY, '''', '''''') + '''' END + ', '
+ CASE WHEN ISPRIMARY IS NULL THEN 'NULL' ELSE '''' + REPLACE(ISPRIMARY, '''', '''''') + '''' END + ', '
+ CASE WHEN ISMAILING IS NULL THEN 'NULL' ELSE '''' + REPLACE(ISMAILING, '''', '''''') + '''' END + ', '
+ CASE WHEN SALUTATION IS NULL THEN 'NULL' ELSE '''' + REPLACE(SALUTATION, '''', '''''') + '''' END + ', '
+ CASE WHEN CREATEDATE IS NULL THEN 'NULL' ELSE '''' + CONVERT(varchar(20), CREATEDATE) + '''' END + ', '
+ CASE WHEN CREATEUSER IS NULL THEN 'NULL' ELSE '''' + 'ADMIN' + '''' END + ', '
+ CASE WHEN MODIFYDATE IS NULL THEN 'NULL' ELSE '''' + CONVERT(varchar(20), MODIFYDATE) + '''' END + ', '
+ CASE WHEN MODIFYUSER IS NULL THEN 'NULL' ELSE '''' + 'ADMIN' + '''' END + ', '
+ CASE WHEN ROUTING IS NULL THEN 'NULL' ELSE '''' + REPLACE(ROUTING, '''', '''''') + '''' END + ', '
+ CASE WHEN ADDRESS3 IS NULL THEN 'NULL' ELSE '''' + REPLACE(ADDRESS3, '''', '''''') + '''' END + ', '
+ CASE WHEN ADDRESS4 IS NULL THEN 'NULL' ELSE '''' + REPLACE(ADDRESS4, '''', '''''') + '''' END + ', '
+ CASE WHEN TIMEZONE IS NULL THEN 'NULL' ELSE '''' + REPLACE(TIMEZONE, '''', '''''') + '''' END + ', '
+ '''' + ADDRESSID + ''');'
FROM sysdba.Address
WHERE ENTITYID IN (SELECT AccountID FROM sysdba.Account)
OR ENTITYID IN (SELECT ContactID FROM sysdba.Contact)

Run that SQL statement in Management Studio. When the result set appears, right-click, and Select All. Then right-click again, and Copy.

Open Notepad, and Paste from the clipboard into a blank file.

Save the file with ANSI encoding (not Unicode!). Note: If you instead right-click on the SSMS result set and select “Save Results As…”, it will save them as Unicode; so if you’re going that route, you’ll still need to open the file in Notepad, and re-save it as ANSI encoding.

In WG Admin, go to Tools -> Execute SQL…, then right-click over the SQL statement box, and Load Script. (If it shows up as just a few character of gibberish, you saved the Notepad file as Unicode.)

Scroll down to the bottom of the grid, and delete the final (blank) row. Then Test/Execute the SQL.

Clear the auto-increment flag on the PK field.

Note: If any of the fields you’re bringing across have semicolons anywhere in their data, that will crash the transfer: WG Admin automatically starts a new row whenever it’s loading a file and encounters a semicolon. To work around that, before you save the Notepad file, search and replace all of the “);” characters in the file into “):” strings; then manually replace each of the remaining semicolons with another string that doesn’t occur anywhere else in the file (e.g., “.,.”); then change the “):” strings back into “);” strings. After the import, Update the field in question, changing its “.,.” strings back into “;”.

Of course, if your valid data has the “);” string anywhere inside it, you’ll need to work around that, too.

😉

Advertisements