Choosing one record over another

How would I select one record and not the another related record in one
Select statement?
I have regular prices that I want to display on most days and promotional
prices on particular days (christmas, summer etc) where I don't want to
display the regular price if I am displaying the promotional price.
I have a table that has 2 types of records in it.
One record is a normal ticket price on a ship. The other record is a
promotional price for that ship.
If it is a Regular Price, there will be null values in the PromoPriceID,
StartDate and End Date (as these are only used for the promotional prices).
If it is a Promotional Price there will be a Null Value in ShipName (as I
would get that from the regular record). The PromoPriceID would contain the
Regular records ShipPricingID (so that it could get the name of the ship and
so that we would know not to display that records regular price). The
StartDate and EndDate would have the date range the promotional prices ran
for.
Here is a test file setup with the display of all the records.
****************************************
************************************
******************************
drop table shipPricing
create table shipPricing
(
ShipPricingID int Identity Not Null,
ShipName varChar(50) Null,
PromoPriceID int Null,
Price Money Not Null,
StartDate smalldatetime Null,
EndDate smalldatetime Null
)
insert shipPricing(ShipName,Price) values ('Sea Witch',1500)
insert shipPricing(PromoPriceID,Price,StartDate
,EndDate) values
(Scope_Identity(),1300,'12/01/05','12/31/05')
insert shipPricing(ShipName,Price) values ('Southern Cross',2200)
insert shipPricing(PromoPriceID,Price,StartDate
,EndDate) values
(Scope_Identity(),2050,'12/15/05','12/31/05')
select ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID, Price
= Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) from shipPricing
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
1 Sea Witch NULL 1,500.00
NULL NULL
2 NULL 1
1,300.00 12/01/05 12/31/05
3 Southern Cross NULL 2,200.00
NULL NULL
4 NULL 3
2,050.00 12/15/05 12/31/05
****************************************
************************************
****************************************
**
What I want to do is have a Select statement that would give me back the
prices available for a particular date (Select ... where date = some date).
If it is a promotion I don't want to show the regular price and I would
like it to say something like "Promo".
For example:
If I want the prices for 11/15/01 I should get something like:
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
1 Sea Witch NULL 1,500.00
NULL NULL
3 Southern Cross NULL 2,200.00
NULL NULL
If I want prices for 12/05/05, I should get something like:
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
2 Sea Witch 1
1,300.00 12/01/05 12/31/05 Promo
3 Southern Cross NULL 2,200.00
NULL NULL
If I want prices for 12/18/05, I should get something like:
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
2 Sea Witch 1
1,300.00 12/01/05 12/31/05 Promo
4 Southern Cross 3 2,050.00
12/15/05 12/31/05 Promo
I can't figure out how to select one record and not the other in one Select
statement.
Thanks,
Tomyou could do it with a simple if exists statement, like so:
-- let's assume you've got some kind of variable with your Date value
in it
DECLARE @.date datetime
IF EXISTS (SELECT * FROM shipPricing
WHERE @.date > StartDate AND @.date < EndDate)
SELECT * FROM shipPricing WHERE @.date > StartDate AND @.date < EndDate
ELSE
SELECT * FROM shipPricing WHERE StartDate IS NULL AND EndDate IS NULL
if the exists is true, you won't likely be taxed for the second query,
because the rows will already be cached from the if condition test. so
i'm pretty sure performance-wise you'll only be running 1 query,
essentially. also, you can simply replace * with whatever you want to
return, and since the promo stuff is in its own query, you can throw in
a 'Promo' value in for kicks.|||also, having the condition of one entity versus another being
determined by the presence or absense of values in a field seems a
little odd to me. if it's not a huge conversion, i would consider
migrating this data into two tables: shipPricingStandard, and
shipPricingPromo. that way your queries would look more like:
IF EXISTS (SELECT * FROM shipPricingPromo
WHERE @.date > StartDate AND @.date < EndDate)
SELECT * FROM shipPricingPromo WHERE @.date > StartDate AND @.date <
EndDate
ELSE
SELECT * FROM shipPricingStandard
not sure why, but it seems like a clearer division of what seem like
two similar, but separate entities. but that's probably open to debate,
and more cosmetic than anything.
hope this helps,
jason|||"jason" <iaesun@.yahoo.com> wrote in message
news:1124995465.591667.36420@.g14g2000cwa.googlegroups.com...
> you could do it with a simple if exists statement, like so:
> -- let's assume you've got some kind of variable with your Date value
> in it
> DECLARE @.date datetime
> IF EXISTS (SELECT * FROM shipPricing
> WHERE @.date > StartDate AND @.date < EndDate)
> SELECT * FROM shipPricing WHERE @.date > StartDate AND @.date < EndDate
> ELSE
> SELECT * FROM shipPricing WHERE StartDate IS NULL AND EndDate IS NULL
>
This was the problem I had.
The problem is that you can either get the Promo prices (those with dates)
or regular prices (those without dates). The problem is that I want to
either a regular price OR a promotional price for both ships.
Here are the results if you add in dates you can see the problem. In the
first 2 it would either be on or the other for those dates, but the last
date should have the regular price for the Sea Witch and the promo price for
the other. Also I need to grab the name of the ship from the regular price
record if a promo price.
****************************************
************************************
***************************************
declare @.date smalldatetime
select @.date = '11/01/05'
IF EXISTS (SELECT * FROM shipPricing
WHERE @.date > StartDate AND @.date < EndDate)
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE @.date >
StartDate AND @.date < EndDate
ELSE
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE StartDate
IS NULL AND EndDate IS NULL
ShipPricingID ShipName PromoPriceID Price StartDate
EndDate
-- -- -- -- -- --
--
1 Sea Witch NULL 1,500.00
NULL NULL
3 Southern Cross NULL 2,200.00
NULL NULL
****************************************
************************************
****************************************
**
****************************************
************************************
*************************************
declare @.date smalldatetime
select @.date = '12/16/05'
IF EXISTS (SELECT * FROM shipPricing
WHERE @.date > StartDate AND @.date < EndDate)
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE @.date >
StartDate AND @.date < EndDate
ELSE
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE StartDate
IS NULL AND EndDate IS NULL
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
2 NULL 1
1,300.00 12/01/05 12/31/05
4 NULL 3
2,050.00 12/15/05 12/31/05
****************************************
************************************
****************************************
****
****************************************
************************************
****************************************
****
declare @.date smalldatetime
select @.date = '12/03/05'
IF EXISTS (SELECT * FROM shipPricing
WHERE @.date > StartDate AND @.date < EndDate)
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE @.date >
StartDate AND @.date < EndDate
ELSE
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE StartDate
IS NULL AND EndDate IS NULL
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
2 NULL 1
1,300.00 12/01/05 12/31/05
****************************************
************************************
****************************************
*****
Thanks,
Tom
> if the exists is true, you won't likely be taxed for the second query,
> because the rows will already be cached from the if condition test. so
> i'm pretty sure performance-wise you'll only be running 1 query,
> essentially. also, you can simply replace * with whatever you want to
> return, and since the promo stuff is in its own query, you can throw in
> a 'Promo' value in for kicks.
>|||"jason" <iaesun@.yahoo.com> wrote in message
news:1124995909.532864.267610@.g47g2000cwa.googlegroups.com...
> also, having the condition of one entity versus another being
> determined by the presence or absense of values in a field seems a
> little odd to me. if it's not a huge conversion, i would consider
> migrating this data into two tables: shipPricingStandard, and
> shipPricingPromo. that way your queries would look more like:
The reason I didn't pick 2 tables is the data is essentially the same except
for type of record as well as the dates. I could also add a type code
field, but I can do that by testing for the shipPricingID (if nothing there
it is a regular price) as well as the dates (again if nothing there it is a
regular record).
Tom
> IF EXISTS (SELECT * FROM shipPricingPromo
> WHERE @.date > StartDate AND @.date < EndDate)
> SELECT * FROM shipPricingPromo WHERE @.date > StartDate AND @.date <
> EndDate
> ELSE
> SELECT * FROM shipPricingStandard
> not sure why, but it seems like a clearer division of what seem like
> two similar, but separate entities. but that's probably open to debate,
> and more cosmetic than anything.
> hope this helps,
> jason
>|||t - try this:
declare @.dt datetime
set @.dt = cast('12/18/05' as datetime)
select
case
when @.dt between s2.startdate and s2.enddate then s2.ShipPricingID
else s.ShipPricingID
end as ShipPricingID,
ShipName = substring(s.ShipName,1,20),
case
when @.dt between s2.startdate and s2.enddate then s2.PromoPriceID
else s.PromoPriceID
end as PromoPriceID,
case
when @.dt between s2.startdate and s2.enddate then s2.Price
else s.Price
end as Price,
case
when @.dt between s2.startdate and s2.enddate then s2.StartDate
else s.StartDate
end as StartDate,
case
when @.dt between s2.startdate and s2.enddate then s2.EndDate
else s.EndDate
end as EndDate,
case
when @.dt between s2.startdate and s2.enddate then 'PROMO'
else NULL
end as Promo
from shipPricing s inner join shipPricing s2 on s.ShipPricingID =
s2.PromoPriceID
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O84VJtZqFHA.2276@.TK2MSFTNGP10.phx.gbl...
> How would I select one record and not the another related record in one
> Select statement?
> I have regular prices that I want to display on most days and promotional
> prices on particular days (christmas, summer etc) where I don't want to
> display the regular price if I am displaying the promotional price.
> I have a table that has 2 types of records in it.
> One record is a normal ticket price on a ship. The other record is a
> promotional price for that ship.
> If it is a Regular Price, there will be null values in the PromoPriceID,
> StartDate and End Date (as these are only used for the promotional
prices).
> If it is a Promotional Price there will be a Null Value in ShipName (as I
> would get that from the regular record). The PromoPriceID would contain
the
> Regular records ShipPricingID (so that it could get the name of the ship
and
> so that we would know not to display that records regular price). The
> StartDate and EndDate would have the date range the promotional prices ran
> for.
> Here is a test file setup with the display of all the records.
>
****************************************
************************************
******************************kred">
> drop table shipPricing
> create table shipPricing
> (
> ShipPricingID int Identity Not Null,
> ShipName varChar(50) Null,
> PromoPriceID int Null,
> Price Money Not Null,
> StartDate smalldatetime Null,
> EndDate smalldatetime Null
> )
> insert shipPricing(ShipName,Price) values ('Sea Witch',1500)
> insert shipPricing(PromoPriceID,Price,StartDate
,EndDate) values
> (Scope_Identity(),1300,'12/01/05','12/31/05')
> insert shipPricing(ShipName,Price) values ('Southern Cross',2200)
> insert shipPricing(PromoPriceID,Price,StartDate
,EndDate) values
> (Scope_Identity(),2050,'12/15/05','12/31/05')
> select ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price
> = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
> Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
> SubString(Convert(varChar,EndDate,1),1,1
2) from shipPricing
> ShipPricingID ShipName PromoPriceID Price
> StartDate EndDate
> -- -- -- -- --
--
> --
> 1 Sea Witch NULL 1,500.00
> NULL NULL
> 2 NULL 1
> 1,300.00 12/01/05 12/31/05
> 3 Southern Cross NULL 2,200.00
> NULL NULL
> 4 NULL 3
> 2,050.00 12/15/05 12/31/05
>
****************************************
************************************
****************************************
**
> What I want to do is have a Select statement that would give me back the
> prices available for a particular date (Select ... where date = some
date).
> If it is a promotion I don't want to show the regular price and I would
> like it to say something like "Promo".
> For example:
> If I want the prices for 11/15/01 I should get something like:
> ShipPricingID ShipName PromoPriceID Price
> StartDate EndDate
> -- -- -- -- --
--
> --
> 1 Sea Witch NULL 1,500.00
> NULL NULL
> 3 Southern Cross NULL 2,200.00
> NULL NULL
> If I want prices for 12/05/05, I should get something like:
> ShipPricingID ShipName PromoPriceID Price
> StartDate EndDate
> -- -- -- -- --
--
> --
> 2 Sea Witch 1
> 1,300.00 12/01/05 12/31/05 Promo
> 3 Southern Cross NULL 2,200.00
> NULL NULL
> If I want prices for 12/18/05, I should get something like:
> ShipPricingID ShipName PromoPriceID Price
> StartDate EndDate
> -- -- -- -- --
--
> --
> 2 Sea Witch 1
> 1,300.00 12/01/05 12/31/05 Promo
> 4 Southern Cross 3
2,050.00
> 12/15/05 12/31/05 Promo
> I can't figure out how to select one record and not the other in one
Select
> statement.
> Thanks,
> Tom
>|||This is not a table. It has no key, uses proprietary data types, etc.
This one table has more not null-able columns than the payroll for a
major auto manufacture.
Are you aware that "camelCase" adds 8-12% more time to reading code?
The eye jumps to the uppercase letter, then back to the front of the
word.
Is there really a ship with a CHAR(50) name or were you just too lazy
to pick a proper size? With the complete lack of data integrity in
this schema, you will get one.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. IDENTITY cannot ever be a key
**by definition**, which I would hope you have learned by now.
CREATE TABLE VoyagePricing -- were you pricing the ships or the
trips?
(ship_name VARCHAR(30) NOT NULL -- I would use a ship code
CHECK (ship_name IN (..)),
promo_name CHAR(10) DEFAULT 'Regular Price' NOT NULL,
trip_price DECIMAL(7,2) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (ship_name, start_date)
);
Notice the use of duration ranges. Promos are nested inside the
regular fare ranges.
That is really bad business! You can show them what they are saving
with this query.
SELECT ship_name, promo_name, trip_price
FROM VoyagePricing AS P
WHERE @.my_date BETWEEN start_date AND end_date;
This will give you both the promo and regular prices. I assume that we
give the customer the lower price.
SELECT ship_name, MIN(trip_price)
FROM VoyagePricing
WHERE @.my_date BETWEEN start_date AND end_date
GROUP BY ship_name;
You keep posting the worst code of anyone in this newsgroup. Can you
get your boss to pay for a basic RDBMS course for you and the other
programmers?
A little over a year ago, I got to watch incompetent RDBMS programmers
like you kill children in Africa by messing up a medical supply system.|||Nice Rant.
First of all, and you seem to miss the point, this is not my actual table.
If you look closely (and I know this is difficult), there are NO ship
details. That is because "I MADE THIS TABLE UP JUST TO IRRITATE YOU".
The table has exactly what I felt was necessary to illustrate my problem and
allow others to quickly run it (if they want) without wasting anyones time.
I am not worrying about keys here, I am not worried about indexes, I am not
worried about the size of the Ship name (anywhy would you think a 50 was not
a proper size for a ship name - too big, too little')
Everyone here has been great and I appreciate the time that people take to
help others. I would not presume on their time by not putting only the
necessary elements to illustrate the problem. I'm not sure where
nullable/non nullable, camelCase, Pascal, Uppercase, lower case, variable
size etc. has anything to do with the question I was asking.
BTW, you weren't incorrect in your assessment as to what I was looking for.
VC got it. What happened to you? This was the reason I made the table as
spartan as possible as well as multiple examples to show what I was looking
for. You obviously missed it.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124999165.137822.47650@.g44g2000cwa.googlegroups.com...
> This is not a table. It has no key, uses proprietary data types, etc.
> This one table has more not null-able columns than the payroll for a
> major auto manufacture.
>
It is a table.
A table must have keys?
And you even call it a table (one that has more not nullable columns ...).
It has 2 BTW, one being identity column, which by definition would not be
nullable anyway.
create table shipPricing
(
ShipPricingID int Identity Not Null,
ShipName varChar(50) Null,
PromoPriceID int Null,
Price Money Not Null,
StartDate smalldatetime Null,
EndDate smalldatetime Null
)
What propriety data types?

> Are you aware that "camelCase" adds 8-12% more time to reading code?
> The eye jumps to the uppercase letter, then back to the front of the
> word.
>
Never seen that statistic. Where did you get that from?
Actually, I use camelCase for variable names (as do many). I also don't do
ship_names, I do ShipNames or shipNames. I never liked the underscore. But
that's just me.
You use ShipNames and ship_names style - but that's just you.

> Is there really a ship with a CHAR(50) name or were you just too lazy
> to pick a proper size? With the complete lack of data integrity in
> this schema, you will get one.
YUP.
schema? What schema? This is just a table (wait a minute, this isn't a
table) ! :)
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. IDENTITY cannot ever be a key
> **by definition**, which I would hope you have learned by now.
>
Cannot ever be a key'?
By what definition ?
Here is one I have read:
Definition: The primary key of a relational table uniquely identifies each
record in the table. It can either be a normal attribute that is guaranteed
to be unique (such as Social Security Number in a table with no more than
one record per person) or it can be generated by the DBMS (such as a
globally unique identifier, or GUID, in Microsoft SQL Server). Primary keys
may consist of a single attribute or multiple attributes in combination.
Does an Identity uniquely identify each record in the table?
Is it guaranteed to be unique? ( by definition)
Is it generated by the DBMS'?
Single attribute?

> CREATE TABLE VoyagePricing -- were you pricing the ships or the
> trips?
Why is that even a question and what does it have to do with the question?
Maybe I'm pricing the popcorn on the ship. What difference does it make?

> (ship_name VARCHAR(30) NOT NULL -- I would use a ship code
So would I, but I wanted to see the best way to get the name both from the
record that had the name and the Promo that didn't, but refered to the
record that had the name.

> CHECK (ship_name IN (..)),
> promo_name CHAR(10) DEFAULT 'Regular Price' NOT NULL,
Not really quite sure what you are doing here. There are 2 prices one
regular and one or more Promotional record that would display (instead of)
the regular price between the start and end dates (such as Chrismas in my
examples).

> trip_price DECIMAL(7,2) NOT NULL,
> start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_date DATETIME NOT NULL,
> CHECK (start_date < end_date),
> PRIMARY KEY (ship_name, start_date)
> );
> Notice the use of duration ranges. Promos are nested inside the
> regular fare ranges.
>
Not what I am trying to do. How does this handle multiple Promos'?
where is the Date Range for the Promos'

> That is really bad business! You can show them what they are saving
> with this query.
>
Bad Business? How about incorrect assessment of the question? or the
problem?

> SELECT ship_name, promo_name, trip_price
> FROM VoyagePricing AS P
> WHERE @.my_date BETWEEN start_date AND end_date;
> This will give you both the promo and regular prices. I assume that we
> give the customer the lower price.
>
Won't give them what I was trying to give them.

> SELECT ship_name, MIN(trip_price)
> FROM VoyagePricing
> WHERE @.my_date BETWEEN start_date AND end_date
> GROUP BY ship_name;
>
Also, won't give them what I was trying to give them. You have what I
needed above - how did you miss it' "If it is a promotion I don't want
to show the regular price and I would like it to say something like "Promo".
Where is that done with either of your examples'?
Ok, let me change the statement slightly.
"If the date that I am asking for is between any of the dates in the
Promotion records (row) for each Ship, display price for that row as well as
the ships name which you can get from the regular price record of that ship
and also pass back the word "Promo". If the date requested is not between
any of the dates in any of the Promotion records (row) for each ship, then
show the regular price and blank (null) instead of the word 'Promo'". In
any case, I should get 1 and only 1 record (row) back for each ship (either
a regular price or a promotional price)."

> You keep posting the worst code of anyone in this newsgroup.
Could be the case. If I was an expert, I wouldn't be asking questions.
<Can you
> get your boss to pay for a basic RDBMS course for you and the other
> programmers?
>
Already been there done that (many years ago). But I am also not an RDBMS
expert (as you may have gathered). And I am sure there are many ways to
skin a cat, as can be seen in this group. They can be done multiple ways
and still be right.

> A little over a year ago, I got to watch incompetent RDBMS programmers
> like you kill children in Africa by messing up a medical supply system.
Never been to Africa.
Tom|||"VC" <me@.here.com> wrote in message
news:XOCdnQobJZG1gpPeRVn-pw@.comcast.com...
> t - try this:
> declare @.dt datetime
> set @.dt = cast('12/18/05' as datetime)
> select
> case
> when @.dt between s2.startdate and s2.enddate then s2.ShipPricingID
> else s.ShipPricingID
> end as ShipPricingID,
> ShipName = substring(s.ShipName,1,20),
> case
> when @.dt between s2.startdate and s2.enddate then s2.PromoPriceID
> else s.PromoPriceID
> end as PromoPriceID,
> case
> when @.dt between s2.startdate and s2.enddate then s2.Price
> else s.Price
> end as Price,
> case
> when @.dt between s2.startdate and s2.enddate then s2.StartDate
> else s.StartDate
> end as StartDate,
> case
> when @.dt between s2.startdate and s2.enddate then s2.EndDate
> else s.EndDate
> end as EndDate,
> case
> when @.dt between s2.startdate and s2.enddate then 'PROMO'
> else NULL
> end as Promo
> from shipPricing s inner join shipPricing s2 on s.ShipPricingID =
> s2.PromoPriceID
Seems to do the job, except it shows Promotional records when it should show
Regular records and vice versa. I am sure it is just that one of the tests
is in reverse. Just need to look at it to see exactly what it does.
Just what I need, though.
Thanks a lot,
Tom
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:O84VJtZqFHA.2276@.TK2MSFTNGP10.phx.gbl...
> prices).
> the
> and
> ****************************************
**********************************
**
> ******************************
> Price
> --
> ****************************************
**********************************
**
> ****************************************
**
> date).
> --
> --
> --
> 2,050.00
> Select
>|||tshad wrote:
> How would I select one record and not the another related record in one
> Select statement?
> I have regular prices that I want to display on most days and promotional
> prices on particular days (christmas, summer etc) where I don't want to
> display the regular price if I am displaying the promotional price.
> I have a table that has 2 types of records in it.
> One record is a normal ticket price on a ship. The other record is a
> promotional price for that ship.
>
Can I ask why you don't have two tables - one with just the ship
pricing (for the regular price - surely every ship must have a regular
price), and then a seperate table, foreign keying to the first,
containing the promotions? It seems like you're actually trying to
squeeze two tables into one, even though they don't share any
attributes?
Damien

Choosing one record from many

I have a table that lists user ID's and their last login times, and most of the records have duplicates with the only difference being the date field showing the last login time. How can I retrieve only the most current record for each ID from this table?

For example, I have ID's ABC and XYZ. Both are listed in the table 6 times each, but I want a resultant table of only 2 records, one for ABC and one for XYZ, and each of these records is the one with the most current (latest)date.

I am using CR 8.5

ThanksWhy not do this in your SQL query, Select Distinct(yourId) from...|||Note: You cannot change the SELECT clause of the SQL statement.

This note is from Crystal Reports Online Help. It seems that when I open the "Show SQL Query", I can edit anything else but the SELECT clause. So it seems that I can't use the DISTINCT from here.

Any other help please!!!

choosing a the column that is not null

I have two columns in a table. For every record, one column contains a value and the other one is null. Which one is filled and which one is empty differs per record. In my query I only want to retrieve the column that contains the value, but not the other one. Is it possible to do this with a standard tsql function? Other solutions are also welcome.REfer IS [NOT] NULL from BOL.|||I tried to use "is null", "not null" and stuff like that, but it did not get the desired results. Meanwhile I found the functions "ISNULL()" and "NULLIF()". As I can see it now, these provide the correct results.|||Originally posted by jora
I tried to use "is null", "not null" and stuff like that, but it did not get the desired results. Meanwhile I found the functions "ISNULL()" and "NULLIF()". As I can see it now, these provide the correct results.

You might want to take a look at the function COALESCE() which is designed to solve the problem you have.

The function is also part of the ANSI standard and is supported by many RDBMS.

Choosing a record by radiobutton

Please help me out:

I have some records in a sqldatasource and want to show it column wise. Now I do it with a datalist because it's easy. But other options are open.

Every item/record should have a radiobutton (in a group, so that you can only choose one from all). People advised me to do this with a html radiobutton inside the template.

After the user has selected an item and chooses the next-button I need to know what item the user has choosen.

Furthermore, when the user likes to step back, the same radiobutton should allready be selected.

Please help, this is bothering me for a while,

best regards from The Netherlands,

Gert

Addition to my question in the hope for more reply.

I have a sqldatasource with records. This I like to show to the user (when possible using paging). I need the user to select one item (I thought using a radiobutton). Somekind of radiobutton in a group, because only one could be selected.

Presenting the data with a datalist is really nice, but maybe I have to give up this nice buildin control and make my own thing.
A guy advised me to put a html radiobutton inside the template of the datalist. This works a bit, but the user has a next and previous all though the program. When I do that, and the user wants to do a previous, the choosen item should be allready selected with the radiobutton as he choose before. This is not possible for me at the moment.

this code someone provided me :

<input type="radio" name="keuze" value='<%# Eval("fruitmandnr") %>' onclick="form.submit();" <%#Hulpfunctie(Eval("fruitmandnr"))%> /
Function Hulpfunctie(ByVal obj As Object) As String
If (obj Is Nothing) Then Return ""
Dim keuze As Object = Request.Form("keuze")
If (keuze Is Nothing) Then Return ""
If (keuze.ToString() = obj.ToString) Then Return "checked"
Return ""
End Function

checktable repair_rebuild taking long time

Hi! I am running dbcc checktable with repair_rebuild option for a table of
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.
I would check out Kalen Delaney's article on the sysprocesses table at www.sqlmag.com.
Check the delta of the CPU usage in sysprocesses to determine how much progress the check is making.
|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>
|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
of[vbcol=seagreen]
> for
(how
>
|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and[vbcol=seagreen]
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
long.[vbcol=seagreen]
> You'd
> rights.
table[vbcol=seagreen]
> of
74[vbcol=seagreen]
(1:11667248),[vbcol=seagreen]
command
> (how
>

checktable repair_rebuild taking long time

Hi! I am running dbcc checktable with repair_rebuild option for a table of
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.I would check out Kalen Delaney's article on the sysprocesses table at com." target="_blank">www.sqlmag.
com.
Check the delta of the CPU usage in sysprocesses to determine how much progr
ess the check is making.|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
of[vbcol=seagreen]
> for
(how[vbcol=seagreen]
>|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
long.[vbcol=seagreen]
> You'd
> rights.
table[vbcol=seagreen]
> of
74[vbcol=seagreen]
(1:11667248),[vbcol=seagreen]
command[vbcol=seagreen]
> (how
>

checktable repair_rebuild taking long time

Hi! I am running dbcc checktable with repair_rebuild option for a table of
121 Million record (about 150 GB) in size and its already running for 74
hours and still going. Table had Keys out of order on page (1:11667248),
slots 5 and 6 (Which was clustered Index).
Could anyone tell me how long does it normally take to run this command for
table of this size? Is there any way we can see the status of process (how
far it has gone percentage wise)?
Environment:
Sql 2k SP2 running on Wi2k Advanced server
8 CPU 2.7 GH and 8 GB RAM.I would check out Kalen Delaney's article on the sysprocesses table at www.sqlmag.com
Check the delta of the CPU usage in sysprocesses to determine how much progress the check is making.|||It's rebuilding the clustered index and all the non-clustered indexes as
part of the repair - depending on how much and the distribution of free
space this could take a while but I wouldn't expect it to take that long.
What was the exact output from checkdb before you re-ran with repair? You'd
have been much better off restoring from your backups (which is the
recommeneded strategy)
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> Hi! I am running dbcc checktable with repair_rebuild option for a table of
> 121 Million record (about 150 GB) in size and its already running for 74
> hours and still going. Table had Keys out of order on page (1:11667248),
> slots 5 and 6 (Which was clustered Index).
> Could anyone tell me how long does it normally take to run this command
for
> table of this size? Is there any way we can see the status of process (how
> far it has gone percentage wise)?
> Environment:
> Sql 2k SP2 running on Wi2k Advanced server
> 8 CPU 2.7 GH and 8 GB RAM.
>|||I end up cancelling the job because it was already running for 78 hours and
still going. following was the result of checktable:
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 437576597, Index ID 0. Keys out of order on page
(1:11667248), slots 5 and 6.
DBCC results for 'TableA'.
There are 100344909 rows in 13487540 pages for object 'TableA'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'TableA'(object ID 437576597).
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKTABLE (DatabaseA.dbo.TableA ).
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> It's rebuilding the clustered index and all the non-clustered indexes as
> part of the repair - depending on how much and the distribution of free
> space this could take a while but I wouldn't expect it to take that long.
> What was the exact output from checkdb before you re-ran with repair?
You'd
> have been much better off restoring from your backups (which is the
> recommeneded strategy)
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "james" <kush@.brandes.com> wrote in message
> news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> > Hi! I am running dbcc checktable with repair_rebuild option for a table
of
> > 121 Million record (about 150 GB) in size and its already running for 74
> > hours and still going. Table had Keys out of order on page (1:11667248),
> > slots 5 and 6 (Which was clustered Index).
> > Could anyone tell me how long does it normally take to run this command
> for
> > table of this size? Is there any way we can see the status of process
(how
> > far it has gone percentage wise)?
> > Environment:
> > Sql 2k SP2 running on Wi2k Advanced server
> > 8 CPU 2.7 GH and 8 GB RAM.
> >
> >
>|||So there are three problems here:
1) why did the corruption happen?
2) why did repair take so long?
3) removing the corruption
3) is easy - simply rebuild the index - that's all repair was doing.
However, you should run a full checkdb first as I suspect the answer to 2)
is that you have other corruptions in the database. If the checkdb comes up
clean, there's something more insidious happening and you should call PSS to
help determine the cause.
To do root-cause analysis for 1), you should check through all relevant logs
(NT event and SQL) for hardware problems, check whether there are any known
issues fixed in SP3+ that could be the problem. Again, PSS can help you with
this.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pgDF6ePEHA.620@.TK2MSFTNGP10.phx.gbl...
> I end up cancelling the job because it was already running for 78 hours
and
> still going. following was the result of checktable:
> Server: Msg 2511, Level 16, State 2, Line 1
> Table error: Object ID 437576597, Index ID 0. Keys out of order on page
> (1:11667248), slots 5 and 6.
> DBCC results for 'TableA'.
> There are 100344909 rows in 13487540 pages for object 'TableA'.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'TableA'(object ID 437576597).
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKTABLE (DatabaseA.dbo.TableA ).
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23NmJWGcPEHA.540@.TK2MSFTNGP11.phx.gbl...
> > It's rebuilding the clustered index and all the non-clustered indexes as
> > part of the repair - depending on how much and the distribution of free
> > space this could take a while but I wouldn't expect it to take that
long.
> > What was the exact output from checkdb before you re-ran with repair?
> You'd
> > have been much better off restoring from your backups (which is the
> > recommeneded strategy)
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "james" <kush@.brandes.com> wrote in message
> > news:eoZKA1PPEHA.3052@.TK2MSFTNGP12.phx.gbl...
> > > Hi! I am running dbcc checktable with repair_rebuild option for a
table
> of
> > > 121 Million record (about 150 GB) in size and its already running for
74
> > > hours and still going. Table had Keys out of order on page
(1:11667248),
> > > slots 5 and 6 (Which was clustered Index).
> > > Could anyone tell me how long does it normally take to run this
command
> > for
> > > table of this size? Is there any way we can see the status of process
> (how
> > > far it has gone percentage wise)?
> > > Environment:
> > > Sql 2k SP2 running on Wi2k Advanced server
> > > 8 CPU 2.7 GH and 8 GB RAM.
> > >
> > >
> >
> >
>

Checking to see if a record exists before inserting

I can't seem to get this work. I'm using SQL2005

I want to check if a record exists before entering it. I just can't figure out how to check it before hand.

Thanks in advance.

Protected Sub BTNCreateProdIDandName_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles BTNCreateProdIDandName.Click' Define data objectsDim connAs SqlConnectionDim commAs SqlCommand' Reads the connection string from Web.configDim connectionStringAs String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString' Initialize connection conn =New SqlConnection(connectionString)' Check to see if the record existsIf comm =New SqlCommand("EXISTS (SELECT (BuilderID, OptionNO FROM optionlist WHERE (BuilderID = @.BuilderID) AND (OptionNO = @.OptionNO)", conn)Then'if the record is exists display this message. LBerror.Text ="This item already exists in your Option List."Else'If the record does not exist, add it. FYI - This part works fine by itself. comm =New SqlCommand("INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName]) VALUES (@.BuilderID, @.OptionNO, @.OptionName)", conn) comm.Parameters.Add("@.BuilderID", System.Data.SqlDbType.Int) comm.Parameters("@.BuilderID").Value = LBBuilderID.Text comm.Parameters.Add("@.OptionNO", System.Data.SqlDbType.NVarChar) comm.Parameters("@.OptionNO").Value = DDLProdID.SelectedItem.Value comm.Parameters.Add("@.OptionName", System.Data.SqlDbType.NVarChar) comm.Parameters("@.OptionName").Value = DDLProdname.SelectedItem.Value LBerror.Text = DDLProdname.SelectedItem.Value &" was added to your Option List."Try'open connection conn.Open()'execute comm.ExecuteNonQuery()Catch'Display error message LBerror.Text ="There was an error adding this Option. Please try again."Finally'close connection conn.Close()End Try End If End Sub

You need to execute the command to see if the record exists

comm =New SqlCommand("EXISTS (SELECT (BuilderID, OptionNO FROM optionlist WHERE (BuilderID = @.BuilderID) AND (OptionNO = @.OptionNO)", conn)

If cbool(comm.executescalar) then

|||

Hello my friend,

Try this in your SQL: -

IF EXISTS (SELECT 1 FROM optionlist WHERE BuilderID = @.BuilderID AND OptionNO = @.OptionNO)
BEGIN
RETURN 'ALREADY_EXISTS'
END
ELSE BEGIN
INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName])
VALUES (@.BuilderID, @.OptionNO, @.OptionName)

RETURN 'INSERT_OKAY'
END

Then execute this with Dim strResult as string = comm.ExecuteScalar(), not ExecuteNonQuery(), and check the strResult string to determine whether or not to display the LBerror text.

If you have any questions on this, please let me know.

Kind regards

Scotty

|||

Scotty,

Thanks for your help. I inserted the sql and I'm getting an error.

Incorrect syntax near ')'.
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.

Line 81: Dim strResult As String = comm.ExecuteScalar()

Here is what I'm using.

I don't think I did this right..."Then execute this with Dim strResult as string = comm.ExecuteScalar(), not ExecuteNonQuery(), and check the strResult string to determine whether or not to display the LBerror text. "

Protected

Sub BTN1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles BTN1.Click' Define data objectsDim connAs SqlConnectionDim commAs SqlCommand' Reads the connection string from Web.configDim connectionStringAsString = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString' Initialize connection

conn =

New SqlConnection(connectionString)' Check to see if the record exists

conn.Open()

comm =

New SqlCommand("IF EXISTS (SELECT 1 FROM optionlist WHERE BuilderID = @.BuilderID AND OptionNO = @.OptionNO) BEGIN()Return 'ALREADY_EXISTS' End Else : BEGIN()INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName]) VALUES (@.BuilderID, @.OptionNO, @.OptionName)Return 'INSERT_OKAY'End)", conn)

comm.Parameters.Add(

"@.BuilderID", System.Data.SqlDbType.Int)

comm.Parameters(

"@.BuilderID").Value = LBBuilderID.Text

comm.Parameters.Add(

"@.OptionNO", System.Data.SqlDbType.NVarChar)

comm.Parameters(

"@.OptionNO").Value = DDLProdID.SelectedItem.Value

comm.Parameters.Add(

"@.OptionName", System.Data.SqlDbType.NVarChar)

comm.Parameters(

"@.OptionName").Value = DDLProdname.SelectedItem.ValueDim strResultAsString = comm.ExecuteScalar()

' I'm not sure what to do here. to get my error message to show.

LBerror.Text = strResult.ToString

conn.Close()

EndSub

|||

Option 1

=====

You can modify the query as

SELECT COUNT(*) as CountOfRecords

FROM

FROM optionlist WHERE BuilderID = @.BuilderID AND OptionNO = @.OptionNO

If the Count is greater than 0 then you know the record exists

Option2

=======

Wrap Scotty's SQL in a Stored procedure and call the SP. I would do this way. SPs are fast and adds alayer of abstraction.

|||

rednelo:

Scotty,

Thanks for your help. I inserted the sql and I'm getting an error.

Incorrect syntax near ')'.
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.

Line 81: Dim strResult As String = comm.ExecuteScalar()

That because you added a set of parentheses following the BEGIN statement that Scott did not have in his supplied code. Remove those and you should have better luck.

|||

I removed the parentheses. Don't know how they got there...

I also tired putting it in a sproc, but I keep getting the same message.

Msg 178, Level 15, State 1, Line 3

A RETURN statement with a return value cannot be used in this context.

Msg 178, Level 15, State 1, Line 9

A RETURN statement with a return value cannot be used in this context.

|||

rednelo:

I removed the parentheses. Don't know how they got there...

I also tired putting it in a sproc, but I keep getting the same message.

Msg 178, Level 15, State 1, Line 3

A RETURN statement with a return value cannot be used in this context.

Msg 178, Level 15, State 1, Line 9

A RETURN statement with a return value cannot be used in this context.

A RETURN statement can only return an integer value. Scotty made a typo in his original code. Use SELECT instead:

SELECT 'ALREADY_EXISTS'

and

SELECT 'INSERT_OKAY'

|||

Those little typos can really cause one to pull their hair out! 4 hours later... We finally got it.

Thank-you for your help this works well.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.
|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.
|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.
|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.

Checking to see if a record exists and if so update else insert

I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

PhilThis is exactly what I was looking for.

I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.

However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.

Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.

Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.

I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.|||

This is very helpful. Thanks!

I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?

Thanks!

|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

|||

Sudhir Kesharwani wrote:

Hi phill,

Great article, I loved it.

This is what i had been looking for since long time.

One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.

Will this method work for me or there is any other better way.. ?

regards

Sudhir Kesharwani

You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||

Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".

I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split

I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.

So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).

What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.

When the data was updated (second round), SQL was always faster.

Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.

Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?

Leonce

|||

Leonce,

First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?

Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.

SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)

Phil

|||

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

I use OLEDB Destination, I'll try with SQL Server Destination.

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

Leonce

|||

fleo wrote:

I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?

MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.

fleo wrote:

I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.

True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.

|||

Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.