CODEWITHSUNDEEP
sqlsql-server
Throkar
Throkar

Reputation: 73

Inserting empty spaces instead of NULLs with BULK INSERT

I'm using BULK INSERT to insert multiple registers into a database, which already has data in it. I was wondering if there's any way to insert empty spaces instead of NULLs, so the inserted data is visually consistent with the previous data. This is the script I'm using.

BULK INSERT Documentos
FROM '\\Cate-des-tn-032\2015\InsertAWeb_07-05-2019.csv'
WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '\n'
)

UPDATE Documentos
    SET FechaCargaLote = CONVERT(VARCHAR(50), GETDATE(), 120)
    WHERE FechaCarga LIKE '%2015%'

Upvotes: 0

Views: 1353

Answers (1)

level3looper
level3looper

Reputation: 1051

Here is a scenario for using a staging table. First load all data as VarChar(). Then Insert Into your actual table with conversions as needed.

Setup Tables:

Create Table #staging_tbl
(
id VarChar(5),
dt VarChar(15)
)

Create Table #actual_tbl
(
id Int,
dt VarChar(10)
)

Sample File [test.txt]

1;2019-05-12
1;2019-05-11
1;2019-05-10
1;
1;2019-05-08
2;2019-04-19

Bulk Insert

BULK INSERT #staging_tbl
FROM 'c:\Downloads\test.txt' 
WITH 
(
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '\n'
)

Move data from staging table to actual table: Convert NULL to 'blank'

Insert Into #actual_tbl
SELECT 
  id,
  Case When dt Is Null Then '' Else dt End As d
FROM #staging_tbl

Query actual table

Select * From #actual_tbl

Result:

id  dt
1   2019-05-12
1   2019-05-11
1   2019-05-10
1   
1   2019-05-08
2   2019-04-19

Upvotes: 1

Related Questions