Member Avatar for asmikwen

Hi,

I am trying to transfer some data from a Mysql database onto a Microsoft SQL database. I would need your help on how to operate this migration as I am new to MSSQL. I have been looking on Google but there are not a lot of pages on that. Can someone help me?

Thanks.

  • 3 Contributors
  • 3 Replies
  • 304 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by bgkalbente

Dani AI

Generated

Quick summary and practical options for : ’s ODBC/OPENROWSET approach is a good quick way to pull rows directly when you need an ad‑hoc move; is right that ETL tools are better for repeatable, transform-heavy jobs. For a reliable, controllable migration you’ll usually pick one of three paths: 1) export/import flat files (CSV) and BULK INSERT/BCP into SQL Server, 2) build an SSIS (Import/Export Wizard) package for transformations and logging, or 3) use an ETL product if you need heavy cleansing and monitoring.

A compact file-based workflow (safe for large tables):

  • Create a consistent export on MySQL (use a snapshot or lock depending on engine).
  • Export to CSV (example MySQL SELECT ... INTO OUTFILE):
    SELECT col1, col2
INTO OUTFILE '/tmp/mytable.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM mydb.mytable;
  • Copy CSV to the SQL Server host and load with BULK INSERT:
    BULK INSERT dbo.mytable
FROM 'C:\import\mytable.csv'
WITH (FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', KEEPIDENTITY, TABLOCK);

Key checklist and cautions before you run a full load: map types (AUTO_INCREMENT → IDENTITY, TINYINT(1) → BIT, ENUM → VARCHAR), plan for character-encoding (import into NVARCHAR or handle UTF‑8), handle NULLs/defaults, and watch DATETIME/TIMESTAMP/timezone semantics. For big loads, drop/disable nonclustered indexes and FK checks, bulk load, then rebuild constraints and indexes. Use small test runs and compare row counts and checksums before/after.

Troubleshooting tips: capture BULK INSERT error rows with ERRORFILE, monitor transaction log growth (use batching), and preserve referential integrity with staged loads. For one-off moves try ’s direct approach; for recurring or complex projects invest in SSIS or an ETL tool as suggested.

Recommended Answers

All 3 Replies

Member Avatar for achied

Hi..

i have test it before, i was trying to transfer data from mysql to SQL.Sever. i was using MSDASQL Provider, it's an OLEDB provider for ODBC.
1.First of all, you have to install odbc driver for My.SQL. So you can access MySQL through ODBC provider.
2. After that you create a DSN in Data Sources through [Control panel]>[Administrative tools]>[Data Sources(ODBC)]
3. after you've created dsn, then you can access it trough OPENROWSET (The one that i was used) or BULK INSERT statement.

for example, i was trying to import data from My SQL to SQL Sever. The database is Northwind (SQL Server). I've created a DSN called Data_Karyawan.

INSERT INTO Northwind.dbo.Customers(CustomerID,CompanyName,ContactTitle,
	Address,City,Region,PostalCode,Country,Phone,Fax)
SELECT CustomerID,CompanyName,ContactName,Address,City,Region,PostalCode,Country,Phone,Fax
	FROM OPENROWSET(
	'MSDASQL',
	'DSN=Data_Karyawan',
	'Select * FROM Karyawan'
	);

it worked like a charm

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.