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
- It's a utility to copy the data in bulk.
- Data from files (or) to files in a specific format can be achieved using this BCP.
Format file:
- 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.
- To inform SQL Server about the datatype in the files. We use the Format file.
- 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
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
We should go to the Windows command prompt and execute the above command, a file will be generated in the desired location.
On opening the Format file, you will get the below details.
Here is a generic details to understand the format file.
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.