Primary Key Problem

OK, I create a table called Customer, I imported date from my as400, when i
want to set a primary key it telling me that i have duplicate key.
Keep in mind the layout of my table CustId, SubCustId, in CustId they can
have more then one same CustId, but SubCustID have not the same SubCustID.
example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
can be the same but the sub is always different. Could anyone help me on that
problem I have no more Idea.
Thanks!
JF
Seem like the primary key should be the combination of CustId and SubCustId.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
> OK, I create a table called Customer, I imported date from my as400, when i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on that
> problem I have no more Idea.
> Thanks!
> JF
|||Is the primary key defined on both columns? If it is, try to import the data
over without the primary key, then check for duplicates.
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
"Jean-Francois" wrote:

> OK, I create a table called Customer, I imported date from my as400, when i
> want to set a primary key it telling me that i have duplicate key.
> Keep in mind the layout of my table CustId, SubCustId, in CustId they can
> have more then one same CustId, but SubCustID have not the same SubCustID.
> example: CustId 68818 Sub 999, Cust 68818 Sub 618, as you can see custid
> can be the same but the sub is always different. Could anyone help me on that
> problem I have no more Idea.
> Thanks!
> JF
|||I did
SELECT CustId, SubCustId FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
But it give me all my data. That seem to be that all my data is duplicate.
I try to import the data before put the primary key and after put the
primary key and get the same result.
"Tibor Karaszi" wrote:

> Seem like the primary key should be the combination of CustId and SubCustId.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jean-Francois" <JeanFrancois@.discussions.microsoft.com> wrote in message
> news:53D0FE4E-20C0-489D-8F26-C04C7568E454@.microsoft.com...
>
>
|||How many times have you try to import the data into SQL table? Did you
delete all records in the table before you import the data if you did it
multiple times?
"Jean-Francois" wrote:
[vbcol=seagreen]
> I did
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> But it give me all my data. That seem to be that all my data is duplicate.
> I try to import the data before put the primary key and after put the
> primary key and get the same result.
>
> "Tibor Karaszi" wrote:
|||I first delete the table and start over every time.
"Jack" wrote:
[vbcol=seagreen]
> How many times have you try to import the data into SQL table? Did you
> delete all records in the table before you import the data if you did it
> multiple times?
> "Jean-Francois" wrote:
|||Does the total row returned from the "check duplicate" query match SELECT
COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
DTS?
"Jean-Francois" wrote:
[vbcol=seagreen]
> I first delete the table and start over every time.
> "Jack" wrote:
|||I did an connection with client access driver to connect to the as400 and a
query the as400 to retreive the data.
"Jack" wrote:
[vbcol=seagreen]
> Does the total row returned from the "check duplicate" query match SELECT
> COUNT(*) FROM table? How do you import data from AS400 to SQL? Text file?
> DTS?
> "Jean-Francois" wrote:
|||1> Bring the data over with primary defined in SQL table.
2> After the import, issue "ALTER TABLE table ADD id INT IDENTITY (1, 1).
This will create a primary key for this table.
3> Use this command to delete duplicates:
DELETE FROM table
WHERE id NOT IN (
SELECT MAX(id) FROM table
GROUP BY CustId, SubCustId)
4> Remove "id" column and set your primary key using CustId and SubCustId
Then you would need to compare the data between SQL and AS400.........
"Jean-Francois" wrote:
[vbcol=seagreen]
> I did an connection with client access driver to connect to the as400 and a
> query the as400 to retreive the data.
> "Jack" wrote:
|||Add a Count(*) to the result set to see how many duplicates there are..
Then pick one and look to see whats up with the rows.
SELECT CustId, SubCustId,Count(*)
FROM table
GROUP BY CustId, SubCustId
HAVING COUNT(*) > 1
Bill
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:8DE0E7B0-7EF9-472E-B8F7-CF53F4F1A25A@.microsoft.com...[vbcol=seagreen]
> Is the primary key defined on both columns? If it is, try to import the
> data
> over without the primary key, then check for duplicates.
> SELECT CustId, SubCustId FROM table
> GROUP BY CustId, SubCustId
> HAVING COUNT(*) > 1
> "Jean-Francois" wrote: