data mining test data loading script

 

exec sp_configure 'show advanced options', 1
reconfigure with override

exec sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure with override

 

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

 

/* 1 */
if object_id ('[Table Analysis Tools Sample]') is not null
drop table [Table Analysis Tools Sample]

SELECT * into [Table Analysis Tools Sample]
FROM OPENROWSET
 (
 'Microsoft.ACE.OLEDB.12.0',
 'Excel 12.0; HDR=YES; IMEX=1; Database=C:\DMAddins_SampleData.xlsx',
 'SELECT * FROM [Table Analysis Tools Sample$a3:m]'
 )

select * from [Table Analysis Tools Sample] 

/* 2 */
if object_id ('Forecasting') is not null
drop table Forecasting

SELECT * into Forecasting
FROM OPENROWSET
 (
 'Microsoft.ACE.OLEDB.12.0',
 'Excel 12.0; HDR=YES; IMEX=1; Database=C:\DMAddins_SampleData.xlsx',
 'SELECT * FROM [Forecasting$b5:e]'
 )

/* 3 */
if object_id ('[Fill From Example]') is not null
drop table [Fill From Example]

SELECT * into [Fill From Example]
FROM OPENROWSET
 (
 'Microsoft.ACE.OLEDB.12.0',
 'Excel 12.0; HDR=YES; IMEX=1; Database=C:\DMAddins_SampleData.xlsx',
 'SELECT * FROM [Fill From Example$b3:m]'
 )

/* 4 */
if object_id ('[Source Data]') is not null
drop table [Source Data]

SELECT * into [Source Data]
FROM OPENROWSET
 (
 'Microsoft.ACE.OLEDB.12.0',
 'Excel 12.0; HDR=YES; IMEX=1; Database=C:\DMAddins_SampleData.xlsx',
 'SELECT * FROM [Source Data$a3:m]'
 )

/* 5 */
if object_id ('[Training Data]') is not null
drop table [Training Data]

SELECT * into [Training Data]
FROM OPENROWSET
 (
 'Microsoft.ACE.OLEDB.12.0',
 'Excel 12.0; HDR=YES; IMEX=1; Database=C:\DMAddins_SampleData.xlsx',
 'SELECT * FROM [Training Data$a3:m]'
 )

/* 6 */
if object_id ('[Testing Data]') is not null
drop table [Testing Data]

SELECT * into [Testing Data]
FROM OPENROWSET
 (
 'Microsoft.ACE.OLEDB.12.0',
 'Excel 12.0; HDR=YES; IMEX=1; Database=C:\DMAddins_SampleData.xlsx',
 'SELECT * FROM [Testing Data$a3:m]'
 )

/* 7 */
if object_id ('[New Customers]') is not null
drop table [New Customers]

SELECT * into [New Customers]
FROM OPENROWSET
 (
 'Microsoft.ACE.OLEDB.12.0',
 'Excel 12.0; HDR=YES; IMEX=1; Database=C:\DMAddins_SampleData.xlsx',
 'SELECT * FROM [New Customers$a3:q]'
 )

/* 8 */
if object_id ('[Associate]') is not null
drop table [Associate]

SELECT * into [Associate]
FROM OPENROWSET
 (
 'Microsoft.ACE.OLEDB.12.0',
 'Excel 12.0; HDR=YES; IMEX=1; Database=C:\DMAddins_SampleData.xlsx',
 'SELECT * FROM [Associate$a3:d]'
 )