Re: Distinct insert

From: Robert (Robert_at_discussions.microsoft.com)
Date: 08/26/04 

Date: Thu, 26 Aug 2004 10:55:18 -0700

That did the trick, I should have thought about grouping them.

Hugo... Your a genius!

Thanks,

"Hugo Kornelis" wrote:

> 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)
>

Relevant Pages

  • 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
    ... >provide you something that looks similair. ... >however at the same time inserting a unique primary key into the same ... >Primary Key From Table 1, Account Number ...
    (microsoft.public.sqlserver.mseq)
  • 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: 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)