sql server 2008 导入导出数据大全

sql server 2008 导入导出数据大全

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

p_cmdshell 'bcp u out c: -c -q -

S"GNETDATA/GNETDATA" -U"sa" -P""'

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

SELECT *

FROM OpenDataSource( '.4.0',

'Data Source="c:";User ID=Admin;Password=;Extended properties=Excel

5.0')...xactions

/*动态文件名

declare @fn varchar(20),@s varchar(1000)

set @fn = 'c:'

set @s ='''.4.0'',

''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0'''

set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$'

exec(@s)

*/

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

FROM OpenDataSource( '.4.0',

'Data Source="c:";User ID=Admin;Password=;Extended properties=Excel

5.0')...xactions

/********************** EXCEL导到远程SQL

insert OPENDATASOURCE(

'SQLOLEDB',

'Data Source=远程ip;User ID=sa;Password=密码'

).库名.dbo.表名 (列名1,列名2)

SELECT 列名1,列名2

FROM OpenDataSource( '.4.0',

'Data Source="c:";User ID=Admin;Password=;Extended properties=Excel

5.0')...xactions

/** 导入文本文件

p_cmdshell 'ablename in c: -c -Sservername -Usa -

Ppassword'

/** 导出文本文件

p_cmdshell 'ablename out c: -c -Sservername -Usa -

Ppassword'

或

p_cmdshell 'bcp "Select * ablename" queryout c: -c -

Sservername -Usa -Ppassword'

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

p_cmdshell 'bcp "库名..表名" out "d:" -c -t ,-U sa -P password'

BULK INSERT 库名..表名

FROM 'c:'

WITH (

FIELDTERMINATOR = ';',

ROWTERMINATOR = 'n'

)

--/* dBase IV文件

select * from

OPENROWSET('.4.0'

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

--*/

--/* dBase III文件

select * from

OPENROWSET('.4.0'

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

--*/

--/* FoxPro 数据库

select * from openrowset('MSDASQL',

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

'select * from []')

--*/

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

select * from openrowset('MSDASQL',

'Driver=Microsoft Visual FoxPro Driver;

SourceDB=e:VFP98data;

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 []')

select * from 表

说明:

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

指定foxpro表的文件名.

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

insert into openrowset('.4.0',

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

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

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

'x:';'admin';'',A表)

文件名为参数

declare @fname varchar(20)

set @fname = 'd:'

exec('SELECT a.* FROM opendatasource(''.4.0'',

'''+@fname+''';''admin'';'''', topics) as a ')

SELECT *

FROM OpenDataSource( '.4.0',

'Data Source="f:";Jet OLEDB:Database Password=123;User

ID=Admin;Password=;')...产品

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

DECLARE @idoc int

DECLARE @doc varchar(1000)

--sample XML document

SET @doc ='

Customer was very satisfied

white red">

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

/**********************Excel导到Txt****************************************/

想用

select * into opendatasource(...) from opendatasource(...)

实现将一个Excel文件内容导入到一个文本文件

假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位)

且银行帐号导出到文本文件后分两部分，前8位和后8位分开。

邹健：

如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2

然后就可以用下面的语句进行插入

注意文件名和目录根据你的实际情况进行修改.

insert into

opendatasource('.4.0'

,'Text;HDR=Yes;DATABASE=C:'

)...[aa#txt]

--,aa#txt)

--*/

select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)

from

opendatasource('.4.0'

,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:'

--,Sheet1$)

)...[Sheet1$]

如果你想直接插入并生成文本文件,就要用bcp

declare @sql varchar(8000),@tbname varchar(50)

--首先将excel表内容导入到一个全局临时表

select @tbname='[##temp'+cast(newid() as varchar(40))+']'

,@sql='select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)

into '+@tbname+' from

opendatasource(''.4.0''

,''Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:''

)...[Sheet1$]'

exec(@sql)

--然后用bcp从全局临时表导出到文本文件

set @sql='bcp "'+@tbname+'" out "c:" /S"(local)" /P"" /c'

p_cmdshell @sql

--删除临时表

exec('drop table '+@tbname)

/********************导整个数据库*********************************************/

用bcp实现的存储过程

/*

实现数据导入/导出的存储过程

根据不同的参数,可以实现导入/导出整个数据库/单个表

调用示例:

--导出调用示例

----导出单个表

exec file2table 'zj','','','xzkh_sa..地区资料','c:',1

----导出整个数据库

exec file2table 'zj','','','xzkh_sa','C:docman',1

--导入调用示例

----导入单个表

exec file2table 'zj','','','xzkh_sa..地区资料','c:',0

----导入整个数据库

exec file2table 'zj','','','xzkh_sa','C:docman',0

*/

if exists(select 1 from sysobjects where name='File2Table' and objectproperty

(id,'IsProcedure')=1)

drop procedure File2Table

go

create procedure File2Table

@servername varchar(200) --服务器名

,@username varchar(200) --用户名,如果用NT验证方式,则为空''

,@password varchar(200) --密码

,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表

,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个

参数是文件存放路径,文件名自动用表名.txt

,@isout bit --1为导出,0为导入

as

declare @sql varchar(8000)

if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表

begin

set @sql='bcp '+@tbname

+case when @isout=1 then ' out ' else ' in ' end

+' "'+@filename+'" /w'

+' /S '+@servername

+case when isnull(@username,'')='' then '' else ' /U '+@username end

+' /P '+isnull(@password,'')

p_cmdshell @sql

end

else

begin --导出整个数据库,定义游标,取出所有的用户表

declare @m_tbname varchar(250)

if right(@filename,1)<>'' set @filename=@filename+''

set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where

xtype=''U'''

exec(@m_tbname)

open #tb

fetch next from #tb into @m_tbname

while @@fetch_status=0

begin

set @sql='bcp '+@tbname+'..'+@m_tbname

+case when @isout=1 then ' out ' else ' in ' end

+' "'+@filename+@m_tbname+'.txt " /w'

+' /S '+@servername

+case when isnull(@username,'')='' then '' else ' /U '+@username end

+' /P '+isnull(@password,'')

p_cmdshell @sql

fetch next from #tb into @m_tbname

end

close #tb

deallocate #tb

end

go

/************* Oracle **************/

EXEC sp_addlinkedserver 'OracleSvr',

'Oracle 7.3',

'MSDAORA',

'ORCLDB'

GO

delete from openquery(mailser,'select * from yulin')

select * from openquery(mailser,'select * from yulin')

update openquery(mailser,'select * from yulin where id=15')set disorder=555,catago=888

insert into openquery(mailser,'select disorder,catago from yulin')values(333,777)

补充：

对于用bcp导出,是没有字段名的.

用openrowset导出,需要事先建好表.

用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导入

posted on 2005-08-02 23:03 任搏软 阅读(1133) 评论(4) 编辑 收藏 引用 网摘 所属分类: DataBase

FeedBack:

# re: SQL语句导入导出大全[收集] 2005-08-06 00:32 任搏软

熟悉SQL SERVER 2000的数据库管理员都知道，其DTS可以进行数据的导入导出，其实，我们也可以使用

Transact-SQL语句进行导入导出操作。在Transact-SQL语句中，我们主要使用OpenDataSource函数、

OPENROWSET 函数，关于函数的详细说明，请参考SQL联机帮助。利用下述方法，可以十分容易地实现SQL

SERVER、ACCESS、EXCEL数据转换，详细说明如下：

一、SQL SERVER 和ACCESS的数据导入导出

常规的数据导入导出：

使用DTS向导迁移你的Access数据到SQL Server，你可以使用这些步骤:

1在SQL SERVER企业管理器中的Tools（工具）菜单上，选择Data Transformation

2Services（数据转换服务），然后选择 czdImport Data（导入数据）。

3在Choose a Data Source（选择数据源）对话框中选择Microsoft Access as the Source，然后键

入你的.mdb数据库(.mdb文件扩展名)的文件名或通过浏览寻找该文件。

4在Choose a Destination（选择目标）对话框中，选择Microsoft OLE DB Prov ider for SQL

Server，选择数据库服务器，然后单击必要的验证方式。

5在Specify Table Copy（指定表格复制）或Query（查询）对话框中，单击Copy tables（复制表格

）。

6在Select Source Tables（选择源表格）对话框中，单击Select All（全部选定）。下一步，完成。

Transact-SQL语句进行导入导出：

1. 在SQL SERVER里查询access数据:

-- ======================================================

SELECT *

FROM OpenDataSource( '.4.0',

'Data Source="c:";User ID=Admin;Password=')...表名

2.将access导入SQL server

-- ======================================================

在SQL SERVER 里运行:

SELECT *

INTO newtable

FROM OPENDATASOURCE ('.4.0',

'Data Source="c:";User ID=Admin;Password=' )...表名

3. 将SQL SERVER表里的数据插入到Access表中

-- ======================================================

在SQL SERVER 里运行：

insert into OpenDataSource( '.4.0',

'Data Source=" c:";User ID=Admin;Password=')...表名

(列名1,列名2)

select 列名1,列名2 from sql表

实例：

insert into OPENROWSET('.4.0',

'C:';'admin';'', Test)

select id,name from Test

INSERT INTO OPENROWSET('.4.0', 'c:'; 'admin'; '', 表名)

SELECT *

FROM sqltablename

二、 SQL SERVER 和EXCEL的数据导入导出

1、在SQL SERVER里查询Excel数据:

-- ======================================================

SELECT *

FROM OpenDataSource( '.4.0',

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

[Sheet1$]

下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。

SELECT *

FROM OpenDataSource ( '.4.0',

'Data Source="c:";User ID=Admin;Password=;Extended properties=Excel

5.0')...xactions

2、将Excel的数据导入SQL server :

-- ======================================================

SELECT * into newtable

FROM OpenDataSource( '.4.0',

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

[Sheet1$]

实例:

SELECT * into newtable

FROM OpenDataSource( '.4.0',

'Data Source="c:";User ID=Admin;Password=;Extended properties=Excel

5.0')...xactions

3、将SQL SERVER中查询到的数据导成一个Excel文件

-- ======================================================

T-SQL代码：

p_cmdshell 'bcp 库名.dbo.表名out c: -c -q -S"servername" -U"sa" -P""'

参数：S 是SQL服务器名；U是用户；P是密码

说明：还可以导出文本文件等多种格式

实例:p_cmdshell 'bcp ount out c: -c -q -

S"pmserver" -U"sa" -P"sa"'

p_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY

au_lname" queryout C: -c -Sservername -Usa -Ppassword'

在VB6中应用ADO导出EXCEL文件代码：

Dim cn As New tion

"Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"

e "p_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout

E: -c -Sservername -Usa -Ppassword'"

4、在SQL SERVER里往Excel插入数据:

-- ======================================================

insert into OpenDataSource( '.4.0',

'Data Source="c:";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1

(A1,A2,A3) values (1,2,3)

T-SQL代码：

INSERT INTO

OPENDATASOURCE('.4.0',

'Extended Properties=Excel 8.0;Data source=C:')...[Filiale1$]

(bestand, produkt) VALUES (20, 'Test')

总结：利用以上语句，我们可以方便地将SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换，

为我们提供了极大方便！

回复

# re: SQL语句导入导出大全[收集] 2005-08-06 00:37 任搏软

ASP导出Excel数据的四种方法

来源： aspsky 作者： tonny

一、使用OWC

什么是OWC？

OWC是Office Web Compent的缩写，即Microsoft的Office Web组件，它为在Web中绘制图形提供了灵

活的同时也是最基本的机制。在一个intranet环境中，如果可以假设客户机上存在特定的浏览器和一些功

能强大的软件（如IE5和Office 2000），那么就有能力利用Office Web组件提供一个交互式图形开发环境

。这种模式下，客户端工作站将在整个任务中分担很大的比重。

＜%Option Explicit

Class ExcelGen

Private objSpreadsheet

Private iColOffset

Private iRowOffset

Sub Class_Initialize()

Set objSpreadsheet = Object("sheet")

iRowOffset = 2

iColOffset = 2

End Sub

Sub Class_Terminate()

Set objSpreadsheet = Nothing 'Clean up

End Sub

Public Property Let ColumnOffset(iColOff)

If iColOff ＞ 0 then

iColOffset = iColOff

Else

iColOffset = 2

End If

End Property

Public Property Let RowOffset(iRowOff)

If iRowOff ＞ 0 then

iRowOffset = iRowOff

Else

iRowOffset = 2

End If

End Property Sub GenerateWorksheet(objRS)

'Populates the Excel worksheet based on a Recordset's contents

'Start by displaying the titles

If then Exit Sub

Dim objField, iCol, iRow

iCol = iColOffset

iRow = iRowOffset

For Each objField in

(iRow, iCol).Value =

s(iCol).AutoFitColumns

'设置Excel表里的字体

(iRow, iCol). = True

(iRow, iCol). = False

(iRow, iCol). = 10

(iRow, iCol).Halignment = 2 '居中

iCol = iCol + 1

Next 'objField

'Display all of the data

Do While Not

iRow = iRow + 1

iCol = iColOffset

For Each objField in

If IsNull() then

(iRow, iCol).Value = ""

Else

(iRow, iCol).Value =

s(iCol).AutoFitColumns

(iRow, iCol). = False

(iRow, iCol). = False

(iRow, iCol). = 10

End If

iCol = iCol + 1

Next 'objField

xt

Loop

End Sub Function SaveWorksheet(strFileName)

'Save the worksheet to a specified filename

On Error Resume Next

Call (strFileName, 0)

SaveWorksheet = ( = 0)

End Function

End Class

Dim objRS

Set objRS = Object("set")

"SELECT * FROM xxxx", "Provider=SQLOLEDB.1;Persist Security

Info=True;User ID=xxxx;Password=xxxx;Initial Catalog=xxxx;Data source=xxxx;"

Dim SaveName

SaveName = s("savename")("name")

Dim objExcel

Dim ExcelPath

ExcelPath = "Excel" & SaveName & ".xls"

Set objExcel = New ExcelGen

set = 1

Offset = 1

teWorksheet(objRS)

If rksheet(h(ExcelPath)) then

' "＜html＞＜body bgcolor='gainsboro' text='#000000'＞已保存为Excel文件.

＜a href='" & ode(ExcelPath) & "'＞下载＜/a＞"

Else

"在保存过程中有错误!"

End If

Set objExcel = Nothing

Set objRS = Nothing

%＞

二、用Excel的Application组件在客户端导出到Excel或Word

注意：两个函数中的“data“是网页中要导出的table的 id

＜input type="hidden" name="out_word" onclick="vbscript:buildDoc" value="导出到word"

class="notPrint"＞

＜input type="hidden" name="out_excel" onclick="AutomateExcel();" value="导出到excel"

class="notPrint"＞

导出到Excel代码

＜SCRIPT LANGUAGE="javascript"＞

＜!--

function AutomateExcel()

{

// Start Excel and get Application object.

var oXL = new ActiveXObject("ation");

// Get a new workbook.

var oWB = ();

var oSheet = Sheet;

var table = ;

var hang = ;

var lie = (0).;

// Add table headers going cell by cell.

for (i=0;i＜hang;i++)

{

for (j=0;j＜lie;j++)

{

(i+1,j+1).value = (i).cells(j).innerText;

}

}

e = true;

ntrol = true;

}

//--＞

＜/SCRIPT＞

导出到Word代码

＜script language="vbscript"＞

Sub buildDoc

set table =

row =

column = (1).

Set objWordDoc = CreateObject("nt")

theTemplate, False

e=True

Dim theArray(20,10000)

for i=0 to row-1

for j=0 to column-1

theArray(j+1,i+1) = (i).cells(j).innerTEXT

next

next

Before("综合查询结果集") //

显示表格标题

Before("")

Set rngPara = aphs(1).Range

With rngPara

.Bold = True //将标题设为粗体

.ent = 1 //将标题居中

. = "隶书" //设定标题字体

. = 18 //设定标题字体大小

End With

Set rngCurrent = aphs(3).Range

Set tabCurrent = (rngCurrent,row,column)

for i = 1 to column

(1).Rows(1).Cells(i).After theArray

(i,1)

(1).Rows(1).Cells

(i).ent=1

next

For i =1 to column

For j = 2 to row

(1).Rows(j).Cells(i).After theArray

(i,j)

(1).Rows(j).Cells

(i).ent=1

Next

Next

End Sub

＜/SCRIPT＞

三、直接在IE中打开，再存为EXCEL文件

把读出的数据用＜table＞格式，在网页中显示出来，同时，加上下一句即可把EXCEL表在客客户端显

示。

＜%tType ="application/-excel"%＞

注意：显示的页面中，只把＜table＞输出，最好不要输出其他表格以外的信息。

四、导出以半角逗号隔开的csv

用fso方法生成文本文件的方法，生成一个扩展名为csv文件。此文件，一行即为数据表的一行。生成

数据表字段用半角逗号隔开。（有关fso生成文本文件的方法，在此就不做介绍了）

CSV文件介绍 （逗号分隔文件）

选择该项系统将创建一个可供下载的CSV 文件； CSV是最通用的一种文件格式，它可以非常容易地被

导入各种PC表格及数据库中。

请注意即使选择表格作为输出格式，仍然可以将结果下载CSV文件。在表格输出屏幕的底部，显示有

"CSV 文件"选项，点击它即可下载该文件。

如果您把浏览器配置为将您的电子表格软件与文本（TXT）/逗号分隔文件（CSV） 相关联，当您下载

该文件时，该文件将自动打开。下载下来后，如果本地已安装EXCEL，点击此文件，即可自动用EXCEL软件

打开此文件。 回复

# re: SQL语句导入导出大全[收集] 2005-08-06 00:38 任搏软

标题 导入/导出Excel zjcxc（原作）

关键字 导入/导出Excel

从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:

/*===================================================================*/

--如果接受数据导入的表已经存在

insert into 表 select * from

OPENROWSET('.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:',sheet1$)

--如果导入数据并生成表

select * into 表 from

OPENROWSET('.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:',sheet1$)

/*===================================================================*/

--如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头

,就可以简单的用:

insert into OPENROWSET('.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:',sheet1$)

select * from 表

--如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:

--导出表的情况

p_cmdshell 'bcp 数据库名.dbo.表名 out "c:" /c -/S"服务器名" /U"用户名

" -P"密码"'

--导出查询的情况

p_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY

au_lname" queryout "c:" /c -/S"服务器名" /U"用户名" -P"密码"'

说明.

c: 为导入/导出的Excel文件名.

sheet1$ 为Excel文件的工作表名,一般要加上$才能正常使用.

下面是导出真正Excel文件的方法:

/*--数据导出EXCEL

导出表中的数据到Excel,包含字段名,文件为真正的Excel文件

,如果文件不存在,将自动创建文件

,如果表不存在,将自动创建表

基于通用性考虑,仅支持导出标准数据类型

---*/

/*--调用示例

p_exporttb @tbname='地区资料',@path='c:',@fname=''

--*/

if exists (select * from ects where id = object_id(N'[dbo].[p_exporttb]') and

OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[p_exporttb]

GO

create proc p_exporttb

@tbname sysname, --要导出的表名,注意只能是表名/视图名

@path nvarchar(1000), --文件存放目录

@fname nvarchar(250)='' --文件名,默认为表名

as

declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int

declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--参数检测

if isnull(@fname,'')='' set @fname=@tbname+'.xls'

--检查文件是否已经存在

if right(@path,1)<>'' set @path=@path+''

create table #tb(a bit,b bit,c bit)

set @sql=@path+@fname

insert into #tb p_fileexist @sql

--数据库创建语句

set @sql=@path+@fname

if exists(select 1 from #tb where a=1)

set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'

+';CREATE_DB="'+@sql+'";DBQ='+@sql

else

set @constr='Provider=.4.0;Extended Properties="Excel 5.0;HDR=YES'

+';DATABASE='+@sql+'"'

--连接数据库

exec @err=sp_oacreate 'tion',@obj out

if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr

if @err<>0 goto lberr

--创建表的SQL

select @sql='',@fdlist=''

select @fdlist=@fdlist+','+

,@sql=@sql+',['++'] '

+case when in('char','nchar','varchar','nvarchar') then

'text('+cast(case when >255 then 255 else end as varchar)+')'

when in('tynyint','int','bigint','tinyint') then 'int'

when in('smalldatetime','datetime') then 'datetime'

when in('money','smallmoney') then 'money'

else end

FROM syscolumns a left join systypes b on =ype

where not in

('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')

and object_id(@tbname)=id

select @sql='create table ['+@tbname

+']('+substring(@sql,2,8000)+')'

,@fdlist=substring(@fdlist,2,8000)

exec @err=sp_oamethod @obj,'execute',@out out,@sql

if @err<>0 goto lberr

exec @err=sp_oadestroy @obj

--导入数据

set @sql='openrowset(''.4.0'',''Excel 5.0;HDR=YES

;DATABASE='+@path+@fname+''',['+@tbname+'$])'

exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)

return

lberr:

exec sp_oageterrorinfo 0,@src out,@desc out

lbexit:

select cast(@err as varbinary(4)) as 错误号

,@src as 错误源,@desc as 错误描述

select @sql,@constr,@fdlist

go

*--数据导出EXCEL

导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件

,如果文件不存在,将自动创建文件

,如果表不存在,将自动创建表

基于通用性考虑,仅支持导出标准数据类型

--*/

/*--调用示例

p_exporttb @sqlstr='select * from 地区资料'

,@path='c:',@fname='',@sheetname='地区资料'

--*/

if exists (select * from ects where id = object_id(N'[dbo].[p_exporttb]') and

OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[p_exporttb]

GO

create proc p_exporttb

@sqlstr sysname, --查询语句,如果查询语句中使用了order by ,请加上top 100 percent,注意,如果导

出表/视图,用上面的存储过程

@path nvarchar(1000), --文件存放目录

@fname nvarchar(250), --文件名

@sheetname varchar(250)='' --要创建的工作表名,默认为文件名

as

declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int

declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--参数检测

if isnull(@fname,'')='' set @fname=''

if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')

--检查文件是否已经存在

if right(@path,1)<>'' set @path=@path+''

create table #tb(a bit,b bit,c bit)

set @sql=@path+@fname

insert into #tb p_fileexist @sql

--数据库创建语句

set @sql=@path+@fname

if exists(select 1 from #tb where a=1)

set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'

+';CREATE_DB="'+@sql+'";DBQ='+@sql

else

set @constr='Provider=.4.0;Extended Properties="Excel 5.0;HDR=YES'

+';DATABASE='+@sql+'"'

--连接数据库

exec @err=sp_oacreate 'tion',@obj out

if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr

if @err<>0 goto lberr

--创建表的SQL

declare @tbname sysname

set @tbname='##tmp_'+convert(varchar(38),newid())

set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'

exec(@sql)

select @sql='',@fdlist=''

select @fdlist=@fdlist+','+

,@sql=@sql+',['++'] '

+case when in('char','nchar','varchar','nvarchar') then

'text('+cast(case when >255 then 255 else end as varchar)+')'

when in('tynyint','int','bigint','tinyint') then 'int'

when in('smalldatetime','datetime') then 'datetime'

when in('money','smallmoney') then 'money'

else end

FROM tempdb..syscolumns a left join tempdb..systypes b on =ype

where not in

('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')

and =(select id from tempdb..sysobjects where name=@tbname)

select @sql='create table ['+@sheetname

+']('+substring(@sql,2,8000)+')'

,@fdlist=substring(@fdlist,2,8000)

exec @err=sp_oamethod @obj,'execute',@out out,@sql

if @err<>0 goto lberr

exec @err=sp_oadestroy @obj

--导入数据

set @sql='openrowset(''.4.0'',''Excel 5.0;HDR=YES

;DATABASE='+@path+@fname+''',['+@sheetname+'$])'

exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')

set @sql='drop table ['+@tbname+']'

exec(@sql)

return

lberr:

exec sp_oageterrorinfo 0,@src out,@desc out

lbexit:

select cast(@err as varbinary(4)) as 错误号

,@src as 错误源,@desc as 错误描述

select @sql,@constr,@fdlist

go

回复

# re: SQL语句导入导出大全[收集] 2005-08-07 22:19 任搏软

--ACCESS中操作 SQL Server 数据库,需要你能连接远程的 SQL Server 服务器:

然后打开ACCESS数据库(用ACCESS打开/程序中打开均可)

--导入数据到ACCESS数据库中

--如果是下载SQL的数据(表已经存在的情况)

insert into 表名

SELECT *

FROM [ODBC;Driver=SQL Server;Server=远程SQL服务器名;Uid=用户名;Pwd=密码;database=数据库名].

表名

--如果是下载SQL的数据(表不存在的情况)

SELECT * into 表名

FROM [ODBC;Driver=SQL Server;Server=远程SQL服务器名;Uid=用户名;Pwd=密码;database=数据库名].

表名

--如果是上传数据库到SQL(表已经存在的情况):

insert into [ODBC;Driver=SQL Server;Server=远程SQL服务器名;Uid=用户名;Pwd=密码;database=数据

库名].表名

SELECT * FROM 表名

--如果是上传数据库到SQL(表不存在的情况):

SELECT * into [ODBC;Driver=SQL Server;Server=远程SQL服务器名;Uid=用户名;Pwd=密码;database=数

据库名].表名

FROM 表名

--*/