代码为:
Dim rs1 As New ADODB.Recordset
Set rs1 = CreateObject("ADODB.Recordset")
strSql = "insert into A "
rs1.Open strSql, cn1, adOpenForwardOnly, adLockReadOnly
Dim rs2 As New ADODB.Recordset
Set rs2 = CreateObject("ADODB.Recordset")
strSql = "select * from B"
rs2.Open strSql, cn2, adOpenForwardOnly, adLockReadOnlyrs1为SQL SERVER,rs2为Interbase,现在是把interbase中的数据整个转到sql server中,可是每次总是报错,说 A 附近有语法错误,为什么呢?请教各位
Dim rs1 As New ADODB.Recordset
Set rs1 = CreateObject("ADODB.Recordset")
strSql = "insert into A "
rs1.Open strSql, cn1, adOpenForwardOnly, adLockReadOnly
Dim rs2 As New ADODB.Recordset
Set rs2 = CreateObject("ADODB.Recordset")
strSql = "select * from B"
rs2.Open strSql, cn2, adOpenForwardOnly, adLockReadOnlyrs1为SQL SERVER,rs2为Interbase,现在是把interbase中的数据整个转到sql server中,可是每次总是报错,说 A 附近有语法错误,为什么呢?请教各位
--
这样可以吗?没有值呀.INSERT的格式:
INSERT INTO MyTable (PriKey, Description)
VALUES (123, 'A description of part 123.')
//还是我没看明白你说的?
格式是不是这样的:insert into A select * from B
A跟B的数据库结构一样
这个格式应该是对的吧
cn2.excute "insert into A"
不过A表的字段不为空的字段要有默认值
cn2.excute "insert into A select * FROM B"
不过A表的结构要和B的相同
sql="select * from b into a"
dim com as new command
com.a
com.exect(sql)
记不住属性名了,你执行这个语句应该就能实现你要的效果
因为A是SQL SERVER数据库
B是INTERBASE数据库
好象有点麻烦
strSql = "select * from B"
rs2.Open strSql, cn2, adOpenForwardOnly, adLockReadOnly strSql = "select * from A"
rs1.Open strSql, cn1, 1,3
while not rs2.eof
rs1.addnew
rs1("field1").value=rs2("field1").value
rs1("field2").value=rs2("field2").value
...
rs2.movenext
wend
rs1.UpdateBatch adAffectAll(类似这样,以上代码未经调试,你试一下先.不过这样效率不高,如果数据多的话)--
另外还有一个方法:
把rs2查到的值,化成INSERT INTO 语句,然后用cn.execute sSql提交.例下:
sSql=""
while not rs2.eof
sSql=sSql+"insert into A values('" & rs2(0) & "','" & rs2(1) & "','"....'余略
rs2.movenext
wend
cn1.execute sSql '此cn1为联接到sql server之Connection对象
strSql = "select * from B"
rs2.Open strSql, cn2, adOpenForwardOnly, adLockReadOnly strSql = "select * from A"
rs1.Open strSql, cn1, 1,3
while not rs2.eof
rs1.addnew
rs1("field1").value=rs2("field1").value
rs1("field2").value=rs2("field2").value
...
rs2.movenext
wend
rs1.UpdateBatch adAffectAll
怕是只能这样做吧,别的办法可以吗?这样做估计还是最好的办法
第二个方法能说的清楚点吗?--
第二个方法,其实就是产生许多INSERT INTO语句,一次性提交给SQL SERVER让它执行。
(是可以的哟,就像.sql文件在SQL SERVER里执行一样,不信你试试)
sSql=sSql+"insert into A values('" & rs2(0) & "','" & rs2(1) & "')" & vbcrlf//这里应该要加个回车vbcrlf才行。gn.
Set rs1 = CreateObject("ADODB.Recordset") '--不必要
strSql = "insert into A " '--sql 语句完全错误,如何能插
rs1.Open strSql, cn1, adOpenForwardOnly, adLockReadOnly
Dim rs2 As New ADODB.Recordset
Set rs2 = CreateObject("ADODB.Recordset")‘--不必要
strSql = "select * from B"
rs2.Open strSql, cn2, adOpenForwardOnly, adLockReadOnly