All geek questions in one place

Import MySQL dump file in MSSQL

Browsermob / Neustar creates a MySQLdump file that you can use to query test load data without sacrificing cloud hours (which are naturally paid). The dump file is a standard dump file, and although I understand that mysqldump has compatible = mssql option , I'm obviously not able to run that myself. Looking at the contents of a file (which is extremely large) using a text editor, it seems to display a series of SQL statements ending with a massive bulk insert. I tried to open the file in Microsoft SQL Management Studio, but it crashed (known error) because the file was too large.

So, does anyone know a tool that will import a MySQL dump file into MsSQL. The only options I can think of is to install MySQL and import the dump file into it instead, and then transfer the data via ODBC, but I really don't want to install MySQL unless I have to.

IMPORTANT: Therefore, to be clear, I have a dump file, nothing else, and I can’t access the database and not restart the dump with any parameters. I need to work with the file that was provided to me.

Any ideas?

The first lines of the file are as follows:

-- MySQL dump 10.13 Distrib 5.1.58, for debian-linux-gnu (i686) -- -- Host: localhost Database: collector -- ------------------------------------------------------ -- Server version 5.1.58-1ubuntu1 /*!40101 SET @ OLD_CHARACTER_SET_CLIENT=@ @CHARACTER_SET_CLIENT */; /*!40101 SET @ OLD_CHARACTER_SET_RESULTS=@ @CHARACTER_SET_RESULTS */; /*!40101 SET @ OLD_COLLATION_CONNECTION=@ @COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @ OLD_TIME_ZONE=@ @TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @ OLD_UNIQUE_CHECKS=@ @UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @ OLD_FOREIGN_KEY_CHECKS=@ @FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @ OLD_SQL_MODE=@ @SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @ OLD_SQL_NOTES=@ @SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `name_value_pairs` -- DROP TABLE IF EXISTS `name_value_pairs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `name_value_pairs` ( `id` int(8) unsigned DEFAULT NULL, `tx_id` char(24) NOT NULL, `step_id` int(8) unsigned DEFAULT NULL, `name` varchar(100) NOT NULL, `value` varchar(100) DEFAULT NULL, KEY `name_value_pairs_id` (`id`), KEY `name_value_pairs_tx_id` (`tx_id`), KEY `name_value_pairs_step_id` (`step_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `name_value_pairs` -- LOCK TABLES `name_value_pairs` WRITE; /*!40000 ALTER TABLE `name_value_pairs` DISABLE KEYS */; /*!40000 ALTER TABLE `name_value_pairs` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `object` -- DROP TABLE IF EXISTS `object`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `object` ( `bytes` int(10) unsigned NOT NULL, `domain` varchar(255) NOT NULL, `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `err_msg` varchar(255) DEFAULT NULL, `host` varchar(255) NOT NULL, `method` varchar(16) DEFAULT NULL, `obj_id` int(8) unsigned NOT NULL, `obj_num` mediumint(4) unsigned NOT NULL, `path` varchar(4096) NOT NULL, `partial_url_md5` varchar(32) NOT NULL, `protocol` varchar(16) NOT NULL, `resolved_ip_addr` varchar(255) DEFAULT NULL, `start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `status_code` smallint(4) NOT NULL, `step_id` int(8) unsigned NOT NULL, `time_active` mediumint(6) unsigned DEFAULT NULL, `blocked_time` mediumint(6) unsigned DEFAULT NULL, `dns_lookup_time` mediumint(6) unsigned DEFAULT NULL, `connect_time` mediumint(6) unsigned DEFAULT NULL, `ssl_handshake_time` mediumint(6) unsigned DEFAULT NULL, `send_time` mediumint(6) unsigned DEFAULT NULL, `time_to_first_byte` mediumint(6) unsigned DEFAULT NULL, `receive_time` mediumint(6) unsigned DEFAULT NULL, `tx_id` char(24) NOT NULL, `url` varchar(4096) NOT NULL, KEY `object_partial_url_md5` (`partial_url_md5`), KEY `object_obj_id` (`obj_id`), KEY `object_obj_num` (`obj_num`), KEY `object_step_id` (`step_id`), KEY `object_tx_id` (`tx_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `object` -- LOCK TABLES `object` WRITE; /*!40000 ALTER TABLE `object` DISABLE KEYS */;

Then a volume insert appears.

+4
source share
2 answers

This information in the file is intended only to delete and recreate the database schema (which I assume you already have), so you probably won't need it.

Most of the inserts are interesting, perhaps you can select this part for each table in different files and load it into MsSQL with BULK INSERT from T-SQL, you can define the separator and other parameters for this BULK, paste it, it must correspond to any format, which you have.

+2
source

This is the official docs of MS docs.

Migrating MySQL to Microsoft SQL Server 2000 http://technet.microsoft.com/en-us/library/cc966396.aspx

-2
source

Source: https://habr.com/ru/post/1395778/

More articles:


All Articles