通过异类查询(OpenDataSource)把外部数据(Excel,TXT)导入到SqlSe

USE [CUSTOMS]
GO
/****** Object:  StoredProcedure [dbo].[sp_ReadXLSSource]    Script Date: 11/21/2014 3:01:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ReadXLSSource]
@trgPath varchar(255),
@trgTable varchar(255),
@xlsFile varchar(255),
@xlsSheet varchar(255),
@xlsFields varchar(8000)
AS
declare @mySql varchar(8000)
declare @myIntermediate varchar(8000)
declare @Firstfield varchar(100)
declare @FirstfieldValue varchar(100)

--Lookup lastfield
set @Firstfield = dbo.fnGetFirstField(@xlsFields)
set @FirstfieldValue = RTRIM(LTRIM(SUBSTRING(@Firstfield,2,CHARINDEX(']',@Firstfield,0)-2)))
--PRINT @FirstfieldValue

--Drop table if exists
set @myIntermediate = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @trgTable + ']'') AND type in (N''U''))'
set @mySql = @myIntermediate + ' DROP TABLE ' + @trgTable
exec(@mySql)
--PRINT @mySql

--Load Table
set @mySql = 'SELECT ' + @xlsFields + ' INTO ' + @trgTable + ' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',  
''Excel 12.0;DATABASE=' + @trgPath + '\' + @xlsFile + ';IMEX=1'',  ''Select * from ' + @xlsSheet + ''')'
+ 'WHERE NOT ' + @Firstfield + ' IS NULL'
--PRINT @mySql
exec(@mySql)

USE [WOOX_CQM]
GO
/****** Object:  StoredProcedure [dbo].[sp_ReadSource]    Script Date: 11/21/2014 2:54:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ReadSource]
@trgPath varchar(255),
@trgTable varchar(255),
@trgFields varchar(8000)
AS
declare @mySql varchar(8000)
declare @myIntermediate varchar(8000)
declare @Firstfield varchar(100)
declare @FirstfieldValue varchar(100)

--Lookup lastfield
set @Firstfield = dbo.fnGetFirstField(@trgFields)
set @FirstfieldValue = RTRIM(LTRIM(SUBSTRING(@Firstfield,2,CHARINDEX(']',@Firstfield,0)-2)))
--PRINT @FirstfieldValue

--Drop table if exists
set @myIntermediate = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @trgTable + ']'') AND type in (N''U''))'
set @mySql = @myIntermediate + ' DROP TABLE ' + @trgTable
exec(@mySql)
--PRINT @mySql

--Load Table
set @mySql = 'SELECT ' + @trgFields + ' INTO ' + @trgTable + ' ' +
'FROM OpenDataSource (''Microsoft.ACE.OLEDB.12.0'',
''Data Source="' + @trgPath + '";Extended properties=Text'')...' 
+ @trgTable + '#txt ' 
+ &#39;WHERE NOT &#39; + @Firstfield + &#39; IS NULL AND &#39; + @Firstfield + &#39; <> &#39; + &#39;&#39;&#39;&#39; + @FirstfieldValue + &#39;&#39;&#39;&#39;
--PRINT @mySql
exec(@mySql)

TRUNCATE TABLE WOOX_INFORMATICA..VBPA;
INSERT INTO WOOX_INFORMATICA..VBPA select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\InitialData\db_data.xlsx;HDR=YES', 'SELECT * FROM [VBPA$]')
--(适用于office2007及以上版本，2003用Jet - "Microsoft.Jet.OLEDB.4.0")

USE WOOX_CQM

EXECUTE sp_ActivateDistributedQueries
----------------------------- SAP Tables -----------------------------
DECLARE @LoadPath varchar(2000)
SET @LoadPath = 'C:\InitialData\SAPdata'

--import Excel--
EXECUTE sp_ReadXLSSource 
@LoadPath, 
'SAP_TCURF', 'SAP_TCURF.xls', '[Sheet1$]',
'[KURST],[FCURR],[TCURR],[GDATU], [FFACT],[TFACT],[FromDate],[ToDate]'

--import txt--
EXECUTE sp_ReadSource 
@LoadPath, 
'SAP_TVSTZ', 
'[VSBED],[LADGR],[WERKS],[LGORT],[VSTEL]'

select * 
FROM OpenDataSource ('Microsoft.ACE.OLEDB.12.0',
'Data Source="C:\InitialData\SAPdata";Extended properties=Text')...SAP_TW06S#txt