/*
SCRIPT FILE : 2 :  Script Generated To Import Data From Text Files, Migration Upsizing Tool
copyright asc associates 2007, All rights Reserved
 This is the script should be run after the basic structures have been crted to import the data into the SQL Server.
*/


/*
-- You May Need To Enable The Following Option To Set the Advanced option below
USE master
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
GO
-- You Need To Enable The Following Option To Perform The Imports
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
*/


-- ENSURE YOU ARE IN THE CORRECT DATABASE TO EXECUTE THE SCRIPT
USE [Northwind]
GO



IF EXISTS(SELECT * FROM sysObjects where name ='UPSIZE_SQLLOG')
DROP TABLE [UPSIZE_SQLLOG]
GO
CREATE TABLE UPSIZE_SQLLOG (StepId INT IDENTITY(1,1) PRIMARY KEY, TableName VARCHAR(255) null, SchemaName VARCHAR(255) null, TargetRows INT DEFAULT 0, ActualRows INT DEFAULT 0, StartTime DATETIME DEFAULT GETDATE(), LatestTime DateTime)
GO
INSERT INTO UPSIZE_SQLLOG(TableName,SchemaName,TargetRows) VALUES('Suppliers','', 29)
GO
PRINT 'Starting Data Transfer For [Suppliers]'
SET IDENTITY_INSERT [Suppliers] ON
GO


INSERT INTO [dbo].[Suppliers]([SupplierID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax],[HomePage]) 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\MustRelease\Test\Northwind.mdb';'admin';'', [Suppliers])
-- SELECT [SupplierID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax],[HomePage] FROM [Suppliers]
GO


SET IDENTITY_INSERT [Suppliers] OFF
GO
UPDATE  UPSIZE_SQLLOG SET LatestTime = GETDATE(), ActualRows = (SELECT COUNT('*') FROM [Suppliers])  WHERE TableName =  'Suppliers'           
GO
INSERT INTO UPSIZE_SQLLOG(TableName,SchemaName,TargetRows) VALUES('Shippers','', 3)
GO
PRINT 'Starting Data Transfer For [Shippers]'
SET IDENTITY_INSERT [Shippers] ON
GO


INSERT INTO [dbo].[Shippers]([ShipperID],[CompanyName],[Phone]) 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\MustRelease\Test\Northwind.mdb';'admin';'', [Shippers])
-- SELECT [ShipperID],[CompanyName],[Phone] FROM [Shippers]
GO


SET IDENTITY_INSERT [Shippers] OFF
GO
UPDATE  UPSIZE_SQLLOG SET LatestTime = GETDATE(), ActualRows = (SELECT COUNT('*') FROM [Shippers])  WHERE TableName =  'Shippers'           
GO
INSERT INTO UPSIZE_SQLLOG(TableName,SchemaName,TargetRows) VALUES('Products','', 77)
GO
PRINT 'Starting Data Transfer For [Products]'
SET IDENTITY_INSERT [Products] ON
GO


INSERT INTO [dbo].[Products]([ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]) 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\MustRelease\Test\Northwind.mdb';'admin';'', [Products])
-- SELECT [ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] FROM [Products]
GO


SET IDENTITY_INSERT [Products] OFF
GO
UPDATE  UPSIZE_SQLLOG SET LatestTime = GETDATE(), ActualRows = (SELECT COUNT('*') FROM [Products])  WHERE TableName =  'Products'           
GO
INSERT INTO UPSIZE_SQLLOG(TableName,SchemaName,TargetRows) VALUES('Orders','', 830)
GO
PRINT 'Starting Data Transfer For [Orders]'
SET IDENTITY_INSERT [Orders] ON
GO


INSERT INTO [dbo].[Orders]([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],[ShipPostalCode],[ShipCountry]) 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\MustRelease\Test\Northwind.mdb';'admin';'', [Orders])
-- SELECT [OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],[ShipPostalCode],[ShipCountry] FROM [Orders]
GO


SET IDENTITY_INSERT [Orders] OFF
GO
UPDATE  UPSIZE_SQLLOG SET LatestTime = GETDATE(), ActualRows = (SELECT COUNT('*') FROM [Orders])  WHERE TableName =  'Orders'           
GO
INSERT INTO UPSIZE_SQLLOG(TableName,SchemaName,TargetRows) VALUES('Order Details','', 2155)
GO
PRINT 'Starting Data Transfer For [Order Details]'


INSERT INTO [dbo].[Order Details]([OrderID],[ProductID],[UnitPrice],[Quantity],[Discount]) 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\MustRelease\Test\Northwind.mdb';'admin';'', [Order Details])
-- SELECT [OrderID],[ProductID],[UnitPrice],[Quantity],[Discount] FROM [Order Details]
GO


UPDATE  UPSIZE_SQLLOG SET LatestTime = GETDATE(), ActualRows = (SELECT COUNT('*') FROM [Order Details])  WHERE TableName =  'Order Details'           
GO
INSERT INTO UPSIZE_SQLLOG(TableName,SchemaName,TargetRows) VALUES('Employees','', 9)
GO
PRINT 'Starting Data Transfer For [Employees]'
SET IDENTITY_INSERT [Employees] ON
GO


INSERT INTO [dbo].[Employees]([EmployeeID],[LastName],[FirstName],[Title],[TitleOfCourtesy],[BirthDate],[HireDate],[Address],[City],[Region],[PostalCode],[Country],[HomePhone],[Extension],[Photo],[Notes],[ReportsTo]) 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\MustRelease\Test\Northwind.mdb';'admin';'', [Employees])
-- SELECT [EmployeeID],[LastName],[FirstName],[Title],[TitleOfCourtesy],[BirthDate],[HireDate],[Address],[City],[Region],[PostalCode],[Country],[HomePhone],[Extension],[Photo],[Notes],[ReportsTo] FROM [Employees]
GO


SET IDENTITY_INSERT [Employees] OFF
GO
UPDATE  UPSIZE_SQLLOG SET LatestTime = GETDATE(), ActualRows = (SELECT COUNT('*') FROM [Employees])  WHERE TableName =  'Employees'           
GO
INSERT INTO UPSIZE_SQLLOG(TableName,SchemaName,TargetRows) VALUES('Customers','', 91)
GO
PRINT 'Starting Data Transfer For [Customers]'


INSERT INTO [dbo].[Customers]([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax]) 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\MustRelease\Test\Northwind.mdb';'admin';'', [Customers])
-- SELECT [CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax] FROM [Customers]
GO


UPDATE  UPSIZE_SQLLOG SET LatestTime = GETDATE(), ActualRows = (SELECT COUNT('*') FROM [Customers])  WHERE TableName =  'Customers'           
GO
INSERT INTO UPSIZE_SQLLOG(TableName,SchemaName,TargetRows) VALUES('Categories','', 8)
GO
PRINT 'Starting Data Transfer For [Categories]'
SET IDENTITY_INSERT [Categories] ON
GO


INSERT INTO [dbo].[Categories]([CategoryID],[CategoryName],[Description],[Picture]) 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\MustRelease\Test\Northwind.mdb';'admin';'', [Categories])
-- SELECT [CategoryID],[CategoryName],[Description],[Picture] FROM [Categories]
GO


SET IDENTITY_INSERT [Categories] OFF
GO
UPDATE  UPSIZE_SQLLOG SET LatestTime = GETDATE(), ActualRows = (SELECT COUNT('*') FROM [Categories])  WHERE TableName =  'Categories'           
GO