Access to Sql Server - Converting Existing Data
Conversion Options
There was an obvious requirement to convert existing data held in Microsoft Access databases into the new SQL Server format while retaining data integrity. There are a number of tools that can automate the conversion (Upsizing Wizard in Access, Data Transformation Services in Sql Server), but after a series of tests, we became aware of various weaknesses in the way that certain data fields were handled, and wanted more control over the process.
We decided to develop our own program to control the movement of data from the old Access databases to the single new Sql Server database.
The new program let us specify how tables were named (some existing databases had table names in common)
We were able to control the conversion of data types (although Access data types map fairly well to Sql Server data types, we had particular requirements with regard to memo fields, and datetime fields)
We were able to track autoincrement fields (and produce the neccessary scripting to initialise the fields in the Sql Server database)
Perhaps most importantly, we were able to log the process to ensure that all records were transferred successfully, and that data integrity was maintained (The process is relatively slow, but at least we know that we have read and written all the required data)
Conversion Process
The process runs from a script file that contains information about the Sql Server connection, and the databases to be converted:
ODBC=ODBC;DSN=LocalServer;UID=sa;PWD=;
C:\Temp\CABMAINT.MDB:CABMAINT+
C:\Temp\FINSTOCK.MDB:FINSTOCK+
C:\Temp\SETUP.MDB:SETUP+
........
........
The program iterates through the script file loading each database in turn
For each table in the loaded database it calls a conversion routine that actually adds the
tables (renamed as specified in the script file to avoid clashes) and creates any necessary Sql scripting:
For each field in the table
..Convert the data type as necessary
..Take note of autoIncrement fields
Next field
For each index in the table
..add equivalent in Sql Server
Next index
If no unique index exists, create one (and if neccessary a supporting
autoincrement data field)
Copy all the data from the Access table to the newly created Sql Server table
Where an autoincrement field existed in the Access table (or had to be created to support
a unique index), write the Sql Server scripting that will automatically create the required
identity field and associated seed and increment values
Code snippet (dots represent indentation):
WriteScript:
Print #Fchan, "/**************************************************************/"
Print #Fchan, "BEGIN TRANSACTION"
Print #Fchan, "SET QUOTED_IDENTIFIER ON"
Print #Fchan, "GO"
Print #Fchan, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
Print #Fchan, "GO"
Print #Fchan, "COMMIT"
Print #Fchan, "BEGIN TRANSACTION"
TempName = OtherRs.Name
If InStr(1, TempName, " ") Then
..TempName = SkipTidy(TempName, " ")
End If
Print #Fchan, "CREATE TABLE dbo.Tmp_" & TempName
Print #Fchan, " ("
For Wloop = 0 To OtherRs.Fields.Count - 1
..Work = " ["
..Work = Work & OtherRs.Fields(Wloop).Name & "]"
..Select Case OtherRs.Fields(Wloop).Type
....Case dbBoolean
......Work = Work & " bit NULL,"
....Case dbCurrency
......Work = Work & " float NULL,"
....Case dbDouble
......Work = Work & " float NULL,"
....Case dbFloat
......Work = Work & " float NULL,"
....Case dbInteger
......Work = Work & " smallint NULL,"
....Case dbLong
......If OtherRs.Fields(Wloop).Name = IdField Then
........Work = ""
......Else
........Work = Work & " int NULL,"
......End If
....Case dbSingle
......Work = Work & " real NULL,"
....Case dbText
......Work = Work & " nvarchar(" & OtherRs.Fields(Wloop).Size & ") NULL,"
....Case dbDate
......Work = Work & " datetime NULL,"
....Case dbMemo
......Work = Work & " ** Memo Warning **"
....Case dbLongBinary
......Work = Work & " image NULL,"
....Case Else
......Work = Work & " ** Else Warning **"
..End Select
..If Work > "" Then
....Print #Fchan, Work
..End If
Next Wloop
For Wloop = 0 To OtherRs.Fields.Count - 1
..Work = " ["
..Work = Work & OtherRs.Fields(Wloop).Name & "]"
..Select Case OtherRs.Fields(Wloop).Type
....Case dbLong
......If OtherRs.Fields(Wloop).Name = IdField Then
........Work = Work & " int NOT NULL IDENTITY (" & IdCount & ", 1)"
........Work = Work & vbCrLf
........Work = Work & ") ON [PRIMARY]"
........' Primary Is File Group Not Index Name
......Else
........Work = ""
......End If
....Case Else
......Work = ""
..End Select
..If Work > "" Then
....Print #Fchan, Work
..End If
Next Wloop
Print #Fchan, "GO"
Print #Fchan, "SET IDENTITY_INSERT dbo.Tmp_" & TempName & " ON"
Print #Fchan, "GO"
Print #Fchan, "IF EXISTS(SELECT * FROM dbo.[" & OtherRs.Name & "])"
Print #Fchan, " EXEC('INSERT INTO dbo.Tmp_" & TempName & "("
Work = " "
For Wloop = 0 To OtherRs.Fields.Count - 1
..If Wloop = OtherRs.Fields.Count - 1 Then
....Work = Work & "[" & OtherRs.Fields(Wloop).Name & "] )"
..Else
....Work = Work & "[" & OtherRs.Fields(Wloop).Name & "], "
..End If
Next Wloop
Print #Fchan, Work
Work = " SELECT "
For Wloop = 0 To OtherRs.Fields.Count - 1
..If Wloop = OtherRs.Fields.Count - 1 Then
....Work = Work & "[" & OtherRs.Fields(Wloop).Name & "] "
..Else
....Work = Work & "[" & OtherRs.Fields(Wloop).Name & "], "
..End If
Next Wloop
Work = Work & "FROM dbo.[" & OtherRs.Name & "] TABLOCKX')"
Print #Fchan, Work
Print #Fchan, "GO"
Print #Fchan, "SET IDENTITY_INSERT dbo.Tmp_" & TempName & " OFF"
Print #Fchan, "GO"
Print #Fchan, "DROP TABLE " & "dbo.[" & OtherRs.Name & "]"
Print #Fchan, "GO"
If TempName = OtherRs.Name Then
..Print #Fchan, "Execute sp_rename 'dbo.Tmp_" & TempName & "', '" & OtherRs.Name & "'"
Else
..Print #Fchan, "Execute sp_rename 'dbo.Tmp_" & TempName & "', '[" & OtherRs.Name & "]'"
End If
Print #Fchan, "GO"
Print #Fchan, "COMMIT"
Print #Fchan, "/**************************************************************/"
Return
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The whole process is logged to a file:
/* 20/09/2004 12:05:14 */
/**************************************************************/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_CABMAINTBRANDNAMES
(
[MILLCODE] nvarchar(3) NULL,
[MATCODE] nvarchar(3) NULL,
[NAME] nvarchar(60) NULL,
[UPLIFT] real NULL,
[RECNUM] int NOT NULL IDENTITY (37, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_CABMAINTBRANDNAMES ON
GO
IF EXISTS(SELECT * FROM dbo.[CABMAINTBRANDNAMES])
EXEC('INSERT INTO dbo.Tmp_CABMAINTBRANDNAMES(
[MILLCODE], [MATCODE], [NAME], [RECNUM], [UPLIFT] )
SELECT [MILLCODE], [MATCODE], [NAME], [RECNUM], [UPLIFT] FROM dbo.[CABMAINTBRANDNAMES] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_CABMAINTBRANDNAMES OFF
GO
DROP TABLE dbo.[CABMAINTBRANDNAMES]
GO
Execute sp_rename 'dbo.Tmp_CABMAINTBRANDNAMES', 'CABMAINTBRANDNAMES'
GO
COMMIT
/**************************************************************/
/*CABMAINTBRANDNAMES RECNUM Needs Identity Of 37 */
/*Name Changed By Adding Stub CABMAINT */
/* 20/09/2004 12:05:16 */
/**************************************************************/
Post Conversion Tasks
The whole log file can be run as script in Sql Server Query Analyzer (lines that are not script
are effectively commented out)