Is this correct?

Are these two statements equivalent? If not, how can I rephrase the 2nd
one?
UPDATE Contact
SET DoNotCallTypeKey = 20
WHERE EXISTS (
SELECT *
FROM SSA
INNER JOIN SSP
ON SSP.SampleSourceArchiveKey = SSA.SampleSourceArchiveKey
WHERE ssp.ContactKey = Contact.ContactKey
AND ssa.SampleSourceKey = @.sampleSourceKey
AND ssa.SurveyFlag = 'DNS'
)
vs
UPDATE Contact
SET DoNotCallTypeKey = 20
FROM Contact
INNER JOIN SSP
ON ssp.ContactKey = ssp.ContactKey
INNER JOIN SSA
ON ssa.SampleSourceArchiveKey = ssp.SampleSourceArchiveKey
WHERE ssa.SampleSourceKey = @.sampleSourceKey
AND ssa.SurveyFlag = 'DNS'
They both look like they return the same results, but when it operates on
1.5 million records, I really don't want to have to scroll trhough both
result sets to prove it. I want to rephrase it because I hate all these
dumb "where exists" things that are upside down with the criteria and joins
all stuffed into sub-selects.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneMike,
They look the same with the exception:
ON ssp.ContactKey = ssp.ContactKey
should be
ON ssp.ContactKey = Contact.ContactKey
HTH
Jerry
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:Ou%23dxAvuFHA.908@.tk2msftngp13.phx.gbl...
> Are these two statements equivalent? If not, how can I rephrase the 2nd
> one?
> UPDATE Contact
> SET DoNotCallTypeKey = 20
> WHERE EXISTS (
> SELECT *
> FROM SSA
> INNER JOIN SSP
> ON SSP.SampleSourceArchiveKey = SSA.SampleSourceArchiveKey
> WHERE ssp.ContactKey = Contact.ContactKey
> AND ssa.SampleSourceKey = @.sampleSourceKey
> AND ssa.SurveyFlag = 'DNS'
> )
> vs
> UPDATE Contact
> SET DoNotCallTypeKey = 20
> FROM Contact
> INNER JOIN SSP
> ON ssp.ContactKey = ssp.ContactKey
> INNER JOIN SSA
> ON ssa.SampleSourceArchiveKey = ssp.SampleSourceArchiveKey
> WHERE ssa.SampleSourceKey = @.sampleSourceKey
> AND ssa.SurveyFlag = 'DNS'
>
> They both look like they return the same results, but when it operates on
> 1.5 million records, I really don't want to have to scroll trhough both
> result sets to prove it. I want to rephrase it because I hate all these
> dumb "where exists" things that are upside down with the criteria and
> joins all stuffed into sub-selects.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> They look the same with the exception:
> ON ssp.ContactKey = ssp.ContactKey
> should be
> ON ssp.ContactKey = Contact.ContactKey
Sorry, that was just a typo.
I'm just looking for confirmation that they're the same. Thanks.
These people around here come from a MS Access background and they don't
know how to use joins right. So they just write all these upside down
things with WHERE [NOT] EXISTS and a long list of sub-selects, and it's
almost indecipherable sometimes.
Thank You!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OcA08JvuFHA.3864@.TK2MSFTNGP12.phx.gbl...
> Mike,
>
> HTH
> Jerry
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:Ou%23dxAvuFHA.908@.tk2msftngp13.phx.gbl...
>|||The first is correct SQL, while the seconnd is proprietary and
problematic.
It makes no sense in terms of the SQL language model. A FROM clause is
always suppose effectively materialize a working table that disappeare
at the end of the statement. Likewise, an alias is supposed to act as
it materializes a new working table with the data from the original
table expression in it. To be consistent, this syntax says that you
have done nothing to the base table.
Sybase and some other vendors had the same syntax but with different
semantics. Worst of both worlds!
And on top of that, it is unpredictable. This is a simple example from
Adam Machanic
CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);
CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);
You run this proprietary UPDATE with a FROM clause:
UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;
The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).
The join mechanism hides cardinality violations, turns your data into
garbage.|||On Fri, 16 Sep 2005 14:55:46 -0400, Mike Labosh wrote:

>Are these two statements equivalent? If not, how can I rephrase the 2nd
>one?
Hi Mike,
Apart from the typo, they might be. But it's also possible that the
second version sets the DoNotCallTypeKey for a row to 20 hundreds of
times during the execution. I'd have to know the table structure to be
sure.
But there are some important other issues that you should think about.
First: The first statement is ANSI standard SQL, that will eaasily port
to other database platforms. The second is proprietary syntax that runs
fine on SQL Server, but can't be ported to other databases. Even MS' own
"other" database product (Access) won't run this code - it has a similar
non-ANSI syntax for UPDATE and DELETE, but it's not the same as in SQL
Server!
Second: There are definitely situations where I would choose to use the
non-standard UPDATE ... FROM syntax. But this is not one of them. I
would consider using the proprietary syntax if the new value for the
DoNotCallTypeKey had to be taken from one of the tables in the subquery
(as SQL Server isn't very clever about optimizing statements that have
the same subquery twice).
Third: Even on SQL Server, the second syntax will sometimes fail. If
Contact is not a base table, but a view, AND you have an INSTEAD OF
UPDATE trigger defined on Contact, you'll get an error if you try the
second syntax. SQL Server somehow doesn't know how to handle this
situation.
Fourth:
> I want to rephrase it because I hate all these
>dumb "where exists" things that are upside down with the criteria and joins
>all stuffed into sub-selects.
I consider this to be a very bad reason. Personal bias should always
come second to professional impartiality.
Instead of trying to get your Access developers to learn a syntax that
won't work on Acces, you'd be better off getting yourself acquainted
with the syntax that will work on all SQL-92 compliant databases. You'll
also find that this syntax grows on you when you use it more often (as I
found out when I had to rewrite dozens of UPDATE ... FROM statements
because the view they updated had to be equipped with an ISNTEAD OF
trigger).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Is there the equivalent of the Access Query Design Grid in SQL Server?

Hi. Is there the equivalent of the Access Query Design Grid in SQL
Server 2005 beta? In other words, a SQL Server 2005 beta Query Design
Grid? If so, does it design queries for XML data? Also if so, I read
somewhere on the web that the Access Query Design Grid doesn't do joins
so would I be correct to assume the SQL Server version wouldn't do
joins either?
Thank you. Regards.
There is no XQuery designer unfortunately.
Sorry
Michael
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1133931766.736605.160010@.z14g2000cwz.googlegr oups.com...
> Hi. Is there the equivalent of the Access Query Design Grid in SQL
> Server 2005 beta? In other words, a SQL Server 2005 beta Query Design
> Grid? If so, does it design queries for XML data? Also if so, I read
> somewhere on the web that the Access Query Design Grid doesn't do joins
> so would I be correct to assume the SQL Server version wouldn't do
> joins either?
> Thank you. Regards.
>

Is there the equivalent of the Access Query Design Grid in SQL Server?

Hi. Is there the equivalent of the Access Query Design Grid in SQL
Server 2005 beta? In other words, a SQL Server 2005 beta Query Design
Grid? If so, does it design queries for XML data? Also if so, I read
somewhere on the web that the Access Query Design Grid doesn't do joins
so would I be correct to assume the SQL Server version wouldn't do
joins either?
Thank you. Regards.There is no XQuery designer unfortunately.
Sorry
Michael
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1133931766.736605.160010@.z14g2000cwz.googlegroups.com...
> Hi. Is there the equivalent of the Access Query Design Grid in SQL
> Server 2005 beta? In other words, a SQL Server 2005 beta Query Design
> Grid? If so, does it design queries for XML data? Also if so, I read
> somewhere on the web that the Access Query Design Grid doesn't do joins
> so would I be correct to assume the SQL Server version wouldn't do
> joins either?
> Thank you. Regards.
>

Is there equivalent to Sybase Dynamic Archiving for SQL Server

Sybase has a product called Dynamic Archiving which will automatically move
old records to a archive database, but all applications will see the two
database as one (no changes to source code etc.)
Is there an equivalent for SQL Server (either now, or part of 2005, or third
party) ?
Any help would be appreciated...
Regards,
Mark Donoghue
MDonoghue@.refco.comI'm not aware of anything identical.
In SQL2K... you might be able to achieve a similiar effect by looking at
Partioned Views. Not real archiving, but you might get some of the benefits
you're looking for. Stricktly a roll your own solution.
SQL2005 adds support for partioned ranges (using multiple tables) that will
make it much easier to manage this. But still roll your own.
--
"Mark Donoghue" <MarkDonoghue@.discussions.microsoft.com> wrote in message
news:A27078A5-CD17-46C1-8DD1-AA9DA6002730@.microsoft.com...
> Sybase has a product called Dynamic Archiving which will automatically
move
> old records to a archive database, but all applications will see the two
> database as one (no changes to source code etc.)
> Is there an equivalent for SQL Server (either now, or part of 2005, or
third
> party) ?
> Any help would be appreciated...
> Regards,
> Mark Donoghue
> MDonoghue@.refco.com

Is there equivalent to Sybase Dynamic Archiving for SQL Server

Sybase has a product called Dynamic Archiving which will automatically move
old records to a archive database, but all applications will see the two
database as one (no changes to source code etc.)
Is there an equivalent for SQL Server (either now, or part of 2005, or third
party) ?
Any help would be appreciated...
Regards,
Mark Donoghue
MDonoghue@.refco.com
I'm not aware of anything identical.
In SQL2K... you might be able to achieve a similiar effect by looking at
Partioned Views. Not real archiving, but you might get some of the benefits
you're looking for. Stricktly a roll your own solution.
SQL2005 adds support for partioned ranges (using multiple tables) that will
make it much easier to manage this. But still roll your own.
"Mark Donoghue" <MarkDonoghue@.discussions.microsoft.com> wrote in message
news:A27078A5-CD17-46C1-8DD1-AA9DA6002730@.microsoft.com...
> Sybase has a product called Dynamic Archiving which will automatically
move
> old records to a archive database, but all applications will see the two
> database as one (no changes to source code etc.)
> Is there an equivalent for SQL Server (either now, or part of 2005, or
third
> party) ?
> Any help would be appreciated...
> Regards,
> Mark Donoghue
> MDonoghue@.refco.com

Is there equivalent to Sybase Dynamic Archiving for SQL Server

Sybase has a product called Dynamic Archiving which will automatically move
old records to a archive database, but all applications will see the two
database as one (no changes to source code etc.)
Is there an equivalent for SQL Server (either now, or part of 2005, or third
party) ?
Any help would be appreciated...
Regards,
Mark Donoghue
MDonoghue@.refco.comI'm not aware of anything identical.
In SQL2K... you might be able to achieve a similiar effect by looking at
Partioned Views. Not real archiving, but you might get some of the benefits
you're looking for. Stricktly a roll your own solution.
SQL2005 adds support for partioned ranges (using multiple tables) that will
make it much easier to manage this. But still roll your own.
"Mark Donoghue" <MarkDonoghue@.discussions.microsoft.com> wrote in message
news:A27078A5-CD17-46C1-8DD1-AA9DA6002730@.microsoft.com...
> Sybase has a product called Dynamic Archiving which will automatically
move
> old records to a archive database, but all applications will see the two
> database as one (no changes to source code etc.)
> Is there an equivalent for SQL Server (either now, or part of 2005, or
third
> party) ?
> Any help would be appreciated...
> Regards,
> Mark Donoghue
> MDonoghue@.refco.com

Is there an equivalent to an MSAccess IIF statement when creating Store Procs

I'm creating a stored proc and need to use some sort of IF or IIF statement. Is theresome sort of equivalent statement i can use?T-SQL supports an IF and CASE statment. you can read up on both in Books Online.

IF...ELSE
Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement following an IF keyword and its condition is executed if the condition is satisfied (when the Boolean expression returns TRUE). The optional ELSE keyword introduces an alternate Transact-SQL statement that is executed when the IF condition is not satisfied (when the Boolean expression returns FALSE).

Syntax
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

CASE
Evaluates a list of conditions and returns one of multiple possible result expressions.

CASE has two formats:

The simple CASE function compares an expression to a set of simple expressions to determine the result.

The searched CASE function evaluates a set of Boolean expressions to determine the result.
Both formats support an optional ELSE argument.

Syntax
Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:

CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END|||IF(<CHECK CONDITION>)
BEGIN
<CODE>
END
ELSE
BEGIN
< CODE>
END|||IIF doesn't exists in SQL but you can always use the old fashion way:

IF <your_condition>
BEGIN
:
:
END
ELSE
BEGIN
:
:
END

Originally posted by Sammy_S
I'm creating a stored proc and need to use some sort of IF or IIF statement. Is theresome sort of equivalent statement i can use?

is there an equivalent table copy command in ms sql a la oracle?

is there a command in ms sql server 2000 equivalent to this oracle table copy command?
create table myTable_bak as select * from myTable;I guess you can do it as
select * into table1 from table2 which is a bulk copy.

Is there an equivalent syntax to TOP IN SQL?

Hi everyone, I am new to SQL, and would really appreciate help with this.

I have a database with the following fields:
IDNumber: sequential running from 1 to approx 50000
SURNAME: Surname
FNAME: Forename.

I want to return the last 100 IDNUmbers and return the surname and fname associated with the IDNumbers.
When I try TOP it gives me IDNumbers 1 to 100, is there an equilvant for the bottom 100 numbers.
Please help if you can.
Thanks
ScottReturn the top 100, but just order by IDNumbers DESC|||Thank you so much, I would never have worked that out.
And DESC stands for descending !? Fab!
Thanks again, I owe you a beer.

Scott

Is there an equivalent Replicate() function in Access

Hi,

I am trying to build a string from a datetime datetype and want to make sure that if a day or month is only one digit long then it is padded with a leading 0.

In SQL I can do it like this;

declare @.datetime datetime

select @.datetime = getdate()

select 'TPR'

+ replicate('0', 2 - len(datepart(dd,@.datetime))) + convert(varchar(2),datepart(dd,@.datetime))

+ replicate('0', 2 - len(datepart(mm,@.datetime))) + convert(varchar(2),datepart(mm,@.datetime))

+ convert(varchar(4),datepart(yyyy,@.datetime))

Do you know how I can do the same thing in Access?

Thanks for your help

It would be SO-O-O-O much easier to do it in this manner:

SELECT right( '00' + cast( datepart( day, @.DateTime ) AS varchar(2) ), 2 )

(Should work in Access AND SQL Server.)

But really, wouldn't something like this be a better solution: (this is SQL)

SELECT ( 'TPR' + SELECT replace( convert( varchar(10), @.DateTime, 101 ), '/', '' ))

For Access, you would have to use the FORMAT function instead of convert. Refer to the help for FORMAT().

|||

Yes Buddy.. You have it..

STRING(<number>, <Char>)

example:

String(5,"M") => MMMMM