1
USE [campaigns]
DECLARE @FILENAME varchar(400) = 'W:\PlayerListing\List.txt'
    ,@EMAIL_SOURCE varchar(50) = 'listing'
    ,@SQLCMD varchar(8000)

PRINT (CAST(GETDATE() AS varchar(30)) + '     BULK INSERT TABLE CREATION STARTING')
SELECT @SQLCMD = '
CREATE TABLE BULK_INSERT_TABLE(
[id] [int] NOT NULL,
[e-mail] [varchar](60) NOT NULL,
[first_name] [varchar](60) NOT NULL,
[last_name] [varchar](60) NOT NULL,
[zip] [varchar](60) NULL)'
EXECUTE (@SQLCMD)
PRINT (CAST(GETDATE() AS varchar(30)) + '     BULK INSERT TABLE CREATION COMPLETE')
PRINT (CAST(GETDATE() AS varchar(30)) + '     BULK INSERT LOAD STARTING')
SELECT @SQLCMD = '
BULK
 INSERT BULK_INSERT_TABLE
 FROM ''' + @FILENAME + ''' 
 WITH
 (
 FIELDTERMINATOR = ''|'',
 ROWTERMINATOR = ''
'', FIRE_TRIGGERS 
 )'
EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')


PRINT (CAST(GETDATE() AS varchar(30)) + '     BULK INSERT LOAD COMPLETE')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update ' + @EMAIL_SOURCE + ' STARTING')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update Production from Staging (opt-out)     STARTING')
SELECT @SQLCMD = '
-- Does not exist on Staging
UPDATE  ' + @EMAIL_SOURCE + '  set isActive = 0 where [ID] not in (select [ID] from     BULK_INSERT_TABLE)
'
EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update Production from Staging (opt-out)     COMPLETE')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update Production from Staging (opt-in)     STARTING')
-- Does not exist on Production
SELECT @SQLCMD = '
insert into  ' + @EMAIL_SOURCE + '  ([id],[E-mail],[first_name],[last_name],[zip])
SELECT [id],[E-mail],[first_name],[last_name],[zip] FROM BULK_INSERT_TABLE WHERE [ID] NOT IN (select [ID] from  ' + @EMAIL_SOURCE + ' )'
    EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update Production from Staging (opt-in) COMPLETE')
-- Mismatch of data
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update Production from Staging (user data changes) STARTING')
SELECT @SQLCMD = 'CREATE TABLE CHECKSUM (CheckSum1 int, CheckSum2 int, [id] int)'
EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')

SELECT @SQLCMD = '
INSERT INTO CHECKSUM
    SELECT BINARY_CHECKSUM(t1.[id],t1.[e-mail],t1.[first_name],t1.[last_name],t1.[zip]) AS ''CheckSum1'' 
    ,BINARY_CHECKSUM(t2.[id],t2.[e-mail],t2.[first_name],t2.[last_name],t2.[zip]) AS ''CheckSum2''
    ,t1.[id] 
    FROM   ' + @EMAIL_SOURCE + '   t1 
    JOIN BULK_INSERT_TABLE as t2 ON t1.id =t2.id'
EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')

SELECT @SQLCMD = '
        UPDATE   ' + @EMAIL_SOURCE + '   
        set [e-mail] = BI.[e-mail]
            ,[first_name] = BI.[first_name]
            ,[last_name] = BI.[last_name]
            ,[zip] = BI.[zip]
            ,[isActive] = 1 
        FROM   ' + @EMAIL_SOURCE + '   EL
        INNER JOIN BULK_INSERT_TABLE as BI on BI.[id] = EL.[id]
        INNER JOIN CHECKSUM as C on C.[id] = EL.[id]'
EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')

SELECT @SQLCMD = 'DROP TABLE CHECKSUM'
EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update Production from Staging (user data changes) COMPLETE')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update ' + @EMAIL_SOURCE + ' COMPLETE')

SELECT @SQLCMD = '
DROP TABLE BULK_INSERT_TABLE'
EXECUTE (@SQLCMD)

Errors:

Nov 11 2013  4:59PM     BULK INSERT TABLE CREATION STARTING
Nov 11 2013  4:59PM     BULK INSERT TABLE CREATION COMPLETE
Nov 11 2013  4:59PM     BULK INSERT LOAD STARTING

Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider     did not give any information about the error.

Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Here are some lines from my txt file:

596385|[email protected]|first|last
243662|[email protected]|first|last
43010|[email protected]|first|last
214963|[email protected]|first|last
3
  • Have you tried using KEEPNULLS ? Any reason, you are not using SSIS ? Also, try to isolate the problem rows as described at stackoverflow.com/a/13129140/1387418
    – Kin Shah
    Nov 11, 2013 at 22:29
  • Instead of starting with this very verbose dynamic SQL-based solution, why not just try messing with ROWTERMINATOR in a naked BULK INSERT statement? It currently doesn't look right, it should probably be literal \r or \n or \r\n. Nov 11, 2013 at 22:34
  • I tried using \r\n, : Msg 4866, Level 16, State 1, Line 2 The bulk load failed. The column is too long in the data file for row 1, column 5. Verify that the field terminator and row terminator are specified correctly. Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 2 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
    – walt
    Nov 11, 2013 at 22:37

1 Answer 1

2

In BULK INSERT statement

give ROWTERMINATOR = '0x0a'

Instead of ROWTERMINATOR = ''

2
  • 1
    Would you explain why this works?
    – Hannah Vernon
    Nov 14, 2013 at 13:54
  • 0x0a It's a byte with the value "10" in hexadecimal. 10 happens to be the ASCII value for a newline character. Aug 26, 2016 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.