Skip to content

Sql Server

Data Loading

Upload_script.bat

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


Create XML

bcp TABLE1 format nul -c -t, -x -f C:\MSTR\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