Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
David Portas
SQL Server MVP
--|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
--|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:

> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.
SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
David Portas
SQL Server MVP
|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:

> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
--
David Portas
SQL Server MVP
--|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
--|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:
> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate table structures to a new database

Hi all expert,

Question as stated title above, how do I duplicate the table structure to another database whether it copies all columns, primary and foreign keys, default values, descriptions, constraints, indexes

Many thanks for the help.

regards,

Use Enterprise Manager's Generate Script Feature, and execute the Generated SQL on your target server.

|||

you can also check Database Publishing Wizard.

http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Madhu

|||

I want to done this using T-SQL. So what is the script it can be?

|||

There isn't a built-in t-sql command to script However, you can use sp_oa* and smo/dmo. You can check google for archive script.

As to just creating a new table based on the old one, you can just do this:

select top 0 *

into new_table

from old_table

Note that this method does not create constraints/dependencies on the new table.

|||

You can run a trace with profiler to see what Management Studio does to create the script, is a bit more than i expected, but if you really want to you should be able to replicate that on your own.

|||Manivannan gave you the answer. Use the Generate Script feature, it will make the script for you, and then you can re-use that script.

|||

Yes I know there is a Generate Script functions. But I want to schedule it and run on every month, therefore a pre-generated script must be prepared so that it can auto run on every month.

So, can this be done? Thanks a lot for the help.

regards

sql

Duplicate row

hi all,
IF i had one table. in that table three records are there.all there have
same datas.there is no primary key.how i can delete the second row in that
table
regards
balaHi,
How can u say which is the second row?
U want to delete duplicate and want only one row?
Reply
--
Herbert
"balakarthik" wrote:

> hi all,
> IF i had one table. in that table three records are there.all there have
> same datas.there is no primary key.how i can delete the second row in that
> table
> regards
> bala|||hello there
try this
use northwind
select p.supplierid, p.unitprice,p.productid from products p
join
(
select supplierid, min(unitprice)as unitprice from products
group by supplierid
) as p2
on p.supplierid=p2.supplierid
and p.unitprice=p2.unitprice
hope it helps
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"balakarthik" wrote:

> hi all,
> IF i had one table. in that table three records are there.all there have
> same datas.there is no primary key.how i can delete the second row in that
> table
> regards
> bala|||sorry wrong post
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"balakarthik" wrote:

> hi all,
> IF i had one table. in that table three records are there.all there have
> same datas.there is no primary key.how i can delete the second row in that
> table
> regards
> bala|||bala
what do you mean by second row? rows not stored in an order in RDBMS.
CELKO is not going to leave you !!!!!
post ddl/sample data.
Regards
R.D
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> sorry wrong post
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "balakarthik" wrote:
>|||You've haven't seen the rath of Hurricane Celko!
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:F742D9A8-CAC8-4501-B4C3-1374E68FCD83@.microsoft.com...
> hi all,
> IF i had one table. in that table three records are there.all there have
> same datas.there is no primary key.how i can delete the second row in that
> table
> regards
> bala|||the best way is to create the table again - correctly [with primary key]
- and insert the distinct values into it from the old table. The new
table will of course have to have a different name.
balakarthik wrote:

>hi all,
>IF i had one table. in that table three records are there.all there have
>same datas.there is no primary key.how i can delete the second row in that
>table
>regards
>bala
>|||>> You've haven't seen the wrath of Hurricane Celko! <<
LOL!!

Duplicate primary keys in input file

'm trying to import a text file but the primary key column contains duplicatres (tunrs out to be the nature of the legacy data). How can I kick out all duplicates except, say, for a single primary key value?

TIA,

Barkingdog

SORT component has the ability to eradicate dupes. Have you looked into using that?

-Jamie

|||

No, but I will.

BTW does SORT allow me to send those dups to an "erorr" file so I can see I can further investigate those "bad" records?

Barkingdog

|||

No, you can't do that. But you could easily use an AGGREGATE followed by a conditional split to get all those where the key appears more than once.

-Jamie

duplicate primary key error

Im gettig the error "

---

Violation of PRIMARY KEY constraint 'PK_Employees'. "

for the following code..i cant seem to find the error in the code.

---

'data adapter

Dim dAdapt As New SqlClient.SqlDataAdapter

'create a command object

Dim objCommand As New SqlClient.SqlCommand

'command builder

Dim builder As SqlClient.SqlCommandBuilder

'connection string

Dim cnStr As String = "Data Source=ELEARN-FRM-BETA;Initial Catalog=StudentPlayGround;Integrated Security=True"

'connection

Dim cnObj As New SqlConnection(cnStr)

'dataset

Dim ds As DataSet

Dim courseID As Integer

Dim courseName As String

Dim desc As String

Dim maxT As Integer

Dim sql As StringBuilder

Dim cmdTxt As String

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

BindData()

End If

End Sub

Private Sub BindData()

connect()

DataBind()

End Sub

Private Sub connect()

'connection

objCommand.Connection = cnObj

'query string

Dim query As String = "SELECT * from StudentPlayground..Courses"

'setting the select command

dAdapt.SelectCommand = New SqlClient.SqlCommand(query, objCommand.Connection)

'dataset

ds = New DataSet("Course Listings")

cnObj.Open()

dAdapt.Fill(ds, "Courses")

cnObj.Close()

End Sub

Protected Sub submitButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton1.Click

courseID = CInt(CType(FindControl("TextBox1"), TextBox).Text)

courseName = CType(FindControl("TextBox2"), TextBox).Text

desc = CType(FindControl("Textbox3"), TextBox).Text

maxT = CInt(CType(FindControl("TextBox4"), TextBox).Text)

REM Build our parameterized insert statement

sql = New StringBuilder("INSERT INTO Courses ")

sql.Append("(CourseID,CourseName,Description,MaxTrainees)")

sql.Append("VALUES (@.courseID,@.courseName,@.desc,@.maxT)")

cmdTxt = sql.ToString

REM Add parameter values to command

REM Parameters used to protect DB from SQL injection attacks

With objCommand.Parameters

.Clear()

.AddWithValue("@.courseID", courseID)

.AddWithValue("@.courseName", courseName)

.AddWithValue("@.desc", desc)

.AddWithValue("@.maxT", maxT)

End With

objCommand.CommandText = cmdTxt

objCommand.Connection = cnObj

REM Now execute the statement

'connection

cnObj.Open()

objCommand.ExecuteNonQuery()

cnObj.Close()

objCommand.Dispose()

submitButton1.Text = "Record Added!"

End Sub

Well in that particular listing, I don't see any insert into an Employees table. Are you familiar with the PK_Employees primary key constraint in your schema? What table does it exist on (my guess is employees) and on what colum(s) is it on?

I would look at what is going on when the one insert shown above does occur. Are there any triggers entering info that violates this constraint?

If not, I would reanalyze and make sure this is really the section of code causing this problem.

|||checkout whick table has this primary key defined 'pk_employees' (if u r not aware , use the syskeys system table to get that info) . ur code may be adding (repeating)values to that table either directly or thru a trigger.....try to resolve it from there|||

My Apologies...the error is Violation of PRIMARY KEY constraint 'PK_Courses'. Cannot insert duplicate key in object 'dbo.Courses'. This is on the CourseID column in the table Courses. there r no triggers on this table.

aThe strange thing is even though this error appears..my record is still getting inserted correctly in the db. Also this is the ONLY code-beind code present for this page. I just left out the import statements and classdefinition in my post.

|||

The PK constraint error is thrown because of a duplicate "value" (Course) not key attempting to insert into the table.

You can sloppily add On Error Resume Next into your vb.net so you don't see the server side errors, or you check for dupes before you insert records.

Adamus

|||there are no duplicate courses inthe table..i try and add new unique courses each time and experience the error.|||

Do you have a concatenated key or one primary key?

I've experienced similar errors using concatenation and had to remove all constraints and add one key at a time, then one record at a time until I found the error.

Adamus

|||just one primary key on the id field. thats it. no triggers.|||

1. Remove the key - drop the constraint (sp_helpconstraint)
2. Relocate the records
3. Recreate the key
4. Re-import the records

When in doubt, resort to the basics.

Adamus

sql

duplicate error report

I have an app which downloads data from mainframe daily.
Because the primary key definition is different, I have to verify duplicates
before import to my local table.
I would like to know that is it possible to insert duplicates into a table
to investigate later on.
If yes, where is the best place to do it?
For example may I use insert triggers?
I may insert current record into my table if insert fails.
If I am wrong, any information is great appreciated.
Souris,Hi
If you load the data into a loading table then you could do something like:
INSERT INTO Duplicates
SELECT *
FROM LoadingTable
WHERE PKCol IN( SELECT PKCol FROM DestinationTable )
INSERT INTO DestinationTable
SELECT *
FROM LoadingTable
WHERE PKCol NOT IN ( SELECT PKCol FROM DestinationTable )
John
"souris" <soukkris@.viddotron.com> wrote in message
news:ORYSttMEFHA.1408@.TK2MSFTNGP10.phx.gbl...
>I have an app which downloads data from mainframe daily.
> Because the primary key definition is different, I have to verify
> duplicates before import to my local table.
> I would like to know that is it possible to insert duplicates into a table
> to investigate later on.
> If yes, where is the best place to do it?
> For example may I use insert triggers?
> I may insert current record into my table if insert fails.
> If I am wrong, any information is great appreciated.
> Souris,
>
>|||Thanks millions,
Souris,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6GmI3QEFHA.3992@.tk2msftngp13.phx.gbl...
> Hi
> If you load the data into a loading table then you could do something
> like:
> INSERT INTO Duplicates
> SELECT *
> FROM LoadingTable
> WHERE PKCol IN( SELECT PKCol FROM DestinationTable )
> INSERT INTO DestinationTable
> SELECT *
> FROM LoadingTable
> WHERE PKCol NOT IN ( SELECT PKCol FROM DestinationTable )
> John
> "souris" <soukkris@.viddotron.com> wrote in message
> news:ORYSttMEFHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Thanks for the sample code.
This works for one primary key column.
Are there any example for 4 primary key columns ?
Thanks again
Souris,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6GmI3QEFHA.3992@.tk2msftngp13.phx.gbl...
> Hi
> If you load the data into a loading table then you could do something
> like:
> INSERT INTO Duplicates
> SELECT *
> FROM LoadingTable
> WHERE PKCol IN( SELECT PKCol FROM DestinationTable )
> INSERT INTO DestinationTable
> SELECT *
> FROM LoadingTable
> WHERE PKCol NOT IN ( SELECT PKCol FROM DestinationTable )
> John
> "souris" <soukkris@.viddotron.com> wrote in message
> news:ORYSttMEFHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Hi
Try:
INSERT INTO Duplicates
SELECT *
FROM LoadingTable L
WHERE EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
L.PKCol4 )
INSERT INTO DestinationTable
SELECT *
WHERE NOT EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
L.PKCol4 )
John
souris wrote:
> Thanks for the sample code.
> This works for one primary key column.
> Are there any example for 4 primary key columns ?
> Thanks again
> Souris,
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:u6GmI3QEFHA.3992@.tk2msftngp13.phx.gbl...
something
a|||Thanks millions,
Souris,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1108538560.326295.15100@.c13g2000cwb.googlegroups.com...
> Hi
> Try:
> INSERT INTO Duplicates
> SELECT *
> FROM LoadingTable L
> WHERE EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
> L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
> L.PKCol4 )
> INSERT INTO DestinationTable
> SELECT *
> WHERE NOT EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
> L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
> L.PKCol4 )
> John
>
> souris wrote:
> something
> a
>sql

duplicate entry in a primary key field

Hi everybody couldn't get through with saving my data on the table with two primary keys...

my table structure is this

pubid int Unchecked (primary key)
pub char(1) Unchecked
publ char(1) Unchecked
pubcode char(2) Unchecked (primary key)

a sample data is here

pubid pub publ pubcode

1 a b ab
1 b b bb
2 a b ab
2 b b bb

when i save this table modifying the pubid and pubcode as primary keys the following error displays...

Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.

what i understand is that on the primary key duplicates are not allowed how could i allow it?

thanksAlex - you could probably do with a brush up on relational database design & theory.
http://r937.com/relational.html

1) You can't have two primary keys - there can only be one. Most likely you have a composite primary key.
2) The whole point of a primary key is that it is unique - this is pretty well the central tenet of a primary key.

What columns in your table uniquely identify a row? It looks like it could be a combination of pubid and pubcode but please let us know.|||You can have exactly 1 primary key. If I understand correctly, the PK is now a composite key on 2 columns, pubid and pubcode. There are no duplicate combinations of values in that combination of columns, even though both columns individually do contain duplicate values.|||there is no column that uniquely identify a row actually the table is a junction table for many to many relationship pubid is the foreign key for table1 and pubcode for table3 I'm normalizing the database so i created this juction table for tables1 & 3 so with it is it okey if i will not make both a primary key?|||so with it is it okey if i will not make both a primary key?is it okay? well, yes, provided that you really do wish to allow the possibility of the same pubid being related to the same pubcode more than once

in most many-to-many relationships, this would be an error|||so what's the best way i could do it?

thanks|||create a composite primary key on both columns|||You have a composite primary key - this is one primary key comproised of 2+ columns.

i.e.
CREATE TABLE MyTable
(
pubid int
, pub char(1)
, publ char(1)
, pubcode char(2)
, CONSTRAINT pk_MyTable PRIMARY KEY CLUSTERED (pubid, pubcode)
)Again I recommend you read the link. It is only an article not a whole book but it will help you get some of the basics figured out.

Duplicate Domains Listed - Unable to Add Logins

I have a Windows 2003 server with SQL Server 2000 running on it. The server
is performing dual roles as both a database server and a Primary Domain
Controller. When attempting to add logins for the server, I see the domain
listed twice. This causes any attempt to add a user to fail. I get the
following error:
Error 15401: Windows NT user or group 'DOMAIN\Administrators' not found.
Check the name again.
Does anyone know why this might be happening and how to fix it?
Thank You,
Jason Williard
You can take a look at this kb:
http://support.microsoft.com/kb/258025
-oj
"Jason Williard" <jason@.pcsafe.net> wrote in message
news:GZudnS-WBtrKZr7fRVn-uA@.comcast.com...
>I have a Windows 2003 server with SQL Server 2000 running on it. The
>server is performing dual roles as both a database server and a Primary
>Domain Controller. When attempting to add logins for the server, I see the
>domain listed twice. This causes any attempt to add a user to fail. I get
>the following error:
> Error 15401: Windows NT user or group 'DOMAIN\Administrators' not found.
> Check the name again.
>
> Does anyone know why this might be happening and how to fix it?
> Thank You,
> Jason Williard
>

Duplicate Domains Listed - Unable to Add Logins

I have a Windows 2003 server with SQL Server 2000 running on it. The server
is performing dual roles as both a database server and a Primary Domain
Controller. When attempting to add logins for the server, I see the domain
listed twice. This causes any attempt to add a user to fail. I get the
following error:
Error 15401: Windows NT user or group 'DOMAIN\Administrators' not found.
Check the name again.
Does anyone know why this might be happening and how to fix it?
Thank You,
Jason WilliardYou can take a look at this kb:
http://support.microsoft.com/kb/258025
-oj
"Jason Williard" <jason@.pcsafe.net> wrote in message
news:GZudnS-WBtrKZr7fRVn-uA@.comcast.com...
>I have a Windows 2003 server with SQL Server 2000 running on it. The
>server is performing dual roles as both a database server and a Primary
>Domain Controller. When attempting to add logins for the server, I see the
>domain listed twice. This causes any attempt to add a user to fail. I get
>the following error:
> Error 15401: Windows NT user or group 'DOMAIN\Administrators' not found.
> Check the name again.
>
> Does anyone know why this might be happening and how to fix it?
> Thank You,
> Jason Williard
>

Duplicate Domains Listed - Unable to Add Logins

I have a Windows 2003 server with SQL Server 2000 running on it. The server
is performing dual roles as both a database server and a Primary Domain
Controller. When attempting to add logins for the server, I see the domain
listed twice. This causes any attempt to add a user to fail. I get the
following error:
Error 15401: Windows NT user or group 'DOMAIN\Administrators' not found.
Check the name again.
Does anyone know why this might be happening and how to fix it?
Thank You,
Jason WilliardYou can take a look at this kb:
http://support.microsoft.com/kb/258025
-oj
"Jason Williard" <jason@.pcsafe.net> wrote in message
news:GZudnS-WBtrKZr7fRVn-uA@.comcast.com...
>I have a Windows 2003 server with SQL Server 2000 running on it. The
>server is performing dual roles as both a database server and a Primary
>Domain Controller. When attempting to add logins for the server, I see the
>domain listed twice. This causes any attempt to add a user to fail. I get
>the following error:
> Error 15401: Windows NT user or group 'DOMAIN\Administrators' not found.
> Check the name again.
>
> Does anyone know why this might be happening and how to fix it?
> Thank You,
> Jason Williard
>

dumb question, but I need help

Help people,

I am trying to figure out the syntax to declare a @.table varible with a primary key clusterd

Hence,

declare @.RANK_FIGROSS_TEST table
{
RANK int NOT NULL IDENTITY (1, 1)|PRIMARY KEY CLUSTERED,
FINAME nvarchar(255) NULL,
FIGROSS decimal (10,2)
}

But I am not having any luck, anyone know what I am missing ??should be ( and not { and no | for the primary key declaration

here is the code modified:

declare @.RANK_FIGROSS_TEST table
(
RANK int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
FINAME nvarchar(255) NULL,
FIGROSS decimal (10,2))|||that worked, told you it was a stupid question, just couldn't see it.

Thanks|||no such thing as a stupid question :-) we all have to learn somehow|||hmm.... I did even more stupid "ER" things than u ever imagined!!!
:)