1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
| -----------------------------------------------------------
-- SQL Server 2000 Bible
-- Hungry Minds
-- Paul Nielsen
-- Cape Hatteras Advntures v.2 sample database - Populate
-- this script will populate the CHA2 database
-- from CHA1_Customers.mdb Access file
-- and CHA1_Schedule.xls Excel Spreadsheet
-- using distributed queries
-- This script mirrors the CHA_Convert DTS package
-----------------------------------------------------------
-----------------------------------------------------------
USE CHA2
-- establish Access Linked Server
EXEC sp_DropServer @server = 'CHA1_Customers'
go
EXEC sp_addlinkedserver
'CHA1_Customers',
'Access 2003',
'Microsoft.Jet.OLEDB.4.0',
'C:\SQLData\CHA1_Customers.mdb'
go
-- establish Excel Linked Server
EXEC sp_DropServer @server = 'CHA1_Schedule'
go
Execute sp_addlinkedserver
'CHA1_Schedule',
'Excel',
'Microsoft.Jet.OLEDB.4.0',
'C:\SQLData\CHA1_Schedule.xls',
NULL,
'Excel 5.0'
go
EXEC sp_helpserver
-- Step 0: Initialize the Database
DELETE Customer
DELETE CustomerType
DELETE Event_mm_Customer
DELETE Event_mm_Guide
DELETE Tour_mm_Guide
DELETE Event
DELETE Tour
DELETE BaseCamp
DELETE Guide ;
GO
-- Step 1: Customer Types
SELECT DISTINCT CustomerType
FROM CHA1_Customers...Customers
WHERE CustomerType IS NOT NULL
INSERT CustomerType(Name)
SELECT DISTINCT CustomerType
FROM CHA1_Customers...Customers
WHERE CustomerType IS NOT NULL |
Partager