适用于文档解析,定时备份等工作;
一、bcp语句规则说明
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]
二、数据库表数据导出bcp文档
exec master.dbo.xp_cmdshell ‘bcp TestDatabase.dbo.myNative OUT D:\BCP\myNative.bcp -T -n’
三、bcp文档导入数据库表
bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -T -n
四、非 XML 格式化文件导入(需用fmt格式的中间说明文档)
1、fmt文档说明 示例如下
12.0
4
1 SQLSMALLINT 0 2 "" 1 DepartmentID ""
2 SQLNCHAR 2 100 "" 2 Name SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 100 "" 3 GroupName SQL_Latin1_General_CP1_CI_AS
4 SQLDATETIME 0 8 "" 4 ModifiedDate ""
格式说明:
2、导出
exec master.dbo.xp_cmdshell ‘bcp TestDatabase.dbo.myNative IN D:\BCP\myNative.bcp -f D:\BCP\myNative.fmt -T’
3、导入
exec master.dbo.xp_cmdshell ‘bcp TestDatabase.dbo.myNative OUT D:\BCP\myNative.bcp -f D:\BCP\myNative.fmt -T’
只能是本机的导入导出,期待下次更新吧!