-
Insert Statement error
Hi All,
I am trying to run an insert statement and getting an error.
Insert statement:
insert into conv_owner (name,street_num,
street_direction,street_name,street_type,street_un it,
address2,city,state,city_state,zip,wphone,inservic e,
db_name,table_name)
select "Facility's Owner",null,null,null,null,null,null,null,
null,null,null,"Owner's Telephone Number",inservice,
'BKFoodProtection.mdb','FP_Master'
from fp_master
Error message:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Any idea why? Thanks.
-
I think wphone is a numbering and you are inserting the chararcter value "Owner's Telephone Number"
-
Both fields are alphanumeric.
-
please post the schema of the table.
-
CREATE TABLE [CONV_OWNER] (
[OWNER_ID] [DM_RECORDID] NOT NULL ,
[NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STREET_NUM] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STREET_DIRECTION] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STREET_NAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STREET_TYPE] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STREET_UNIT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [DM_CITY] NULL ,
[STATE] [DM_STATE] NULL ,
[CITY_STATE] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [DM_ZIP] NULL ,
[WPHONE] [DM_PHONE] NULL ,
[INSERVICE] [numeric](1, 0) NULL ,
[DB_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TABLE_NAME] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
-
check all the datatypes of DM_* like DM_STATE
DM_ZIP
DM_PHONE
DM_RECORDID
make sure the the datatype of the column INSERVICE in the source is same as target table. If not use CONVERT or CAST
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|