INSERT into biao select * from b 你这句SQL本身就是错误的?前面是插入表后面是查询表怎么可能写一起dim RS as new adodb.recordset dim COnn as new adodb.connetion dim strSQL as stringStrSql="Select * from b" if rs.state<>adstateclosed then rs.close rs.open strSQL ,conn,3,3strSQL="INSERT into biao " COnn.Execute strSQL
你用recordset吧 先用这个 select gh,lx,gg,sum(zs)as exp1,sum(sl) as epx2 from a group by gh,lx,gg 获取记录集 再用"insert into"语句
strsql="insert into tablea select * from tableb" cn.execute strsqlcn是你的连接对象
Dim cn As New Connection Dim mk As New Recordset With cn .Provider = "SQLOLEDB" .ConnectionString = "UserID=sa;Password=123;Data Source=199.10.10.10;Initial Catalog=xxgl" .Open End With With mk Set .ActiveConnection = cn .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockPessimistic .Open "select hth,gh,bz,gg,sum(zs) as zs,sum(sl) as sl from cpzck group by hth,gh,bz,gg where hth='" & Trim(Text1) & "'" If .RecordCount > 0 Then .ActiveCommand = "insert into cpcw" End If End With 那位老大 帮我改改
insert into new_table(col1,col2..) select gh,lx,gg,sum(zs),sum(sl) from a group by gh,lx,gg如果新表与查询的结果表字段不完全相同,要col1,col2换成对应的列 如果结构相同insert into new_table select gh,lx,gg,sum(zs),sum(sl) from a group by gh,lx,gg
Dim cn As New Connection Dim mk As New Recordset With cn .Provider = "SQLOLEDB" .ConnectionString = "UserID=sa;Password=123;Data Source=199.10.10.10;Initial Catalog=xxgl" .Open End Withdim strsql as string strsql="insert into tableb (相应的列名) " _ & " select hth,gh,bz,gg,sum(zs) as zs,sum(sl) as sl from cpzck " _ & " where hth='" & Trim(Text1) & "' group by hth,gh,bz,gg "cn.execute strsql
group by 要放在 where 后面
我知道了 但是能告诉我 怎么写成代码呀,我写的有问题呀 Dim cn As New Connection Dim mk As New Recordset With cn .Provider = "SQLOLEDB" .ConnectionString = "User ID=sa;PWD=123;Data Source=(local);Initial Catalog=xxgl" .Open End With With mk Set .ActiveConnection = cn .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockPessimistic .Open "insert into newtable select hth,gh,bz,gg,sum(zs) as zs,sum(sl) as sl from cpzck group by hth,gh,bz,gg where hth='" & Trim(Text1) & "'" End With 这段代码有问题呀
你这句SQL本身就是错误的?前面是插入表后面是查询表怎么可能写一起dim RS as new adodb.recordset
dim COnn as new adodb.connetion
dim strSQL as stringStrSql="Select * from b"
if rs.state<>adstateclosed then rs.close
rs.open strSQL ,conn,3,3strSQL="INSERT into biao "
COnn.Execute strSQL
单号 钢号 类型 规格 支数 重量 总计(重量最后人工输入)
dh gh lx gg zs sl zj
01 abc 123 12*5 12
02 abc 123 12*5 15
03 abc 123 12*5 18
04 abc 123 12*5 45
05 abc 123 12*51 42
06 qwe 234 12*51 45
07 qwe 234 12*51 19
08 qwe 234 12*51 18
09 qwe 234 12*51 30
10 qwe 234 12*51 15下面对这个表 钢号 类型 规格 分类汇总
select gh,lx,gg,sum(zs),sum(sl) from a group by gh,lx,gg
我想问的是 如何将汇总后的记录 添加到另一个表中去???有一个表 a
单号 钢号 类型 规格 支数 重量 总计(重量最后人工输入)
dh gh lx gg zs sl zj
01 abc 123 12*5 12
02 abc 123 12*5 15
03 abc 123 12*5 18
04 abc 123 12*5 45
05 abc 123 12*51 42
06 qwe 234 12*51 45
07 qwe 234 12*51 19
08 qwe 234 12*51 18
09 qwe 234 12*51 30
10 qwe 234 12*51 15下面对这个表 钢号 类型 规格 分类汇总
select gh,lx,gg,sum(zs),sum(sl) from a group by gh,lx,gg
我想问的是 如何将汇总后的记录 添加到另一个表中去???有一个表 a
单号 钢号 类型 规格 支数 重量 总计(重量最后人工输入)
dh gh lx gg zs sl zj
01 abc 123 12*5 12
02 abc 123 12*5 15
03 abc 123 12*5 18
04 abc 123 12*5 45
05 abc 123 12*51 42
06 qwe 234 12*51 45
07 qwe 234 12*51 19
08 qwe 234 12*51 18
09 qwe 234 12*51 30
10 qwe 234 12*51 15下面对这个表 钢号 类型 规格 分类汇总
select gh,lx,gg,sum(zs),sum(sl) from a group by gh,lx,gg
我想问的是 如何将汇总后的记录 添加到另一个表中去???回复人: flying02() ( ) 信誉:100
dim cn as ado.connection
dim dbstr as string
dbstr = "Provider=SQLOLEDB.1;Password=" & PW & ";Persist Security Info=True;User ID=" & ID & ";Initial Catalog=" & DBNM & ";Data Source=" & IPSet s_cnn = New ADODB.Connection
s_cnn.Open dbstr
s_cnn.Execute "insert into new_table select gh,lx,gg,sum(zs),sum(sl) from a group by gh,lx,gg"
这好象也行啊 只是 NEWTABLE 的结构要包含 选出的记录集的结构 字段名要一样
这好象也行啊 只是 NEWTABLE 的结构要包含 选出的记录集的结构 字段名要一样
先用这个
select gh,lx,gg,sum(zs)as exp1,sum(sl) as epx2 from a group by gh,lx,gg
获取记录集
再用"insert into"语句
cn.execute strsqlcn是你的连接对象
Dim mk As New Recordset
With cn
.Provider = "SQLOLEDB"
.ConnectionString = "UserID=sa;Password=123;Data Source=199.10.10.10;Initial Catalog=xxgl"
.Open
End With
With mk
Set .ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockPessimistic
.Open "select hth,gh,bz,gg,sum(zs) as zs,sum(sl) as sl from cpzck group by hth,gh,bz,gg where hth='" & Trim(Text1) & "'"
If .RecordCount > 0 Then
.ActiveCommand = "insert into cpcw"
End If
End With
那位老大 帮我改改
select gh,lx,gg,sum(zs),sum(sl) from a group by gh,lx,gg如果新表与查询的结果表字段不完全相同,要col1,col2换成对应的列
如果结构相同insert into new_table
select gh,lx,gg,sum(zs),sum(sl) from a group by gh,lx,gg
Dim mk As New Recordset
With cn
.Provider = "SQLOLEDB"
.ConnectionString = "UserID=sa;Password=123;Data Source=199.10.10.10;Initial Catalog=xxgl"
.Open
End Withdim strsql as string
strsql="insert into tableb (相应的列名) " _
& " select hth,gh,bz,gg,sum(zs) as zs,sum(sl) as sl from cpzck " _
& " where hth='" & Trim(Text1) & "' group by hth,gh,bz,gg "cn.execute strsql
Dim cn As New Connection
Dim mk As New Recordset
With cn
.Provider = "SQLOLEDB"
.ConnectionString = "User ID=sa;PWD=123;Data Source=(local);Initial Catalog=xxgl"
.Open
End With
With mk
Set .ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockPessimistic
.Open "insert into newtable select hth,gh,bz,gg,sum(zs) as zs,sum(sl) as sl from cpzck group by hth,gh,bz,gg where hth='" & Trim(Text1) & "'"
End With
这段代码有问题呀
在查询分析其里面一试不就知道了么(如果你用sql server)