node从SQLSERVER转移数据到MYSQL
nodejs想要从sqlserver转移数据到mysal,需要连接两个数据库
这里整合了sqlserver参数但未连接
const mysql = require('mysql');
var mssql = require('mssql');
const moment = require('moment');
var config = {
user: 'sa',
password: 'abcd',
server: 'yanhaiserver',
database: 'lyb',
port:1433,
options: {
encrypt: false // Use this if you're on Windows Azure
},
pool: {
min: 0,
max: 10,
idleTimeoutMillis: 3000
}
};
// var sql = 'select count(infoid) from c_info';
var sql = 'select * from c_info t';
var connection = mysql.createConnection({
host : '127.0.0.1',
user : 'root',
password : '1234',
port: '3306',
database: 'phpcmsv9',
//multipleStatements: true //返回的值为key/value
});
因为我写的是个脚本,下面直接连接开始转移了。
/**
* 文章类模型迁移
*/
for(let j=0;j<C_CFG.length;j++){
let moudleid=C_CFG[j].moudleid;
let nsql=sql+' where t.Moduleid='+moudleid;
// console.log(nsql);
mssql.connect(config).then(function (data) {
let req = new mssql.Request().query(nsql).then(function (recordset) {
// console.log('数据数据',recordset);
if(recordset&&recordset.recordset){
var param=[],dataparam=[];
let data=recordset.recordset;
for(let i=0;i<data.length;i++){
var index=it+i;
// 0, 99, 1, 0, 'admin', 1584523391, 1584690798, NULL, NULL, NULL)
var val=[],dataval=[];
//第一张表数据
val.push(index);
val.push(C_CFG[j].catid);
val.push(0);
val.push(data[i].Title);
val.push('');
val.push('');
val.push(data[i].KeyWords);
val.push(data[i].Description);
val.push(0);
val.push('http://localhost:8892/install_package/index.php?m=content&c=index&a=show&catid='+C_CFG[j].catid+'&id='+index);
val.push(0);
val.push(99);
val.push(1);
val.push(data[i].IsExtURL);
val.push('admin');
val.push(parseInt(data[i].CreateDate.getTime()/1000));
val.push(0);
val.push(0);
val.push(data[i].ShortTitle);
val.push(0);
param.push(val); //第一张表数据参数组
//第二张表数据
//, 0, '', 0, 10000, '', 0, '', 0, 1, '第一旅游网|0');
dataval.push(index);
dataval.push(data[i].Content);
dataval.push(0);
dataval.push('');
dataval.push(0);
dataval.push(10000);
dataval.push('');
dataval.push(0);
dataval.push('');
dataval.push(0);
dataval.push(1);
dataval.push(data[i].Source);
dataparam.push(dataval);//第二张表数据参数组
}
it+=data.length;
var query = connection.query(adds,[param],function (err, result) {
if(err){
console.log('[INSERT ERROR1] - ',err.message);
return;
}
// console.log('--------------------------INSERT----------------------------');
console.log('INSERT ID1:',result);
console.log('111-----------------------------------------------------------------\n\n');
});
// console.log('query',query);
var dquery = connection.query(adddata,[dataparam],function (err, result) {
if(err){
console.log('[INSERT ERROR2] - ',err.message);
return;
}
// console.log('--------------------------INSERT----------------------------');
console.log('INSERT ID2:',result);
console.log('222-----------------------------------------------------------------\n\n');
});
}else{
}
}).catch(function (err) {
console.log('222出错了',err);
});
}).then(result => {
// var param=[],dataparam=[];
}).catch(function (err) {
console.log('333出错了',err);
});
}
接下来附上完整的代码
/**
* 常量
*/
var C_CFG=[
{name:'高层',moudleid:10850,catid:15},{name:'行业',moudleid:10869,catid:16},
{name:'地方',moudleid:10865,catid:17},{name:'国际',moudleid:13555,catid:18},
{name:'专家',moudleid:10864,catid:19},{name:'创优',moudleid:13556,catid:20},
{name:'人才',moudleid:13557,catid:152},{name:'文创',moudleid:13558,catid:153},
{name:'公益',moudleid:13559,catid:154},{name:'专题',moudleid:6974,catid:155},
{name:'创投',moudleid:13561,catid:156},{name:'装备',moudleid:13562,catid:157},
{name:'线路',moudleid:13563,catid:158},{name:'美食',moudleid:13564,catid:159},
{name:'民宿',moudleid:13565,catid:160},{name:'特产',moudleid:13566,catid:161},
// {name:'直播',moudleid:7585,catid:162},{name:'旅图',moudleid:7006,catid:163},,{name:'云视频',moudleid:13582,catid:165}
{name:'大数据',moudleid:13560,catid:164}
// 高层=10850,行业=10869,地方=10865,国际=13555,专家=10864,
// 创优=13556,人才=13557,文创=13558,公益=13559,专题=6974,
// 创投=13561,装备=13562,线路=13563,美食=13564,民宿=13565,
// 特产=13566,直播=7585,旅图=7006,大数据=13560,云视频=13582
//10850,10869,10864,10865,13555,13556,13557,13558,13559,6974,13561,13562,13563,13564,13565,13566,7585,7006,13560,13582
];
//常量结束
const mysql = require('mysql');
var mssql = require('mssql');
const moment = require('moment');
var config = {
user: 'sa',
password: 'abcd',
server: 'yanhaiserver',
database: 'lyb',
port:1433,
options: {
encrypt: false // Use this if you're on Windows Azure
},
pool: {
min: 0,
max: 10,
idleTimeoutMillis: 3000
}
};
// var sql = 'select count(infoid) from c_info';
var sql = 'select * from c_info t';
var connection = mysql.createConnection({
host : '127.0.0.1',
user : 'root',
password : '1234',
port: '3306',
database: 'phpcmsv9',
//multipleStatements: true //返回的值为key/value
});
//批量添加
// var userAddSql = 'INSERT INTO t_test_oracle(Name,Age) VALUES ? '; //最后是"?" 不是 "(?,?)"
var adds = "INSERT INTO `v9_news`(`id`,`catid`, `typeid`, `title`, `style`, `thumb`, `keywords`, `description`, `posids`, `url`, `listorder`, `status`, `sysadd`, `islink`, `username`, `inputtime`, `updatetime`, `shortdate`, `short_title`, `user_id`) VALUES ?"; //最后是"?" 不是 "(?,?)"
var adddata = "INSERT INTO `v9_news_data`(`id`,`content`, `readpoint`, `groupids_view`, `paginationtype`, `maxcharperpage`, `template`, `paytype`, `relation`, `voteid`, `allow_comment`, `copyfrom`) VALUES ?"; //最后是"?" 不是 "(?,?)"
var it=1;
/**
* 文章类模型迁移
*/
for(let j=0;j<C_CFG.length;j++){
let moudleid=C_CFG[j].moudleid;
let nsql=sql+' where t.Moduleid='+moudleid;
// console.log(nsql);
mssql.connect(config).then(function (data) {
let req = new mssql.Request().query(nsql).then(function (recordset) {
// console.log('数据数据',recordset);
if(recordset&&recordset.recordset){
var param=[],dataparam=[];
let data=recordset.recordset;
// console.log('888888',data[0].CreateDate.getTime()/1000);
// console.log('9999',new Date('2015-06-06').getTime());
for(let i=0;i<data.length;i++){
var index=it+i;
// 0, 99, 1, 0, 'admin', 1584523391, 1584690798, NULL, NULL, NULL)
var val=[],dataval=[];
val.push(index);
val.push(C_CFG[j].catid);
val.push(0);
val.push(data[i].Title);
val.push('');
val.push('');
val.push(data[i].KeyWords);
val.push(data[i].Description);
val.push(0);
val.push('http://localhost:8892/install_package/index.php?m=content&c=index&a=show&catid='+C_CFG[j].catid+'&id='+index);
val.push(0);
val.push(99);
val.push(1);
val.push(data[i].IsExtURL);
val.push('admin');
val.push(parseInt(data[i].CreateDate.getTime()/1000));
val.push(0);
val.push(0);
val.push(data[i].ShortTitle);
val.push(0);
param.push(val);
//data数据
//, 0, '', 0, 10000, '', 0, '', 0, 1, '第一旅游网|0');
dataval.push(index);
dataval.push(data[i].Content);
dataval.push(0);
dataval.push('');
dataval.push(0);
dataval.push(10000);
dataval.push('');
dataval.push(0);
dataval.push('');
dataval.push(0);
dataval.push(1);
dataval.push(data[i].Source);
dataparam.push(dataval);
}
it+=data.length;
var query = connection.query(adds,[param],function (err, result) {
if(err){
console.log('[INSERT ERROR1] - ',err.message);
return;
}
// console.log('--------------------------INSERT----------------------------');
console.log('INSERT ID1:',result);
console.log('111-----------------------------------------------------------------\n\n');
});
// console.log('query',query);
var dquery = connection.query(adddata,[dataparam],function (err, result) {
if(err){
console.log('[INSERT ERROR2] - ',err.message);
return;
}
// console.log('--------------------------INSERT----------------------------');
console.log('INSERT ID2:',result);
console.log('222-----------------------------------------------------------------\n\n');
});
}else{
}
}).catch(function (err) {
console.log('222出错了',err);
});
}).then(result => {
// var param=[],dataparam=[];
}).catch(function (err) {
console.log('333出错了',err);
});
}
//关闭链接
// connection.end();