CODEWITHSUNDEEP
sql-serverbulkinserttrim
SƲmmēr Aƥ
SƲmmēr Aƥ

Reputation: 2504

Trim Empty Space Before Bulk Insert

How to I trim empty space when doing bulk insert? I have the sample data as below:

| |100  |4000009|1000 |01    |                                   |201004471|          |28.01.1972|10.04.2012|300485|ABC 307.07B            |01   |                         |          |SSC |SSC  |CA02      |00   |      |0       |SESC    |COM  |01      |01   |00.00.0000|00.00.0000|FR1  |

Upvotes: 1

Views: 15270

Answers (3)

rouhollah ghasempour
rouhollah ghasempour

Reputation: 124

You can do it as below:

  1. insert data into yourTable
  2. remove spaces by UPDATE yourTable SET FirstName = LTRIM(RTRIM(FirstName))

Upvotes: 0

Eric L
Eric L

Reputation: 76

It may not be the most elegant solution, but you could bulk insert into a temporary table and then insert trimmed data from temp table into target table.

CREATE TABLE #temp
(
    [field1] [varchar](20) NOT NULL,
    [field2] [varchar](100) NOT NULL
) 

BULK
INSERT #temp
FROM 'C:\Temp\data.txt' 
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
FIRSTROW = 1
)
GO

Insert Into MyDatabase..TargetTable ([field1], [field2])
Select RTRIM(LTRIM([field1])), RTRIM(LTRIM([field2])) From #temp

Drop table #temp

Upvotes: 6

George Dgebuadze
George Dgebuadze

Reputation: 111

If you want to insert string value, this approach may be useful

    REPLACE('Original String', ' ', '')

Otherwise you may use dinamic SQL, put all your insert into the string, trim it and execute with sp_executesql

    DECLARE @szSQL NVARCHAR(MAX)
    SET @szSQL = 'INSERT INTO YourTable(YourCol1, YourCol2, YourCol3)
    VALUES(YourVal1, YourVal2, YourVal3)'
    SET @szSQL REPLACE(@szSQL, ' ', '')
    EXEC sp_executesql @szSQL

Upvotes: 1

Related Questions