SQL Server批量数据导出导入BCP&Bulk使用

时间:2021-12-21 13:26:48

数据导出导入,首先考虑使用什么技术实现导出与导入
利用BCP结合Bulk技术实现数据的导出与导入

  • 1.bcp数据导出(这里是命令行方式),导出的数据需是格式化的,有两种方式可选
    • a.对传输的数据格式要求宽松,无需使用约束格式的文件,使用BCP技术直接导出指定分隔符的文本文件,如csv

     导出数据示例

bcp "SELECT * from updext.dbo.ext_Users where 1>=1" queryout "c:\a.csv" -c -t"0x0b" -r"\n" -S".\SQLSERVER2012" -U"user" -P"password"

    • b.数据格式要求较高,需要专门导出格式文件,用于导入数据时使用

先导出数据格式文件
bcp "updext.dbo.ext_users" format null -c -t"0x0a" -x -f "c:\data.fmt" -r"\n" -S".\SQLSERVER2012" -U"username" -P"password"

再导出数据文件,导入导出往往用于系统间数据同步,因此考虑使用SQL查询queryout

  • 若需要按数据格式文件导出数据

bcp "SELECT * FROM updext.dbo.ext_users WHERE changetime<GETDATE()" queryout "c:\DATA1.csv" -f "c:/data.fmt" -t"0x0a" -r"\n" -S".\SQLSERVER2012" -U"username" -P"password"

  • 若自定义数据分隔符

bcp "SELECT * FROM updext.dbo.ext_users WHERE changetime<GETDATE()" queryout "c:\DATA1.csv" -c -t"0x0a" -r"\n" -S".\SQLSERVER2012" -U"username" -P"password"

 

  • 2.导入数据
    • 如果是整体数据导入

BULK INSERT updext.dbo.ax--(UserId,UserNo,UserName,PassWord,Email,Code,IsValid,ChangeTime)
FROM 'c:\DATA1.csv'
WITH(
--KEEPIDENTITY,
--FIELDTERMINATOR ='0x0a',--避免使用逗号分隔,内容中出现逗号的概率太大
--ROWTERMINATOR ='\n',
--FIRSTROW = 1
FORMATFILE = 'c:\data.fmt'
)

    • 如果只需要导入部分数据

INSERT INTO updext.dbo.ax(UserId,UserNo,UserName,PassWord,Email,Code,IsValid,ChangeTime)
SELECT UserId,UserNo,UserName,PassWord,Email,Code,IsValid,ChangeTime
          FROM OPENROWSET(BULK N'c:\DATA1.csv', FORMATFILE=N'c:\data.fmt') a
                 WHERE ChangeTime<'2017-01-06 12:02:31.067'