Identity column: number

Showing posts with label number. 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
CharlesThis is a multi-part message in MIME format.
--=_NextPart_000_048F_01C6FC3D.3D5AC000
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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=20
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
<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)) =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
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!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:
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

Monday, March 26, 2012

if date_made > or = to birth_date +30

I have a question...I have two tables, one with an ID number and year_made in it, and another one with a birth_date and death_date.

I'm trying to bring back ID numbers where the artist was 30 years of age or younger when the item was made. It requires a calulation to figure out the birth date + 30 years, and to return any id numbers where date_made falls into one of those 30 years between the birth_date and birth_date + 30.

However, I've not the foggiest on how to set it up in SQL. Or rather, I have too much fog, and not enough clear vision. ;)

Can someone help me?

Thanks ahead of time!There's datediff() in TSQL and JETSQL, not sure about the PL/SQL equivilent..|||Hey,

Thanks, that got me on the right track--until I realized the date fields in question were not true dates, because some are entered as "circa 1900" and the like. Gah.

Thank you again. :)|||Bad database design ... why is a date column created using the character datatype?

Teddy, why did you mention PL/SQL? Is it Oracle's Procedural Languange extension to SQL? If so, would it be more precise to talk about Oracle SQL (rather than PL/SQL) equivalent function?

And, if it was about Oracle, you could use ADD_MONTHS function (by multiplying 30 (years) with 12 (months)); something like

WHERE date_made <= ADD_MONTHS(birth_date, 30 * 12)

(If the artist was 30 years of age or younger, wouldn't you rather need <= instead of >= as you suggested in the thread subject?)

if condition within select query sql server 2000

Hi all,

I have to write a select query which need some logic to be implemented.

Query is like

select name,number,active,

if active ="true" then

select @.days=days from tbl_shdsheet

else

@.days=''

end

from tbl_emp

In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet

So how can i write queery for this.

You probably want to implement this using a Left Join:http://www.w3schools.com/sql/sql_join.asp

|||

Hi Thanks for replying.

My Problem has solved.I wrote a user defined function with the if else condition to be checked by sending the value to be checked as parameter to that function

|||

You can use user defined function to achieve this but you can easily do this by using case..when..else..end statement and joins. Below is your modified statement. You need to fill in the column names for mapping both the tables.

select name,number,active,
case
when te.active ='true'then ts.days
else''
end as Days
from tbl_emp teleftjoin tbl_shdsheeton te.[column to map] = ts.[column to map]

Friday, March 23, 2012

Identity/Seed Values

I have a question regarding using the Identity/Seed values. I have migrated
a number of tables form Access to SQL. In Access the identity/seed values
were called autonumbers.
The big problem with autonumbers in Access was that they could change at any
time on you, getting renumbered or sometimes even becoming negative values,
etc.
Now, in my application I have need for a unique number as the primary key AS
LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
formatted a special way, I do not need it to be sequential, etc. But I do
need it to stay the same after it has been entered.
So, my question is this: I know in Access i could not rely on the autonumber
to stay the same. Does the identity/seed value propoerties of a SQL table
have this same issue? Or can I plan on this value staying the same over
time?
Thanks
JoeIdentity values in SQL Server start at the seed value. By default it
increments by 1 and cannot be manually inserted or updated.
"Joe Williams" <joe@.anywhere.com> wrote in message
news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> I have a question regarding using the Identity/Seed values. I have
migrated
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at
any
> time on you, getting renumbered or sometimes even becoming negative
values,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the
autonumber
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>|||Hi
Once the Identity value is stored in a table, it reamains the same and does
not change.
Regards
Mike
"Joe Williams" wrote:

> I have a question regarding using the Identity/Seed values. I have migrate
d
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at a
ny
> time on you, getting renumbered or sometimes even becoming negative values
,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the autonumb
er
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>
>|||When you say that the autonumbers change in Access, do you mean that
previously inserted values change (with no intervention on the user or
programmer) or just that the values inserted do not always increment
sequentially?
"Joe Williams" <joe@.anywhere.com> wrote in message
news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> I have a question regarding using the Identity/Seed values. I have
migrated
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at
any
> time on you, getting renumbered or sometimes even becoming negative
values,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the
autonumber
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>|||> I have need for a unique number...
What you absolutely do need is a proper natural key in your data. IDENTITY
is not a substitute for this. Logically you should always be able to remove
IDENTITY and its referencing columns and replace it with another key in your
table or with another artificial key without changing its meaning. Don't
expose the IDENTITY key to users otherwise you build business processes on
something over which you don't have complete control in all cases - for
example, if you need to integrate data from multiple tables with IDENTITY
keys or in some replication scenarios.
Don't make assumptions about the sequence of IDENTITY values, its continuity
(there may be gaps) or even its uniqueness (except when it's defined with a
PK or unique constraint).
Having said all that, SQL Server doesn't change the IDENTITY value
automatically for any reason once it's assigned and you cannot change an
IDENTITY yourself except by deleting and then inserting a row (another reaso
n
why you shouldn't tie external meaning to an arbitrary IDENTITY value).
David Portas
SQL Server MVP
--|||you need see "SET IDENTITY_INSERT" en SQL libray
"JohnnyAppleseed" wrote:

> When you say that the autonumbers change in Access, do you mean that
> previously inserted values change (with no intervention on the user or
> programmer) or just that the values inserted do not always increment
> sequentially?
> "Joe Williams" <joe@.anywhere.com> wrote in message
> news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> migrated
> any
> values,
> AS
> autonumber
>
>

Wednesday, March 21, 2012

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
--
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>sql

Identity/Seed Values

Identity...I need to get the last (or highest number in Identity column)...

Ok,
I just need to know how to get the last record inserted by the highest
IDENTITY number. Even if the computer was rebooted and it was two
weeks ago. (Does not have to do with the session).
Any help is appreciated.
Thanks,
TrintSELECT IDENT_CURRENT('table_name') ;

--
David Portas
SQL Server MVP
--

"trint" <trinity.smith@.gmail.com> wrote in message
news:1127164340.720014.70160@.g14g2000cwa.googlegro ups.com...
> Ok,
> I just need to know how to get the last record inserted by the highest
> IDENTITY number. Even if the computer was rebooted and it was two
> weeks ago. (Does not have to do with the session).
> Any help is appreciated.
> Thanks,
> Trint|||Thank you for your quick response, David.
Trint

..Net programmer
trinity.smith@.gmail.com

*** Sent via Developersdex http://www.developersdex.com ***

Identity whithout identity column

Hi!
Please help!
Is there a function in SQL Server that returns "ROWID" from any table
(without identity column) or how to get that number?
Regards, MarkoThere is no internal rowid in SQL Server (such violated the relational model). If you say what you
would need it for, we can suggest alternatives.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Marko Kopaè" <marko.kopac@.mais.si> wrote in message
news:%23Gp%23PYngDHA.2484@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Please help!
> Is there a function in SQL Server that returns "ROWID" from any table
> (without identity column) or how to get that number?
> Regards, Marko
>

Monday, March 19, 2012

identity seed questions

My client has a need for the auto identity field to be 6 digits in length starting with the number 001000. They want the leading 0's preserved since this will be a casenumber. Even if I set the identity seed to 001000 it gets rid of the leading 0's. How can I get it to keep those?

Since the identity property works on numeric column leading zeroes doesn't matter. You can however use an identity column with seed 1000 and then use a computed column that formats the value in the desired format. See example below:

create table #t ( i int identity(1000, 1) not null check(i between 1000 and 999999), inum as right(replicate('0', 6) + cast(i as varchar), 6))

insert into #t default values

select * from #t

drop table #t

Monday, March 12, 2012

Identity or Sequence column in non-table SELECT ?

I'm looking for a way to specify a column in a SELECT statement
that is a sequence number related to the record number in the result set.
Ideally, just a number from 1 to N.
Example:
Select <?> as Sequence, column1, column2 from table1
The Identity function can do this, but only for SELECT INTO
a new table. I don't want to create a new table.
The NewID() function returns a new global ID, but those
are not sequential.
I was hoping there might be some special variable @.@.XXX
that represents the row number in the results, but could not find
anything like that in the SQL Server documentation.
Any ideas ?
Hi,
There is no concept of Rownum in sql server.
But you could write ur own query to get the serial number.
Use the below script as sample:-
create table item(item_code varchar(05))
go
insert into item values('a1')
insert into item values('a2')
insert into item values('a3')
insert into item values('a4')
go
SELECT (SELECT COUNT(i.item_code)
FROM item i
WHERE i.item_code >= o.item_code ) AS RowID,
item_code
FROM item o
ORDER BY RowID
Note:
This aproach is not recommended on a huge table. This query takes long time
and take more resource.
Thanks
Hari
SQL Server MVP
"GlennM" <GlennM@.discussions.microsoft.com> wrote in message
news:D4FDC49B-262B-49CB-9789-E9AE4570FEA9@.microsoft.com...
> I'm looking for a way to specify a column in a SELECT statement
> that is a sequence number related to the record number in the result set.
> Ideally, just a number from 1 to N.
> Example:
> Select <?> as Sequence, column1, column2 from table1
> The Identity function can do this, but only for SELECT INTO
> a new table. I don't want to create a new table.
> The NewID() function returns a new global ID, but those
> are not sequential.
> I was hoping there might be some special variable @.@.XXX
> that represents the row number in the results, but could not find
> anything like that in the SQL Server documentation.
> Any ideas ?
>
|||On Thu, 2 Jun 2005 12:44:02 -0700, GlennM wrote:

>I'm looking for a way to specify a column in a SELECT statement
>that is a sequence number related to the record number in the result set.
>Ideally, just a number from 1 to N.
>Example:
>Select <?> as Sequence, column1, column2 from table1
>The Identity function can do this, but only for SELECT INTO
>a new table. I don't want to create a new table.
>The NewID() function returns a new global ID, but those
>are not sequential.
>I was hoping there might be some special variable @.@.XXX
>that represents the row number in the results, but could not find
>anything like that in the SQL Server documentation.
>Any ideas ?
>
Hi Glenn,
http://www.aspfaq.com/show.asp?id=2427
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

IDENTITY limit

I defined a primary field using integer 4 bytes as IDENTITY.
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I defined a primary field using integer 4 bytes as IDENTITY.
>> What will be the limit of this number?
>> I am not sure the number is big enough in 10 years of time after my
>> application have been running.
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>

IDENTITY limit

I defined a primary field using integer 4 bytes as IDENTITY.
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>

Friday, March 9, 2012

Identity Increment

If delete all the records from a table that has an incremental identity. Is there a TSQL way of reset the first number on an insert back to be 1 again without going to the table taking it off then saving it the putting it back on again?TRUNCATE table clears all data and resets the Identity counter.|||Perfect! Thank you.

Identity Increment

Hello, I need some help writing a script to generate Identity keys. I cannot use the row number generator because I would like to start the identity at a package level variable. Is this possible?
Thank you in advance

I just find this using a web search engine:

http://www.sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx

I know that has been also discussed in this forum; I recommned you tu use the search functionality of the forum

|||http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

identity fields

how do I start an auto increment field at a certain number? other than 1CREATE TABLE test (
test_id int NOT NULL IDENTITY (100, 1)
)

will create a table where the test_id column starts at 100|||Open desired table in design mode
Set the Identity property to "Yes" for the desired field
Set Identity Seed property to the desired value

You can even set the increment value here.

?

Identity Field

Folks

I am inserting some values into a table with the following stmt

Insert into table(number,name) values ('12','name')

In the table I have one more identity column ID. I know that I cannot insert a value in that column and the value is automatically increased once I insert a record. After this insert statment, I need to get the value
of the ID (the most recent one) in the next select statement.

ie Select @.@.identity from table (any condition??)

How do I get the most recent ID value? Actually I m inserting the records in a loop and the ID is increased for every insert.

Thanks for the help,There is only one @.@.identity tracked for any given connection to MS-SQL (each spid). To retrieve its value, you just select it (no table needed). Something like:DECLARE @.id INT
INSERT INTO HHGtable (theAnswer) VALUES (43) // whatever
SELECT @.id = @.@.identity-PatP|||Pat

Thanks for the idea. BTW I have a question can I use

select Max(ID) from table

So that It gives only the maximum value and it would be same value
when the record is inserted? I am just asking your suggestion. Is that logically correct??

Thanks for the help,|||I would think so, but only as long as the tables next identity never is reset to a lower value (dbcc checkident is able to do so).

Wednesday, March 7, 2012

Identity Column Rollover

If I have an Identity column defined as a smallint
seed=1, increment=1
Does it automatically roll back to 1 when the number of records reaches
32,767. Assuming of course, the last record assigned identity 1 has been
removed.
I assume it does, but hey, I've assumed stuff like this before and gotten
burned.
JohnNo it does not automatically roll over. Instead you will receive an
overflow error and future inserts will fail. You could then reseed the
value using the DBCC CHECKIDENT command. A much better plan however, is
to create the column large enough that it won't overflow for the
forseeable future.
David Portas
SQL Server MVP
--|||No, you assume wrong. You'll get an error when you try to insert a new row.
"Arithmetic overflow error for data type smallint, value = 32768"
Change to Int if you believe that it may be a problem.
"John Manion" <JohnManion@.discussions.microsoft.com> wrote in message
news:785FF58A-2C96-4AAF-9E2A-30C918853DDE@.microsoft.com...
> If I have an Identity column defined as a smallint
> seed=1, increment=1
> Does it automatically roll back to 1 when the number of records reaches
> 32,767. Assuming of course, the last record assigned identity 1 has been
> removed.
> I assume it does, but hey, I've assumed stuff like this before and gotten
> burned.
> John|||No. SQL Server will try to increment the identity value and also give you an
error when trying to store the new value.
Example:
use northwind
go
create table t (
colA smallint not null identity
)
go
set identity_insert t on
go
insert into t (colA) values(32767)
go
set identity_insert t off
go
select * from t
go
insert into t default values
go
select * from t
go
drop table t
go
AMB
"John Manion" wrote:

> If I have an Identity column defined as a smallint
> seed=1, increment=1
> Does it automatically roll back to 1 when the number of records reaches
> 32,767. Assuming of course, the last record assigned identity 1 has been
> removed.
> I assume it does, but hey, I've assumed stuff like this before and gotten
> burned.
> John|||Not surprising.
Thanks,
John
"David Portas" wrote:

> No it does not automatically roll over. Instead you will receive an
> overflow error and future inserts will fail. You could then reseed the
> value using the DBCC CHECKIDENT command. A much better plan however, is
> to create the column large enough that it won't overflow for the
> forseeable future.
> --
> David Portas
> SQL Server MVP
> --
>

Friday, February 24, 2012

identity column

Hi,
I have a quesion. When I create a indentity colunm named
as [ID] in one new table, I think the number should be
consecutive, like "1, 2, 3, ...". However some users
reported that the number jumped, like "1, 2, 3, 5, 6 ...",
4 is skipped.
I asked one of my friends good at SQL, and he said that
the identity field isn't reliable. Sometimes cancel action
or roll back will cause the number skipped. Is that true?
What's the best way that I could get the consecutive
number? We really need it. Thanks.
CindyYour friend is correct - an insert that is rolled back will still consume
the next identity value. This is a FAQ - a newsgroup search (suggest
.programming rather than .server) will yield some alternatives. I think
there is a KB on the topic in MSDN.
"Cindy" <cindy@.atfreeweb.com> wrote in message
news:018801c3a56e$472fdad0$a601280a@.phx.gbl...
> Hi,
> I have a quesion. When I create a indentity colunm named
> as [ID] in one new table, I think the number should be
> consecutive, like "1, 2, 3, ...". However some users
> reported that the number jumped, like "1, 2, 3, 5, 6 ...",
> 4 is skipped.
> I asked one of my friends good at SQL, and he said that
> the identity field isn't reliable. Sometimes cancel action
> or roll back will cause the number skipped. Is that true?
> What's the best way that I could get the consecutive
> number? We really need it. Thanks.
> Cindy

Sunday, February 19, 2012

Identity

hi all,

is it somehow possible to reset the identity number for a table
before starting a bulk insert ?
What i want to do is to have a column start counting for a
given insert. If i do another bulk insert i want that column start by 1.
Right now i have set that column to identity (yes) .

thx in advanceWhat do you plan to do with the existing data?

Look up

DBCC CHECKIDENT

in BOL

Friday, March 30, 2012

If numeric query

Monday, March 26, 2012

if date_made > or = to birth_date +30

if condition within select query sql server 2000

Friday, March 23, 2012

Identity/Seed Values

Wednesday, March 21, 2012

Identy Number

Identy Number

Identity/Seed Values

Identity...I need to get the last (or highest number in Identity column)...

Identity whithout identity column

Monday, March 19, 2012

identity seed questions

Monday, March 12, 2012

Identity or Sequence column in non-table SELECT ?

IDENTITY limit

IDENTITY limit

Friday, March 9, 2012

Identity Increment

Identity Increment

identity fields

Identity Field

Wednesday, March 7, 2012

Identity Column Rollover

Friday, February 24, 2012

identity column

Sunday, February 19, 2012

Identity

Identity column

Blog Archive

About Me