Creating the Build Scripts to Automate the Incremental Daily Database Builds:
I had the Requirement to run multiple scripts to create the Database, Stored Procedures, Functions, Triggers; etc.It was time consuming to run the scripts manually.
So I thought of using the Batch Files to automate the process.
Tools Used:
SQLCMD: This command line utility tool is shipped with SQL Server Database Engine and provides efficient integration with Batch files and SQL Server Databases.
Before running the scripts, I have to make sure the Database Exists.
If the database does not exist, I have to create the database with the Name passed as Parameter and then execute the T-SQL Scripts. I selected SQLCMD Tool to accomplish this task.
SYNTAX:
SQLCMD
-S Server Name
-d Database Name
-b Exit on Error in the Batch
-Q Run the Query in Database and close the Connection with the Database
-q Run the Query in Database and leave the Connection Open with the Server
SQLCMD Uses the OLEDB Interface to connect to the Server where as the
SQL Management Studio uses the SQLCLIENT to connect to the SQL Server.
It might cause some difference in the Query Execution.
Passing the variables to the command batch file:
In the Batch file all the variables are treated as strings. It’s different from any other procedural languages. So we do not have to define the variable data type.
Set DatabaseServer =%1
It means the first argument we pass in the command line would be the Database Server Name.
Set DatbaseName=%2 .
The second argument would be the Database Name. While passing the arguments in the command line, there has to be space between the arguments.
The final Build Script:
ECHO OFF
REM: -b On Error Batch Abort in sqlcmd
REM: >> or > this operator can be used in place of -o option to append the output.
set ServerName=%1
set DatabaseName=%2
set WorkDirectory =%3
set LogfileName =%4
echo The ServerName used in applying this Build: %1
echo The DatabaseName used in applying this Build:%2
echo The directory used in the Build: %3
echo The Logfile Used to log Errors in this Build: %4
echo: The Build Start time %date% %time%
ECHO: Checking the existence of database: %2 in server: %1
Echo: If not exists creating the Database: %2 in Server: %1
set Query="IF NOT EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='%2')
BEGIN CREATE DATABASE %2 END"
sqlcmd -S %1 -d Master -Q %Query%
echo: Checking the connection to the Server: %1 and Database: %2
set qry ="set nocount on"
sqlcmd -S %1 -d %2 -Q "%qry%"
IF %Errorlevel% NEQ 0 goto FailureExit:
Echo Applying the Scripts at Server: %1 and Database %2
for %%B in (%3\*.sql) do (sqlcmd -S %1 -d %2 -b -i "%%B" >> %4)
IF %Errorlevel% NEQ 0 goto FailureExit:
REM: for %%B in (%3\.\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
REM: IF %Errorlevel% NEQ 0 goto FailureExit:
REM: for %%B in (%3\.\*.sql) do (sqlcmd -S %1 -d %2 -i %%B)
REM: IF %Errorlevel% NEQ 0 goto FailureExit:
Echo The Build successfully applied at Server: %1 and Database: %2
Echo The Build completed Time %date% %Time%
:FailureExit echo The build Cancelled at Server: %1 and Database: %2
Processing the SQL Files in the Subdirectory
The following batch file processes the .sql files in the Given Server Name, DatabaseName, and WorkingDirectory.The log file is passed as the fourth Parameter where the output of the command execution saved.
echo off
set servername=%1
set DatabaseName=%2
set foldername=%3
set Logfilename=%4
for /d %%B in (%3\*) do (
for %%C in ("%%B\*.sql") do sqlcmd -S %1 -d %2 -i %%C >>%4)
Iterating through the Files without the Double loop:
echo off
set servername=%1
set DatabaseName=%2
set foldername=%3
set Logfilename=%4
for /r %3 %%B in (*.sql) do (sqlcmd -S %1 -d %2 -i %%B >>%4)
Using the /r command to search the .sql extension files in the Foldername passed as 3rd
parameter.
Wednesday, August 09, 2006
IMPORTING AND EXPORTING DATA FROM SQL SERVER TO EXCEL FILE:
Today I had the requirement to Read the Excel File and Extract the Data from SQL Server based on the Excel File and then Export the Data to the Different Excel Files.
So I break down the Task into Three Aspects:
Importing the Data From Excel File: OPENDATASOURCE:
Invoking the Command Prompt :XP_CMDSHELL
Using the Bcp.exe to Export the Data to the Excel File: BCP.EXE
Before running those commands,the SQL server should be enabled to run xp_cmdshell and OpenDataSource Commands.Those are server wide settings.
1.Reading the Data From Excel:
select * into temp from opendatasource
('microsoft.jet.oledb.4.0','data source= "G:Test.xls";extended properties=excel 5.0')...sheet1$
This reads the Data From the Excel File: G:\Test.xls
The sheetName :sheet1$
2.Using the XP_CMDSHELL:
I use xp_cmdshell with the BCP Option.
This invokes the command Prompt.
3.Using the BCP.EXE:
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[TestSp_ExcelFile]
(@file varchar(255))
as
begin
set nocount on
if exists(select * from sysobjects where id=object_id('temp')and xtype='u')
begin
print cast('dropping the table'as varchar(255))
drop table temp
end
declare @filename varchar(255)
declare @string nvarchar(2000)
declare @id int
declare @commandstring varchar(2000)
declare @vtargetfilename varchar(255)
declare @targetfilename varchar(255)
declare @targetpath varchar(255)
declare @position int
set @filename=@file
set @string='('+'''microsoft.jet.oledb.4.0'''+','+''+'''data
source= '+'"'+@filename+'"'+';'+'extended properties=excel 5.0'')'+'...sheet1
set @string='select * into temp from opendatasource '+ @string
execute sp_executesql @string
Print @string
declare c scroll cursor for
select columnname from temp
open c
fetch absolute 1 from c into @id
while @@fetch_status=0
begin
set @commandstring='select * from Adventureworks.Person.Address where AddressID ='+ cast(@id as varchar(20))
set @vtargetfilename=@filename
set @position=(datalength(@vtargetfilename)-charindex ('\',reverse(@vtargetfilename),0))
select @targetpath=left(@vtargetfilename,@position+1)
set @targetfilename=@targetpath +cast(@id as varchar(20))+'.xls'
set @commandstring = 'bcp.exe '+'"'+@commandstring+'"'+' queryout '+'"'+ @targetfilename +'"'+'-T -c'
print @commandstring
execute xp_cmdshell @commandstring
fetch next from c into @id
end
close c
deallocate c
end
I am using the dynamic SQL to create the Excel File for Each Row available in the Source Excel File.
Today I had the requirement to Read the Excel File and Extract the Data from SQL Server based on the Excel File and then Export the Data to the Different Excel Files.
So I break down the Task into Three Aspects:
Importing the Data From Excel File: OPENDATASOURCE:
Invoking the Command Prompt :XP_CMDSHELL
Using the Bcp.exe to Export the Data to the Excel File: BCP.EXE
Before running those commands,the SQL server should be enabled to run xp_cmdshell and OpenDataSource Commands.Those are server wide settings.
1.Reading the Data From Excel:
select * into temp from opendatasource
('microsoft.jet.oledb.4.0','data source= "G:Test.xls";extended properties=excel 5.0')...sheet1$
This reads the Data From the Excel File: G:\Test.xls
The sheetName :sheet1$
2.Using the XP_CMDSHELL:
I use xp_cmdshell with the BCP Option.
This invokes the command Prompt.
3.Using the BCP.EXE:
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[TestSp_ExcelFile]
(@file varchar(255))
as
begin
set nocount on
if exists(select * from sysobjects where id=object_id('temp')and xtype='u')
begin
print cast('dropping the table'as varchar(255))
drop table temp
end
declare @filename varchar(255)
declare @string nvarchar(2000)
declare @id int
declare @commandstring varchar(2000)
declare @vtargetfilename varchar(255)
declare @targetfilename varchar(255)
declare @targetpath varchar(255)
declare @position int
set @filename=@file
set @string='('+'''microsoft.jet.oledb.4.0'''+','+''+'''data
source= '+'"'+@filename+'"'+';'+'extended properties=excel 5.0'')'+'...sheet1
set @string='select * into temp from opendatasource '+ @string
execute sp_executesql @string
Print @string
declare c scroll cursor for
select columnname from temp
open c
fetch absolute 1 from c into @id
while @@fetch_status=0
begin
set @commandstring='select * from Adventureworks.Person.Address where AddressID ='+ cast(@id as varchar(20))
set @vtargetfilename=@filename
set @position=(datalength(@vtargetfilename)-charindex ('\',reverse(@vtargetfilename),0))
select @targetpath=left(@vtargetfilename,@position+1)
set @targetfilename=@targetpath +cast(@id as varchar(20))+'.xls'
set @commandstring = 'bcp.exe '+'"'+@commandstring+'"'+' queryout '+'"'+ @targetfilename +'"'+'-T -c'
print @commandstring
execute xp_cmdshell @commandstring
fetch next from c into @id
end
close c
deallocate c
end
I am using the dynamic SQL to create the Excel File for Each Row available in the Source Excel File.
Sunday, August 06, 2006
Friday I had some need for using the Format files to load the data into the sql server using the BCP Utility.I was getting some weired errors.(EOF File Error,Data Truncation Error,I/O Error,etc).I googled to find out the reason for those Errors and I found few reasons.
So I thoght of writing about using the format files in BCP Utility.
Creating the Format Files:
The easiest way I found is to Create the Format file using the Existing Table.Then it's easy to Edit the format file as the requirement changes.
a.Creating the format file with char Dataset:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -c -f G:\FormatFile.fmt -T'
Sample Format File:
9.0
13
1 SQLCHAR 0 16 "\t" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "\t" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 35 "\t" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 3 "\t" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 24 "\t" 5 EffectiveStartDate ""
6 SQLCHAR 0 50 "\t" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 3 "\t" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 3 "\t" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 255 "\t" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 3 "\t" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 255 "\t" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 30 "\t" 12 FeePerUnit ""
13 SQLCHAR 0 3 "\r\n" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
b.Creating the format file for Unicode DataSet:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -w -f G:\FormatFile.fmt -T'
9.0
13
1 SQLNCHAR 0 32 "\t\0" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 5 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 12 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
Here the data type SQLNCHAR is selected to support the UNICODE.This is done through the -w option while creating the format file.
c.Creating the Native Format Format Files:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -N -f G:\FormatFile.fmt -T'
9.0
13
1 SQLNCHAR 2 32 "" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 2 6 "" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 70 "" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 6 "" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLDATETIME 1 8 "" 5 EffectiveStartDate ""
6 SQLNCHAR 2 100 "" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 2 6 "" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 2 6 "" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 2 510 "" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 2 6 "" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 510 "" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLMONEY 1 8 "" 12 FeePerUnit ""
13 SQLNCHAR 2 6 "" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
In the native format,you can see the different data types from the char and widechar types.
Once done with creating the format files,the format files can be used to import the data into the existing tables.This provides the greater flexibility in importing the selective columns into the existing tables.
1.SameNumber of columns in the DataFile and the Table structure:
If the same number of columns exist between the data file and the table structure,its easy to create the one to one Relationship between the Data File Structure and the Table Structure.
The format file definies the relationship between the datafile and the table structure.
The first column in the above structure defines the Order of the columns in the DataFile.
We have to specify the all the Columns available in the DataFile.But we can skip specifying all the columns available in the Table.
I am using the Data File with 13 columns and the format file created using the -w option above.
Create the CharFormat Table with 13 Columns using the following script.
CREATE TABLE [dbo].[charFormat](
[PartNumber] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
After creating the table,Insert the data into the table:
FormatFile:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -w -f G:\FormatFile.fmt -T'
Loding Process:
xp_cmdshell 'bcp Test.dbo.CharFormat in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
Options: in -- For the Input File Name
f -- FormatFile..It should be avialble at the time of loading process
T --Trusted Connection
2.Loading into the Table which has different Structure than the available DataFile:
DataFile: 13 Fields
Table : 12 Fields
In this case ,we have to change the Format File,so we can direct the data we want into the Required Columns into the Table.
create the table with the following scripts:
CREATE TABLE [dbo].lessfields
([ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Change the Format File:
9.0
13
1 SQLNCHAR 0 32 "\t\0" 0 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 1 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 2 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 3 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 4 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 5 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 6 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 7 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 8 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 9 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 10 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 0 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 0 CurrencyCode SQL_Latin1_General_CP1_CI_AS
I have routed the first column avilable in the data file to 0 th column in the Table Structure.So we neglect this column in the datafile.
If you want to neglect the data in the FIle ,Put 0 in the sixth column in the format File.
First column corresponds to the Datafile Structure.Sixth Column corrosponds to the Table Column Order.So its easy to direct the data file to the required Column structure in the Table Structure.
Loading the Data:
xp_cmdshell 'bcp Test.dbo.lessfields in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
3.Working with the Table,where Table is having more columns than the Data File:
Table: 15 Columns
DataFile: 13 Columns
Create the Table with the FollowingScripts:
CREATE TABLE [dbo].MoreFields(
Field1 int Identity(1,1),
Field2 nvarchar(255),
[PartNumber] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Change the Format FIle.We only need all the fields avialable in the DataFile. It's not required to specify all the fields available in the Table.
Change the FormatFile:
9.0
13
1 SQLNCHAR 0 32 "\t\0" 3 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 4 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 5 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 6 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 7 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 8 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 9 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 10 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 11 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 12 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 13 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 0 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 0 CurrencyCode SQL_Latin1_General_CP1_CI_AS
First Column in the Data File corresponds to the 3 rd column in the Table. when you specify 0 in the 6 th column,We are going to Neglect the Data available in the file for those columns.
Load the Data into the Table:
xp_cmdshell 'bcp Test.dbo.MoreFields in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
Format files provide greater flexibility when interacting with different systems.
So I thoght of writing about using the format files in BCP Utility.
Creating the Format Files:
The easiest way I found is to Create the Format file using the Existing Table.Then it's easy to Edit the format file as the requirement changes.
a.Creating the format file with char Dataset:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -c -f G:\FormatFile.fmt -T'
Sample Format File:
9.0
13
1 SQLCHAR 0 16 "\t" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "\t" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 35 "\t" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 3 "\t" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 24 "\t" 5 EffectiveStartDate ""
6 SQLCHAR 0 50 "\t" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 3 "\t" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 3 "\t" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 255 "\t" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 3 "\t" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 255 "\t" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 30 "\t" 12 FeePerUnit ""
13 SQLCHAR 0 3 "\r\n" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
b.Creating the format file for Unicode DataSet:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -w -f G:\FormatFile.fmt -T'
9.0
13
1 SQLNCHAR 0 32 "\t\0" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 5 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 12 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
Here the data type SQLNCHAR is selected to support the UNICODE.This is done through the -w option while creating the format file.
c.Creating the Native Format Format Files:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -N -f G:\FormatFile.fmt -T'
9.0
13
1 SQLNCHAR 2 32 "" 1 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 2 6 "" 2 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 70 "" 3 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 6 "" 4 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLDATETIME 1 8 "" 5 EffectiveStartDate ""
6 SQLNCHAR 2 100 "" 6 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 2 6 "" 7 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 2 6 "" 8 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 2 510 "" 9 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 2 6 "" 10 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 510 "" 11 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLMONEY 1 8 "" 12 FeePerUnit ""
13 SQLNCHAR 2 6 "" 13 CurrencyCode SQL_Latin1_General_CP1_CI_AS
In the native format,you can see the different data types from the char and widechar types.
Once done with creating the format files,the format files can be used to import the data into the existing tables.This provides the greater flexibility in importing the selective columns into the existing tables.
1.SameNumber of columns in the DataFile and the Table structure:
If the same number of columns exist between the data file and the table structure,its easy to create the one to one Relationship between the Data File Structure and the Table Structure.
The format file definies the relationship between the datafile and the table structure.
The first column in the above structure defines the Order of the columns in the DataFile.
We have to specify the all the Columns available in the DataFile.But we can skip specifying all the columns available in the Table.
I am using the Data File with 13 columns and the format file created using the -w option above.
Create the CharFormat Table with 13 Columns using the following script.
CREATE TABLE [dbo].[charFormat](
[PartNumber] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
After creating the table,Insert the data into the table:
FormatFile:
xp_cmdshell 'bcp Test.dbo.Formatfile format nul -w -f G:\FormatFile.fmt -T'
Loding Process:
xp_cmdshell 'bcp Test.dbo.CharFormat in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
Options: in -- For the Input File Name
f -- FormatFile..It should be avialble at the time of loading process
T --Trusted Connection
2.Loading into the Table which has different Structure than the available DataFile:
DataFile: 13 Fields
Table : 12 Fields
In this case ,we have to change the Format File,so we can direct the data we want into the Required Columns into the Table.
create the table with the following scripts:
CREATE TABLE [dbo].lessfields
([ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Change the Format File:
9.0
13
1 SQLNCHAR 0 32 "\t\0" 0 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 1 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 2 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 3 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 4 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 5 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 6 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 7 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 8 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 9 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 10 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 0 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 0 CurrencyCode SQL_Latin1_General_CP1_CI_AS
I have routed the first column avilable in the data file to 0 th column in the Table Structure.So we neglect this column in the datafile.
If you want to neglect the data in the FIle ,Put 0 in the sixth column in the format File.
First column corresponds to the Datafile Structure.Sixth Column corrosponds to the Table Column Order.So its easy to direct the data file to the required Column structure in the Table Structure.
Loading the Data:
xp_cmdshell 'bcp Test.dbo.lessfields in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
3.Working with the Table,where Table is having more columns than the Data File:
Table: 15 Columns
DataFile: 13 Columns
Create the Table with the FollowingScripts:
CREATE TABLE [dbo].MoreFields(
Field1 int Identity(1,1),
Field2 nvarchar(255),
[PartNumber] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramName] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISOCountryCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveStartDate] [datetime] NULL,
[EffectiveEndDate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProgramLevelCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductFamilyName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FeePerUnit] [money] NULL,
[CurrencyCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Change the Format FIle.We only need all the fields avialable in the DataFile. It's not required to specify all the fields available in the Table.
Change the FormatFile:
9.0
13
1 SQLNCHAR 0 32 "\t\0" 3 PartNumber SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 6 "\t\0" 4 ProgramCode SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 70 "\t\0" 5 ProgramName SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 6 "\t\0" 6 ISOCountryCode SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 48 "\t\0" 7 EffectiveStartDate ""
6 SQLNCHAR 0 100 "\t\0" 8 EffectiveEndDate SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 6 "\t\0" 9 ProgramLevelCode SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 6 "\t\0" 10 ProductTypeCode SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 510 "\t\0" 11 ProductTypeName SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 6 "\t\0" 12 ProductFamilyCode SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 510 "\t\0" 13 ProductFamilyName SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 60 "\t\0" 0 FeePerUnit ""
13 SQLNCHAR 0 6 "\r\0\n\0" 0 CurrencyCode SQL_Latin1_General_CP1_CI_AS
First Column in the Data File corresponds to the 3 rd column in the Table. when you specify 0 in the 6 th column,We are going to Neglect the Data available in the file for those columns.
Load the Data into the Table:
xp_cmdshell 'bcp Test.dbo.MoreFields in G:\Feeschedule.txt -fG:\FormatFile.fmt -T '
Format files provide greater flexibility when interacting with different systems.