Re: problem with query moving data from 1 dbase to another.
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 11/04/04
- Next message: Dallara: "Streaming out image field data"
- Previous message: Uri Dimant: "Re: Curious performance experiment"
- In reply to: Paul: "Re: problem with query moving data from 1 dbase to another."
- Next in thread: Paul: "Re: problem with query moving data from 1 dbase to another."
- Reply: Paul: "Re: problem with query moving data from 1 dbase to another."
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 4 Nov 2004 08:30:36 +0100
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. > > >> > > > > >> > > > > >> > > > > > > > >
- Next message: Dallara: "Streaming out image field data"
- Previous message: Uri Dimant: "Re: Curious performance experiment"
- In reply to: Paul: "Re: problem with query moving data from 1 dbase to another."
- Next in thread: Paul: "Re: problem with query moving data from 1 dbase to another."
- Reply: Paul: "Re: problem with query moving data from 1 dbase to another."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
