Hi All,
I have a table with data:
CREATE TABLE [dbo].[Relation] (
[ID] [uniqueid] IDENTITY (1, 1) NOT NULL ,
[PersonID] [uniqueid] NOT NULL ,
[CurrentID] [uniqueid] NOT NULL ,
[RelativeName] [varchar] (50) NOT NULL ,
)
Here there are duplicate records for a combination of PersonID, CurrentID
and RelativeName.
I need to create a unique non clustered index on PersonID, CurrentID and
RelativeName.
But it throws an error saying duplicate values...
Why I want to create such an index is to stop inserting further duplicates
and the old data is important for me.
Is there any other way i can accomplish this?
Thanks,
PradYou ought to be able to get rid of the duplicates, otherwise your table
lacks integrity because of the redundant data. Why do you say that the old
data is important to you? In what way?
First, you'll have to fix any foreign key references:
UPDATE foo
SET id =
(SELECT MIN(R2.id)
FROM Relation AS R1
, Relation AS R2
WHERE R1.id = foo.id
AND R1.personid = R2.personid
AND R1.currentid = R2.currentid
AND R1.relativename = R2.relativename) ;
Then delete the duplicates:
DELETE FROM Relation
WHERE EXISTS
(SELECT *
FROM Relation AS R
WHERE R.personid = Relation.personid
AND R.currentid = Relation.currentid
AND R.relativename = Relation.relativename
AND R.id < Relation.id) ;
Now you can add the unique constraint.
Hope this helps.
David Portas
SQL Server MVP
--|||Hi
Maybe you should be changing/consolidating the data related that makes this
non-unique. This does not necessarily mean deleting it, for example having a
alternateids table would mean that only one id needs to exist in the "master
"
table.
John
"Pradeep Kutty" wrote:
> Hi All,
> I have a table with data:
> CREATE TABLE [dbo].[Relation] (
> [ID] [uniqueid] IDENTITY (1, 1) NOT NULL ,
> [PersonID] [uniqueid] NOT NULL ,
> [CurrentID] [uniqueid] NOT NULL ,
> [RelativeName] [varchar] (50) NOT NULL ,
> )
> Here there are duplicate records for a combination of PersonID, CurrentID
> and RelativeName.
> I need to create a unique non clustered index on PersonID, CurrentID and
> RelativeName.
> But it throws an error saying duplicate values...
> Why I want to create such an index is to stop inserting further duplicates
> and the old data is important for me.
> Is there any other way i can accomplish this?
> Thanks,
> Prad
>
>|||I prefer this one:
Delete from Relation
Where
[id] not in
(
SELECT MAX([id])
FROM Relation
GROUP BY [personid],[currentid],[relativename])
Regards,
"David Portas" wrote:
> You ought to be able to get rid of the duplicates, otherwise your table
> lacks integrity because of the redundant data. Why do you say that the old
> data is important to you? In what way?
> First, you'll have to fix any foreign key references:
> UPDATE foo
> SET id =
> (SELECT MIN(R2.id)
> FROM Relation AS R1
> , Relation AS R2
> WHERE R1.id = foo.id
> AND R1.personid = R2.personid
> AND R1.currentid = R2.currentid
> AND R1.relativename = R2.relativename) ;
> Then delete the duplicates:
> DELETE FROM Relation
> WHERE EXISTS
> (SELECT *
> FROM Relation AS R
> WHERE R.personid = Relation.personid
> AND R.currentid = Relation.currentid
> AND R.relativename = Relation.relativename
> AND R.id < Relation.id) ;
> Now you can add the unique constraint.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>
Showing posts with label null. Show all posts
Showing posts with label null. Show all posts
Monday, March 19, 2012
Sunday, February 19, 2012
Doing the lookup on the server while inserting
I have these 2 tables:
CREATE TABLE tbl_ft_testsegments (
SegmentID int(10) AUTO_INCREMENT NOT NULL,
TestID smallint(6) NOT NULL DEFAULT '0',
CarrierFreq smallint(6) NOT NULL DEFAULT '0',
BeatFreq smallint(6) NOT NULL DEFAULT '0',
BeatFreqDelta smallint(6) NOT NULL DEFAULT '0',
Duration tinyint(4) NOT NULL DEFAULT '0',
AmplitudeRampID tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY(SegmentID)
)
CREATE TABLE tbl_ft_amplituderamps (
AmplitudeRampID tinyint(4) AUTO_INCREMENT NOT NULL,
Name char(12) NOT NULL,
PRIMARY KEY(AmplitudeRampID)
)
tbl_ft_amplituderamps is constant and will not change.
On the client, I load a text file that has string values that match what is
in amplituderamps, or atleast they are supposed to!
I store this data in a business object that has the string representation of
an "amplitude ramp"
When I insert them, I am currently getting the AmplitudeRampID from
tbl_ft_amplituderamps where <text file value> = tbl_ft_amplituderamps.Name
THis isn't *too* bad cause I have a local copy of the whole table in memory.
I am considering instead of doing the insert with string instead of the ID
and doing the lookup on teh server side.
Something like:
@.myString varchar(20)
INSERT INTO tbl_ft_testsegments (AmplitudeRampID)
SELECT tbl_ft_amplituderamps.AmplitudeRampID
FROM tbl_ft_amplituderamps WHERE tbl_ft_amplituderamps.Name = myString
Being far from a professional, I'm wondering if this is the *preferred*
method? Is it better to do a little lookup on the client or should I do
this on the server?
Any suggestions would be much appreciated. I have MANY cases where I need
to do this and I would like to get teh best method sorted out before I add
the sprocs for all of the different tables.
Thanks for reading!
Steve"sklett" <sklett@.mddirect.com> wrote in message
news:uddCEzXZGHA.1204@.TK2MSFTNGP04.phx.gbl...
>I have these 2 tables:
> CREATE TABLE tbl_ft_testsegments (
> SegmentID int(10) AUTO_INCREMENT NOT NULL,
> TestID smallint(6) NOT NULL DEFAULT '0',
> CarrierFreq smallint(6) NOT NULL DEFAULT '0',
> BeatFreq smallint(6) NOT NULL DEFAULT '0',
> BeatFreqDelta smallint(6) NOT NULL DEFAULT '0',
> Duration tinyint(4) NOT NULL DEFAULT '0',
> AmplitudeRampID tinyint(4) NOT NULL DEFAULT '0',
> PRIMARY KEY(SegmentID)
> )
> CREATE TABLE tbl_ft_amplituderamps (
> AmplitudeRampID tinyint(4) AUTO_INCREMENT NOT NULL,
> Name char(12) NOT NULL,
> PRIMARY KEY(AmplitudeRampID)
> )
>
> tbl_ft_amplituderamps is constant and will not change.
> On the client, I load a text file that has string values that match what
> is in amplituderamps, or atleast they are supposed to!
> I store this data in a business object that has the string representation
> of an "amplitude ramp"
> When I insert them, I am currently getting the AmplitudeRampID from
> tbl_ft_amplituderamps where <text file value> =
> tbl_ft_amplituderamps.Name
> THis isn't *too* bad cause I have a local copy of the whole table in
> memory. I am considering instead of doing the insert with string instead
> of the ID and doing the lookup on teh server side.
> Something like:
> @.myString varchar(20)
> INSERT INTO tbl_ft_testsegments (AmplitudeRampID)
> SELECT tbl_ft_amplituderamps.AmplitudeRampID
> FROM tbl_ft_amplituderamps WHERE tbl_ft_amplituderamps.Name = myString
>
> Being far from a professional, I'm wondering if this is the *preferred*
> method? Is it better to do a little lookup on the client or should I do
> this on the server?
> Any suggestions would be much appreciated. I have MANY cases where I need
> to do this and I would like to get teh best method sorted out before I add
> the sprocs for all of the different tables.
> Thanks for reading!
> Steve
>
One approach that works well with large data sets is to insert the entire
file into a temporary "staging" table in the database, then do the whole
thing with a join:
INSERT INTO tbl_ft_testsegments (AmplitudeRampID)
SELECT DISTINCT A.AmplitudeRampID
FROM tbl_ft_amplituderamps AS A
JOIN staging AS S
ON A.Name = S.String ;
For loading the file itself you have a number of options. DTS (SQL Server
2000) or Integration Services (2005) or BCP for example.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
> Being far from a professional, I'm wondering if this is the *preferred*
> method? Is it better to do a little lookup on the client or should I do
> this on the server?
Yes. Centralise as much of your logic on the server as possible. Keep
your client-side software as light-weight as you can - ideally, limit it to
presentation only. The processing work belongs on the server(s). Golden
rule, end of story :)
Robert.
"sklett" <sklett@.mddirect.com> wrote in message
news:uddCEzXZGHA.1204@.TK2MSFTNGP04.phx.gbl...
>I have these 2 tables:
> CREATE TABLE tbl_ft_testsegments (
> SegmentID int(10) AUTO_INCREMENT NOT NULL,
> TestID smallint(6) NOT NULL DEFAULT '0',
> CarrierFreq smallint(6) NOT NULL DEFAULT '0',
> BeatFreq smallint(6) NOT NULL DEFAULT '0',
> BeatFreqDelta smallint(6) NOT NULL DEFAULT '0',
> Duration tinyint(4) NOT NULL DEFAULT '0',
> AmplitudeRampID tinyint(4) NOT NULL DEFAULT '0',
> PRIMARY KEY(SegmentID)
> )
> CREATE TABLE tbl_ft_amplituderamps (
> AmplitudeRampID tinyint(4) AUTO_INCREMENT NOT NULL,
> Name char(12) NOT NULL,
> PRIMARY KEY(AmplitudeRampID)
> )
>
> tbl_ft_amplituderamps is constant and will not change.
> On the client, I load a text file that has string values that match what
> is in amplituderamps, or atleast they are supposed to!
> I store this data in a business object that has the string representation
> of an "amplitude ramp"
> When I insert them, I am currently getting the AmplitudeRampID from
> tbl_ft_amplituderamps where <text file value> =
> tbl_ft_amplituderamps.Name
> THis isn't *too* bad cause I have a local copy of the whole table in
> memory. I am considering instead of doing the insert with string instead
> of the ID and doing the lookup on teh server side.
> Something like:
> @.myString varchar(20)
> INSERT INTO tbl_ft_testsegments (AmplitudeRampID)
> SELECT tbl_ft_amplituderamps.AmplitudeRampID
> FROM tbl_ft_amplituderamps WHERE tbl_ft_amplituderamps.Name = myString
>
> Being far from a professional, I'm wondering if this is the *preferred*
> method? Is it better to do a little lookup on the client or should I do
> this on the server?
> Any suggestions would be much appreciated. I have MANY cases where I need
> to do this and I would like to get teh best method sorted out before I add
> the sprocs for all of the different tables.
> Thanks for reading!
> Steve
>
CREATE TABLE tbl_ft_testsegments (
SegmentID int(10) AUTO_INCREMENT NOT NULL,
TestID smallint(6) NOT NULL DEFAULT '0',
CarrierFreq smallint(6) NOT NULL DEFAULT '0',
BeatFreq smallint(6) NOT NULL DEFAULT '0',
BeatFreqDelta smallint(6) NOT NULL DEFAULT '0',
Duration tinyint(4) NOT NULL DEFAULT '0',
AmplitudeRampID tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY(SegmentID)
)
CREATE TABLE tbl_ft_amplituderamps (
AmplitudeRampID tinyint(4) AUTO_INCREMENT NOT NULL,
Name char(12) NOT NULL,
PRIMARY KEY(AmplitudeRampID)
)
tbl_ft_amplituderamps is constant and will not change.
On the client, I load a text file that has string values that match what is
in amplituderamps, or atleast they are supposed to!
I store this data in a business object that has the string representation of
an "amplitude ramp"
When I insert them, I am currently getting the AmplitudeRampID from
tbl_ft_amplituderamps where <text file value> = tbl_ft_amplituderamps.Name
THis isn't *too* bad cause I have a local copy of the whole table in memory.
I am considering instead of doing the insert with string instead of the ID
and doing the lookup on teh server side.
Something like:
@.myString varchar(20)
INSERT INTO tbl_ft_testsegments (AmplitudeRampID)
SELECT tbl_ft_amplituderamps.AmplitudeRampID
FROM tbl_ft_amplituderamps WHERE tbl_ft_amplituderamps.Name = myString
Being far from a professional, I'm wondering if this is the *preferred*
method? Is it better to do a little lookup on the client or should I do
this on the server?
Any suggestions would be much appreciated. I have MANY cases where I need
to do this and I would like to get teh best method sorted out before I add
the sprocs for all of the different tables.
Thanks for reading!
Steve"sklett" <sklett@.mddirect.com> wrote in message
news:uddCEzXZGHA.1204@.TK2MSFTNGP04.phx.gbl...
>I have these 2 tables:
> CREATE TABLE tbl_ft_testsegments (
> SegmentID int(10) AUTO_INCREMENT NOT NULL,
> TestID smallint(6) NOT NULL DEFAULT '0',
> CarrierFreq smallint(6) NOT NULL DEFAULT '0',
> BeatFreq smallint(6) NOT NULL DEFAULT '0',
> BeatFreqDelta smallint(6) NOT NULL DEFAULT '0',
> Duration tinyint(4) NOT NULL DEFAULT '0',
> AmplitudeRampID tinyint(4) NOT NULL DEFAULT '0',
> PRIMARY KEY(SegmentID)
> )
> CREATE TABLE tbl_ft_amplituderamps (
> AmplitudeRampID tinyint(4) AUTO_INCREMENT NOT NULL,
> Name char(12) NOT NULL,
> PRIMARY KEY(AmplitudeRampID)
> )
>
> tbl_ft_amplituderamps is constant and will not change.
> On the client, I load a text file that has string values that match what
> is in amplituderamps, or atleast they are supposed to!
> I store this data in a business object that has the string representation
> of an "amplitude ramp"
> When I insert them, I am currently getting the AmplitudeRampID from
> tbl_ft_amplituderamps where <text file value> =
> tbl_ft_amplituderamps.Name
> THis isn't *too* bad cause I have a local copy of the whole table in
> memory. I am considering instead of doing the insert with string instead
> of the ID and doing the lookup on teh server side.
> Something like:
> @.myString varchar(20)
> INSERT INTO tbl_ft_testsegments (AmplitudeRampID)
> SELECT tbl_ft_amplituderamps.AmplitudeRampID
> FROM tbl_ft_amplituderamps WHERE tbl_ft_amplituderamps.Name = myString
>
> Being far from a professional, I'm wondering if this is the *preferred*
> method? Is it better to do a little lookup on the client or should I do
> this on the server?
> Any suggestions would be much appreciated. I have MANY cases where I need
> to do this and I would like to get teh best method sorted out before I add
> the sprocs for all of the different tables.
> Thanks for reading!
> Steve
>
One approach that works well with large data sets is to insert the entire
file into a temporary "staging" table in the database, then do the whole
thing with a join:
INSERT INTO tbl_ft_testsegments (AmplitudeRampID)
SELECT DISTINCT A.AmplitudeRampID
FROM tbl_ft_amplituderamps AS A
JOIN staging AS S
ON A.Name = S.String ;
For loading the file itself you have a number of options. DTS (SQL Server
2000) or Integration Services (2005) or BCP for example.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
> Being far from a professional, I'm wondering if this is the *preferred*
> method? Is it better to do a little lookup on the client or should I do
> this on the server?
Yes. Centralise as much of your logic on the server as possible. Keep
your client-side software as light-weight as you can - ideally, limit it to
presentation only. The processing work belongs on the server(s). Golden
rule, end of story :)
Robert.
"sklett" <sklett@.mddirect.com> wrote in message
news:uddCEzXZGHA.1204@.TK2MSFTNGP04.phx.gbl...
>I have these 2 tables:
> CREATE TABLE tbl_ft_testsegments (
> SegmentID int(10) AUTO_INCREMENT NOT NULL,
> TestID smallint(6) NOT NULL DEFAULT '0',
> CarrierFreq smallint(6) NOT NULL DEFAULT '0',
> BeatFreq smallint(6) NOT NULL DEFAULT '0',
> BeatFreqDelta smallint(6) NOT NULL DEFAULT '0',
> Duration tinyint(4) NOT NULL DEFAULT '0',
> AmplitudeRampID tinyint(4) NOT NULL DEFAULT '0',
> PRIMARY KEY(SegmentID)
> )
> CREATE TABLE tbl_ft_amplituderamps (
> AmplitudeRampID tinyint(4) AUTO_INCREMENT NOT NULL,
> Name char(12) NOT NULL,
> PRIMARY KEY(AmplitudeRampID)
> )
>
> tbl_ft_amplituderamps is constant and will not change.
> On the client, I load a text file that has string values that match what
> is in amplituderamps, or atleast they are supposed to!
> I store this data in a business object that has the string representation
> of an "amplitude ramp"
> When I insert them, I am currently getting the AmplitudeRampID from
> tbl_ft_amplituderamps where <text file value> =
> tbl_ft_amplituderamps.Name
> THis isn't *too* bad cause I have a local copy of the whole table in
> memory. I am considering instead of doing the insert with string instead
> of the ID and doing the lookup on teh server side.
> Something like:
> @.myString varchar(20)
> INSERT INTO tbl_ft_testsegments (AmplitudeRampID)
> SELECT tbl_ft_amplituderamps.AmplitudeRampID
> FROM tbl_ft_amplituderamps WHERE tbl_ft_amplituderamps.Name = myString
>
> Being far from a professional, I'm wondering if this is the *preferred*
> method? Is it better to do a little lookup on the client or should I do
> this on the server?
> Any suggestions would be much appreciated. I have MANY cases where I need
> to do this and I would like to get teh best method sorted out before I add
> the sprocs for all of the different tables.
> Thanks for reading!
> Steve
>
Friday, February 17, 2012
Doing away with like '%%'
Here's the setup
@.searchcondition varchar(50) --is a parameter passed into a stored procedure
IF(@.searchconditions is null)
BEGIN
SET @.searchcondition = '%'
END
ELSE
BEGIN
SET @.searchcondtion = '%' + @.searchcondition + '%'
END
select * from sometable
where somecolumn like @.searchcondition
Now the procedure is fine if the user passes a string, but if it's null, it seems really inefficient to search for '%'. Is there a generalized approach, other than say putting slightly different versions of the query in an if block?
Have you considering using the Fulltext features in SQL Server 2005 rather than LIKE?|||I'm trying to make minor optimizations to a setup I do not have full control of. Thanks for the pointer to the fulltext stuff though, I'll definitely be using that on my future projects.|||Did you already try the following:
select * from sometable
where (somecolumn like @.searchcondition) or (@.searchcondition is null)
?
|||Nice, works like a charm. Thanks for the tip.
Subscribe to:
Posts (Atom)