2

On my test server (SQL 16.0.1110.1), have this table defined this way:

CREATE TABLE [dbo].[mytable](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [IDOpSt] [bigint] NULL,
    [Order] [smallint] NULL,
    [TopPosition] [bit] NULL,
    [Caption] [varchar](500) NULL,
    [PrimaryField] [bit] NULL,
    [GroupField] [bigint] NULL,
    [VarName] [varchar](250) NULL,
    [DisableField] [bit] NULL,
    [ReadOnlyField] [bit] NULL,
    [RequiredField] [bit] NULL,
    [HiddenField] [bit] NULL,
    [FormatField] [varchar](400) NULL,
    [Observation] [varchar](400) NULL,
    [Tooltip] [varchar](400) NULL,
    [DefaultValue] [varchar](250) NULL,
    [Sufix] [varchar](5) NULL,
    [IDFieldType] [bigint] NULL,
    [IDFieldDependence] [bigint] NULL,
    [Formula] [varchar](max) NULL,
    [Parameter1] [varchar](max) NULL,
    [Parameter2] [varchar](max) NULL,
    [Parameter3] [varchar](200) NULL,
    [Parameter4] [varchar](100) NULL,
    [Parameter5] [varchar](max) NULL,
    [Parameter6] [varchar](max) NULL,
    [Parameter7] [varchar](max) NULL,
    [ChildsField] [int] NULL,
    [Transferable] [bit] NULL,
    [HistoryPosition] [int] NOT NULL,
 CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

If I insert this single line, I got no error

INSERT INTO [dbo].[mytable]
           ([IDOpSt]
           ,[Order]
           ,[TopPosition]
           ,[Caption]
           ,[PrimaryField]
           ,[GroupField]
           ,[VarName]
           ,[DisableField]
           ,[ReadOnlyField]
           ,[RequiredField]
           ,[HiddenField]
           ,[FormatField]
           ,[Observation]
           ,[Tooltip]
           ,[DefaultValue]
           ,[Sufix]
           ,[IDFieldType]
           ,[IDFieldDependence]
           ,[Formula]
           ,[Parameter1]
           ,[Parameter2]
           ,[Parameter3]
           ,[Parameter4]
           ,[Parameter5]
           ,[Parameter6]
           ,[Parameter7]
           ,[ChildsField]
           ,[Transferable]
           ,[HistoryPosition])
     VALUES
(141,3530,1, 'some text 01',1,0, NULL,1,1,0,0, '', NULL, NULL, 'some text 02', NULL,39,0, NULL, NULL, 'some text 03', 'some text 04', NULL, NULL,1, NULL,0,0, 1)

But if I insert these 2 lines:

INSERT INTO [dbo].[mytable]
           ([IDOpSt]
           ,[Order]
           ,[TopPosition]
           ,[Caption]
           ,[PrimaryField]
           ,[GroupField]
           ,[VarName]
           ,[DisableField]
           ,[ReadOnlyField]
           ,[RequiredField]
           ,[HiddenField]
           ,[FormatField]
           ,[Observation]
           ,[Tooltip]
           ,[DefaultValue]
           ,[Sufix]
           ,[IDFieldType]
           ,[IDFieldDependence]
           ,[Formula]
           ,[Parameter1]
           ,[Parameter2]
           ,[Parameter3]
           ,[Parameter4]
           ,[Parameter5]
           ,[Parameter6]
           ,[Parameter7]
           ,[ChildsField]
           ,[Transferable]
           ,[HistoryPosition])
     VALUES
(141,3085,1, 'Value 01',1,0, NULL,1,1,0,0, '', NULL, NULL, 'Value 02', NULL,10,0, NULL, NULL, NULL,0, NULL, NULL, NULL, NULL,0,0, 1),
(141,3530,1, 'some text 01',1,0, NULL,1,1,0,0, '', NULL, NULL, 'some text 02', NULL,39,0, NULL, NULL, 'some text 03', 'some text 04', NULL, NULL,1, NULL,0,0, 1)

I got this error:

<Msg 245, Level 16, State 1, Line 6 Conversion failed when converting the varchar value 'some text 04' to data type int.> Can anyone shed some light on what could be the problem?

0

1 Answer 1

5

In your INSERT statement, the 0 (zero) - in the first row of VALUES - that is in the same field position as the 'some text 04' value (in the second row of VALUES), is causing SQL to interpret that field as as INT field. (SQL is essentially evaluating the rows of values to be inserted, and making decisions about their data types, based on Data Type Precedence rules). An argument could be made that SQL should validate the data types of the columns in the destination table, rather than the INSERT VALUES specified - but, hey, 'tis what 'tis.

A relatively simple example that demonstrates SQL making the decision about data types, and their compatibility, is something like this:

SELECT 0 AS [Val]
UNION ALL
SELECT 'a'

If you execute the above example you will receive a similar error message (Conversion failed when converting varchar value 'a' to data type int). So, SQL has evaluated the SELECT rows - and determined that the appropriate data type for the column is INT, therefore any of the rows with an "incompatible" data type (eg. "a" can't be converted to INT) will cause an error.

Info about Data Type Precedence rules: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16

2
  • Thanks for the explanation and example @Craig! I knew about data type precedence, but not about that inference in INSERTs, rather than SQL checking what is the column correct data type. The obvious solution is to insert everything in this column between single quotes, which I do in automated inserts. But I was testing a manual INSERT (1st row without single quotes, 2nd with) and couldn't see the problem generating the error.
    – ofri cofri
    Apr 5 at 10:35
  • 1
    That's why we love SQL Server - with all its little quirks and all!
    – Craig
    Apr 7 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.