set IDENTITY_INSERT temporary_invoice_backup3 on
insert into temporary_invoice_backup3 (除了自增列之外全部列名写上)
select 列名要一一对应 from temporary_invoice
where xxxx
set IDENTITY_INSERT temporary_invoice_backup3 off语法是这样
insert into temporary_invoice_backup3 (除了自增列之外全部列名写上)
select 列名要一一对应 from temporary_invoice
where xxxx
set IDENTITY_INSERT temporary_invoice_backup3 off语法是这样
sql = "set IDENTITY_INSERT temporary_invoice_backup3 on "
sql = "insert into temporary_invoice_backup3 origin(列名) select origin(列名) from temporary_invoice where xxxx
sql = "set IDENTITY_INSERT temporary_invoice_backup3 off"
不要set那两句执行的sql语句为:
insert into temporary_invoice id,origin select id,origin from temporary_invoice_backup3 where supplier = '1015035' and week_name = 'WK21' and sctuid = 'ALY033'报错id附近有语法错误。
如果加上set那两句则执行的是:
sql = "set IDENTITY_INSERT temporary_invoice_backup3 on "
sql = "insert into temporary_invoice id,origin select id,origin from temporary_invoice_backup3 where supplier = '" & Forms!Main!supplier & "' and week_name = '" & Forms!Main!week_name & "' and sctuid = '" & Forms!Main!sctuid & "'" sql = "set IDENTITY_INSERT temporary_invoice_backup3 off"
不报错了,但是数据没有被复制过去
select col1,col2,col3,...
from 表1
我现在执行的是
sql = "set IDENTITY_INSERT temporary_invoice_backup3 on "
sql = "insert into temporary_invoice id,origin select id,origin from temporary_invoice_backup3 where supplier = '1015035' and week_name = 'WK21' and sctuid = 'ALY033'" sql = "set IDENTITY_INSERT temporary_invoice_backup3 off"执行成功了,但符合这个条件的有5条数据,只有第一条被复制过去了,大概是什么原因后面的没有被复制过去呢
sql = "set IDENTITY_INSERT temporary_invoice_backup3 on "
sql = "insert into temporary_invoice id,origin select id,origin from temporary_invoice_backup3 where supplier = '1015035' and week_name = 'WK21' and sctuid = 'ALY033'" sql = "set IDENTITY_INSERT temporary_invoice_backup3 off"执行成功了,但符合这个条件的有5条数据,只有第一条被复制过去了,大概是什么原因后面的没有被复制过去呢
这句有5条数据?
我又修改了下,现在执行的语句是
sql = "set IDENTITY_INSERT temporary_invoice_backup3 on "
sql = “INSERT INTO temporary_invoice SELECT temporary_invoice_backup3.* FROM temporary_invoice_backup3 where supplier = '1147648' and week_name = 'AWK17' and sctuid = 'ALY033' " sql = "set IDENTITY_INSERT temporary_invoice_backup3 off"也不报错,但是数据被copy过去
额意思现在符合这3个条件的数据有5条,在temporary_invoice_backup3表里面,我想执行一个sql语句,把这5条数据
insert到temporary_invoice这张表里面
那这个语句没错啊
但是报错报错为仅当使用了列列表并且IDENTITY_INSERT为ON时,才能为表“temporary_invoice_backup3 ”中的标识列指定显示值...
恩 语句是没错,但是数据没有被insert过去temporary_invoice这张表
sql = "set IDENTITY_INSERT temporary_invoice_backup3 on "
sql = "insert into temporary_invoice id,origin select id,origin from temporary_invoice_backup3 where supplier = '1015035' and week_name = 'WK21' and sctuid = 'ALY033'" sql = "set IDENTITY_INSERT temporary_invoice_backup3 off"1.这三条语句,最好你执行三次,如果执行一次话,可以考虑加一下 go
2.放在 sp 里跑吧,这个比较方便。