高效实施SQLServer和PostgreSQL相互间的导入导出

为什么80%的码农都做不了架构师？>>>   

前提：将要导入或者导出的所有表名字写入一个txt文件

* SQlServer

导出：（注意：tokens=1,2,3，要真正拿到表名）

@echo off
cd /d "%~dp0"
for /f "tokens=1,2,3 delims=." %%i in (tableName.txt) do (
echo %date% %time% start to export %%k from sqlserver >>copy.log
bcp %%i.%%j.%%k out "%%k.csv" -c -t ","  -S ".\Instance名" -T
echo %date% %time% end to export %%k  from sqlserver >>copy.log
echo. >>copy.log
)

导入：批处理写法一样，SQL文【out】变为【】
 

bcp {table名1} in "E:\temp\table1.csv" -c -t ","  -S ".\Instance名" -T
bcp {table名2} in "E:\temp\table2.csv" -c -t ","  -S ".\Instance名" -T
....

* PostgreSQL

导入：

@echo off
cd /d "%~dp0"
rem DB接続情報
set USERID=postgres  
set DBNAME=database名  
set HOST=localhost  
set PORT=5432  

rem ポスグレ接続
echo %date% %time% start to copy >>copy.log
"c:\Program Files\PostgreSQL\9.5\bin\psql.exe" -h %HOST% -p %PORT% -U %USERID% -d %DBNAME% -f copyToPostgreSQL.sql 
echo %date% %time% end to copy >>copy.log

exit

SQL文：copyToPostgreSQL.sql

copy table名1 from 'E:\temp\table1.csv' delimiter ','
copy table名1 from 'E:\temp\table2.csv' delimiter ','
...

导出

批处理一样，SQL文【from】变成【to】

copy table名1 to 'E:\temp\table1.csv' delimiter ','
copy table名2 to 'E:\temp\table2.csv' delimiter ','
...

 

转载于:https://my.oschina.net/mj23/blog/794455