?
Solved

INSERT INTO + Update additional fields in the to file.

Posted on 2012-04-04
11
Medium Priority
?
319 Views
Last Modified: 2012-04-06
I'm inserting data from one database to another but I also want it to update/populate additional fields that aren't in the original database.  How would I add that to the following code?

CurrentProject.Connection.Execute "INSERT INTO SupplierRFQSLT SELECT * FROM SUPPLIERRFQ"
0
Comment
Question by:CompTech810
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 37808425
If the columns aren't in the target database you'll need to add them first with an ALTER statement. It has the following syntax:


ALTER TABLE table_name
  ADD column_name column-definition;
0
 
LVL 3

Expert Comment

by:Emenizer
ID: 37808558
If you want to update some fields (or populate in case of empty) than you run an UPDATE query.
The syntax is something like this:
UPDATE table_name
SET column1=value, column2=value2
WHERE column0=value0

The values in the SET statement can also be references to or formulas using other columns instead of just hard-coded values.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 37809805
You supply those values as expressions.
Rearrange your query like this:
INSERT INTO 
  SupplierRFQSLT 
  (FieldA, FieldB, FieldC, FieldD, FieldE)
SELECT 
  SourceFieldForFieldA,
  SourceFieldForFieldB,
  'SomeStringExpression' AS FieldC,
  1234 As FieldD,
  SourceFieldForFieldE
FROM 
  SUPPLIERRFQ

Open in new window


/gustav
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 2

Author Comment

by:CompTech810
ID: 37811555
Maybe I'm thinking this the wrong way.

What I want to do I is loop through a query and create a new record(for each loop from query) in the SupplierRfqSlt file by writing all fields from the SupplierRFQ file, which is only one record, and while writing all the fields from SupplierRFQ populate a few fields that are in the SupplierRFQSlt file but not in the SupplierRFQ at the same time.  I want to be able to use '*' instead of typing each field and only type the few fields that do not come from SupplierRFQ.  I already have the loop working.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 37811636
Here is how to do a fast copy of records using DAO avoiding a loop with SQL insert:
Public Sub CopyRecords()
  
  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  Dim strSQL      As String
  Dim lngLoop     As Long
  Dim lngCount    As Long

  strSQL = "SELECT * FROM tblStatus WHERE Location = '" & _
                "DEFx" & "' Order by Total"

  Set rstInsert = CurrentDb.OpenRecordset(strSQL)
  Set rstSource = rstInsert.Clone
  With rstSource
    lngCount = .RecordCount
    For lngLoop = 1 To lngCount
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "Total" Then
                ' Insert default job code.
                  datNow = Now
                rstInsert.Fields(.Name).Value = 0
              ElseIf .Name = "PROCESSED_IND" Then
                rstInsert.Fields(.Name).Value = vbNullString
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
      End With
      .MoveNext
    Next
    rstInsert.Close
    .Close
  End With
  
  Set rstInsert = Nothing
  Set rstSource = Nothing
  
End Sub

Open in new window

Of course, you will have to modify this to match the fields of your source query/table and to the field names of this.

/gustav
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37813103
I'm not a VBA programmer so I'm trying to understand the code you provided.

Is tblstatus the tabel that is being cloned to?  Please give a little explaination of what the code is doing, I'm kind of lost when I read the code.....  I'm a RPG programmer...
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 37813280
rstSource and rstInsert are the two recordsets. You will have to adjust the SQL for both as the code here is for copying records from a table to the same table.

It loops through the records, and for each record it loops through the fields, copying/inserting the value. The If .. Else If .. section is for exceptions to the straight copy done after the Else line.

/gustav
0
 
LVL 2

Author Comment

by:CompTech810
ID: 37815818
Ok, I am pretty lost here......  I'm going to bump up the points because this is clear as mud.

Could you change your code to reflect the tables and two fields that don't exist in the to table.

The table that I'm reading from is SupplierRFQ, it has one record.  I want to copy that one record to SupplierRFQSlt which is identical to the SupplierRFQ with the exception of a few fields.  I am using a query that loops through a contact database that has contacts.  I need to add the record from SupplierRFQ to SupplierRFQSlt for each contact in the query and populate the additional fields in SupplierRFQSlt during that add.

The query loop I have in place is working fine with the exception of adding the single record to the table SUpplierRFQSlt for each contact the query finds..
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 750 total points
ID: 37816960
That would be something like this (air code):
Public Sub CopyRecords()
  
  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  Dim strSQL      As String
  Dim lngLoop     As Long
  Dim lngCount    As Long

  strSQL = "SELECT * FROM SupplierRFQ"
  Set rstSource = CurrentDb.OpenRecordset(strSQL)

  strSQL = "SELECT * FROM SupplierRFQSlt"
  Set rstInsert = CurrentDb.OpenRecordset(strSQL)

  With rstSource
    lngCount = .RecordCount
    For lngLoop = 1 To lngCount
      With rstInsert
        .AddNew
          ' Copy fields from source table.
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
          ' Insert fixed values. Adjust to your needs:
          rstInsert.Fields("YourFirstAdditionalFieldName").Value = dblSomeValue
          rstInsert.Fields("YourSecondAdditionalFieldName").Value = strSomeString
          ' etc.
          rstInsert.Fields("YourLastAdditionalFieldName").Value = lngSomeOtherValue
        .Update
      End With
      .MoveNext
    Next
    rstInsert.Close
    .Close
  End With
  
  Set rstInsert = Nothing
  Set rstSource = Nothing
  
End Sub

Open in new window


dblSomeValue etc. must either be passed as parameters or declared (Dim'ed) in the function and assigned proper values matching the data types of the fields of table SupplierRFQSlt.
/gustav
0
 
LVL 2

Author Closing Comment

by:CompTech810
ID: 37817205
I actually used an apend query, check this link.  I still want you to have the points for all your help!

http://www.experts-exchange.com/Microsoft/Development/Q_27665148.html
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 37817570
That could be used as well for one record. If many records, the VBA method is way faster.

/gustav
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question