想請問我有120個table
我要將其中共同id='1' 的資料匯到其他DB
要如何產生insert sql語法過給其他DB使用?
讓其他使用者執行這串SQL就能成功匯入資料?
已邀請的邦友 {{ invite_list.length }}/5
-- 假設建立三個測試tables
create table ithelp190705a (
id tinyint unsigned not null auto_increment primary key
, val tinyint not null
);
create table ithelp190705b (
id tinyint unsigned not null auto_increment primary key
, txt char(10) not null
);
create table ithelp190705c (
id tinyint unsigned not null auto_increment primary key
, mdate date not null
);
insert into ithelp190705a (val) values
(10), (30);
insert into ithelp190705b (txt) values
('test1'),('test2');
insert into ithelp190705c (mdate) values
('2019-07-05'), ('2019-07-06');
---
mysqldump -u miku1 -p --databases miku1 --tables ithelp190705a ithelp190705b ithelp190705c --where="id=1" > ithelp190705.sql
這樣會依照指定的 database , 此例是 miku1, tables 是指定的三個,若不使用 tables 參數,則是 miku1底下的所有table.
where 就是過濾條件,此例是 id=1. ithelp190705.sql 就是相關的SQL Command了.會有 create table 的DDL,還有
insert into 的Command.
部分內容如下:
--
DROP TABLE IF EXISTS `ithelp190705c`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `ithelp190705c` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`mdate` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ithelp190705c`
--
-- WHERE: id=1
LOCK TABLES `ithelp190705c` WRITE;
/*!40000 ALTER TABLE `ithelp190705c` DISABLE KEYS */;
INSERT INTO `ithelp190705c` VALUES (1,'2019-07-05');
/*!40000 ALTER TABLE `ithelp190705c` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
--
一級屠豬士感謝大大，這就是我要的方法
SELECT * INTO otherDATABASE.NEWTABLE
FROM youtDB.OLDTABLE
WHERE id = '1'
INSERT INTO otherDB.NewTable (field1,field2,field3)
(SELECT field1,field2,field3 FROM oldDB.OldTable WHERE id='1')
120個TABLE...........那就用
DECLARE @sqlCommand as nvarchar(2000)
DECLARE @DBID AS INT
SET @DBID=1
WHILE( @DBID<=120)
BEGIN
SET @sqlCommand = 'INSERT INTO NEWTABLE (FIELD1,FIELD2,FIELD3) (SELECT FIELD1,FIELD2,FIELD3 FROM OLDTABLE' + @DBID + ' WHERE id=''1'')'
EXEC (@sqlCommand)
SET @DBID=@DBID+1
END
從OLDTABLE1~OLDTABLE120拉資料加進NEWTABLE裡去
大大，我要的是產出insert命令
而不是直接過資料
INSERT [table] ([ID], [field2], [field3], [field4], [field5].....])
VALUES (N'1', N'VALUE2', N'VALUE3', N'VALUE4', N'VALUE5',......)
.
.
.
如果是非序數TABLE做名稱
DECLARE @TABLENAME AS NVARCHAR(100)
DECLARE @sqlCommand AS NVARCHAR(2000)
USE oldDB
DECLARE MYCURSOR CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO @TABLENAME
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @sqlCommand = 'INSERT INTO ....(同上例).......'
EXEC (@sqlCommand)
FETCH NEXT FROM MYCURSOR INTO @TABLENAME
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR
iThome鐵人賽
看影片追技術