How to Insert Null Values into SQL Server

how do you insert null values into sql server

INSERT INTO atable (x,y,z) VALUES ( NULL,NULL,NULL)

Cannot insert 'NULL' value into SQL Server column

By looking at the error message that you have posted

Cannot insert the value NULL into column 'amount', table 'db2.dbo.theTable'; column does not allow nulls. INSERT fails. The statement has been terminated.

there is a Null value for amount column in `db2.dbo.theTable'. You can use ISNULL() to get rid of this issue.

If you want to see the NULL values, you gotta use query like

select * from db2.dbo.theTable where amount IS NULL

Have a look at is Null vs =Null to see why you weren't seeing those null records in your previous query.

Insert null value into SQL Server database

If you were writing a tsql with literals, you would need to detect the null scenario and append "null" rather than the value, so the SQL looks like

1,2,null,4,5

If you see what I mean. However, this is a bad way to do it - your SQL right now is really really dangerous. You should parameterize instead - this solves a range of problems:

• sql injection(your code is a security risk right now)
• formatting (dates etc)
• null values
• query plan reuse (or the lack of it)

For example:

query = "insert into Assignment Values(@eventId, @branchId, @isPremiere, @isNew, ...)"

where you add a parameter with value for each of the placeholders. Note that due to how ado.net you need to represent nulls with DBNull:

cmd.Parameters.AddWithValue("foo",
    foo == null ? (object)DBNull.Value : (object)foo);

(For each parameter)

Note that orms and micro-orms will help make this simple. For example with dapper:

DateTime foo = ...
int? bar = ...
connection.Execute(
     @"insert ... values (@foo, @bar)",
    new { foo, bar });

Create Insert query with null value in SQL Server

If a value is NULL, then adding it to a string will produce a NULL.
This allows us to add the quotes in the ISNULL check and just produce NULL in the true value of the check, producing the correct syntax for nulls or not nulls as necessary.

select 'Insert into Organizations(Name, IndustryId, ContactPerson, Email, Website, LocationId, ContactNumber, Mobilenumber) values(' + 
       IsNull(''''+Nameofthecompany+'''', 'NULL') + ', ' + 
       Isnull(''''+IndustryType+'''', 'NULL') + ', ' +
       Isnull(''''+Nameofthepersonresponsibleforrecruitment+'''', 'NULL') + ', ' +
       Isnull(''''+EmailId+'''', 'NULL') + ', ' +
       Isnull(''''+websiteaddress+'''', 'NULL') + ', ' +
       Isnull(''''+Location+'''', 'NULL') + ', ' +
       Isnull(PhoneNumber, 'NULL') + ', ' +
       Isnull(MobileNumber, 'NULL') + ')' 
from Organization

Need to insert null values into certain columns of my table

You can do that multiple ways:

Provide NULL

insert into dbo.Student
    [Number], [Timestamp], [Education], [Roll No],
    [Name], [Age],[marks])
Select
    NULL as [Number], NULL as [Timestamp], NULL as [Education],    
    Convert(nvarchar(50),dbo.Table2([a.Description Description])) as [Roll No],    
    convert(nvarchar(50),dbo.Table3([a.Description Description])) as [Name],        
    dbo.Table4([a.Description Description]) as [Age],    
    convert(nvarchar(50),dbo.Table5([a.Description Description])) as [marks]
from dbo.Data a

Avoid inserting data into those fields

insert into dbo.Student
    [Roll No],
    [Name], [Age],[marks])
Select
    Convert(nvarchar(50),dbo.Table2([a.Description Description])) as [Roll No],    
    convert(nvarchar(50),dbo.Table3([a.Description Description])) as [Name],        
    dbo.Table4([a.Description Description]) as [Age],    
    convert(nvarchar(50),dbo.Table5([a.Description Description])) as [marks]
from dbo.Data a

Change data after insert

After you do insert into dbo.Student ..., do:

update dbo.student set 
   [Number] = NULL,
   [Timestamp] = NULL,
   [Education] = NULL
where ...

You'll have to be careful with the where clause and ensure that you are changing only the records you recently entered.

---

Related Topics