SQL Server : BCP Out and Bulk Insert – Simple Example

Export and Import Data to/from files

-- Export Data to csv file

declare @cmd varchar(8000)

set @cmd = N'bcp "SELECT * from DB..TableName" queryout C:\MP.csv -T -c -t,'

exec sys.xp_cmdshell @cmd

-- Import Data

BULK INSERT TableName

FROM 'C:\MP.csv'

WITH

(

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n'

);

PS:
BCP utility requires sys.xp_cmdshell system procedure to execute. To execute sys.xp_cmdshell procedure you need to enable xp_cmdshell feature. By default this is disabled.

You can enable this by running the following commands:


-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable xp_cmdshell feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO