Error converting data type varchar to numeric. thread183-974713

[ddeegan]

thread183-974713

Error converting data type varchar to numeric.

Hello

If someone could help I would be grateful - this error is driving me nuts.

I am trying to insert data from 1 table into another. I can't fiind the conversion problem and have replaced the fields in the receiving table to receive 0 just so I can rule out the fields

insert USR_COMPANY_ADDITIONAL_CODES
select
ADDDATE as ADDDATE,
ADDOPER as ADDOPER,
0 as CONCURRENCY_ID, -- numeric
MASTER_CUSTOMER_ID as MASTER_CUSTOMER_ID,
MODDATE as MODDATE,
MODOPER as MODOPER,
0 as SEQUENCE_NO,
0 as SUB_CUSTOMER_ID,
1 as USR_CODE_type, -- numeric
Usr_code_id as USR_CODE_ID
from usr_company_type_of_operations

Can anyone see the problem? Does this error come up for another reason I don't know about?

Thanks
Dave

 

[vongrunt]

INSERT without explicit field list can do that.

Run this in Query Analyzer:

sp_help USR_COMPANY_ADDITIONAL_CODES

and copy&paste second (2nd) result here. Ditto for:

sp_help usr_company_type_of_operations

Then we will see what is wrong.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

run
select COLUMN_NAME,DATA_TYPE,NUMERIC_PRECISION from INFORMATION_SCHEMA.COLUMNS where tablename = 'USR_COMPANY_ADDITIONAL_CODES'
order by ORDINAL_POSITION

and

select COLUMN_NAME,DATA_TYPE,NUMERIC_PRECISION from INFORMATION_SCHEMA.COLUMNS where tablename = 'usr_company_type_of_operations'
order by ORDINAL_POSITION

and compare

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ddeegan]

Hello - sorry this is really hard to read. I will try to clean it up so that it will fit nicer. Here it is now in case your doing a text search and can use this.

Thanks
Dave

sp_help USR_COMPANY_ADDITIONAL_CODES
returns

sp_help USR_COMPANY_ADDITIONAL_CODES
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------------------------------------------------------
USR_COMPANY_ADDITIONAL_CODES dbo user table 2006-01-11 14:20:22.793



Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
MASTER_CUSTOMER_ID CUSTOMER_ID no 12 no no no SQL_Latin1_General_CP1_CI_AS
SUB_CUSTOMER_ID SUB_SEQUENCE no 5 3 0 no (n/a) (n/a) NULL
SEQUENCE_NO NUMERIC_ID no 9 12 0 no (n/a) (n/a) NULL
USR_CODE_TYPE numeric no 9 18 0 no (n/a) (n/a) NULL
ADDOPER varchar no 20 no no no SQL_Latin1_General_CP1_CI_AS
ADDDATE datetime no 8 no (n/a) (n/a) NULL
MODOPER varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
MODDATE datetime no 8 yes (n/a) (n/a) NULL
CONCURRENCY_ID numeric no 9 18 0 yes (n/a) (n/a) NULL
USR_CODE_ID varchar no 24 yes no no SQL_Latin1_General_CP1_CI_AS


Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL


RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.


Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY


index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USR_COMPANY_ADDITIONAL_CODES_PK clustered, unique, primary key located on PRIMARY MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID, SEQUENCE_NO


constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FOREIGN KEY USR_COMPANY_ADDITIONAL_CODES_FK No Action No Action Enabled Is_For_Replication MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID
REFERENCES TPRO.dbo.CUSTOMER (MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID)
PRIMARY KEY (clustered) USR_COMPANY_ADDITIONAL_CODES_PK (n/a) (n/a) (n/a) (n/a) MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID, SEQUENCE_NO


No foreign keys reference this table.
No views with schema binding reference this table.



sp_help usr_company_type_of_operations returns

Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------------------------------------------------------
USR_COMPANY_TYPE_OF_OPERATIONS dbo user table 2005-10-12 16:30:17.120



Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
MASTER_CUSTOMER_ID CUSTOMER_ID no 12 no no no SQL_Latin1_General_CP1_CI_AS
SUB_CUSTOMER_ID SUB_SEQUENCE no 5 3 0 no (n/a) (n/a) NULL
SEQUENCE_NO NUMERIC_ID no 9 12 0 no (n/a) (n/a) NULL
USR_CODE_ID varchar no 2 no no no SQL_Latin1_General_CP1_CI_AS
ADDOPER varchar no 20 no no no SQL_Latin1_General_CP1_CI_AS
ADDDATE datetime no 8 no (n/a) (n/a) NULL
MODOPER varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
MODDATE datetime no 8 yes (n/a) (n/a) NULL
CONCURRENCY_ID numeric no 5 9 0 yes (n/a) (n/a) NULL
USR_DESCRIPTTION varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS


Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- -------------------
No identity column defined. NULL NULL NULL


RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.


Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY


index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USR_COMPANY_TYPE_OF_OPERATIONS_PK clustered, unique, primary key located on PRIMARY MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID, SEQUENCE_NO


constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FOREIGN KEY USR_COMPANY_TYPE_OF_OPERATIONS_FK No Action No Action Enabled Is_For_Replication MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID
REFERENCES TPRO.dbo.CUSTOMER (MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID)
PRIMARY KEY (clustered) USR_COMPANY_TYPE_OF_OPERATIONS_PK (n/a) (n/a) (n/a) (n/a) MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID, SEQUENCE_NO


No foreign keys reference this table.
No views with schema binding reference this table.

 

[vongrunt]

Argh... only second (2nd) result set matters - the one with column definitions.

Btw. to format that stuff nicely, use TGML tags (see links below reply textarea box).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[ddeegan]

when I run

select COLUMN_NAME,DATA_TYPE,NUMERIC_PRECISION from INFORMATION_SCHEMA.COLUMNS where tablename = 'USR_COMPANY_ADDITIONAL_CODES'
order by ORDINAL_POSITION

I get the error

Invalid column name 'tablename'

sorry about the formatting - I think had that checked but thats the way it turned out

Thanks for your guys patience

 

[SQLDenis]

replace tablename with table_name and it should work

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ddeegan]

after running

select COLUMN_NAME,DATA_TYPE,NUMERIC_PRECISION from INFORMATION_SCHEMA.COLUMNS where tablename = 'USR_COMPANY_ADDITIONAL_CODES'
order by ORDINAL_POSITION

I get the following. I'm looking at it now but still figuring it out - any tips are appreciated - thanks


USR_COMPANY_ADDITIONAL_CODES

column_name data_type NUMERIC_PRECISION
-------------------- -------------------- -----------------
MASTER_CUSTOMER_ID varchar NULL
SUB_CUSTOMER_ID numeric 3
SEQUENCE_NO numeric 12
USR_CODE_TYPE numeric 18
ADDOPER varchar NULL
ADDDATE datetime 23
MODOPER varchar NULL
MODDATE datetime 23
CONCURRENCY_ID numeric 18
USR_CODE_ID varchar NULL

USR_COMPANY_TYPE_OF_OPERATIONS

column_name data_type NUMERIC_PRECISION
-------------------- -------------------- -----------------
MASTER_CUSTOMER_ID varchar NULL
SUB_CUSTOMER_ID numeric 3
SEQUENCE_NO numeric 12
USR_CODE_ID varchar NULL
ADDOPER varchar NULL
ADDDATE datetime 23
MODOPER varchar NULL
MODDATE datetime 23
CONCURRENCY_ID numeric 9
USR_DESCRIPTTION varchar NULL

 

[SQLDenis]

what about this?
USR_CODE_ID varchar NULL

it's varchar in table USR_COMPANY_TYPE_OF_OPERATIONS
but numeric in the other table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ddeegan]

I don't follow you - it looks liek its varchar for both

column_name data_type NUMERIC_PRECISION
-------------------- -------------------- -----------------
MASTER_CUSTOMER_ID varchar NULL
SUB_CUSTOMER_ID numeric 3
SEQUENCE_NO numeric 12
USR_CODE_TYPE numeric 18
ADDOPER varchar NULL
ADDDATE datetime 23
MODOPER varchar NULL
MODDATE datetime 23
CONCURRENCY_ID numeric 18
USR_CODE_ID varchar NULL

USR_COMPANY_TYPE_OF_OPERATIONS

column_name data_type NUMERIC_PRECISION
-------------------- -------------------- -----------------
MASTER_CUSTOMER_ID varchar NULL
SUB_CUSTOMER_ID numeric 3
SEQUENCE_NO numeric 12
USR_CODE_ID varchar NULL
ADDOPER varchar NULL
ADDDATE datetime 23
MODOPER varchar NULL
MODDATE datetime 23
CONCURRENCY_ID numeric 9
USR_DESCRIPTTION varchar NULL

and if I put 0 in for each numeric field, wouldn't that work?

 

[SQLDenis]

Look
USR_CODE_TYPE numeric 18
USR_CODE_ID varchar NULL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Lemme try to explain...

If column list for INSERT statement is not specified, all columns are assumed - starting from left. Which means your query is functionally the same as:

insert USR_COMPANY_ADDITIONAL_CODES ( MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID, SEQUENCE_NO, USR_CODE_TYPE, ADDOPER, ADDDATE, MODOPER, MODDATE, CONCURRENCY_ID, USR_CODE_ID )
select ...

Now let's take a look what would get inserted where if things worked:

MASTER_CUSTOMER_ID   varchar              ADDATE
SUB_CUSTOMER_ID      numeric              ADDOPER
SEQUENCE_NO          numeric              0
USR_CODE_TYPE        numeric              MASTER_CUSTOMER_ID
ADDOPER              varchar              MODDATE
ADDDATE              datetime             MODOPER
MODOPER              varchar              0
MODDATE              datetime             0
CONCURRENCY_ID       numeric              1
USR_CODE_ID          varchar              Usr_code_id

Now ya see da problem?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[ddeegan]

Yes, I do now, thank you.

It became obvious after your example, thanks for taking the time.

I need improvement on my syntax - I though if I said "as fieldname" it would put the value in that field name. I was wrong - I won't forget it now.



Thanks
Dave