--数据表备份
select *into 目标表 from 备份表;
--Excel2007 导入到sql server2005
--1.启用Ad Hoc Distributed Queries(为导入数据做准备)
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--2.将excel2007的数据和表结构直接copy到sql server 2007
SELECT * into exceltosql
FROM OpenDataSource( 'Microsoft.Ace.OleDB.12.0','Data Source="e:\1.xlsx";User ID=Admin;Password=;Extended Properties=Excel 12.0')...[Sheet1$]
改成这种格式:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE procExcel2007ToSQL2005
@tableName varchar(50),--表名(sql2005)
@excelPath varchar(50) --Excel路径
AS
DECLARE @SQL NVARCHAR(1000)
BEGIN
SET NOCOUNT ON;
SELECT * into @tableName
FROM OpenDataSource('Microsoft.Ace.OleDB.12.0','Data Source="e:\1.xlsx";User ID=Admin;Password=;Extended Properties=Excel 12.0')...[Sheet1$]
END
GO