mssql sql语句导入mysql_MSSQL数据库导入导出大全二(SQL语句)

Excel文件导入数据库多个Sheet

if exists(select 1 from sysobjects where name=N'p_import_excel' and type='P')

drop proc p_import_excel

go

--调用示例exec p_import_excel 'c:\123.xls','Sheet1;Sheet2;Sheet3','tbl'

create proc p_import_excel

@excfilename nvarchar(100),--文件路径

@sheet nvarchar(1000), --所要导的sheet，以';'分隔,如'Sheet1;Sheet2;Sheet3'

@tblname nvarchar(100)--要导入的表名

as

begin

set @sheet=@sheet+';'

DECLARE @excsheet nvarchar(100)

while charindex(';',@sheet)>0

begin

select @excsheet=left(@sheet,charindex(';',@sheet)-1)

EXEC('SELECT * into '+@tblname+'  FROM openrowSET(''microsoft.jet.oledb.4.0'',''Excel

8.0;HDR=YES,DATABASE='+@excfileName+''',''select * from ['+@excsheet+'$]'')  ')

set @sheet=stuff(@sheet,1,charindex(';',@sheet),'')

end

end

go

无论excel版本是什么，在Extended Properties里都要写Excel 8.0，注意空格

HDR=yes：说明excel文件的第一行是列字，而不是数据。如果第一行也是数据的话，用HDR=no。默认值为yes

IMEX=1：就是将混合型的列转换成文本列

注意查询语句：工作表名要用[$]框起来

/*******  导出到excel

EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'

/***********  导入Excel

SELECT *

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+'　' 转换后的别名

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/** 导入文本文件

EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword'

/** 导出文本文件

EXEC master..xp_cmdshell 'bcp "dbname..tablename" out c:\DT.txt -c -Sservername -Usa -Ppassword'

或

EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'

导出到TXT文本，用逗号分开

exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'

BULK INSERT 库名..表名

FROM 'c:\test.txt'

WITH (

FIELDTERMINATOR = ';',

ROWTERMINATOR = '\n'

)

--/* dBase IV文件

select * from

OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料4.dbf]')

--*/

--/* dBase III文件

select * from

OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料3.dbf]')

--*/

--/* FoxPro 数据库

select * from openrowset('MSDASQL',

'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',

'select * from [aa.DBF]')

--*/

/**************导入DBF文件****************/

select * from openrowset('MSDASQL',

'Driver=Microsoft Visual FoxPro Driver;

SourceDB=e:\VFP98\data;

SourceType=DBF',

'select * from customer where country != "USA" order by country')

go

/***************** 导出到DBF ***************/

如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句

insert into openrowset('MSDASQL',

'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',

'select * from [aa.DBF]')

select * from 表

说明:

SourceDB=c:\  指定foxpro表所在的文件夹

aa.DBF        指定foxpro表的文件名.

/*************导出到Access********************/

insert into openrowset('Microsoft.Jet.OLEDB.4.0',

'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表

/*************导入Access********************/

insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',

'x:\A.mdb';'admin';'',A表)

*********************  导入 xml　文件

DECLARE @idoc int

DECLARE @doc varchar(1000)

--sample XML document

SET @doc ='

Customer was very satisfied

Important

Happy Customer.

'

-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, '/root/Customer/Order', 1)

WITH (oid     char(5),

amount  float,

comment ntext 'text()')

EXEC sp_xml_removedocument @idoc