Identity column: table

Showing posts with label table. Show all posts

Friday, March 30, 2012

If numeric query

I have a table with a memo field that sometimes contains an account
number at the same location. I need to find a record related to that
account in another table. I can write and run a query for a record
which works fine if the account number exists, but (obviously) get an
error if it does not. I wrote the following query which I don't
understand why it will not work.
IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1)
BEGIN
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
END
ELSE
BEGIN
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate
FROM tblRegister
END
Any help on this would be greatly appreciated.
Thanks
CharlesA couple of comments.
First, isnumeric isn't foolproof. You may wish to review this source:
isnumeric -What is wrong?
http://www.aspfaq.com/show.asp?id=2390
Second, it appears that you are attempting to compare a string to a number,
e.g.,
SUBSTRING(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
(I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
You need to cast the first part as a numeric value in order make the compari
son. And this will not use indexing, so expect performance to be poor.
If you were to add another column to the Register table, set its value to be
the substring of the Memo field (or better yet, have the application provid
e the number), and then index that column, performance would be rather sprig
htly.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<cbanks@.bjtsupport.com> wrote in message news:1162249400.557355.12560@.k70g2000cwa.googlegrou
ps.com...
>I have a table with a memo field that sometimes contains an account
> number at the same location. I need to find a record related to that
> account in another table. I can write and run a query for a record
> which works fine if the account number exists, but (obviously) get an
> error if it does not. I wrote the following query which I don't
> understand why it will not work.
>
> IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) = 1)
>
> BEGIN
> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> fldStatus, fldLogDate, fldNote
> FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
> (tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> END
>
> ELSE
>
> BEGIN
> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> fldStatus, fldLogDate
> FROM tblRegister
> END
>
> Any help on this would be greatly appreciated.
> Thanks
> Charles
>|||Arnie,
Thanks for the reply. My main issue is when I try to run this as an IF
statement I get an error "Incorrect syntax near the keyword 'BEGIN'."
I actually tried it without the 'Exists' after IF. If I run
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo with a where clause to
locate a record with a valid member number, it works fine. My issue is
running this when I don't have a valid member ID, which is what I am
trying to accomplish with the if statement.
It would have been better to include the member number in my register
table, but this was not in the original scope and I really don't want
to modify my table structure.
Charles
Arnie Rowland wrote:
> A couple of comments.
> First, isnumeric isn't foolproof. You may wish to review this source:
> isnumeric -What is wrong?
> http://www.aspfaq.com/show.asp?id=2390
>
> Second, it appears that you are attempting to compare a string to a number
, e.g.,
> SUBSTRING(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> (I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
>
> You need to cast the first part as a numeric value in order make the compa
rison. And this will not use indexing, so expect performance to be poor.
> If you were to add another column to the Register table, set its value to
be the substring of the Memo field (or better yet, have the application prov
ide the number), and then index that column, performance would be rather spr
ightly.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e top yourself.
> - H. Norman Schwarzkopf
>
> <cbanks@.bjtsupport.com> wrote in message news:1162249400.557355.12560@.k70g
2000cwa.googlegroups.com...
> --=_NextPart_000_048F_01C6FC3D.3D5AC000
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 5062
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.5296.0" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=Arial size=2>A couple of comments.</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>First, isnumeric isn't foolproof. You may wis
h to
> review this source:</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial; mso-no-proof: yes
">isnumeric
> -What is wrong?<BR></SPAN><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s"><A
> href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2390">http://www.aspfaq.com/show.asp?id=
2390</A>
> </SPAN></P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s"><FONT
> face=Arial size=2></FONT></SPAN> </P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">Second,
> it appears that you are attempting to compare a string to a number, e.g.,
> </SPAN></P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s"><FONT
> face="Courier New">SUBSTRING(tblRegister.fldMemo,6,9) =
> tblNotes.fldMemberNo</FONT></SPAN></P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">(I'm assuming
> that tblNotes.fldMemberNo is a numeric datatype.)<BR></P></SPAN>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">You
> need to cast the first part as a numeric value in order make the compariso
n.
> </SPAN><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">And
> this will not use indexing, so expect performance to be poor.</P></SPAN></
DIV>
> <DIV><FONT face=Arial size=2>If you were to add another column to the Regi
ster
> table, set its value to be the substring of the Memo field (or better yet,
have
> the application provide the number), and then index that column, performan
ce
> would be rather sprightly.</FONT><BR><FONT face=Arial size=2>-- <BR>Arnie
> Rowland, Ph.D.<BR>Westwood Consulting, Inc</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Most good judgment comes from experience. <BR
>Most
> experience comes from bad judgment. <BR>- Anonymous</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>You can't help someone get up a hill without
> getting a little closer to the top yourself.<BR>- H. Norman
> Schwarzkopf</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2><</FONT><A
> href="http://links.10026.com/?link=mailto:cbanks@.bjtsupport.com"><FONT face=Arial
> size=2>cbanks@.bjtsupport.com</FONT></A><FONT face=Arial size=2>> wrote
in
> message </FONT><A
> href="http://links.10026.com/?link=news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com"><FONT
> face=Arial
> size=2>news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com</FONT></A
><FONT
> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>>I have a tab
le
> with a memo field that sometimes contains an account<BR>> number at the
same
> location. I need to find a record related to that<BR>> accoun
t in
> another table. I can write and run a query for a record<BR>>
which
> works fine if the account number exists, but (obviously) get an<BR>> er
ror if
> it does not. I wrote the following query which I don't<BR>> u
nderstand
> why it will not work.<BR>> <BR>> IF EXISTS (SELECT fldMemo FROM
> tblRegister WHERE ISNUMERIC(SUBSTRING<BR>> (tblRegister.fldMemo,6,9)) =
> 1)<BR>> <BR>> BEGIN<BR>> SELECT fldChkNo, fldDate, fldName, fldMe
mo,
> fldAmt, fldTransmitted,<BR>> fldStatus, fldLogDate, fldNote<BR>> FRO
M
> tblRegister INNER JOIN tblNotes ON SUBSTRING<BR>> (tblRegister.fldMemo,
6,9) =
> tblNotes.fldMemberNo<BR>> END<BR>> <BR>> ELSE<BR>> <BR>>
> BEGIN<BR>> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt,
> fldTransmitted,<BR>> fldStatus, fldLogDate<BR>> FROM tblRegister<BR>
> END<BR>> <BR>> Any help on this would be greatly appreciated.<BR>>
;
> Thanks<BR>> Charles<BR>></FONT></BODY></HTML>
> --=_NextPart_000_048F_01C6FC3D.3D5AC000--|||Charles,
exists is really for subqueries. However you could use:
IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1) > 0
....
or
SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1
if @.@.rowcount > 0
....
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||> exists is really for subqueries. However you could use:
> IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) = 1) > 0
I'm not sure why this is better than
IF EXISTS (SELECT 1 FROM tblRegister ...)
BEGIN
END
? The exists is potentially faster, and certainly not any slower, than a
select count.
The syntax problem he had was likely just a missing parenthesis...
A|||Thanks Aaron - my goof

.
For some obscure reason I had assumed that it was returning a single row
(long night and needed more coffee!), but as I look more closely at what I
wrote I see issues with both solutions - Charles please disregard my earlier
post.
Regards,
Paul Ibison|||Actually, not a missing parens, but including an equality [ = 1 ) > 0 ]
which made the statement invalid. The statement 'should' work as:
IF EXISTS
( SELECT fldMemo
FROM tblRegister
WHERE isnumeric( substring( tblRegister.fldMemo, 6, 9 ))
)
BEGIN
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:uFkIfWP
$GHA.4708@.TK2MSFTNGP05.phx.gbl...
>
> I'm not sure why this is better than
>
> IF EXISTS (SELECT 1 FROM tblRegister ...)
> BEGIN
> END
>
> ? The exists is potentially faster, and certainly not any slower, than a
> select count.
>
> The syntax problem he had was likely just a missing parenthesis...
>
> A
>
>|||> Actually, not a missing parens, but including an equality [ = 1 ) > 0 ]">
> which made the statement invalid.
Where? I didn't see his code, I just saw fragments of it.
A

If numeric query

A couple of comments.
First, isnumeric isn't foolproof. You =may wish to review this source:
isnumeric -What is wrong?http://www.aspfaq.com/show.asp?id=3D2390">http://www.aspfaq.com/s=how.asp?id=3D2390
Second, it appears that you are attempting to compare a string to a number, =e.g., SUBSTRING(tblRegister.fldMemo,6,9) =3D tblNotes.fldMemberNo
(I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
You need to cast the first part as a numeric value in order make the =comparison. And this will not use indexing, so expect performance to be =poor.
If you were to add another column to =the Register table, set its value to be the substring of the Memo field (or better =yet, have the application provide the number), and then index that column, =performance would be rather sprightly.-- =Arnie Rowland, Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
wrote in message news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com=...>I =have a table with a memo field that sometimes contains an account> number at =the same location. I need to find a record related to that> account =in another table. I can write and run a query for a record> =which works fine if the account number exists, but (obviously) get an> =error if it does not. I wrote the following query which I don't> =understand why it will not work.> > IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING> (tblRegister.fldMemo,6,9)) ==3D 1)> > BEGIN> SELECT fldChkNo, fldDate, fldName, =fldMemo, fldAmt, fldTransmitted,> fldStatus, fldLogDate, fldNote> =FROM tblRegister INNER JOIN tblNotes ON SUBSTRING> =(tblRegister.fldMemo,6,9) =3D tblNotes.fldMemberNo> END> > ELSE> > BEGIN> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,> fldStatus, fldLogDate> FROM =tblRegister> END> > Any help on this would be greatly =appreciated.> Thanks> Charles>

--=_NextPart_000_048F_01C6FC3D.3D5AC000--|||Arnie,
Thanks for the reply. My main issue is when I try to run this as an IF
statement I get an error "Incorrect syntax near the keyword 'BEGIN'."
I actually tried it without the 'Exists' after IF. If I run
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo with a where clause to
locate a record with a valid member number, it works fine. My issue is
running this when I don't have a valid member ID, which is what I am
trying to accomplish with the if statement.
It would have been better to include the member number in my register
table, but this was not in the original scope and I really don't want
to modify my table structure.
Charles
Arnie Rowland wrote:
> A couple of comments.
> First, isnumeric isn't foolproof. You may wish to review this source:
> isnumeric -What is wrong?
> http://www.aspfaq.com/show.asp?id=2390
>
> Second, it appears that you are attempting to compare a string to a number, e.g.,
> SUBSTRING(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> (I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
>
> You need to cast the first part as a numeric value in order make the comparison. And this will not use indexing, so expect performance to be poor.
> If you were to add another column to the Register table, set its value to be the substring of the Memo field (or better yet, have the application provide the number), and then index that column, performance would be rather sprightly.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
> <cbanks@.bjtsupport.com> wrote in message news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com...
> >I have a table with a memo field that sometimes contains an account
> > number at the same location. I need to find a record related to that
> > account in another table. I can write and run a query for a record
> > which works fine if the account number exists, but (obviously) get an
> > error if it does not. I wrote the following query which I don't
> > understand why it will not work.
> >
> > IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> > (tblRegister.fldMemo,6,9)) = 1)
> >
> > BEGIN
> > SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> > fldStatus, fldLogDate, fldNote
> > FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
> > (tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> > END
> >
> > ELSE
> >
> > BEGIN
> > SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> > fldStatus, fldLogDate
> > FROM tblRegister
> > END
> >
> > Any help on this would be greatly appreciated.
> > Thanks
> > Charles
> >
> --=_NextPart_000_048F_01C6FC3D.3D5AC000
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 5062
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &

>
>
>
>
>
> A couple of comments.
>
> First, isnumeric isn't foolproof. You may wish to
> review this source:
>
>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial; mso-no-proof: yes">isnumeric
> -What is wrong? style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes"> href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2390">http://www.aspfaq.com/show.asp?id=2390">http://www.aspfaq.com/show.asp?id=2390
>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes"> face=Arial size=2>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">Second,
> it appears that you are attempting to compare a string to a number, e.g.,
>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes"> face="Courier New">SUBSTRING(tblRegister.fldMemo,6,9) => tblNotes.fldMemberNo
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">(I'm assuming
> that tblNotes.fldMemberNo is a numeric datatype.)
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">You
> need to cast the first part as a numeric value in order make the comparison.
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">And
> this will not use indexing, so expect performance to be poor.
> If you were to add another column to the Register
> table, set its value to be the substring of the Memo field (or better yet, have
> the application provide the number), and then index that column, performance
> would be rather sprightly.-- Arnie
> Rowland, Ph.D.Westwood Consulting, Inc
>
> Most good judgment comes from experience. Most
> experience comes from bad judgment. - Anonymous
>
> You can't help someone get up a hill without
> getting a little closer to the top yourself.- H. Norman
> Schwarzkopf
>
>
>< href="http://links.10026.com/?link=mailto:cbanks@.bjtsupport.com"> size=2>cbanks@.bjtsupport.com> wrote in
> message href="http://links.10026.com/?link=news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com"> face=Arial
> size=2>news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com face=Arial size=2>...>I have a table
> with a memo field that sometimes contains an account> number at the same
> location. I need to find a record related to that> account in
> another table. I can write and run a query for a record> which
> works fine if the account number exists, but (obviously) get an> error if
> it does not. I wrote the following query which I don't> understand
> why it will not work.> > IF EXISTS (SELECT fldMemo FROM
> tblRegister WHERE ISNUMERIC(SUBSTRING> (tblRegister.fldMemo,6,9)) => 1)> > BEGIN> SELECT fldChkNo, fldDate, fldName, fldMemo,
> fldAmt, fldTransmitted,> fldStatus, fldLogDate, fldNote> FROM
> tblRegister INNER JOIN tblNotes ON SUBSTRING> (tblRegister.fldMemo,6,9) => tblNotes.fldMemberNo> END> > ELSE> >
> BEGIN> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt,
> fldTransmitted,> fldStatus, fldLogDate> FROM tblRegister>
> END> > Any help on this would be greatly appreciated.>
> Thanks> Charles>

> --=_NextPart_000_048F_01C6FC3D.3D5AC000--|||Charles,
exists is really for subqueries. However you could use:
IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1) > 0
....
or
SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1
if @.@.rowcount > 0
....
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||> exists is really for subqueries. However you could use:
> IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) = 1) > 0
I'm not sure why this is better than
IF EXISTS (SELECT 1 FROM tblRegister ...)
BEGIN
END
? The exists is potentially faster, and certainly not any slower, than a
select count.
The syntax problem he had was likely just a missing parenthesis...
A|||Thanks Aaron - my goof :(.
For some obscure reason I had assumed that it was returning a single row
(long night and needed more coffee!), but as I look more closely at what I
wrote I see issues with both solutions - Charles please disregard my earlier
post.
Regards,
Paul Ibison|||This is a multi-part message in MIME format.
--=_NextPart_000_057B_01C6FCC2.47296CF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Actually, not a missing parens, but including an equality [ =3D 1 ) > 0 =] which made the statement invalid. The statement 'should' work as:
IF EXISTS ( SELECT fldMemo FROM tblRegister WHERE isnumeric( substring( tblRegister.fldMemo, 6, 9 ))
)
BEGIN
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in =message news:uFkIfWP$GHA.4708@.TK2MSFTNGP05.phx.gbl...
>> exists is really for subqueries. However you could use:
>> IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING >> (tblRegister.fldMemo,6,9)) =3D 1) > 0
> > I'm not sure why this is better than
> > IF EXISTS (SELECT 1 FROM tblRegister ...)
> BEGIN
> END
> > ? The exists is potentially faster, and certainly not any slower, =than a > select count.
> > The syntax problem he had was likely just a missing parenthesis...
> > A > >
--=_NextPart_000_057B_01C6FCC2.47296CF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Actually, not a missing parens, but =including an equality [ =3D 1 ) > 0 ] which made the statement invalid. The =statement 'should' work as:
IF EXISTS
( SELECT =fldMemo =FROM tblRegister
=WHERE isnumeric( substring( tblRegister.fldMemo, 6, 9 ))
=)
=BEGIN-- Arnie =Rowland, Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" = wrote in message news:uFkIfWP$GHA.4708@.TK2MSFTNGP05.phx.gbl...> exists is really for subqueries. However you could use:> IF (SELECT =count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING > (tblRegister.fldMemo,6,9)) =3D 1) > 0> > I'm not sure =why this is better than> > IF EXISTS (SELECT 1 FROM tblRegister =...)> BEGIN> END> > ? The exists is potentially =faster, and certainly not any slower, than a > select count.> > =The syntax problem he had was likely just a missing parenthesis...> => A > >

--=_NextPart_000_057B_01C6FCC2.47296CF0--|||> Actually, not a missing parens, but including an equality [ = 1 ) > 0 ]
> which made the statement invalid.
Where? I didn't see his code, I just saw fragments of it.
A

If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
Regards,
Jamie
On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:

>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching the
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when it
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
Regards,
Jamie
"Hugo Kornelis" wrote:

> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:

>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching the
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially does
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does it
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that there
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.
|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:
[vbcol=seagreen]
>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
(snip)
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
--
Regards,
JamieOn Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:

>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching th
e
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when i
t
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
--
Regards,
Jamie
"Hugo Kornelis" wrote:

> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:

>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 o
r
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching t
he
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when
it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially doe
s
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does i
t
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that ther
e
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:

>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
(snip)[vbcol=seagreen]
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
--
Regards,
JamieOn Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching the
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when it
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
--
Regards,
Jamie
"Hugo Kornelis" wrote:
> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
> >Curious about the above:
> >
> >If there is a query that says
> >If not exists(select * from table)
> >versus one that says
> >If not exists(select * from table with(NoLOCK))
> >
> >Is the second statement more correct than the first (considering that there
> >may be hundreds of hits to that table in a very short space of time -
> >possible 100's per second)?
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:
>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching the
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially does
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does it
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that there
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:
>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
>> Curious about the above:
>> If there is a query that says
>> If not exists(select * from table)
>> versus one that says
>> If not exists(select * from table with(NoLOCK))
(snip)
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

IF NOT EXISTS (... - EXISTS TABLE : nested iteration. Table Scan.Forward scan.

Hi,

This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).

I'm looking at these new databases and I'm seeing code similar to this all over the place:

if not exists (select 1 from dbo.t1 where f1 = @.p1)
begin
select @.errno = @.errno | 1
end

There's a unique clustered in dex on t1.f1.

The execution plan shows this for this statement:

FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.

It's not using my index!!!!!

It seems to be the case with EXISTS statements. Can anybody confirm?

I also hinted to use the index but it still didn't use it.

If the existence check really doesn't use the index, what's a good code alternative to this check?

I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.

SET ROWCOUNT 1
SELECT @.cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @.p1
)
SET ROWCOUNT 0

Appreciate your help.

Do you have the code in a SP? I don't know if Sybase does parameter sniffing or not. Since the search argument is parameterized, the optimizer has to guess the value of @.p1 and use it for generating a plan. That will explain why it picks a table scan. And index hints will not help in this case. You could rewrite the code like below:

if (select min(1) from dbo.t1 where f1 = @.p1) is null

begin

select @.errno = @.errno|1

end

If Sybase has something like OPTIMIZE FOR clause then you can provide hint about the value of @.p1.

|||

Hi, thank you for the reply.

Yes, the code is in an sp. Not sure why the parameter needs to be evaluated for the plan to be generated correctly. I would think that it only needs the table and columns that are being queried. ?

I implemented your suggestion but it still didn't use the index. Once I added a hint, it did use the index.

I'd like to find a solution that doesn't have any aggregates in it, ie min(1). I'm looking for top performance; this query is executed a lot. I'll keep digging.

Thank you,

Etienne

|||

The optimizer analyzes the predicates and search arguments to decide which indexes to use. Indexes have statistics and if a particular search argument value is not known at the time of compilation then a guess will be made. For example, consider following:

Index on table t, column i

Statistics in the index will contain information on how the values of column "i" are distributed in the table

Given query like: "select i from t where i > 1"

Query optimizer can use the statistics to infer how many values of i are greater than 1. Based on this information, a plan to either use index seek or index scan or table scan will be used. The choice depends on the cardinality of the returned result set based on the search arguments.

Now, for the same query if you do: "select i from t where i > @.i"

If query optimizer cannot sniff the value of the parameter/variable @.i then it has to make a guess on what the value of @.i could be at run-time. This guess is often conservative because based on the value of @.i the query can either return zero rows, one row, many rows or entire table.

Both approaches has their pros and cons. Even with parameter sniffing the plan choice depends on the supplied value and that plan will not be great for other potential search arguments. So as you can see it is not just the tables, columns and indexes that matter.

Anyway, it looks like you have to ask this question in a Sybase forum. I don't know about the details of the query optimizer in Sybase SQL Server. You can try various rewrites but I think the crux of the problem is that the value of the search argument is guessed by the optimizer and then the query plan is generated.

IF NOT EXISTS

Hello,

I am trying to create a table if one with the same name does not exists. My code is:

Dim connectionStringAsString ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PensionDistrict4.mdf;Integrated Security=True;User Instance=True"Dim sqlConnectionAs SqlConnection =New SqlConnection(connectionString)Dim newTableAsString ="CREATE TABLE [" + titleString +"Comments" +"] (ID int NOT NULL PRIMARY KEY IDENTITY, Title varchar(100) NOT NULL, Name varchar(100) NOT NULL, Comment varchar(MAX) NOT NULL, Date datetime NOT NULL)"

sqlConnection.Open()

Dim sqlExistsAsString ="IF EXISTS (SELECT * FROM PensionDistrict4 WHERE name = '" + titleString +"Comments" +"')"Dim sqlCommandAsNew SqlCommand(newTable, sqlConnection)If sqlExists =TrueThen

sqlCommand.Cancel()

Else

sqlCommand.ExecuteNonQuery()

sqlConnection.Close()

EndIf

I keep getting a "Input String was incorrect format" for sqlExists? I am new to Transact-SQL statements, any help would be appreciated.

Thanks Matt

your sql Exists is just a string. so your code ofIf sqlExists =TrueThen doesnt make any sense. You need to execute it to find out if a table with the name exists. Alternatively its better to query sysobjects to find out if the table exists.

SELECT * FROM ssyobjects WHERE [Name] = '...' AND xtype = 'u'.

I'd recommend using a stored proc for this, so you can query the sysobjects to see if the table already exists and if it does not then create it else either drop and recreate ot exit appropriately.

If not CURSORS ?

I have a requirement to check a value which MUST be unique forever even if it is removed and readded.I have created a seperate table to maintain this value.

Without using a cursor how would I be able to append a duplicate base value (i.e. smith.j@.here.now) with the next sequential value (i.e. smith.j02@.here.now)

Any takers?

Oh ya, These values are not manually entered but populated through a DTS script. The existing values are repopulated from historic tables and new entries are added automatically. Initially the values would be populated without a number but a number needs to be generated on duplicates.Concatenate the datetime(getdate()) down to 1/1000 second. I am sure it will be unique. That's most of the spam mailers do when they create a fake ID to get around your blocking.|||Better yet, concatenate newid(). That will guarantee you a unique value all the time.|||Originally posted by joejcheng
Better yet, concatenate newid(). That will guarantee you a unique value all the time.

It also has to be sequential, not just unique|||You can use substring and max functions to achieve the same sequentially.|||You can't do this with sequential values if you insist on the stipulation that a record can be removed from the database and readded at another time. Even using a cursor to find out that smith.j02@.here.now, smith.j03@.here.now, and smith.j04@.here.now already exist, there is simply no way to know whether smith.j05@.here.now was not previously created and deleted. You have to store the used values permanently somewhere.|||...if you create a table with two columns:

EMailPrefix varchar(50),
EMailIncrement int

...to store both parts of the e-mail address, it is a simple matter to

select EMailPrefix + cast(Max(EMailIncrement) + 1 as NewEMail from UsedEmails where EMailPrefix = @.NewSubscriber group by EMailPrefix

...to get a new unused E-mail variation. Not sure if the syntax above is correct, but you get the picture...|||Unfortunately it is possible that more than one new entry can be made in the same import. (i.e. smith.j exists and two more smith.j are imported).

The list of historic values are being saved in a seperate table as described without the "EMailIncrement int" field. I had considered your exact solution but did not know how to increment 2 newly added values with different increments.

Originally posted by blindman
...if you create a table with two columns:

EMailPrefix varchar(50),
EMailIncrement int

...to store both parts of the e-mail address, it is a simple matter to

select EMailPrefix + cast(Max(EMailIncrement) + 1 as NewEMail from UsedEmails where EMailPrefix = @.NewSubscriber group by EMailPrefix

...to get a new unused E-mail variation. Not sure if the syntax above is correct, but you get the picture...

Unfortunate|||Use a cursor in combination with the table of historical values.

IF in a Select statement

Hi,

What would be the best way to do this, I have a table like so:


JobID Estimate1 Estimate2
 1 20.00 25.00
 2 52.12 47.31
 3 45.00 34.00
 4 60.00 65.00

and I need to create an SQL query that will grab the Sum of the highest estimates, ie (25.00 + 52.12 + 45.00 + 65.00)

I was hoping to do something similar to the SUMIF function in excel:
SELECT SUMIF(Estimate1>=Estimate2, Estimate1, Estimate2) FROM Estimates
but I haven't seen anything simular to that in the documetation.You should be able to use aCASE statement to accomplish this.

Terrisql

If I had any hair left I'd pull it out. How do I create a SSIS Package which creates a table th

Hello everyone,

I'm not at all comfortable with SSIS so please forgive me if I overload you all with information here:

I need to create a data table using SSIS which does not delete the previous days data. So far all the data tables we use to write reports in Visual Studio are constructed in SSIS as follows.

1 - Excecute SQL Task - DELETE FROM STOCK
2 - Data Flow Task
3 - Data Reader Source - SELECT * FROM ODBCDATASOURCE
4 - OLE DB Destination (Creates table STOCK)

The data tables which are created this way are stored in a data warehouse and scheduled to refresh once a day, which means that any data from yesterday is lost when the updates run. So, I tried to create a table which never has its previous days' data deleted by using just the last three steps above - and it worked great in Visual Studio, no problem at all. However, when I added this SSIS Package to the Update Job in SQL Server Management Studio, the job totally rejected the packed with the message: "The command line parameters are invalid. The step failed".

I thought I could work around this problem by asking the job step to excecute a simple SQL query to insert the data from table1 into table2 (and would thus negate the need for a SSIS Packege at all), but it threw me a curve ball with some message about not being able to use proxy accounts to run T-SQL Scripts.

If anyone knows how to create a SSIS package in which the data never expires please could you impart some wisdom my way. I only need to do this once for a specific report.

Please, when answering, bear in mind that I'm a simple fellow with little understanding of the inner workings of SQL Server and its various components, so please use short sentences and simple words.

Thanks in advance,

Chris

Strange. Looks like a problem in SQL Agent.

I suggest you first attempt to execute the package using dtexec. Once you have got that working we can talk about transferring the command-line to SQL Agent.

To make it easy to build the command-line for use with dtexec, Microsoft provide a tool called dtexecui that builds the command-line for you. Just type "dtexecui" at the command prompt.

If none of that makes sense, just holler.

-Jamie

|||

Jamie,

First of all, many thanks for your reply, your help is much appreciated.

I did what you suggested and tried to run the package via dtexec, but after a couple of validation phases it gave this error message: Error: The product level is insufficient for component "DataReader Source" (1).

Any suggestions on what I've not done correctly would be appreciated.

Many thanks,

Chris

|||

A common issue, normaly caused by not having a full SSIS installation on that machine.

Michael Entin's WebLog : Why do I get "product level is insufficient..." error when I run my SSIS package?
(http://blogs.msdn.com/michen/archive/2006/11/11/ssis-product-level-is-insufficient.aspx)

Product level is insufficient
(http://msdn2.microsoft.com/en-us/library/aa337371.aspx)

|||

Darren,

Thank you, I have just run the package via remote desktop to the server and it ran successfully first time.

How do I now get the package to run as part of the SQL Server Agent Job? The package step still fails whether initiated or scheduled and whether run locally or from the server.

Thanks,

Chris

|||

C.P.Hardcastle wrote:

Darren,
Thank you, I have just run the package via remote desktop to the server and it ran successfully first time.

How did you execute it? From BIDS or using dtexec?

C.P.Hardcastle wrote:

How do I now get the package to run as part of the SQL Server Agent Job? The package step still fails whether initiated or scheduled and whether run locally or from the server.
Thanks,
Chris

What is the error message?

-Jamie

|||

Jamie,

I can execute the package successfully from either BIDS (VS2005) on my local machine, or from dtexec if I remote desktop to the server and run it from there.

The package consistently fails when run in SQL Server Agent within SQL Server Management Studio - I can be running SSMS locally or on the server and it makes no difference. When I open the job and view the job history the step that runs this package fails with the error: The command line parameters are invalid. The step failed.

All other steps (three other steps) in the job complete successfully, this is the only step that fails (I've swapped the order in which they run and this one always fails while the other three always complete successfully). I have checked and double checked that the job and steps are set up properly and I even isolated this one package into a seperate Job all of its own and it still fails.

I have even gone as far as to delete the package, recreate the package, save copy to the server etc and I always get the same results when I try to run the SS Agent Job.

Any suggestions are very welcome.

Many thanks for your help so far and for your time and energy with this,

Chris

|||

C.P.Hardcastle wrote:

Jamie,
I can execute the package successfully from either BIDS (VS2005) on my local machine, or from dtexec if I remote desktop to the server and run it from there.
The package consistently fails when run in SQL Server Agent within SQL Server Management Studio - I can be running SSMS locally or on the server and it makes no difference. When I open the job and view the job history the step that runs this package fails with the error: The command line parameters are invalid. The step failed.
All other steps (three other steps) in the job complete successfully, this is the only step that fails (I've swapped the order in which they run and this one always fails while the other three always complete successfully). I have checked and double checked that the job and steps are set up properly and I even isolated this one package into a seperate Job all of its own and it still fails.
I have even gone as far as to delete the package, recreate the package, save copy to the server etc and I always get the same results when I try to run the SS Agent Job.
Any suggestions are very welcome.
Many thanks for your help so far and for your time and energy with this,
Chris

Did you change to use the command-line subsystem as per my earlier suggestion? If so, what is the command-line that you are using?

-Jamie

|||

Jamie,

I must apologise, I really appreciate your help and I really don't want to exhaust your patience, but please understand your dealing with ineptitude of idiotic proportions here.

Here's exactly what I did. At the command prompt I typed "dtexecui" as you suggested. This opened a new window called Execute Package Utility. I then entered the relevant information about which package to execute and it then went through a few validation phases and the Package execution Process Window returned a message that "component "OLE DB Destination" (13)" wrote 248 rows.

I once again apologise, but I'm not exactly sure what you mean by what is the command-line I'm using, but the only thing that I think you mean is this:

/DTS "/MSDB/C002_UNIDATA_STOCKPE_RD" /SERVER LYSRVBI /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

This command line differs slightly to the one used by the Agent Job Step, which is:

/DTS "/MSDB/C002_UNIDATA_STOCKPE_RD" /SERVER LYSRVBI /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

..so I copied the command line from the dtexec utility into the step for this package but it still fails.

I hope I have given you the right information here.

Thanks again,

Chris

|||

C.P.Hardcastle wrote:

Jamie,
I must apologise, I really appreciate your help and I really don't want to exhaust your patience, but please understand your dealing with ineptitude of idiotic proportions here.
Here's exactly what I did. At the command prompt I typed "dtexecui" as you suggested. This opened a new window called Execute Package Utility. I then entered the relevant information about which package to execute and it then went through a few validation phases and the Package execution Process Window returned a message that "component "OLE DB Destination" (13)" wrote 248 rows.
I once again apologise, but I'm not exactly sure what you mean by what is the command-line I'm using, but the only thing that I think you mean is this:
/DTS "/MSDB/C002_UNIDATA_STOCKPE_RD" /SERVER LYSRVBI /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
This command line differs slightly to the one used by the Agent Job Step, which is:
/DTS "/MSDB/C002_UNIDATA_STOCKPE_RD" /SERVER LYSRVBI /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
..so I copied the command line from the dtexec utility into the step for this package but it still fails.
I hope I have given you the right information here.
Thanks again,
Chris

Chris,

I think you do yourself a disservice. You've done (almost) exactly what I suggested so you're not as inept as you may think.

What you need to do first is take the command line from dtexecui, and run it using dtexec. See if that works or fails.

i.e., Something like this:

dtexec /DTS "/MSDB/C002_UNIDATA_STOCKPE_RD" /SERVER LYSRVBI /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

-Jamie

|||Jamie,

Thanks once again for your reply and for your encouraging comments.

I'm out of the office now until Monday so I won't have the chance to try your suggestion for a few days.

I will let you know how it goes as soon as I can.

Thanks again, your help is very much appreciated.

All the best,

Chris|||Jamie,

I couldn't resist a VPN to the server from home to try your suggestion.

Unfortunately, the job still fails.

Chris|||

C.P.Hardcastle wrote:
Jamie,
I couldn't resist a VPN to the server from home to try your suggestion.
Unfortunately, the job still fails.
Chris

When using dtexec or from SQL Agent? it does not make sense that it would work from dtexecui and not dtexec.

Please provide more info.

-Jamie

|||

Jamie Thomson wrote:
When using dtexec or from SQL Agent? it does not make sense that it would work from dtexecui and not dtexec.
Please provide more info.
-Jamie

Especially since dtexecui is a wrapper to dtexec.|||Jamie,

I'll give you as much information as I can.

As I said before, all our SSIS packages are createde in the same way with the same four steps: Execute SQL Task > Data Flow Task > Data Reader Source > OLE DB Destination.

With the exception that this problem package omits the Execute SQL Task stage it is identically set up to all our other packages, which work flawlessly.

After I created the package in Visual Studio I executed the package and everything turned green and all messages told me that the execution was a success. Sure enough if I viewed the table from server explorer all the data I expected to see was present.

Next I changed the protection level to Server Storage and saved a copy of the package to the server using the following:

Package Location: SSIS Package Store
Server: LYSRVBI
Windows Authentication
Package Path: /MSDB/C002_UNIDATA_STOCKPE_RD
Protectin Level: Rely on server storage and rules for access control

Next I created a new SSA job in SQL Server Management Studio and created a new step using the following:

Job Type: SSIS Package
Package Source: SSIS Package Store
Server: LYSRVBI
Use Windows Authentication
Package Path /MSDB/C002_UNIDATA_STOCKPE_RD

In the advanced section I made sure that close reporting success and close reporting failure were appropriately set depending on the execution outcome.

When I tried to run this job it consistently failed so I then followed your advice and ran the package via dtexecui after connecting to the server via remote desktop - and it worked. I copied the command line from dtexecui and edited the command line within the job to the same adding "dtexec" to the front of the command as you suggested- I left evertything else in the job/step exactly as above. When I started the job it immediately failed.

I don't think I've left anything out and I'm really baffled why it won't run as it's identical to countless other packages we run everyday with the only exception being that it omits the Execute SQL Task step and the "DELETE FROM <TABLENAME>" string within that task.

Let me know if I've made any fundamental errors please?

Thanks,

Chris

If exists..

Hi,
Is there any way to check whether a column is there in the table, if it is there i need to drop it through script.

i'm looking for the script, something like this..

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tbl_Product_Tbl_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tbl_Product] DROP CONSTRAINT FK_Tbl_Product_Tbl_Products
GO

In the same way i need to check for a column and drop it through script.
Any help would be greatly appreciated.
Thanks in advance.Look at the INFORMATION_SCHEMA.Columns View...

But I would advise against any auto mucking|||Hi Brett..
Here is what i did..
if exists (select * from information_schema.columns where table_name = 'tablename' and column_name = 'columnname')
alter table tablename drop column columnname
go
alter table tablename add columnname varchar(255) null
go

It worked..
Thanks.sql

If exists update, if it doesn't exist create

What I am looking for is a table that has two columns we'll say FName and Timestamp. What I want to do is to see if that name exists in my records. If it does I want to update it with a new timestamp. If it does not I want to create a new record in my table. This is going to be a check from vb 6.0 that is running continuously, so it will run the stored procedure to look for Redundancy in the FName column, if it finds it I want it to update the timestamp in that record to show the most up to date time.

Thank you in advance.First, check if the record exists, if not then add it to the table :

IF NOT EXISTS (SELECT (1) FROM 'table' WHERE FNAME = 'name')
Insert new record into table

Now update the table

UPDATE 'table'
SET TimeStamp = CURRENT_TIMESTAMP
WHERE fname = 'name'|||

Quote:

Originally Posted by SkinHead

First, check if the record exists, if not then add it to the table :

IF NOT EXISTS (SELECT (1) FROM 'table' WHERE FNAME = 'name')
Insert new record into table

Now update the table

UPDATE 'table'
SET TimeStamp = CURRENT_TIMESTAMP
WHERE fname = 'name'

Thanks for the reply. I guess I wrote my question wrong. I need this stored procedure to search a table for any names that it finds to be the same, not just a specific one, and replace the timestamp with a new timestamp.|||

Quote:

Originally Posted by JReneau35

You do have a specific name your searching for though right? This method will update all of the records matching your input parameter.

You come in with a name and you want it to search for that name in a table. If it doesnt find it, you want one new record created with a timestamp. If it does find it, no matter how many times it finds it, you want all of them updated.

Am I understanding you right?

IF exists UPDATE ELSE INSERT problem

Hi,

I have a 'Products' table (with: 'uid' and 'CatName' columns) and 'ProductCategory' table (with: 'uid', 'ProductID', 'CategoryID' columns).

I got stored procedure below to update or insert new row to 'ProductCategory' table whenever 'Products' table has been updated or new products has been added to it.

Update part works just fine but when new row has been added to 'Products' this storedProc dosn't insert it into 'ProductCategory' table, it does that only when 'ProductCategory' table is empty, I'm afraid it's because first column 'uid' in 'ProductCategory' table is an Identity column... I'm not sure how should I go about that problem. This is my stored procedure:


DECLARE @.CatNo INT, @.CatName varchar(10)
SET @.CatNo = 2
SET @.CatName = 'bracket'IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @.CatName )
 BEGIN
 UPDATE ProductCategory SET CategoryID = @.CatNo
 FROM Products WHERE Products.CatName = @.CatName and ProductCategory.ProductID = Products.uid
 END
ELSE
 BEGIN
 INSERT INTO ProductCategory ( ProductID, CategoryID)
 SELECT uid, @.CatNo FROM Products
 WHERE Products.CatName = @.CatName
 END
SET @.CatNo = 3
SET @.CatName = 'cable'
IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @.CatName )
 BEGIN
 UPDATE ProductCategory SET CategoryID = @.CatNo
 FROM Products WHERE Products.CatName = @.CatName and ProductCategory.ProductID = Products.uid
 END
ELSE
 BEGIN
 INSERT INTO ProductCategory ( ProductID, CategoryID)
 SELECT uid, @.CatNo FROM Products
 WHERE Products.CatName = @.CatName
 END
(... Goes for another 37 categories)

Thank you for help.

KoobaWhat kind of error message returned?
Check if there are unique index? or referencial integratiies ?

If you insert Identity column, make sure turn on IDENTITY_INSERT.|||Do you actually have that exact same code 39 times? That's not good. You can cut out all of that excess code with a few smart statements and a table with your category names.

Example:

Create a table:
Cat (CatNo, CatName)

Data:
(2, 'bracket')
(3, 'cable')
etc...

with your 39 cats. Then you can get rid of all those tedious repeated SQL statements with just 2 SQL statements:

update ProductCategory
set CategoryID = Cat.CatNo
from Products
join ProductCategory
on ProductCategory.ProductID = Products.uid
join Cat
on Products.CatName = Cat.CatName

insert into ProductCategory
(ProductID,
CategoryID)
select uid,
Cat.CatNo
from Products
join Cat
on Products.CatName = Cat.CatName
left join ProductCategory
on ProductCategory.ProductID = Products.uid
where ProductCategory.ProductID is null

This 2 statements will do exactly the same as your 78.|||Looking at your question again I'm pretty sure your DB is not normalised. From your vague description, I believe your tables should be:

ProductCategory (ProductID, CategoryID)
Category (CategoryID, CategoryName) -- CategoryID should be auto-increment identity
Product (ProductID, CategoryID) --ProductID should be auto-increment identity

I also understand from your question and your existing stored proc that you link all categories into all products, in which case the ProdutCategory table becomes redundant unless it stores another column or two.

If Exists Statement Problem...

Can someone give me a hand with this?

Ok I have a table called allstocks, with a primary key of ticker, I also have a todaysstocks table with a primary key of ticker.

Todaysstock table updates the allstocks table with this statement,

insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under]) select exchange,[todays date],ticker,[opened date], [closed date],[over/under] from todaysstocks

this works fine, unless the ticker already exists. How do I write the statement, that if the ticker already exists, then update the rest of the fields with the new info, or delete the row and recreate it?

ANy help would be appreciated. Thanksif exists (select 1 from allstock a inner join todaysstocks t on a.ticker=t.ticker)
delete a from allstock a inner join todaysstocks t on a.ticker=t.ticker
...|||insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under])
select exchange,[todays date],ticker,[opened date], [closed date],[over/under]
from todaysstocks a
where
ticker not in (select ticker from allstock b where b.ticker = a.ticker)
--|||This may be a hog...
insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under])
select exchange,[todays date],ticker,[opened date], [closed date],[over/under]
from todaysstocks a
where
ticker not in (select ticker from allstock b where b.ticker = a.ticker)
--
This one will use an index (if one exists) on ticker:

insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under])
select a.exchange,a.[todays date],a.ticker,a.[opened date], a.[closed date],a.[over/under]
from todaysstocks a left outer join allstocks b on a.ticker=b.ticker
where b.ticker is null

But this will not alter the data that is already in allstocks, rather it will insert 0 rows. The question was:

How do I write the statement, that if the ticker already exists, then update the rest of the fields with the new info, or delete the row and recreate it?|||You need to passes to accomplish this. The first statement updates existing rows, and the second statement adds new rows.

--Update existing records (you can eliminate fields that are part of the natural key):
update allstocks
set allstocks.exchange = todaysstocks.exchange,
allstocks.transdate = todaysstocks.[todays date],
allstocks.ticker = todaysstocks.ticker,
allstocks.[opened date] = todaysstocks.[opened date],
allstocks.[closed date] = todaysstocks.[closed date],
allstocks.[over/under] = todaysstocks.[over/under]
from allstocks
inner join todaysstocks on allstocks.keyfields = todaysstocks.keyfields

--Add new records:
insert into allstocks (exchange, transdate, ticker, [opened date], [closed date], [over/under])
select exchange, [todays date], ticker, [opened date], [closed date], [over/under]
from todaysstocks
left outer join allstocks on todaysstocks.keyfields = allstocks.keyfields
where allstocks.keyfields is null

Wednesday, March 28, 2012

If exists for temp table

What is the syntax to drop a temporary table if exists ? I want to drop a
temp table ##temptable if exist before I try to create it.
Thanks.create table ##temptable (id int)
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||I forgot the drop part, here is the whole thing
CREATE TABLE ##temptable (id int)
GO
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
DROP TABLE ##temptable
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
GO
CREATE TABLE ##temptable (id int)
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||Thanks............
"SQL" wrote:
> I forgot the drop part, here is the whole thing
> CREATE TABLE ##temptable (id int)
> GO
> IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
> BEGIN
> PRINT '##temptable exists!'
> DROP TABLE ##temptable
> END
> ELSE
> BEGIN
> PRINT '##temptable does not exist!'
> END
> GO
> CREATE TABLE ##temptable (id int)
> GO
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> DXC wrote:
> > What is the syntax to drop a temporary table if exists ? I want to drop a
> > temp table ##temptable if exist before I try to create it.
> >
> > Thanks.
>

If exists for temp table

What is the syntax to drop a temporary table if exists ? I want to drop a
temp table ##temptable if exist before I try to create it.
Thanks.create table ##temptable (id int)
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||I forgot the drop part, here is the whole thing
CREATE TABLE ##temptable (id int)
GO
IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
BEGIN
PRINT '##temptable exists!'
DROP TABLE ##temptable
END
ELSE
BEGIN
PRINT '##temptable does not exist!'
END
GO
CREATE TABLE ##temptable (id int)
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
DXC wrote:
> What is the syntax to drop a temporary table if exists ? I want to drop a
> temp table ##temptable if exist before I try to create it.
> Thanks.|||Thanks............
"SQL" wrote:

> I forgot the drop part, here is the whole thing
> CREATE TABLE ##temptable (id int)
> GO
> IF OBJECT_ID('tempdb..##temptable') IS NOT NULL
> BEGIN
> PRINT '##temptable exists!'
> DROP TABLE ##temptable
> END
> ELSE
> BEGIN
> PRINT '##temptable does not exist!'
> END
> GO
> CREATE TABLE ##temptable (id int)
> GO
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> DXC wrote:
>

IF EXISTS for drop table.

what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
Message posted via http://www.droptable.com
if object_id('tempdb..#temp_item') is not null
drop table #temp_item
AMB
"Grant H via droptable.com" wrote:

> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.droptable.com
>
|||A sample for the employee table in the Northwind database:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees]
GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Grant H via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:1b883ba01f1b4a5481ba1fbd07890119@.droptable.co m...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.droptable.com
|||Use Alejandro's example for temp tables, and Jens' example for permanent
tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Grant H via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:1b883ba01f1b4a5481ba1fbd07890119@.droptable.co m...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.droptable.com

IF EXISTS for drop table.

what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
Message posted via http://www.droptable.comif object_id('tempdb..#temp_item') is not null
drop table #temp_item
AMB
"Grant H via droptable.com" wrote:

> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.droptable.com
>|||A sample for the employee table in the Northwind database:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable
') = 1)
drop table [dbo].[Employees]
GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Grant H via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:1b883ba01f1b4a5481ba1fbd07890119@.SQ
droptable.com...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.droptable.com|||Use Alejandro's example for temp tables, and Jens' example for permanent
tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Grant H via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:1b883ba01f1b4a5481ba1fbd07890119@.SQ
droptable.com...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.droptable.com

IF EXISTS for drop table.

what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
--
Message posted via http://www.sqlmonster.comif object_id('tempdb..#temp_item') is not null
drop table #temp_item
AMB
"Grant H via SQLMonster.com" wrote:
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.sqlmonster.com
>|||A sample for the employee table in the Northwind database:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees]
GO
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Grant H via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:1b883ba01f1b4a5481ba1fbd07890119@.SQLMonster.com...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.sqlmonster.com|||Use Alejandro's example for temp tables, and Jens' example for permanent
tables.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Grant H via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:1b883ba01f1b4a5481ba1fbd07890119@.SQLMonster.com...
> what is the same statment like this "DROP TEMPORARY TABLE IF EXISTS
> temp_item" for MS SQL? Does MS SQL have "IF EXISTS" function?
> --
> Message posted via http://www.sqlmonster.comsql

If Exists Column ?

Hello

How do you check if a column exist ?

for a table :

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myTable]
GO

but I dont find it for a column

Thank youif exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )
drop table [dbo].[myTable]|||I want to drop the column not the table ?

thank you for helping|||Here we go.......
--alter table tablename drop column columnname

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )
alter table tablename drop column columnname
go|||This is one way....

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 varchar(25))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT 3, 'c'
GO

SELECT * FROM myTable99

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'myTable99' AND COLUMN_NAME = 'Col2')
ALTER TABLE myTable99 DROP COLUMN Col2

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Friday, March 30, 2012

If numeric query

If numeric query

If Not Exists?

If Not Exists?

If Not Exists?

IF NOT EXISTS (... - EXISTS TABLE : nested iteration. Table Scan.Forward scan.

IF NOT EXISTS

If not CURSORS ?

IF in a Select statement

If I had any hair left I'd pull it out. How do I create a SSIS Package which creates a table th

If exists..

If exists update, if it doesn't exist create

IF exists UPDATE ELSE INSERT problem

If Exists Statement Problem...

Wednesday, March 28, 2012

If exists for temp table

If exists for temp table

IF EXISTS for drop table.

IF EXISTS for drop table.

IF EXISTS for drop table.

If Exists Column ?

Identity column

Blog Archive

About Me