INSERT STATEMENT ERROR | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

INSERT STATEMENT ERROR

I’ve created a table as shown below but getting this error:.Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
CREATE TABLE VAT_MAPPING_DATE
(
VAT_CODE numeric(10,0) NULL,
FROM_DATE numeric(18,0) NULL,
TO_DATE numeric(18,0) NULL,
VAT_VALUE DECIMAL(5,3) NULL
)
I tried to insert data using the below script:
INSERT INTO VAT_MAPPING_DATE VALUES (‘1′,’26665’,”,”)
INSERT INTO VAT_MAPPING_DATE VALUES (‘3′,’39783′,’40178′,’0.15’)
INSERT INTO VAT_MAPPING_DATE VALUES (‘3′,’40179′,’40546′,’0.175’)
INSERT INTO VAT_MAPPING_DATE VALUES (‘3′,’40547′,”,’0.2’)
Any help will be very much appreciated
cheers everybody

If they are numeric values, why are you putting quotes around them in your script?
Problem could be the use of a full stop as the decimal symbol, which for strings is sensitive to the Regional Settings of the Windows server instance.

It’s the empty string in the very first INSERT that causes the error. Check out CAST & CONVERT in BOL. It says:

SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.
Good catch, Frank – I missed that.

I expect the same error when an empty string is used in other datatypes.
But implicit convertion happens
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/02/empty-string-and-default-values.aspx

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |