SQL Server BCP命令导入、导出数据

-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO

-- 重新配置
RECONFIGURE
GO

-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO

--重新配置
RECONFIGURE
GO

导入：
exec Test..xp_cmdshell'bcp test.dbo.Test_Table in "E:\data\1.dat" -c -t"0x01" -r"0x0102" -k -U"sa" -P"123456"'

导出：
exec Test..xp_cmdshell'bcp test.dbo.Test_Table out E:\data\test.txt -c -t"," -r"\n" -U"sa" -P"123456"'

导出某写字段

exec Test..xp_cmdshell'bcp "select column1,column2 from test.dbo.Test_Table " queryout E:\data\test.txt -c  -t"," -r "\n" -U"sa" -P"123456"'

exec master..xp_cmdshell 'bcp "select column1,column2 from Test.dbo.Test_Table" queryout "E:\data\2.csv" -c -t"," -r"\n"  -U"sa" -P"123456"' 

 

--用完后,要记得将xp_cmdshell禁用(从安全角度安全考虑)

-- 禁用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO

--重新配置
RECONFIGURE
GO

-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 0
GO

-- 重新配置
RECONFIGURE
GO