Insert an empty row in the INT column for SQL Server

Question

There is only one int type ID column in the SAMPLE table, the default is null.

In Oracle, when I do this:

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

a new record is added with an empty value. But in SQL Server 2008, when I run the same insert , the new record is set to 0.

Is there a way to force SQL Server 2008 to use an empty string for NULL instead of 0 (for a numeric column type) by default?

Answer 1

Use NULL instead.

 insert into SAMPLE (ID) values (NULL); 

Answer 2

Assuming your INSERT statement is part of a stored procedure that is reusable in many places in your application (or maybe it's a package that is always created by the same part of the client code) and that the inserted value is the number passed as a string argument, you can change the INSERT in the following way:

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

Answer 3

What about another idea - define INSTEAD OF INSERT INSERT .

Despite the fact that you are trying to insert a row, while the operation is "intercepted", the empty row is replaced by NULL, and the insert was completed successfully.

If you define this trigger in your table, you can continue to insert an empty row, as before, without any changes.

Edit: As Martin Smith points out, this is actually a comparison with 0 (the equivalent of an empty string as an int), which means that you cannot save 0 in this table. I leave this answer here if it is acceptable for your situation - either this or repeat all your requests!

 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

Answer 4

You cannot insert a row into an int column. Oracle should just handle this for you.

Just try inserting NULL if you need it.

 insert into SAMPLE (ID) values (NULL); 

Answer 5

Another option

 insert into SAMPLE (ID) values (DEFAULT)