Distinct insert

I am trying to insert a record id primary at the same time that I am trying
to insert a Distinct Account number.
The insert statement is something kind of like this:
insert into Accounts (AccountsOID,Account)
select Distinct "Rec NO" + 100 as AccountsOID, "LIC TAG" as Account from
AutoExpt
In the AutoExpt table there are multiple occurrences of "LIC TAG" values all
of which need to be put into the Accounts table, however there must be only
one entry for the Lic tag in the accounts table. The Rec No +100 is just to
create a unique primary key for the AccountsOID.
Is there anyway I can get only the Distinct Lic Tag's, but at the same time
get a unique primary key for them.
Thanks,
On Wed, 25 Aug 2004 12:55:03 -0700, Robert wrote:

>I am trying to insert a record id primary at the same time that I am trying
>to insert a Distinct Account number.
>The insert statement is something kind of like this:
>insert into Accounts (AccountsOID,Account)
>select Distinct "Rec NO" + 100 as AccountsOID, "LIC TAG" as Account from
>AutoExpt
>In the AutoExpt table there are multiple occurrences of "LIC TAG" values all
>of which need to be put into the Accounts table, however there must be only
>one entry for the Lic tag in the accounts table. The Rec No +100 is just to
>create a unique primary key for the AccountsOID.
>Is there anyway I can get only the Distinct Lic Tag's, but at the same time
>get a unique primary key for them.
>Thanks,
Hi Robert,
Please provide DDL (CREATE TABLE statements, with constraints), sample
data (as INSERT statements), expected output and a description of the
business problem you're trying to solve.
http://www.aspfaq.com/etiquette.asp?id=5006
http://vyaskn.tripod.com/code.htm#inserts
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Unfortunately I do not have the create table SQL statements
What I am trying to accomplish is importing data from an excel file into a
SQL database. To make it easier to work with I simply straight imported the
excel file into a new table in the SQL database (AutoExpt). Accounts is one
of the tables that I need to import into. I do know that the OID for the
accounts table is not automatically generated, and must be generated manually.
In the AutoExpt table there is a listing of vechiles and what accounts
maintenance should be charged to. I can add the vechiles fine to the
Equipment table (Not listed here because it works just fine). However another
table EquipmentFeature is linked to the Equipment table. It contains things
such as account numbers, and the department that the vechile belongs to.
From what I can tell I can not add records to the Equipment feature table
such as the account number because they do not exist in the Accounts table.
So the solution is to first add all the distinct account numbers into the
accounts table. This also applies to the departments table as well.
I do have a backup of the database so I can fool aroudn with it all I want
without worry of data loss or damage, plus I am working on a dev server so no
actual users are accessing the database. I found that I can add duplicate
records in the accounts table and the departments table, however I have found
that I can't remove some duplicates in the departments table because it
claims that a record needs to exist in the General table first.
My theory is that if I add only the distinct account numbers and departments
I should be fine, and it should stop telling me that "A record must exist in
the accounts table first"
Unfortunately the company that makes the software is not offering much
support on importing the data. So I am left to figure it out myself.
"Hugo Kornelis" wrote:

> On Wed, 25 Aug 2004 12:55:03 -0700, Robert wrote:
>
> Hi Robert,
> Please provide DDL (CREATE TABLE statements, with constraints), sample
> data (as INSERT statements), expected output and a description of the
> business problem you're trying to solve.
> http://www.aspfaq.com/etiquette.asp?id=5006
> http://vyaskn.tripod.com/code.htm#inserts
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 26 Aug 2004 05:55:04 -0700, Robert wrote:

>Unfortunately I do not have the create table SQL statements
(snip)
Hi Robert,
Did you read the links I posted in my message? They contain instructions
on how you can generate the create table statements from the existing
tables (the ewb site shows how it's done with Query Analyzer; there's an
option to generate SQL in Enterprise Manager as well).
I read your description, but I can't figure out what you're trying to do
and what problems you are trying to tackle. I really need the table
structure and sample data to understand your problem.
(BTW, if therre are legal or other issues that prohibit you from posting
the table structure, you may also create a fake table that recreates the
problem).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||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
"Hugo Kornelis" wrote:

> On Thu, 26 Aug 2004 05:55:04 -0700, Robert wrote:
> (snip)
> Hi Robert,
> Did you read the links I posted in my message? They contain instructions
> on how you can generate the create table statements from the existing
> tables (the ewb site shows how it's done with Query Analyzer; there's an
> option to generate SQL in Enterprise Manager as well).
> I read your description, but I can't figure out what you're trying to do
> and what problems you are trying to tackle. I really need the table
> structure and sample data to understand your problem.
> (BTW, if therre are legal or other issues that prohibit you from posting
> the table structure, you may also create a fake table that recreates the
> problem).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||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)
|||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:
>
> 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)
>