Insert into and select excep ID (1 Viewer)

[amir0914]

Hil all,
I'm going to select and insert table data from another database to current database, I can do this easily with select and insert all fields (*), but it's difficult to type each field if I don't want to insert except ID field. because there are many fields in the table.

pth = "I:\Test37.accdb"
pSQL = "INSERT INTO tbl_Data(D_Name,D_Family,D_Father,D_Date,D_Mariddle,D_Edu,........) SELECT D_Name,D_Family,D_Father,D_Date,D_Mariddle,D_Edu,..... FROM [MS Access;DATABASE=" & pth & "].tbl_Data"

And I want to write the code in this form :

 pth = "I:\Test37.accdb"

pSQL = "INSERT INTO tbl_Data("Select all fields except ID") SELECT "Select all fields except ID" FROM [MS Access;DATABASE=" & pth & "].tbl_Data"

Is it possible?

 

[arnelgp]

it is very possible, but you need to list all the fields you need to copy.

pSQL = "INSERT INTO tbl_Data("all fields except ID") SELECT "all fields except ID" FROM tbl_Date In '" & pth & "'"

 

[Galaxiom]

Start creating a Select query on the destination table. In the query designer, highlight all the fields and drag them to the grid.. Then remove the ID field using the Delete key.

If you make the source table the same or similar as the target table in design view, the SQL will list all the source and destination fields in the right syntax Then simply edit the table name in the FROM clause. Once you have the query you can edit it in SQL view to change whatever you need to..

Or use the clipboard in SQL view to duplicate the field names. I usually paste complex Access queries in Notepad++ for edit where I can do find and replace.

 

[amir0914]

it is very possible, but you need to list all the fields you need to copy.

pSQL = "INSERT INTO tbl_Data("all fields except ID") SELECT "all fields except ID" FROM tbl_Date In '" & pth & "'"

I know that, but I don't want to list and type all fields manually, I want to use select all field except "ID".

 

[isladogs]

@amir0914
Whilst your request makes sense and all of us have at some stage wanted the same thing, unfortunately it isn't possible. The method described by @Galaxiom is probably the quickest method available

 

[arnelgp]

you can always create a udf that will return a String SQL without the autonumber field in it:

Public Function fncBuildInsertQuery(ByVal target_table As String, _
                                    ByVal source_table As String, _
                                    Optional ByVal source_database As String = "") As String

    ' well follow this style:
    '
    ' select fiel1, field2 into Target_table from Source_table in Ssource_database';"
    '
    Dim str_sql As String
    Dim db As DAO.Database
    Dim fd As DAO.Field
    
    Set db = CurrentDb
    With db.TableDefs(target_table)
        For Each fd In .Fields
            If fd.Attributes And dbAutoIncrField Then
                ' do not include autonumber field
            Else
                str_sql = str_sql & "[" & fd.Name & "], "
            End If
        Next
    End With
    If Len(str_sql) > 0 Then
        str_sql = Trim(Left(str_sql, Len(str_sql) - 2))
        str_sql = "SELECT " & str_sql & " INTO [" & target_table & "] " & _
                "FROM [" & source_table & "]"
        If Len(source_database) > 0 Then
            str_sql = str_sql & " IN '" & source_database & "'"
        End If
        str_sql = str_sql & ";"
    End If
    
    fncBuildInsertQuery = str_sql
End Function

example ( based on post #1):

pth = "I:\Test37.accdb"
pSQL =fncBuildInsertQuery("tbl_Data", "tbl_Data", pth)

 

[arnelgp]

opps, the first code is a Make table Query, we need an Insert Query:

Public Function fncBuildInsertQuery(ByVal target_table As String, _
                                    ByVal source_table As String, _
                                    Optional ByVal source_database As String = "") As String

    ' well follow this style:
    '
    ' select fiel1, field2 into Target_table from Source_table in Ssource_database';"
    '
    Dim str_sql As String
    Dim db As DAO.Database
    Dim fd As DAO.Field
    
    Set db = CurrentDb
    With db.TableDefs(target_table)
        For Each fd In .Fields
            If fd.Attributes And dbAutoIncrField Then
                ' do not include autonumber field
            Else
                str_sql = str_sql & "[" & fd.Name & "], "
            End If
        Next
    End With
    If Len(str_sql) > 0 Then
        str_sql = Trim(Left(str_sql, Len(str_sql) - 2))
        str_sql = "INSERT INTO [" & target_table & "] (" & str_sql & ") " & _
                "SELECT " & str_sql & " FROM [" & source_table & "]"
        If Len(source_database) > 0 Then
            str_sql = str_sql & " IN '" & source_database & "'"
        End If
        str_sql = str_sql & ";"
    End If
    
    fncBuildInsertQuery = str_sql
End Function

 

[amir0914]

Start creating a Select query on the destination table. In the query designer, highlight all the fields and drag them to the grid.. Then remove the ID field using the Delete key.

If you make the source table the same or similar as the target table in design view, the SQL will list all the source and destination fields in the right syntax Then simply edit the table name in the FROM clause. Once you have the query you can edit it in SQL view to change whatever you need to..

Or use the clipboard in SQL view to duplicate the field names. I usually paste complex Access queries in Notepad++ for edit where I can do find and replace.

In fact, I have about 30 databases with the same fields and I should populate all data of each database in main database, but it's difficult to create query for each database, unfortunately I don't know SQL commands a lot, is there a SQL command to select all field except one field?

 

[Gasman]

In fact, I have about 30 databases with the same fields and I should populate all data of each database in main database, but it's difficult to create query for each database, unfortunately I don't know SQL commands a lot, is there a SQL command to select all field except one field?

Why not just link to them and be done with it?

 

[isladogs]

In fact, I have about 30 databases with the same fields and I should populate all data of each database in main database, but it's difficult to create query for each database, unfortunately I don't know SQL commands a lot, is there a SQL command to select all field except one field?

As already stated, there are no sql commands to select all fields except one. It isn't possible other than by one of the methods described by @Galaxiom and @arnelgp.

 

[amir0914]

opps, the first code is a Make table Query, we need an Insert Query:

Public Function fncBuildInsertQuery(ByVal target_table As String, _
                                    ByVal source_table As String, _
                                    Optional ByVal source_database As String = "") As String

    ' well follow this style:
    '
    ' select fiel1, field2 into Target_table from Source_table in Ssource_database';"
    '
    Dim str_sql As String
    Dim db As DAO.Database
    Dim fd As DAO.Field
   
    Set db = CurrentDb
    With db.TableDefs(target_table)
        For Each fd In .Fields
            If fd.Attributes And dbAutoIncrField Then
                ' do not include autonumber field
            Else
                str_sql = str_sql & "[" & fd.Name & "], "
            End If
        Next
    End With
    If Len(str_sql) > 0 Then
        str_sql = Trim(Left(str_sql, Len(str_sql) - 2))
        str_sql = "INSERT INTO [" & target_table & "] (" & str_sql & ") " & _
                "SELECT " & str_sql & " FROM [" & source_table & "]"
        If Len(source_database) > 0 Then
            str_sql = str_sql & " IN '" & source_database & "'"
        End If
        str_sql = str_sql & ";"
    End If
   
    fncBuildInsertQuery = str_sql
End Function

Thank you arnelgp for the function.