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|email1@yahoo.com|first|last
243662|email2@yahoo.com|first|last
43010|email3@yahoo.com|first|last
214963|email4@yahoo.com|first|last
  • 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 '13 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. – Aaron Bertrand Nov 11 '13 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 '13 at 22:37
2

In BULK INSERT statement

give ROWTERMINATOR = '0x0a'

Instead of ROWTERMINATOR = ''

  • 1
    Would you explain why this works? – Max Vernon Nov 14 '13 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. – Danish Hussain Aug 26 '16 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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