problem with query moving data from 1 dbase to another.

I have a large dbase table and am moving the data from 1 table to another.
The problem is that I had to change the datatypes in the destination table so
am getting the failure
Syntax error converting the nvarchar to int.
INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
Select [Data_Item_Log_ID] from
[dbase2].dbo.dataitemlog
Data_Item_Log_ID (destination field) is type integer and the source field
(from another dbase on the same server) is type nvarchar 4. I have several
columns like this and the table has approximately 20k records.
The origin of the source database was a MySQL dbase which I moved to SQL2000
using a driver and access. Thanks.
--
Paul G
Software engineer.If I understand the problem connect, try:
INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
Select CAST([Data_Item_Log_ID] AS int) from
[dbase2].dbo.dataitemlog
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> I have a large dbase table and am moving the data from 1 table to another.
> The problem is that I had to change the datatypes in the destination table so
> am getting the failure
> Syntax error converting the nvarchar to int.
> INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> Select [Data_Item_Log_ID] from
> [dbase2].dbo.dataitemlog
> Data_Item_Log_ID (destination field) is type integer and the source field
> (from another dbase on the same server) is type nvarchar 4. I have several
> columns like this and the table has approximately 20k records.
> The origin of the source database was a MySQL dbase which I moved to SQL2000
> using a driver and access. Thanks.
> --
> Paul G
> Software engineer.|||Thanks will give this a try. Being a C programmer was thinking of using a
cast but did not know how to impliment this with SQL
"Tibor Karaszi" wrote:
> If I understand the problem connect, try:
> INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> Select CAST([Data_Item_Log_ID] AS int) from
> [dbase2].dbo.dataitemlog
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> > I have a large dbase table and am moving the data from 1 table to another.
> > The problem is that I had to change the datatypes in the destination table so
> > am getting the failure
> > Syntax error converting the nvarchar to int.
> >
> > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > Select [Data_Item_Log_ID] from
> > [dbase2].dbo.dataitemlog
> > Data_Item_Log_ID (destination field) is type integer and the source field
> > (from another dbase on the same server) is type nvarchar 4. I have several
> > columns like this and the table has approximately 20k records.
> > The origin of the source database was a MySQL dbase which I moved to SQL2000
> > using a driver and access. Thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||That's not the problem. You are only changing from an Implicit Cast to an
Explicit Cast.
The problem is that there is some data in the source that can not be
converted to INT.
Try this query:
SELECT *
FROM [dbase2].dbo.dataitemlog
WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
This should produce the records that are causing you grief. Be careful;
this could easily be an ANSI PADDING ON issue where there are extraneous
spaces--those will not be able to convert to numeric.
Sincerely,
Anthony Thomas
"Paul" wrote:
> Thanks will give this a try. Being a C programmer was thinking of using a
> cast but did not know how to impliment this with SQL
> "Tibor Karaszi" wrote:
> > If I understand the problem connect, try:
> >
> > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > Select CAST([Data_Item_Log_ID] AS int) from
> > [dbase2].dbo.dataitemlog
> >
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> > > I have a large dbase table and am moving the data from 1 table to another.
> > > The problem is that I had to change the datatypes in the destination table so
> > > am getting the failure
> > > Syntax error converting the nvarchar to int.
> > >
> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > > Select [Data_Item_Log_ID] from
> > > [dbase2].dbo.dataitemlog
> > > Data_Item_Log_ID (destination field) is type integer and the source field
> > > (from another dbase on the same server) is type nvarchar 4. I have several
> > > columns like this and the table has approximately 20k records.
> > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> > > using a driver and access. Thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >
> >|||Hi thanks for the response, tried the query and it did find half a dozen or
so records with Null or blank fields in the table, I think the fields that
are causing the problem.
"AnthonyThomas" wrote:
> That's not the problem. You are only changing from an Implicit Cast to an
> Explicit Cast.
> The problem is that there is some data in the source that can not be
> converted to INT.
> Try this query:
> SELECT *
> FROM [dbase2].dbo.dataitemlog
> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
> This should produce the records that are causing you grief. Be careful;
> this could easily be an ANSI PADDING ON issue where there are extraneous
> spaces--those will not be able to convert to numeric.
> Sincerely,
>
> Anthony Thomas
>
> "Paul" wrote:
> > Thanks will give this a try. Being a C programmer was thinking of using a
> > cast but did not know how to impliment this with SQL
> >
> > "Tibor Karaszi" wrote:
> >
> > > If I understand the problem connect, try:
> > >
> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > > Select CAST([Data_Item_Log_ID] AS int) from
> > > [dbase2].dbo.dataitemlog
> > >
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> > > > I have a large dbase table and am moving the data from 1 table to another.
> > > > The problem is that I had to change the datatypes in the destination table so
> > > > am getting the failure
> > > > Syntax error converting the nvarchar to int.
> > > >
> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > > > Select [Data_Item_Log_ID] from
> > > > [dbase2].dbo.dataitemlog
> > > > Data_Item_Log_ID (destination field) is type integer and the source field
> > > > (from another dbase on the same server) is type nvarchar 4. I have several
> > > > columns like this and the table has approximately 20k records.
> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> > > > using a driver and access. Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > >
> > >
> > >|||If the column allow NULL, then they shouldn't cause any problems. Also, be aware that SNUMERIC is
close to useless as it returns 1 is the value can be converted to *any* numerical type datatype,
including decimal, numeric etc. As below shows:
SELECT ISNUMERIC(1.2)
SELECT ISNUMERIC(1e3)
You might want to try instead:
SELECT PATINDEX( '%[^0-9]%', '1.2' )
SELECT PATINDEX( '%[^0-9]%', '1e3' )
SELECT PATINDEX( '%[^0-9]%', '133' )
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:596FDD37-72AF-4F49-A33E-3630492F5CD0@.microsoft.com...
> Hi thanks for the response, tried the query and it did find half a dozen or
> so records with Null or blank fields in the table, I think the fields that
> are causing the problem.
>
> "AnthonyThomas" wrote:
>> That's not the problem. You are only changing from an Implicit Cast to an
>> Explicit Cast.
>> The problem is that there is some data in the source that can not be
>> converted to INT.
>> Try this query:
>> SELECT *
>> FROM [dbase2].dbo.dataitemlog
>> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
>> This should produce the records that are causing you grief. Be careful;
>> this could easily be an ANSI PADDING ON issue where there are extraneous
>> spaces--those will not be able to convert to numeric.
>> Sincerely,
>>
>> Anthony Thomas
>>
>> "Paul" wrote:
>> > Thanks will give this a try. Being a C programmer was thinking of using a
>> > cast but did not know how to impliment this with SQL
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> > > If I understand the problem connect, try:
>> > >
>> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
>> > > Select CAST([Data_Item_Log_ID] AS int) from
>> > > [dbase2].dbo.dataitemlog
>> > >
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
>> > > > I have a large dbase table and am moving the data from 1 table to another.
>> > > > The problem is that I had to change the datatypes in the destination table so
>> > > > am getting the failure
>> > > > Syntax error converting the nvarchar to int.
>> > > >
>> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
>> > > > Select [Data_Item_Log_ID] from
>> > > > [dbase2].dbo.dataitemlog
>> > > > Data_Item_Log_ID (destination field) is type integer and the source field
>> > > > (from another dbase on the same server) is type nvarchar 4. I have several
>> > > > columns like this and the table has approximately 20k records.
>> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
>> > > > using a driver and access. Thanks.
>> > > > --
>> > > > Paul G
>> > > > Software engineer.
>> > >
>> > >
>> > >|||The column was set to not allow null so not sure how they got there, did a
MySQL to SQL2000 port using a driver and access. will give SELECT PATINDEX(
'%[^0-9]%', '1.2' ) as well as there are several more tables that need to be
filled. Also not too familiar with Enterprise manager but was wondering if
you know how to connect to an existing database, is this the same as
registering a database?
thanks.
"Tibor Karaszi" wrote:
> If the column allow NULL, then they shouldn't cause any problems. Also, be aware that SNUMERIC is
> close to useless as it returns 1 is the value can be converted to *any* numerical type datatype,
> including decimal, numeric etc. As below shows:
> SELECT ISNUMERIC(1.2)
> SELECT ISNUMERIC(1e3)
> You might want to try instead:
> SELECT PATINDEX( '%[^0-9]%', '1.2' )
> SELECT PATINDEX( '%[^0-9]%', '1e3' )
> SELECT PATINDEX( '%[^0-9]%', '133' )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:596FDD37-72AF-4F49-A33E-3630492F5CD0@.microsoft.com...
> > Hi thanks for the response, tried the query and it did find half a dozen or
> > so records with Null or blank fields in the table, I think the fields that
> > are causing the problem.
> >
> >
> > "AnthonyThomas" wrote:
> >
> >> That's not the problem. You are only changing from an Implicit Cast to an
> >> Explicit Cast.
> >>
> >> The problem is that there is some data in the source that can not be
> >> converted to INT.
> >>
> >> Try this query:
> >>
> >> SELECT *
> >> FROM [dbase2].dbo.dataitemlog
> >> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
> >>
> >> This should produce the records that are causing you grief. Be careful;
> >> this could easily be an ANSI PADDING ON issue where there are extraneous
> >> spaces--those will not be able to convert to numeric.
> >>
> >> Sincerely,
> >>
> >>
> >> Anthony Thomas
> >>
> >>
> >>
> >> "Paul" wrote:
> >>
> >> > Thanks will give this a try. Being a C programmer was thinking of using a
> >> > cast but did not know how to impliment this with SQL
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> > > If I understand the problem connect, try:
> >> > >
> >> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> >> > > Select CAST([Data_Item_Log_ID] AS int) from
> >> > > [dbase2].dbo.dataitemlog
> >> > >
> >> > >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > > http://www.karaszi.com/sqlserver/default.asp
> >> > > http://www.solidqualitylearning.com/
> >> > >
> >> > >
> >> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> >> > > > I have a large dbase table and am moving the data from 1 table to another.
> >> > > > The problem is that I had to change the datatypes in the destination table so
> >> > > > am getting the failure
> >> > > > Syntax error converting the nvarchar to int.
> >> > > >
> >> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> >> > > > Select [Data_Item_Log_ID] from
> >> > > > [dbase2].dbo.dataitemlog
> >> > > > Data_Item_Log_ID (destination field) is type integer and the source field
> >> > > > (from another dbase on the same server) is type nvarchar 4. I have several
> >> > > > columns like this and the table has approximately 20k records.
> >> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> >> > > > using a driver and access. Thanks.
> >> > > > --
> >> > > > Paul G
> >> > > > Software engineer.
> >> > >
> >> > >
> >> > >
>
>|||You register a SQL Server instance, where such an instance can have several databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4777949B-682B-49A3-B17D-C7C2337B4F4C@.microsoft.com...
> The column was set to not allow null so not sure how they got there, did a
> MySQL to SQL2000 port using a driver and access. will give SELECT PATINDEX(
> '%[^0-9]%', '1.2' ) as well as there are several more tables that need to be
> filled. Also not too familiar with Enterprise manager but was wondering if
> you know how to connect to an existing database, is this the same as
> registering a database?
> thanks.
>
> "Tibor Karaszi" wrote:
> > If the column allow NULL, then they shouldn't cause any problems. Also, be aware that SNUMERIC
is
> > close to useless as it returns 1 is the value can be converted to *any* numerical type datatype,
> > including decimal, numeric etc. As below shows:
> >
> > SELECT ISNUMERIC(1.2)
> > SELECT ISNUMERIC(1e3)
> >
> > You might want to try instead:
> >
> > SELECT PATINDEX( '%[^0-9]%', '1.2' )
> > SELECT PATINDEX( '%[^0-9]%', '1e3' )
> > SELECT PATINDEX( '%[^0-9]%', '133' )
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:596FDD37-72AF-4F49-A33E-3630492F5CD0@.microsoft.com...
> > > Hi thanks for the response, tried the query and it did find half a dozen or
> > > so records with Null or blank fields in the table, I think the fields that
> > > are causing the problem.
> > >
> > >
> > > "AnthonyThomas" wrote:
> > >
> > >> That's not the problem. You are only changing from an Implicit Cast to an
> > >> Explicit Cast.
> > >>
> > >> The problem is that there is some data in the source that can not be
> > >> converted to INT.
> > >>
> > >> Try this query:
> > >>
> > >> SELECT *
> > >> FROM [dbase2].dbo.dataitemlog
> > >> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
> > >>
> > >> This should produce the records that are causing you grief. Be careful;
> > >> this could easily be an ANSI PADDING ON issue where there are extraneous
> > >> spaces--those will not be able to convert to numeric.
> > >>
> > >> Sincerely,
> > >>
> > >>
> > >> Anthony Thomas
> > >>
> > >>
> > >>
> > >> "Paul" wrote:
> > >>
> > >> > Thanks will give this a try. Being a C programmer was thinking of using a
> > >> > cast but did not know how to impliment this with SQL
> > >> >
> > >> > "Tibor Karaszi" wrote:
> > >> >
> > >> > > If I understand the problem connect, try:
> > >> > >
> > >> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > >> > > Select CAST([Data_Item_Log_ID] AS int) from
> > >> > > [dbase2].dbo.dataitemlog
> > >> > >
> > >> > >
> > >> > > --
> > >> > > Tibor Karaszi, SQL Server MVP
> > >> > > http://www.karaszi.com/sqlserver/default.asp
> > >> > > http://www.solidqualitylearning.com/
> > >> > >
> > >> > >
> > >> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > >> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> > >> > > > I have a large dbase table and am moving the data from 1 table to another.
> > >> > > > The problem is that I had to change the datatypes in the destination table so
> > >> > > > am getting the failure
> > >> > > > Syntax error converting the nvarchar to int.
> > >> > > >
> > >> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > >> > > > Select [Data_Item_Log_ID] from
> > >> > > > [dbase2].dbo.dataitemlog
> > >> > > > Data_Item_Log_ID (destination field) is type integer and the source field
> > >> > > > (from another dbase on the same server) is type nvarchar 4. I have several
> > >> > > > columns like this and the table has approximately 20k records.
> > >> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> > >> > > > using a driver and access. Thanks.
> > >> > > > --
> > >> > > > Paul G
> > >> > > > Software engineer.
> > >> > >
> > >> > >
> > >> > >
> >
> >
> >|||ok thanks for the information.
"Tibor Karaszi" wrote:
> You register a SQL Server instance, where such an instance can have several databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4777949B-682B-49A3-B17D-C7C2337B4F4C@.microsoft.com...
> > The column was set to not allow null so not sure how they got there, did a
> > MySQL to SQL2000 port using a driver and access. will give SELECT PATINDEX(
> > '%[^0-9]%', '1.2' ) as well as there are several more tables that need to be
> > filled. Also not too familiar with Enterprise manager but was wondering if
> > you know how to connect to an existing database, is this the same as
> > registering a database?
> > thanks.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > If the column allow NULL, then they shouldn't cause any problems. Also, be aware that SNUMERIC
> is
> > > close to useless as it returns 1 is the value can be converted to *any* numerical type datatype,
> > > including decimal, numeric etc. As below shows:
> > >
> > > SELECT ISNUMERIC(1.2)
> > > SELECT ISNUMERIC(1e3)
> > >
> > > You might want to try instead:
> > >
> > > SELECT PATINDEX( '%[^0-9]%', '1.2' )
> > > SELECT PATINDEX( '%[^0-9]%', '1e3' )
> > > SELECT PATINDEX( '%[^0-9]%', '133' )
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:596FDD37-72AF-4F49-A33E-3630492F5CD0@.microsoft.com...
> > > > Hi thanks for the response, tried the query and it did find half a dozen or
> > > > so records with Null or blank fields in the table, I think the fields that
> > > > are causing the problem.
> > > >
> > > >
> > > > "AnthonyThomas" wrote:
> > > >
> > > >> That's not the problem. You are only changing from an Implicit Cast to an
> > > >> Explicit Cast.
> > > >>
> > > >> The problem is that there is some data in the source that can not be
> > > >> converted to INT.
> > > >>
> > > >> Try this query:
> > > >>
> > > >> SELECT *
> > > >> FROM [dbase2].dbo.dataitemlog
> > > >> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
> > > >>
> > > >> This should produce the records that are causing you grief. Be careful;
> > > >> this could easily be an ANSI PADDING ON issue where there are extraneous
> > > >> spaces--those will not be able to convert to numeric.
> > > >>
> > > >> Sincerely,
> > > >>
> > > >>
> > > >> Anthony Thomas
> > > >>
> > > >>
> > > >>
> > > >> "Paul" wrote:
> > > >>
> > > >> > Thanks will give this a try. Being a C programmer was thinking of using a
> > > >> > cast but did not know how to impliment this with SQL
> > > >> >
> > > >> > "Tibor Karaszi" wrote:
> > > >> >
> > > >> > > If I understand the problem connect, try:
> > > >> > >
> > > >> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > > >> > > Select CAST([Data_Item_Log_ID] AS int) from
> > > >> > > [dbase2].dbo.dataitemlog
> > > >> > >
> > > >> > >
> > > >> > > --
> > > >> > > Tibor Karaszi, SQL Server MVP
> > > >> > > http://www.karaszi.com/sqlserver/default.asp
> > > >> > > http://www.solidqualitylearning.com/
> > > >> > >
> > > >> > >
> > > >> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > >> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> > > >> > > > I have a large dbase table and am moving the data from 1 table to another.
> > > >> > > > The problem is that I had to change the datatypes in the destination table so
> > > >> > > > am getting the failure
> > > >> > > > Syntax error converting the nvarchar to int.
> > > >> > > >
> > > >> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > > >> > > > Select [Data_Item_Log_ID] from
> > > >> > > > [dbase2].dbo.dataitemlog
> > > >> > > > Data_Item_Log_ID (destination field) is type integer and the source field
> > > >> > > > (from another dbase on the same server) is type nvarchar 4. I have several
> > > >> > > > columns like this and the table has approximately 20k records.
> > > >> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> > > >> > > > using a driver and access. Thanks.
> > > >> > > > --
> > > >> > > > Paul G
> > > >> > > > Software engineer.
> > > >> > >
> > > >> > >
> > > >> > >
> > >
> > >
> > >
>
>|||Tibor:
Thanks for that. The thought did occur to me but I suspected the issue with
NULLS. This is the reasoning: SQL Server can do a successfull implicit cast
from numeric and float data types to integer by truncating the decimal
portion. However, that will not fail the original query, just a logical
failure.
If the columns are marked NOT NULL, then they are not, regardless of what
system the data came from. Your problem is that they are in all likelihood
spaces.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> If the column allow NULL, then they shouldn't cause any problems. Also, be aware that SNUMERIC is
> close to useless as it returns 1 is the value can be converted to *any* numerical type datatype,
> including decimal, numeric etc. As below shows:
> SELECT ISNUMERIC(1.2)
> SELECT ISNUMERIC(1e3)
> You might want to try instead:
> SELECT PATINDEX( '%[^0-9]%', '1.2' )
> SELECT PATINDEX( '%[^0-9]%', '1e3' )
> SELECT PATINDEX( '%[^0-9]%', '133' )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:596FDD37-72AF-4F49-A33E-3630492F5CD0@.microsoft.com...
> > Hi thanks for the response, tried the query and it did find half a dozen or
> > so records with Null or blank fields in the table, I think the fields that
> > are causing the problem.
> >
> >
> > "AnthonyThomas" wrote:
> >
> >> That's not the problem. You are only changing from an Implicit Cast to an
> >> Explicit Cast.
> >>
> >> The problem is that there is some data in the source that can not be
> >> converted to INT.
> >>
> >> Try this query:
> >>
> >> SELECT *
> >> FROM [dbase2].dbo.dataitemlog
> >> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
> >>
> >> This should produce the records that are causing you grief. Be careful;
> >> this could easily be an ANSI PADDING ON issue where there are extraneous
> >> spaces--those will not be able to convert to numeric.
> >>
> >> Sincerely,
> >>
> >>
> >> Anthony Thomas
> >>
> >>
> >>
> >> "Paul" wrote:
> >>
> >> > Thanks will give this a try. Being a C programmer was thinking of using a
> >> > cast but did not know how to impliment this with SQL
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> > > If I understand the problem connect, try:
> >> > >
> >> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> >> > > Select CAST([Data_Item_Log_ID] AS int) from
> >> > > [dbase2].dbo.dataitemlog
> >> > >
> >> > >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > > http://www.karaszi.com/sqlserver/default.asp
> >> > > http://www.solidqualitylearning.com/
> >> > >
> >> > >
> >> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> >> > > > I have a large dbase table and am moving the data from 1 table to another.
> >> > > > The problem is that I had to change the datatypes in the destination table so
> >> > > > am getting the failure
> >> > > > Syntax error converting the nvarchar to int.
> >> > > >
> >> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> >> > > > Select [Data_Item_Log_ID] from
> >> > > > [dbase2].dbo.dataitemlog
> >> > > > Data_Item_Log_ID (destination field) is type integer and the source field
> >> > > > (from another dbase on the same server) is type nvarchar 4. I have several
> >> > > > columns like this and the table has approximately 20k records.
> >> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> >> > > > using a driver and access. Thanks.
> >> > > > --
> >> > > > Paul G
> >> > > > Software engineer.
> >> > >
> >> > >
> >> > >
>
>|||> This is the reasoning: SQL Server can do a successfull implicit cast
> from numeric and float data types to integer by truncating the decimal
> portion. However, that will not fail the original query, just a logical
> failure.
Good point, Anthony.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:16AEFBBD-9FFB-4351-B0B3-112202AC3C1E@.microsoft.com...
> Tibor:
> Thanks for that. The thought did occur to me but I suspected the issue with
> NULLS. This is the reasoning: SQL Server can do a successfull implicit cast
> from numeric and float data types to integer by truncating the decimal
> portion. However, that will not fail the original query, just a logical
> failure.
> If the columns are marked NOT NULL, then they are not, regardless of what
> system the data came from. Your problem is that they are in all likelihood
> spaces.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
> > If the column allow NULL, then they shouldn't cause any problems. Also, be aware that SNUMERIC
is
> > close to useless as it returns 1 is the value can be converted to *any* numerical type datatype,
> > including decimal, numeric etc. As below shows:
> >
> > SELECT ISNUMERIC(1.2)
> > SELECT ISNUMERIC(1e3)
> >
> > You might want to try instead:
> >
> > SELECT PATINDEX( '%[^0-9]%', '1.2' )
> > SELECT PATINDEX( '%[^0-9]%', '1e3' )
> > SELECT PATINDEX( '%[^0-9]%', '133' )
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:596FDD37-72AF-4F49-A33E-3630492F5CD0@.microsoft.com...
> > > Hi thanks for the response, tried the query and it did find half a dozen or
> > > so records with Null or blank fields in the table, I think the fields that
> > > are causing the problem.
> > >
> > >
> > > "AnthonyThomas" wrote:
> > >
> > >> That's not the problem. You are only changing from an Implicit Cast to an
> > >> Explicit Cast.
> > >>
> > >> The problem is that there is some data in the source that can not be
> > >> converted to INT.
> > >>
> > >> Try this query:
> > >>
> > >> SELECT *
> > >> FROM [dbase2].dbo.dataitemlog
> > >> WHERE ISNUMERIC([Data_Item_Log_ID]) = 0
> > >>
> > >> This should produce the records that are causing you grief. Be careful;
> > >> this could easily be an ANSI PADDING ON issue where there are extraneous
> > >> spaces--those will not be able to convert to numeric.
> > >>
> > >> Sincerely,
> > >>
> > >>
> > >> Anthony Thomas
> > >>
> > >>
> > >>
> > >> "Paul" wrote:
> > >>
> > >> > Thanks will give this a try. Being a C programmer was thinking of using a
> > >> > cast but did not know how to impliment this with SQL
> > >> >
> > >> > "Tibor Karaszi" wrote:
> > >> >
> > >> > > If I understand the problem connect, try:
> > >> > >
> > >> > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > >> > > Select CAST([Data_Item_Log_ID] AS int) from
> > >> > > [dbase2].dbo.dataitemlog
> > >> > >
> > >> > >
> > >> > > --
> > >> > > Tibor Karaszi, SQL Server MVP
> > >> > > http://www.karaszi.com/sqlserver/default.asp
> > >> > > http://www.solidqualitylearning.com/
> > >> > >
> > >> > >
> > >> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > >> > > news:8C6EFEC1-7B3F-48AB-B0BF-82694B3D7596@.microsoft.com...
> > >> > > > I have a large dbase table and am moving the data from 1 table to another.
> > >> > > > The problem is that I had to change the datatypes in the destination table so
> > >> > > > am getting the failure
> > >> > > > Syntax error converting the nvarchar to int.
> > >> > > >
> > >> > > > INSERT INTO [dbase1].dbo.Table1 [Data_Item_Log_ID]
> > >> > > > Select [Data_Item_Log_ID] from
> > >> > > > [dbase2].dbo.dataitemlog
> > >> > > > Data_Item_Log_ID (destination field) is type integer and the source field
> > >> > > > (from another dbase on the same server) is type nvarchar 4. I have several
> > >> > > > columns like this and the table has approximately 20k records.
> > >> > > > The origin of the source database was a MySQL dbase which I moved to SQL2000
> > >> > > > using a driver and access. Thanks.
> > >> > > > --
> > >> > > > Paul G
> > >> > > > Software engineer.
> > >> > >
> > >> > >
> > >> > >
> >
> >
> >