Using the parameters which are passed to the stored
procedure, a bcp command is built dynamically and passed to the extended stored
procedure, xp_cmdshell, which calls the dos shell to execute the bcp command. The
direction can be In or Out. When you are copying data from a table/view to a
file use Out as the direction and when you are copying data from file to a
table/ view use In.
Parameter to be passed to the stored procedure
File path: Valid file path where your Sqlserver run not your
client machine.
Direction: Pass In for import or Out for export.
Table name/view name: Valid table/view name which is stored
in the current database.
Using the stored procedure you can export or import any
table/view from your database to a text file. BCP is the simplest way to
export a single table or view to a text file. When you export the data to a file
you need to give a valid file path and the table/view must be stored in it. A
file will be created and the data will export to the file. You need to specify
the direction as OUT. The file is exported in a format as character, data, and
type with tab space as a field separator and the new line as the row
terminator.
When you import from the file to a table you need to give a
valid file name which contains the data to import. The table/view should be
stored in the current database. Only a simple view, data from one single table,
can be used. You need to specify the direction as IN. The format should be of
character, data, and type with tab space as the field separator and the new
line as the row terminator.