Insert empty string into INT column for SQL Server

问题:

A SAMPLE table has only one column ID of type int, default null.

In Oracle when I do:

  insert into SAMPLE (ID) values ('');

the new record is added with blank value. But in SQL Server 2008, when I run the same insert statement, the new record has the value of 0.

Is there a way to force SQL Server 2008 to default blank string to NULL instead of 0 (for numerical type of columns)?

回答1:

Use NULL instead.

insert into SAMPLE (ID) values (NULL);

回答2:

Assuming that your INSERT statement is part of a stored procedure re-used in many places of your application (or, perhaps, is a batch always constructed by the same part of the client code) and that the inserted value is a number passed as a string argument, you could modify the INSERT like this:

INSERT INTO SAMPLE (ID) VALUES (NULLIF(@argument, ''));

回答3:

How about another idea - define an INSTEAD OF INSERT Trigger.

Despite the fact that you're trying to insert a string, with this the operation is "intercepted", empty string is replaced by NULL, and the insert succeeds.

If you define this trigger on your table, then you can continue to insert empty string as before, with no other changes.

Edit: As Martin Smith points out, this effectively is a comparison to 0 (the equivalent of empty string as an int) meaning you won't be able to store 0 in this table. I leave this answer here in case that's acceptable to your situation - either that or re-do all your queries!

CREATE TRIGGER EmptyStringTrigger
ON [SAMPLE]
INSTEAD OF INSERT
AS
  BEGIN
      INSERT INTO [SAMPLE](ID)
      SELECT CASE
               WHEN ID = '' THEN NULL
               ELSE ID
             END
      FROM   inserted
  END

SQL Fiddle example

回答4:

You can't insert a 'string' into a int column. Oracle must be just handling that for you.

Just try inserting NULL if that's what you need.

insert into SAMPLE (ID) values (NULL);

回答5:

One more option

insert into SAMPLE (ID) values (DEFAULT)