/**/
/*+--------------------------------------
| 存储过程: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