mssql大数据高效率导入mysql

@echo off

::echo 该操作会覆盖原有数据 且会执行很长时间 按任意键将继续执行

::pause>nul

::echo delete from qqinfo..qqinfo>1.sql

del /q 1.sql

setlocal Enabledelayedexpansion

set  p=0

for /l %%i in (1,1,11) do (

        for /l %%j in (1,1,100) do (

                set /a p=!p!+1

                echo INSERT INTO qqinfo.dbo.qqinfo^([QQNum],[Nick],[QunNum]^) SELECT [QQNum],[Nick] ,[QunNum] FROM GroupData%%i.dbo.Group!p!>>1.sql

                echo go>>1.sql

                )

        

        )

 

osql -E -i 1.sql

echo 执行完毕！

bcp "SELECT [QQNum],[Nick] ,[QunNum] FROM qqinfo.dbo.qqinfo"  queryout qun.txt -c -S127.0.0.1 -Usa -Psasa

@echo off

::延时脚本

IF EXIST time.vbs @del /q /s time.vbs

 

@echo wscript.sleep 1000>time.vbs

 

 

::创建数据库和表脚本

 

IF EXIST tables.sql @del /q /s tables.sql

for /l %%i in (1,1,15) do (

echo create table if not exists qqinfo_%%i  ^(^`ID^` int^(11^) NOT NULL AUTO_INCREMENT,^`QQNum^` int^(11^) NOT NULL,^`Nick^` char^(20^) ,^`QunNum^` int^(11^) ,PRIMARY

 

KEY ^(^`ID^`^),KEY ^`inx_qqinfo^` ^(^`QQNum^`,^`QunNum^`^) ^) ENGINE=MyISAM DEFAULT CHARSET=utf8^;>>tables.sql

)

 

 

::创建导入数据脚本

setlocal EnableDelayedExpansion

set  num=0

set p=H:/mysql/data/tmp/

IF EXIST load.sql @del /q /s load.sql

for /r %%i in (qun_a*.txt) do (

set /a num=!num!+1

echo load data infile ^'!p!%%~nxi^' into table qqinfo_2.qqinfo_!num! fields terminated by ^':^' ^(ID,QQNum,Nick,QunNum^)^;>>load.sql

)

 

::创建数据库

mysql -u root -p123456 -e "create database if not exists `qqinfo_2` DEFAULT CHARACTER SET gbk;"

echo 正在创建表...

@cscript.exe //nologo time.vbs

mysql -u root -p123456 qqinfo_2 < tables.sql

@cscript.exe //nologo time.vbs

echo 创建完成...

 

mysql -u root -p123456 -e "show tables;" qqinfo_2

 

@cscript.exe //nologo time.vbs

cls

echo 导入数据 ...

echo 可能需要很久 请耐心等待...

@cscript.exe //nologo time.vbs

 

mysql -u root -p123456 qqinfo_2 < load.sql

 

cls

echo 导入成功!

@del /q /s time.vbs

@del /q /s tables.sql

@del /q /s load.sql

pause>nul