CODEWITHSUNDEEP
sql-server
Divola
Divola

Reputation: 59

Error in inserting values

I am writing a piece of code to populate a table and its not working, it keep sending error message like Error converting data type varchar to numeric. Here is the table creation on MSSQL and here is the insert statement 

create table employee_pay_tbl
( 
    EMP_ID          varchar(9)   NOT NULL  primary key, 
    position        VARCHAR(15)  not null ,
    date_hire        date, 
    pay_rate         decimal (4,2),
    date_last_raise  date, 
    salary           decimal (8,2),    
    bonus            decimal (6,2),
    constraint  EMP_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL(EMP_ID) 
);

This is the insert statement that's not working 

INSERT INTO employee_pay_tbl 
VALUES ('442346889','TEAM LEADER','2000-06-17','14.75','2009-06-01','NULL','NULL');

I tried to identify the offending value using this piece of code 

SELECT * FROM employee_pay_tbl WHERE isnumeric(bonus) = 0 AND bonus IS NOT null;

I think the Date value is causing the error. How can I fix this?

Upvotes: 2

Views: 107

Answers (3)

Nagaraj S
Nagaraj S

Reputation: 13484

If you are inserting NULL try this

For decimal value you can try without single quotes

INSERT INTO employee_pay_tbl
VALUES ('442346889',
        'TEAM LEADER',
        '2000-06-17',
         14.75,
        '2009-06-01',
        NULL,
        NULL);

Working Fiddle

A better approach you can specify columnname like

insert into tablename (col1,col2,col3 ) values (value1,value2.value3);

Upvotes: 0

CloudyMarble
CloudyMarble

Reputation: 37576

You are inserting varchar all over where it expects decimal, delete the quotes aroud the values for decimal columns, try:

INSERT INTO employee_pay_tbl VALUES ('442346889', 'TEAM LEADER', '2000-06-17', 14.75, '2009-06-01', NULL, NULL);

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166546

Change the insert to use NULL instead of 'NULL'

INSERT INTO employee_pay_tbl 
VALUES ('442346889','TEAM LEADER','2000-06-17','14.75','2009-06-01',NULL,NULL);

Also, make it a habbit to specify the column names, so that if the table structure changes, it does not break your query.

Something like

INSERT INTO employee_pay_tbl (EMP_ID, position, date_hire, pay_rate, date_last_raise, salary, bonus)
VALUES ('442346889','TEAM LEADER','2000-06-17','14.75','2009-06-01',NULL,NULL);

Upvotes: 1

Related Questions