[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

Append Query with a New Field

I am creating a process of data import that will be done on a monthly basis. I have the data I need (was created by a query). I want to append it to an existing table, but sometimes there will be a new field name that has been added to the table with the new data I want to append. How can I append the data, and create this new field in the target table at the same time?
0
4charity
Asked:
4charity
  • 5
  • 5
  • 4
1 Solution
 
cfEngineersCommented:
You need to create the new field? or just insert data into the new field?
0
 
4charityAuthor Commented:
Just insert data into the new field - but it doesn't exist in the target table.
0
 
cfEngineersCommented:
INSERT INTO TargetTable (col1,col2,col3,yournewfield)
select col1,col2,col3,yournewfield
from SourceTable
where 1=1

if your source table didnt have that new column you could do this

INSERT INTO TargetTable (col1,col2,col3,yournewfield)
select col1,col2,col3,'mystaticvalue' as yournewfield
from SourceTable
where 1=1
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Dale FyeCommented:
I think the issue is that the destination table does not contain the new table that is in the data the OP is importing.  

If that is the case, you will first need to identify that field, then use the:

 AlterTable [tablename] ADD [NewColumnName] datatype

syntax to add the field to the table.  Then execute your insert query.
0
 
Dale FyeCommented:
Oops that should have read:

"the destination table does not contain the new field that is in the data the OP is importing"
0
 
4charityAuthor Commented:
Hmmmmm.... fyed, how about automating that somehow, so that I do not have to know the name of the new field? I am trying to create a user-friendly 'push the button and the import takes place' environment. Each month a new field or fields may be added.
0
 
cfEngineersCommented:
You would also need to know which data-type the field is.

You do not want to be storing dates and times as strings, this makes sorting a nightmare.

I would suggest some scripting language like ColdFusion or PHP to handle when you click that button.
0
 
4charityAuthor Commented:
We are stuck with doing this in Access.
0
 
cfEngineersCommented:
another suggestion is add some columns

example:

int_1, int_2,int_3,int_4

bit_1, bit_2, bit_3

string_1, string_2

memo_1, memo_2

date_1, date_2

but still you would need to know where to map things to
0
 
4charityAuthor Commented:
The table I am importing actually comes from a crosstab query........ so the name of the field is created from the ROW name, and it is important to show as that name in the final table. For instance (this is an insurance application), if claims have been added to pay out on a certain type of incident, that field needs to be represented - roof, drywall, foundation, etc. automatically.
0
 
cfEngineersCommented:
i would suggest perhaps a customfields table with a customfieldvalues table that you can add as many fields as you like to an existing record

0
 
Dale FyeCommented:
Another way to do this would be to loop through the fields collection of the source tables tabledef and check to see if the field is in the destination tables fields collection.  If not use the append method to add it, something like (untested!):

Public Sub AddFieldsNotFound(SourceTableName as string, DestinationTableName as string)

   Dim tdfSource as dao.tabledef
   Dim tdfDest as dao.tabledef
   Dim fld as dao.field
   Dim intPosition as integer

    On Error GOTO ProcError

   set tdfSource = currentdb.tabledefs(SourceTableName)
   set tdfDest = currentdb.tabledefs(DestinationTableName)

   for each fld in tdfSource.fields
       intPosition = tdfDest.fields(fld.name).ordinalposition
   next

ProcExit:
   if not tdf is nothing then set tdf = nothing
   if not db is nothing then set db = nothing
   Exit Sub
   
ProcError:
   if err.number <> 3265 then
       tdfDest.Fields.Append tdfDest.CreateField(fld.name, fld.type)
       Resume next
   Else
       debug.print err.number & vbcrlf & err.description
       Resume ProcExit
   end if  
End Sub
0
 
4charityAuthor Commented:
fyed: This is along the lines of what I was thinking......... I will test it out.
0
 
Dale FyeCommented:
Now it is tested.  made a couple of minor tweaks.

Public Sub AddFieldsNotFound(SourceTableName as string, DestinationTableName as string)

   Dim tdfSource as dao.tabledef
   Dim tdfDest as dao.tabledef
   Dim fld as dao.field
   Dim intPosition as integer

    On Error GOTO ProcError

   set tdfSource = currentdb.tabledefs(SourceTableName)
   set tdfDest = currentdb.tabledefs(DestinationTableName)

   for each fld in tdfSource.fields
       intPosition = tdfDest.fields(fld.name).ordinalposition
   next

ProcExit:
   if not tdf is nothing then set tdf = nothing
   if not db is nothing then set db = nothing
   Exit Sub
   
ProcError:
   if err.number <> 3265 then
       tdfDest.Fields.Append tdfDest.CreateField(fld.name, fld.type)
       Resume next
   Else
       debug.print err.number & vbcrlf & err.description
       Resume ProcExit
   end if  
End Sub
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 5
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now