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
ROWTERMINATOR
in a nakedBULK INSERT
statement? It currently doesn't look right, it should probably be literal\r
or\n
or\r\n
.