[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

DISTINCT INSERT SQL statement





Hello All,

I am trying to import these fields into an Access DB.

The trick lies in the fact that I am attempting to insert one instance of 'FullName' and 'Email' Fields, while there will be multiple (up to 50) occurences of the 'Location' and 'Material' fields for that one row. Right now it is creating a seperate record for each Name, Email, Location, Material.

Does anyone know how I can group/combine all the Location, Material to force all into a single instance for each Name/Email??

One record should look like this:

FullName    Email              Location                   Material
-------- ---|--------------------- |----------------------|-------------------------------
John Doe | JDoe@go.com    |  Building A            |   Wood  
                |                            |  Building B             |   Steel
----------------------------------------------------------------------------------
 <cfquery name="HR_import" datasource="Material">
    INSERT INTO HR_NoEmail (FullName, Email, Location, Material)
    VALUES
      
       (
       <cfqueryparam value="#FullName#" cfsqltype="CF_SQL_VARCHAR">,
       <cfqueryparam value="#Email#" cfsqltype="CF_SQL_VARCHAR">,
       <cfqueryparam value="#Location#" cfsqltype="CF_SQL_VARCHAR">,
       <cfqueryparam value="#Material#" cfsqltype="CF_SQL_VARCHAR">,
        )
      

  </cfquery>
 
 
  Thanks!!!
 
 
  Zack
0
zlinst28
Asked:
zlinst28
2 Solutions
 
sdstuberCommented:
depends on your database, and how you want to see and work with the data.

if your db supports it,  you could create a collection type column for location and material where each element is one member of your location list or material list.

or

you could also concatenate the locations and materials into long strings prior to inserting

or

go ahead and load the data as individual rows and use a view to concatenate the location and materials into long strings (look out for sql column length limits though)

or

load data as individual rows and use a view to group the locations and materials into collections
0
 
dbbishopCommented:
Looks to me to be a design issue. This should be two tables. one containing a primary key, FullName and Email. The second should contain a primary key, a foreign key back into the first, location and material. What you are showing above is NOT one record, it is two.

To keep it as a single row, you'd need to do what sdstuber suggested and concatenate the locations and materials together, but then you run into the problem of breaking them apart again should you need to do so. COncatenating them would give you something like:

FullName    Email              List
-------- ---|-------------------|----------------------------------------------------------
John Doe | JDoe@go.com    |  Building1  Material1, Building1  Material2, Building1  Material3, Building2  Material1, Building2  Material2, Building2  Material4, Building3  Material5, Building3  Material6...

To do so, you'd still need to load the data into some kind of staging table. Besides, if you are doing this in Access, I don't know that it will give you the ability to write the functions required to concatenate the data. What you are wanting to do might be outside the scope of Access (I'm more of a SQL Server guy).
0
 
Computer101Commented:
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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