Error in "BCP IN" operation

Question

I have a table in 2 different database.

In Database_A, there is a table created as:

CREATE TABLE tbl_Employee(
    E_Id int null
    ,E_Name varchar(24) not null
    ,Joining_Date datetime not null
    )

In Database_B, the same table is created as:

CREATE TABLE tbl_Employee(
    E_Id int null
    ,E_Name varchar(24) not null
    ,Joining_Date datetime null
    )

The difference is, the column Joining_Date is NOT NULL in Database_A but it allows NULL in the Database_B.

Now when I export data from the table Database_A.dbo.tbl_Employee using command

bcp "select * from Database_A.dbo.tbl_Employee" queryout "D:\test.txt" -S Server_Name -n -T

and import into Database_B.dbo.tbl_Employee using command

bcp "Database_B.dbo.tbl_Employee2" in "D:\test.txt"-S Server_Name -n -T

It raises an Error:

SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 11.0]Unexpected EOF encountered in BCP data-file

I can not alter the column to NOT NULL on Database_B

Answer 1

Try using with -c which is character instead of -n native

bcp "select * from Database_A.dbo.tbl_Employee" queryout "D:\test.txt" -S Server_Name -c -T

bcp "Database_B.dbo.tbl_Employee" in "D:\test.txt"-S Server_Name -c -T

Or an alternative is to create a format file.

1. Create format file syntax is:

   bcp Database_A.dbo.tbl_Employee format -null -f D:\BCPFormat.fmt -n -T
   

2. Export as normal with native:

   bcp "select * from Database_A.dbo.tbl_Employee" queryout "D:\test.txt" -S Server_Name -n -T 
   

3. Import with -k keeps nulls and format file, no -n flag needed.

   bcp Database_B.dbo.Employee in "D:\Test.txt" -S Server_Name -f D:\BCPFormat.fmt -k -T