SQL Server2000 数据导入Excel
来源:互联网 发布:算法时间复杂度 编辑:程序博客网 时间:2024/05/01 04:12
/**//*+--------------------------------------
| 存储过程:SP_BulkInsertFromExcel
| 功能说明:根据Excel文件导入数据库中的表
| 维护记录:
| 调用方式:EXEC SP_BulkInsertFromExcel 'C:鞍山市地方税务局登记信息表.xls','aaaa','组织机构代码,注册号,企业名称,法定代表人,纳税人标识,注册地址,纳税人主管税务机关,主体税种,登记状态','地税登记信息'
| 联系方式:Spark.Zou@hotmail.com
| 创建日期:2007-05-07 22:26:09.873
| 注意事项:
| 版权信息: 邹黎鹏
--------------------------------------+*/
CREATE PROC SP_BulkInsertFromExcel
@fname NVARCHAR(260),
@sheename Nvarchar(256),
@columnname varchar(2000),
@TABLENAME VARCHAR(100)
as
set nocount on
declare @srv_name sysname,@sql nvarchar(4000),@COLUMN VARCHAR(2000),@IDENTITYNAME VARCHAR(100),@SQLWhere varchar(2000)
SELECT @COLUMN='',@IDENTITYNAME='',@SQLWhere='',@sql=''
SELECT @COLUMN=@COLUMN+','+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TABLENAME) AND NAME NOT IN
(
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME=@TABLENAME AND COLUMNPROPERTY(
OBJECT_ID(@TABLENAME),COLUMN_NAME,'IsIdentity')=1
)
ORDER BY COLORDER
SET @COLUMN=STUFF(@COLUMN,1,1,'')
SELECT
@SQLWhere=@SQLWhere+' and '+A.NAME+'<>'''' AND '
FROM SYSCOLUMNS A
LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID
WHERE A.ID=OBJECT_ID(@TABLENAME) AND A.ISNULLABLE!=1 AND ISNULL(E.TEXT,'')=''
SET @SQLWhere=stuff(@SQLWhere,1,1,'')
SET @SQLWhere=LEFT(@SQLWhere,LEN(@SQLWhere)-3)
if @SQLWhere<>''
begin
SET @sql=' INSERT INTO '+@TABLENAME+' ( '+@columnname+' )
SELECT '+@columnname+'
from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1;DATABASE='+@fname+''',['+@sheename+'$])
where 1=1 '+@SQLWhere
end
else
begin
SET @sql=' INSERT INTO '+@TABLENAME+' ( '+@columnname+' )
SELECT '+@columnname+'
from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1;DATABASE='+@fname+''',['+@sheename+'$])
'
end
Exec(@sql)
GO
| 存储过程:SP_BulkInsertFromExcel
| 功能说明:根据Excel文件导入数据库中的表
| 维护记录:
| 调用方式:EXEC SP_BulkInsertFromExcel 'C:鞍山市地方税务局登记信息表.xls','aaaa','组织机构代码,注册号,企业名称,法定代表人,纳税人标识,注册地址,纳税人主管税务机关,主体税种,登记状态','地税登记信息'
| 联系方式:Spark.Zou@hotmail.com
| 创建日期:2007-05-07 22:26:09.873
| 注意事项:
| 版权信息: 邹黎鹏
--------------------------------------+*/
CREATE PROC SP_BulkInsertFromExcel
@fname NVARCHAR(260),
@sheename Nvarchar(256),
@columnname varchar(2000),
@TABLENAME VARCHAR(100)
as
set nocount on
declare @srv_name sysname,@sql nvarchar(4000),@COLUMN VARCHAR(2000),@IDENTITYNAME VARCHAR(100),@SQLWhere varchar(2000)
SELECT @COLUMN='',@IDENTITYNAME='',@SQLWhere='',@sql=''
SELECT @COLUMN=@COLUMN+','+NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID(@TABLENAME) AND NAME NOT IN
(
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME=@TABLENAME AND COLUMNPROPERTY(
OBJECT_ID(@TABLENAME),COLUMN_NAME,'IsIdentity')=1
)
ORDER BY COLORDER
SET @COLUMN=STUFF(@COLUMN,1,1,'')
SELECT
@SQLWhere=@SQLWhere+' and '+A.NAME+'<>'''' AND '
FROM SYSCOLUMNS A
LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID
WHERE A.ID=OBJECT_ID(@TABLENAME) AND A.ISNULLABLE!=1 AND ISNULL(E.TEXT,'')=''
SET @SQLWhere=stuff(@SQLWhere,1,1,'')
SET @SQLWhere=LEFT(@SQLWhere,LEN(@SQLWhere)-3)
if @SQLWhere<>''
begin
SET @sql=' INSERT INTO '+@TABLENAME+' ( '+@columnname+' )
SELECT '+@columnname+'
from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1;DATABASE='+@fname+''',['+@sheename+'$])
where 1=1 '+@SQLWhere
end
else
begin
SET @sql=' INSERT INTO '+@TABLENAME+' ( '+@columnname+' )
SELECT '+@columnname+'
from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES;IMEX=1;DATABASE='+@fname+''',['+@sheename+'$])
'
end
Exec(@sql)
GO
- SQL Server2000 数据导入Excel
- excel数据导入到sql server2000中
- Excel数据导入到Sql server2000中
- SQL Server2000后台导入Excel数据
- 文本、Excel、Access数据导入SQL Server2000技巧
- Excel数据导入SQL Server2000的存储过程
- ACCESS 数据导入 SQL Server2000
- 从excel表导入sql server2000数据库
- 将Excel2003数据导入SQL Server2000
- 怎样将备份数据导入sql server2000??
- EXCEL数据导入SQL
- excel数据导入SQL
- Excel 数据导入sql
- EXCEL导入数据----SQL
- EXCEL数据导入SQL
- asp.net2.0将EXCEL导入到MS Sql server2000
- .NET中.TXT文件的数据导入到SQL SERVER2000
- SQL Server2000 数据导出Excel(自动创建有规则的SheetName)
- SQL Server2000 数据导出Excel(自动创建有规则的SheetName)
- ajax如何后台交互
- 怎样让静态分割条固定?~~~~~
- 不问你从哪里来
- 一个有关AWT 的问题
- SQL Server2000 数据导入Excel
- 在单文档界面中,怎样将视类区分割为两部分?
- 获得华南师范大学第七届软件设计大赛三等奖
- 在VC++的工程里,怎样设置头文件的搜索路径
- 二级指针
- 5.27
- linux_第7章 Linux下常用网络命令
- hq2x算法放大灰度图像,如何优化?
- 代码艺术