Migration from Oracle to SQL Server - Null vs Empty Issue

sreenivasa k

8y992Databases & DBA

Hi All,

We have converted Oracle database to SQL server database. We are facing issue with null vs blank.

Scenario:

In oracle:

Create table Student (Sno int, FName varchar(20) not null, MName varchar(30) null,

Lname varchar(20) not null)

Insert into Student(Sno,Fname,Mname,Lname) values (10,'James' ,'','Clark')

Record Inserting into Oracle as Middle name inserting as null even we passed blank value('')

10,James,null,Clark

Record Inserting into SQL Server as Middle name inserting as blank in SQL server (showing empty space in the table, even we have the default constraint)

10,James,,Clark

we want to handle when blank ('') inserting into SQL table it should be treated as 'null' which is the similar behavior of Oracle database.

Thanks in advance.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kapil Singh Kumawat8y ago
you can assign the default value as NULL while creating the table.
Hemant Jindal8y ago
Please ensure that default value for middlename is set to Null in SQL Server as mentioned below:

CREATE TABLE [dbo].[TestTable](
[ID] [int] NULL,
[FirstName] [nvarchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestTable] ADD CONSTRAINT [DF_TestTable_MiddleName] DEFAULT (NULL) FOR [MiddleName]
GO

or share you table script

thanks
Hemant Jindal