MS SQL Server database tips

Paging records

Sql Serveer 2005 implements paging using the following syntax:

sql = "SELECT TOP " + maxRows + " id, updated, builddate, checksum, site, 
comments, uuid, objectUuid, updateType, errors " +
"FROM (SELECT  ROW_NUMBER() OVER (ORDER BY u.updated) AS Row," +
u.id, u.updated, u.builddate, u.checksum, u.site, 
u.comments, u.uuid, u.object_uuid AS objectUuid, u.update_type AS updateType, u.errors " +
"FROM updatelog u)  AS LogWithRowNumbers 
WHERE  Row >= " + offset + " AND Row <= " + (offset + maxRows);

Exporting data from Derby to MSSQL

Derby does not export data in a format that is easy for MSSQL to process. Derby specifies a characterdelimiter that it uses to surround strings when they are not null. When the string is null, it does not output any data. SQL Server expects all of the fields to have uniform delimiting - all values must use the same delimiters. While it is possible to use a format file as mentioned below, SQL Server nulk import cannot handle situations where character strings have null values.

For example, here is a sample string exported by Derby:

1,12,"Housewife",,1,,,"demo","demo"
2,23,"Programmer","Acme",1,,,"demo","demo"

Note that the second line has the string "Acme" in quotations, but its corresponding field is null (,,). Importing this into SQL Server will fail.

To remedy this situation, Zcore has a modified version of the Derby export code (ExportWriteData and ControlInfo) that enables the use of empty character delimiters.

There is also a modification in ExportAbstract that facilitates the import to DateTime values from Timestamp values in Derby. Derby truncates extra zeros on nanosecond values for the Timestamp datatype; however, some SQL Server instances require three character nanosecond values. This modification replaces any values after the 18th character and replaces it w/ ".000".

Note: If you need nanesecond precision for your DateTime values when exporting .csv files from a Zcore project, modify this code.

This is a long-standing issue in Derby:

https://issues.apache.org/jira/browse/DERBY-163

There are a few other issues associated w/ Timestamps:

https://issues.apache.org/jira/browse/DERBY-2602 - resolved, but sheds a little light on the issue we’re facing.
https://issues.apache.org/jira/browse/DERBY-4614 - This issue could be the cause

Resolution: Part of Derby 10.5.3.0 source code has been modified for the MSSQL export functionality and placed in the package org.rti.zcore.derby. It is provided as the zcore-derby.jar. See the Derby code changes document for the changes. 

Importing data from .csv files

Make sure that the login has the bulkadmin server role. In SQL Server Mgmt Studio, go to database -> Security -> Logins and open your user. In Server roles, make sure that bulkadmin is selected.

Example SQL:

BULK INSERT ADMIN.FLOW 
    FROM 'C:\tcc\updates\current\FLOW.csv' 
    WITH 
    ( 
        FIELDTERMINATOR = '~', 
        ROWTERMINATOR = '\n' ,
        DATAFILETYPE='char'
    )

Creating Xml format files

If necessary, you may generate format specifications files to customise import for some fields. Please note that the current import process does not use these files.

Examples:

bcp Tims.ADMIN.APPUPDATE format nul -c -x -f APPUPDATE.xml -t, -T
bcp Tims.ADMIN.DISTRICT format nul -c -x -f DISTRICT.xml -t, -T
bcp Tims.ADMIN.FIELD_ENUMERATION format nul -c -x -f FIELD_ENUMERATION.xml -t, -T
bcp Tims.ADMIN.FLOW format nul -c -x -f FLOW.xml -t, -T
bcp Tims.ADMIN.FORM format nul -c -x -f FORM.xml -t, -T
bcp Tims.ADMIN.FORM_FIELD format nul -c -x -f FORM_FIELD.xml -t, -T
bcp Tims.ADMIN.FORM_TYPE format nul -c -x -f FORM_TYPE.xml -t, -T
bcp Tims.ADMIN.PAGE_ITEM format nul -c -x -f PAGE_ITEM.xml -t, -T
bcp Tims.ADMIN.POLICE_STATION format nul -c -x -f POLICE_STATION.xml -t, -T
bcp Tims.ADMIN.RULE_DEFINITION format nul -c -x -f RULE_DEFINITION.xml -t, -T
bcp Tims.ADMIN.SEX format nul -c -x -f SEX.xml -t, -T

Modify the FIELD section of each xml file with the text generated by the following script (enter the same of each file on the command line)

cscript.exe createBcpXmlFormat.js C:\tcc\updates\current\DRUGS.csv

Then run the following command:

BULK INSERT ADMIN.DISTRICT 
   FROM 'C:\tcc\updates\current\DISTRICT.csv' 
   WITH (FORMATFILE = 'C:\tcc\DISTRICT.xml');

Disabling Constraints

You may need to disable foreign key constaints in order to import data. Examples:

ALTER TABLE ADMIN.APPUPDATE  NOCHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.DISTRICT  NOCHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.DRUGS  NOCHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.FIELD_ENUMERATION  NOCHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.FLOW  NOCHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.FORM  NOCHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.FORM_FIELD NOCHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.FORM_TYPE  NOCHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.IMMUNIZATION  NOCHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.PAGE_ITEM  NOCHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.POLICE_STATION  NOCHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.RULE_DEFINITION  NOCHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.SEX  NOCHECK CONSTRAINT ALL;

Re-enable constraints using the following:

ALTER TABLE ADMIN.APPUPDATE  CHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.DISTRICT  CHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.DRUGS  CHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.FIELD_ENUMERATION  CHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.FLOW  CHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.FORM  CHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.FORM_FIELD CHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.FORM_TYPE  CHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.IMMUNIZATION  CHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.PAGE_ITEM  CHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.POLICE_STATION  CHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.RULE_DEFINITION  CHECK CONSTRAINT ALL;
ALTER TABLE ADMIN.SEX  CHECK CONSTRAINT ALL;