Data Loading - SQL Server


Upload_script.bat

SET LOCAL ENABLE DELAYED EXPANSION
ECHO off
set Server_Name=Server1\Instance1
set DB_Name=DB1
set uploadfiles=TABLE1
for %%u in (%uploadfiles%) DO (
set Fact_Table=%%u
set CsvFactData=!Fact_Table!.csv
set FactTableFormat=Format_!Fact_Table!.xml
set Fact_Table_backup=!Fact_Table!_bkup
set Fact_Table_backup_FullName=%DB_Name%.dbo.!Fact_Table!_bkup

sqlcmd -S %Server_Name% -i .\code\CleanBackupTable.sql -v Backup_Table = !Fact_Table_Backup_FullName!
bcp !Fact_Table_Backup_FullName! in .\csv\!CsvFactData! -f .\code\!FactTableFormat! -S %server_Name% -T -F2 -b1000
)
rem SWITCH DATA TABLES FROM BACKUP TO LIVE
for %%u in (%uploadfiles%) DO (
set Fact_Table=%%u
set Fact_Table_backup=!Fact_Table!_bkup
sqlcmd -S %server_Name% -i .\code\Rename.sql -v backupTable = !Fact_Table_Backup! -v FactTable = !Fact_Table!
)


CleanBackupTable.sql
USE $(DB_Name);
GO
drop table $(Backup_Table)
GO
select * into $(Backup_Table) from $(Fact_Table)
Go


Rename.sql
USE $(DB_Name);
GO
sp_rename $(backupTable), 'Intermediate_temp';
GO
sp_rename $(FactTable), $(backupTable);
GO
sp_rename 'Intermediate_temp', $(FactTable);
GO


Data.xml
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="Latin1_General_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="ID" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="DESC" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="3" NAME="CODE" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="4" NAME="DELIVERY" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="5" NAME="MARKET" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="6" NAME="ORDERED_BY" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="7" NAME="CATEGORY" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="8" NAME="BENCHMARKS_EXIST" xsi:type="SQLNCHAR"/>
</ROW>
</BCPFORMAT>


Create XML
bcp TABLE1 format nul -c -t, -x -f C:\Mstrblog\TABLE1.xml -S SERVER1\INSTANCE1 -T


MD5 code
SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', 'email@dot.com'),2);


Copy data from table to csv
bcp "SELECT * FROM TABLE1" queryout C:\temp\Test.csv -c -t, -T -S SERVER1\INSTANCE1

Leave a Reply

Your email address will not be published. Required fields are marked *