BCP is used to copy the data between the SQL Server database
and the data file. Using BCP you can copy data from a table to a file or from
a file to a table.
Please refer to the on-line book under topic bcp utility to
know syntax.
Listing 1
BCP pubs.. Employee out c:\temp\export.txt’ -c -SKrishna -Usa -Psa12
In the above example pubs is the database and employee is
the table name with the
file path c:\temp\export.txt.
- c character data type
–S server name
-U username
-P password
You need to run the bcp script in the command prompt since
bcp is an operating system level command. On execution a new file will be created
under the c:\ temp as export.txt and the data will be copied.
In a similar way you can import the data from the file to
the table. Change the direction as In instead of Out and the file to import
should be of valid format since I have used –c for character data type, the default
format as /t tab space for field separator and /n new line for row terminator.
Listing 2
BCP pubs..Employee in c:\temp\import.txt -c -S Krishna -Usa -Psa12
Using the extended stored procedure xp_cmdshell to run the
BCP command, Xp_cmdshell calls the command prompt to run the bcp script. The
xp_cmdshell can be executed only through the sysadmin role or you will need to
give permission to the user to execute it.
For example:
Listing 3
Declare @str varchar(4000)
Set @str ='BCP pubs..Employee out c:\temp\export.txt -c -SKrishna –Usa –Psa12'
Exec master..xp_cmdshell @st
