Re: Distinct insert

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/26/04

  • Next message: Robert: "Re: Distinct insert" 
    Date: Thu, 26 Aug 2004 18:45:34 +0200

    On Thu, 26 Aug 2004 08:39:12 -0700, Robert wrote:

    >I can't post the structure or data because it's against our policies. I can
    >provide you something that looks similair. I basically need to be able to
    >insert only the unique account numbers from one table into another table,
    >however at the same time inserting a unique primary key into the same
    >destination table.
    >
    >For example I kinda have something like this:
    >
    >Primary Key From Table 1, Account Number
    >1,A
    >2,A
    >3,B
    >4,C
    >5,B
    >
    >I need to put that information in a seperate table that is similair to this:
    >
    >Primary From table2, Account Number
    >-564564,K
    >564578,D
    >7246,S
    >54567,F
    >
    >The second table can not have any duplicate account numbers and of course
    >the Primary key.
    >
    >What I want to do is select from table1 something looking like this:
    >
    >Primary Key From Table 1, Account Number
    >1,A
    >3,B
    >4,C
    >
    >Leaving me only with unique primary keys and account numbers.
    >
    >The end result for table2 should look something like this:
    >-564564,K
    >564578,D
    >7246,S
    >54567,F
    >1,A
    >3,B
    >4,C

    Hi Robert,

    The following is untested as I only test solutions I provide in newsgroups
    if I can copy and paste CREATE TABLE and INSERT statements. I like solving
    problems and helping people, but I detest typing - I have to do plenty of
    typing in my job :-)

    But try if this gives you what you need:

    INSERT INTO table2 (PrimKeyFromTable2, AccountNumber)
    SELECT MIN(PrimKeyFromTable1), AccountNumber
    FROM Table1
    GROUP BY AccountNumber

    Best, Hugo 

    -- 
(Remove _NO_ and _SPAM_ to get my e-mail address)
  • Next message: Robert: "Re: Distinct insert"

    Relevant Pages

    • Re: 2 identical table designs
      ... physical location is logical or down to some "policy" decision. ... If there is a natural primary key (say an index based on both Company Code ... If the combination of Company Code and Account are currently unique in both ...
      (microsoft.public.access.tablesdbdesign)
    • Re: Table Design - Relationships
      ... how I design the forms, I always get errors, e.g. "Forms not updatable" or ... Can a facility have a bill without an account? ... > FacilityID 'Autonumber - Primary Key ...
      (microsoft.public.access.tablesdbdesign)
    • Re: Distinct insert
      ... however at the same time inserting a unique primary key into the same ... Primary Key From Table 1, Account Number ... I need to put that information in a seperate table that is similair to this: ...
      (microsoft.public.sqlserver.mseq)
    • Re: Distinct insert
      ... "Hugo Kornelis" wrote: ... >>insert only the unique account numbers from one table into another table, ... >>however at the same time inserting a unique primary key into the same ...
      (microsoft.public.sqlserver.mseq)
    • Re: Question Regarding Date Sensitive Tables
      ... Account Number ... Account No., (Joint Primary Key) ... Bill Date, ... Long Distance Charges for this cell phone, ...
      (microsoft.public.access.forms)