You need to use bcp command on Windows command line to create the format file. The generated format file (.fmt) will have all the columns from your table, so you need to delete the unwanted ones. I think deleting 2-3 columns is easier than typing 100 columns :)
Type bcp on Windows command line to see all the options available with bcp command.
C:\Users\praveen\Documents\fmtfiles>bcp
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
[-d database name] [-K application intent]
Once you became familiar with all these options, now it's time to create the format file.
Type the following command on command line to create .fmt file
C:\Users\praveen\Documents\datafiles>bcp truven.native.red_book format nul -f red_book-x.fmt -n -T -S muse1\sqlexpress -r\n -t\t
The above command will create file fmtfile-n.fmt. You can view it in the notepad/any text editor. In the above command:
Database:- Mention your database name.
Schemaname:- Mention your schema name.
Tablename:- Mention your tablename for which you are creating the format file.
\t - if your input data is tab delimited. Change it as per your input data file.
Servername: Mention you database server name. You can find it in MS SQL Server Management Studio.
\n: if the rows in the input file are in newline.
fmtfile-n.fmt: your output filename.
