Format files in SQL Server


For many years, I used to think about writing an article on Format files. I got shocked on seeing a procedure which is using format file and I am not aware of this area. Anyway, Learning starts or goes only if we don't know something right!!!! 

Come one........Let's learn something...........

BCP - Bulk copy program 
  1. It's a utility to copy the data in bulk.
  2. Data from files (or) to files in a specific format can be achieved using this BCP.
Format file:
  1. If we want to read a file, it's very hard for SQL Server to read the file and store it in the database. Yes, we can store it but it will varchar field in the database.
  2. To inform SQL Server about the datatype in the files. We use the Format file.
  3. The Format file will tell you that, the file holds integer value, datetime field value with length 10 etc..,
How to create a Format file????? Let's see the below example to understand it.

Am having a table named Venkat_table

1.gif 

Below is the command to get the Format file,

       bcp Venkat_Table format nul -S servername -T -n -f C:\Venkat_TableFormat.fmt 

The Syntax is like, BCP DatabaseName.Ownername.ObjectName format nul -S servername -T -n -f formatfilename 

nul - If there is no data file name to load. Here, we are fetching data from the table.
-T - Indicates trusted authentication or else -U and -P should come (SQL Server authentication)
-n - Indicates native data type. We will discuss more below.
-f - Informing the format file

2.gif

We should go to the Windows command prompt and execute the above command, a file will be generated in the desired location. 

3.gif

On opening the Format file, you will get the below details. 

4.gif  

Here is a generic details to understand the format file.

5.gif

 
  bcp Venkat_Table format nul -S servername -T -c -f C:\Venkat_TableFormat.fmt 
 
-C indicates the type of columns to be assumed. C indicates the char type. In the below pic, you can see the datatypes were located as character datatype. If it's -n, then the datatype is native datatype.

6.gif

Up Next
    Ebook Download
    View all
    Learn
    View all