Script to convert char to int please help....

I ran the following simple select statement :
select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted =
'20041123'
Then I got the following error message after several thousand records were
fetched.
Syntax error converting the varchar value 'ZAER "' to a column of data
type int.
What I like to do is convert that column which is char to int or select
everything with the value 'ZAER' and copy to another table and then convert.
I did the first and updated, copied the data back and ran the select and
still got the same error. Can someone help please? Thank you.
James
How do you plan to convert the value 'ZAER' to an INT? And why do you have
non-integer strings mixed in with integer amounts in your 'amount' column?
I would recommend that you clean up your data and re-define the column as
INT to avoid these types of issues in the future.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> I ran the following simple select statement :
> select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted =
> '20041123'
> Then I got the following error message after several thousand records were
> fetched.
> Syntax error converting the varchar value 'ZAER "' to a column of data
> type int.
> What I like to do is convert that column which is char to int or select
> everything with the value 'ZAER' and copy to another table and then
convert.
> I did the first and updated, copied the data back and ran the select and
> still got the same error. Can someone help please? Thank you.
> James
|||What I wanted to do was remove all the non-integer strings first with an
update to '0' before converting. Do you know what I can do?
James.
"Adam Machanic" wrote:

> How do you plan to convert the value 'ZAER' to an INT? And why do you have
> non-integer strings mixed in with integer amounts in your 'amount' column?
> I would recommend that you clean up your data and re-define the column as
> INT to avoid these types of issues in the future.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> convert.
>
>
|||Here's what I would do:
UPDATE tcase
SET amount= '0'
WHERE PATINDEX('%[^0-9]%', RTRIM(LTRIM(amount))) > 0
OR amount IS NULL
ALTER TABLE tcase
ALTER COLUMN amount INT NOT NULL
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:506B7C12-D36E-4F82-A2A1-5C545CD643CC@.microsoft.com...[vbcol=seagreen]
> What I wanted to do was remove all the non-integer strings first with an
> update to '0' before converting. Do you know what I can do?
> James.
>
> "Adam Machanic" wrote:
have[vbcol=seagreen]
column?[vbcol=seagreen]
as[vbcol=seagreen]
were[vbcol=seagreen]
data[vbcol=seagreen]
select[vbcol=seagreen]
and[vbcol=seagreen]

Script to convert char to int please help....

I ran the following simple select statement :
select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted = '20041123'
Then I got the following error message after several thousand records were
fetched.
Syntax error converting the varchar value 'ZAER "' to a column of data
type int.
What I like to do is convert that column which is char to int or select
everything with the value 'ZAER' and copy to another table and then convert.
I did the first and updated, copied the data back and ran the select and
still got the same error. Can someone help please? Thank you.
JamesHow do you plan to convert the value 'ZAER' to an INT? And why do you have
non-integer strings mixed in with integer amounts in your 'amount' column?
I would recommend that you clean up your data and re-define the column as
INT to avoid these types of issues in the future.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> I ran the following simple select statement :
> select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => '20041123'
> Then I got the following error message after several thousand records were
> fetched.
> Syntax error converting the varchar value 'ZAER "' to a column of data
> type int.
> What I like to do is convert that column which is char to int or select
> everything with the value 'ZAER' and copy to another table and then
convert.
> I did the first and updated, copied the data back and ran the select and
> still got the same error. Can someone help please? Thank you.
> James|||What I wanted to do was remove all the non-integer strings first with an
update to '0' before converting. Do you know what I can do?
James.
"Adam Machanic" wrote:
> How do you plan to convert the value 'ZAER' to an INT? And why do you have
> non-integer strings mixed in with integer amounts in your 'amount' column?
> I would recommend that you clean up your data and re-define the column as
> INT to avoid these types of issues in the future.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> > I ran the following simple select statement :
> >
> > select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => > '20041123'
> > Then I got the following error message after several thousand records were
> > fetched.
> >
> > Syntax error converting the varchar value 'ZAER "' to a column of data
> > type int.
> >
> > What I like to do is convert that column which is char to int or select
> > everything with the value 'ZAER' and copy to another table and then
> convert.
> > I did the first and updated, copied the data back and ran the select and
> > still got the same error. Can someone help please? Thank you.
> >
> > James
>
>|||Here's what I would do:
UPDATE tcase
SET amount= '0'
WHERE PATINDEX('%[^0-9]%', RTRIM(LTRIM(amount))) > 0
OR amount IS NULL
ALTER TABLE tcase
ALTER COLUMN amount INT NOT NULL
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:506B7C12-D36E-4F82-A2A1-5C545CD643CC@.microsoft.com...
> What I wanted to do was remove all the non-integer strings first with an
> update to '0' before converting. Do you know what I can do?
> James.
>
> "Adam Machanic" wrote:
> > How do you plan to convert the value 'ZAER' to an INT? And why do you
have
> > non-integer strings mixed in with integer amounts in your 'amount'
column?
> > I would recommend that you clean up your data and re-define the column
as
> > INT to avoid these types of issues in the future.
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> > news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> > > I ran the following simple select statement :
> > >
> > > select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => > > '20041123'
> > > Then I got the following error message after several thousand records
were
> > > fetched.
> > >
> > > Syntax error converting the varchar value 'ZAER "' to a column of
data
> > > type int.
> > >
> > > What I like to do is convert that column which is char to int or
select
> > > everything with the value 'ZAER' and copy to another table and then
> > convert.
> > > I did the first and updated, copied the data back and ran the select
and
> > > still got the same error. Can someone help please? Thank you.
> > >
> > > James
> >
> >
> >

SCOPE_IDENITY() - Incorrect Value (Returned)

There are two tables

Table_1:

ProductID (Identity Increment)

ProductDescription (nvarchar)

Table_2:

ProductID (int)

I have a sql statement and it's like this. This SQL is probably incorrect - but hopefully gives you an idea of what I am trying to do:

DECLARE@.getTheNewProductIDint
INSERT INTOTable_1 (ProductDescription)VALUES('SomeValue') ;SET@.getTheNewProductID =SELECT SCOPE_IDENTITY()
INSERT INTOTable_2 (ProductID)VALUES(@.getTheNewProductID)
  

What I need is: when inserting into Table_1 to get the Exact New Product Id from it that occurs from the identity increment - then insert that exact same product ID into table_2

The problem is it is returning incorrect values on the scope identity. Such as values from two transaction ago.

How do you do this?

I did try using @.@.identity which may have stuffed things up??

Thanks for your help

Hi

Try with this code.

DECLARE@.getTheNewProductIDint

INSERT INTOTable_1 (ProductDescription)VALUES('SomeValue') ;

SET@.getTheNewProductID = (SELECTProductID from Table_1 where ProductID= SCOPE_IDENTITY())

INSERT INTOTable_2 (ProductID)VALUES(@.getTheNewProductID)