库里有表为yb_item
CREATE TABLE [dbo].[yb_item] (
[dm] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dmmc] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
[pym] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[wbm] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ybm] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[sfzl] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[fplb] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[fpmc] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cfbz] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[zfbl] [decimal](18, 4) NULL ,
[jx] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ypxh] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO相应的有个对应的excel文档,字段都是一一对应的。想把excel里的内容导入到这个表中,如何写语句实现?而不使用数据的导入导出功能。
CREATE TABLE [dbo].[yb_item] (
[dm] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dmmc] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
[pym] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[wbm] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ybm] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[sfzl] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[fplb] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[fpmc] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cfbz] [varchar] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[zfbl] [decimal](18, 4) NULL ,
[jx] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ypxh] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO相应的有个对应的excel文档,字段都是一一对应的。想把excel里的内容导入到这个表中,如何写语句实现?而不使用数据的导入导出功能。
'Data Source="d:\test.xls";
User ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$(f1)
'Data Source="d:\test.xls";
User ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$
'Data Source="d:\test.xls";--
1
insert into 表1
select * from 表2
以上是从表2向表1插入的基本格式
2
opendatasource('Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\test.xls";
表2换成了外部的数据源,
'Microsoft.Jet.OLEDB.4.0'是数据驱动
'Data Source="d:\test.xls是路径User ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$
--用户名,密码,sheet1是execel中的第一页
'Data Source="d:\test.xls";
不會成功吧!
那用DTS包是可以的
'Data Source="d:\yb_item.xls";
User ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$----运行结果
Server: Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'yb_item' can only be specified when a column list is used and IDENTITY_INSERT is ON.这里什么错?
SET IDENTITY_INSERT OFF
insert into yb_item select * from opendatasource('Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\yb_item.xls";
User ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$
SET IDENTITY_INSERT ON
補充說明:如果是網絡版在客戶端這樣導入是不能成功的,語法沒有錯誤,隻是SQL認為d:\yb_item.xls這是服務器的路徑