批量记录插入 [英] Bulk Record Insert

问题描述

我需要从一个表（多行）获取数据和修改，并增加了一些新领域后插入到另一个表。

I need to fetch data from one table (multiple rows) and insert into other table after modifying and adding some new fields.

例如：

表1 结果
商品ID，结果
价格，结果
QNT，结果
date_of_dispatch

Table 1
itemid,
price,
qnt,
date_of_dispatch

表2 结果
Invoiceid，结果
Invoicedate，结果
CUSTOMER_ID，结果
商品ID，结果
价格，结果
QNT，结果
total_amt，结果
date_of_dispatch，结果
grandtotal

Table2
Invoiceid,
Invoicedate,
customer_id,
itemid,
price,
qnt,
total_amt,
date_of_dispatch,
grandtotal

请帮我用的MS Access使它在ASP

Please help me to make it in asp with ms access

推荐答案

在遍历从第一个表中的记录将记录一个接一个

Insert the records one by one in the loop over the records from the first table:

Dim oConn1
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "connection string here"
Set oRS = oConn.Execute("Select * From Table1")
Do Until oRS.EOF
    strItemId = oRS("item_id")
    strPrice = oRS("price")
    '....get more data
    '....also add new fields like:
    Invoicedate = Date()
    customer_id = 500
    '.......
    If Request("confirm_" & strItemId)="1" Then
        strSQL = "Insert Into Table2 (itemid, price, customer_id, ...) Values ('" & strItemId & "', '" & strPrice & ', ..."
        oConn.Execute(strSQL)
        Response.Write("Item " & strItemId & " inserted<br />")
    Else  
        Response.Write("Confirm insert of item " & strItemId & " <input type=""checkbox"" name=""confirm_" & strItemId & """ value=""1"" />")
    End If
    oRS.MoveNext
Loop
oRS.Close

这是基本的做法，希望这是很清楚。

This is the basic approach, hope it's clear enough.

编辑：于确定每个记录增加的支持 - 现在复选框将显示每个记录，且仅当该复选框将被选中的INSERT语句会发生。这是考虑每个 ITEM_ID 为某种形式的钥匙，你可以使用任何其他独特的价值。

added support on confirming every record - now checkbox will appear for each record, and only if the checkbox will be Checked the INSERT statement will take place. This is taking each item_id as some sort of "key" you can use any other unique value as well.

这篇关于批量记录插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！