I want to connect mysql server system from mssql sever system through network because I want to import or export sql server database from mssql server system to mysql server system.
can anybody tell me that how can I do this?
thanks
-
Below link will be helpful [How to export MS SQL database to MYSQL?][1] [1]: stackoverflow.com/questions/3917081/…Prashant16– Prashant162013-06-12 06:27:30 +00:00Commented Jun 12, 2013 at 6:27
-
There is already answer here in StackOverflow stackoverflow.com/questions/3917081/…user2332130– user23321302013-06-12 07:23:32 +00:00Commented Jun 12, 2013 at 7:23
Add a comment
|
2 Answers
You can do this by using Data Loader
1 Comment
Devart
Trial version of this tool has a some limitation when you export data.
Try to use this script, which allows you to import data. You must specify the table name and run the generated code on your MySQL server.
DDL:
CREATE TABLE Labour.Employee
(
EmployeeID INT IDENTITY (1, 1) PRIMARY KEY NOT NULL
, IsMainWorkPlace BIT NOT NULL
, TabelNumber VARCHAR(6) NULL
, PersonID INT NOT NULL
, DepartmentUID UNIQUEIDENTIFIER NOT NULL
, DateIn DATETIME NOT NULL
, DateOut DATETIME NULL
, PositionID INT NULL
, DatePension DATETIME NULL
, IsLabourUnionMember BIT NOT NULL
, FiringReasonID INT NULL
)
Query:
DECLARE
@Table NVARCHAR(50)
, @Schema NVARCHAR(50)
, @ObjectID INT
, @IsImportIdentity BIT = 1
, @IsCreateTable BIT = 1
DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR
SELECT
t.name
, s.name
, t.[object_id]
FROM (
SELECT DISTINCT
t.[schema_id]
, t.[object_id]
, t.name
FROM sys.tables t WITH (NOWAIT)
JOIN sys.partitions p WITH (NOWAIT) ON p.[object_id] = t.[object_id]
WHERE p.[rows] > 0
) t
JOIN sys.schemas s WITH (NOWAIT) ON t.[schema_id] = s.[schema_id]
WHERE t.name IN ('<your table>')
OPEN [tables]
FETCH NEXT FROM [tables] INTO
@Table
, @Schema
, @ObjectID
DECLARE
@Name SYSNAME = N''
, @NewName SYSNAME = N''
, @SQLCreate NVARCHAR(MAX) = N''
, @SQLInsert NVARCHAR(MAX) = N''
, @SQLColumns NVARCHAR(MAX) = N''
, @SQLTinyColumns NVARCHAR(MAX) = N''
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT
@Name = @Schema + N'.' + @Table
, @NewName = N'`' + LOWER(@Schema) + N'`.`' + LOWER(@Table) + N'`'
, @SQLCreate = N''
, @SQLInsert = N''
, @SQLColumns = N''
, @SQLTinyColumns = N''
SELECT
@SQLCreate = N'DROP TABLE IF EXISTS ' + @NewName + N';' + CHAR(13) +
N'CREATE TABLE ' + @NewName + CHAR(13) + N'(' + CHAR(13) + STUFF((
SELECT CHAR(9) + N', `' +
c.name + N'` ' + CASE WHEN t.name = N'uniqueidentifier' THEN 'VARCHAR(36)' ELSE UPPER(t.name) END +
CASE WHEN t.name IN (N'nvarchar', N'varchar', N'nchar', N'char', N'varbinary', N'binary')
THEN N'(' + CASE WHEN c.max_length = -1 THEN N'MAX' ELSE CAST(c.max_length AS NVARCHAR(5)) END + N')'
WHEN t.name IN (N'datetime2', N'time2', N'datetimeoffset')
THEN N'(' + CAST(c.scale AS NVARCHAR(5)) + N')'
WHEN t.name = N'decimal'
THEN N'(' + CAST(c.[precision] AS NVARCHAR(5)) + N',' + CAST(c.scale AS NVARCHAR(5)) + N')'
ELSE N''
END + N' ' +
CASE WHEN c.is_nullable = 0 THEN N'NOT NULL' ELSE N'DEFAULT NULL' END + N' ' +
CASE WHEN c.is_identity = 1 THEN N'AUTO_INCREMENT PRIMARY KEY' ELSE N'' END
+ CHAR(13)
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.system_type_id = t.user_type_id
WHERE c.[object_id] = o.[object_id]
ORDER BY c.column_id
FOR XML PATH(N''), TYPE, ROOT).value(N'root[1]', N'NVARCHAR(MAX)'), 1, 2, CHAR(9) + N' ') + N');'
, @SQLInsert = N'INSERT INTO ' + @NewName + N' (' + STUFF((
SELECT N', `' + c.name + '`'
FROM sys.columns c
WHERE c.[object_id] = o.[object_id]
AND (c.is_identity = 0 OR @IsImportIdentity = 1)
ORDER BY c.column_id
FOR XML PATH(N''), TYPE, ROOT).value(N'root[1]', N'NVARCHAR(MAX)'), 1, 2, N'') + N')'
, @SQLTinyColumns = STUFF((
SELECT N', ' + c.name
FROM sys.columns c
WHERE c.[object_id] = o.[object_id]
ORDER BY c.column_id
FOR XML PATH(N''), TYPE, ROOT).value(N'root[1]', N'NVARCHAR(MAX)'), 1, 2, N'')
, @SQLColumns = STUFF((SELECT + CHAR(13) +
CASE
WHEN t.name = N'uniqueidentifier'
THEN ' + '', '' + ISNULL('''''''' + CAST([' + c.name + '] AS VARCHAR(MAX)) + '''''''', ''NULL'')'
WHEN t.name IN (N'nvarchar', N'varchar', N'nchar', N'char', N'varbinary', N'binary')
THEN ' + '', '' + ISNULL('''''''' + CAST(REPLACE([' + c.name + '], '''''''', '''''''''''' ) AS NVARCHAR(MAX)) + '''''''', ''NULL'')'
WHEN t.name = N'datetime'
THEN ' + '', '' + ISNULL('''''''' + CONVERT(VARCHAR, [' + c.name + '], 120) + '''''''', ''NULL'')'
ELSE
N' + '', '' + ISNULL(CAST([' + c.name + '] AS NVARCHAR(MAX)), N''NULL'')'
END
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.system_type_id = t.user_type_id
WHERE c.[object_id] = o.[object_id]
AND (c.is_identity = 0 OR @IsImportIdentity = 1)
ORDER BY c.column_id
FOR XML PATH(N''), TYPE, ROOT).value(N'root[1]', N'NVARCHAR(MAX)'), 1, 10, N'CHAR(13) + '', ('' +')
FROM sys.objects o
WHERE o.[type] IN (N'V', N'U')
AND o.object_id = @ObjectID
IF @IsCreateTable = 1
PRINT @SQLCreate
ELSE
PRINT N'TRUNCATE TABLE ' + @NewName + N';'
DECLARE @SQL NVARCHAR(MAX) = N'
SET NOCOUNT ON;
DECLARE
@SQL NVARCHAR(MAX) = N''''
, @x INT = 1
, @count INT = (SELECT COUNT(1) FROM ' + @Name + ')
IF EXISTS(
SELECT 1
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(''tempdb..#import'')
)
DROP TABLE #import;
SELECT *, ''RowNumber'' = ROW_NUMBER() OVER (ORDER BY ' + @SQLTinyColumns + N')
INTO #import
FROM ' + @Name + N'
WHILE @x < @count BEGIN
SELECT @SQL = ''VALUES '' + STUFF((
SELECT ' + @SQLColumns + N' + '')''' + N'
FROM #import
WHERE RowNumber BETWEEN @x AND @x + 9
FOR XML PATH(N''''), TYPE, ROOT).value(N''root[1]'', N''NVARCHAR(MAX)''), 1, 2, CHAR(13) + '' '') + '';''
PRINT(''' + @SQLInsert + ''')
PRINT(@SQL)
SELECT @x = @x + 10
END'
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM [tables] INTO
@Table
, @Schema
, @ObjectID
END
CLOSE [tables]
DEALLOCATE [tables]
Output:
DROP TABLE IF EXISTS `labour`.`employee`;
CREATE TABLE `labour`.`employee`
(
`EmployeeID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, `IsMainWorkPlace` BIT NOT NULL
, `TabelNumber` VARCHAR(6) DEFAULT NULL
, `PersonID` INT NOT NULL
, `DepartmentUID` VARCHAR(36) NOT NULL
, `DateIn` DATETIME NOT NULL
, `DateOut` DATETIME DEFAULT NULL
, `PositionID` INT DEFAULT NULL
, `DatePension` DATETIME DEFAULT NULL
, `IsLabourUnionMember` BIT NOT NULL
, `FiringReasonID` INT DEFAULT NULL
);
INSERT INTO `labour`.`employee` (`EmployeeID`, `IsMainWorkPlace`, `TabelNumber`, `PersonID`, `DepartmentUID`, `DateIn`, `DateOut`, `PositionID`, `DatePension`, `IsLabourUnionMember`, `FiringReasonID`)
VALUES
(165515, 0, '65515', 183994, '9DA9EEC9-F2EC-4FC7-8F21-DF646FDF397E', '1994-03-06 00:00:00', NULL, 35125, '2001-05-01 00:00:00', 1, NULL)
, (165516, 1, '65516', 184184, '9DA9EEC9-F2EC-4FC7-8F21-DF646FDF397E', '1994-12-09 00:00:00', NULL, 35125, NULL, 1, NULL)
, (165517, 1, '65517', 185214, '0BF3F7C4-2590-40D0-880D-923C8D8339A7', '1953-11-02 00:00:00', '2002-10-05 00:00:00', 35125, '1974-08-06 00:00:00', 1, NULL)
, (165518, 1, '65518', 185536, '2CE8E026-FE5B-4BDD-9330-43AB43CE3DE0', '2004-07-12 00:00:00', '2004-12-01 00:00:00', 35129, '2003-08-06 00:00:00', 0, NULL)
, (165519, 1, '65519', 185596, '2CE8E026-FE5B-4BDD-9330-43AB43CE3DE0', '2004-11-17 00:00:00', NULL, 35129, NULL, 0, NULL)
, (165520, 1, '65520', 184912, '0BF3F7C4-2590-40D0-880D-923C8D8339A7', '1988-10-22 00:00:00', '2003-03-05 00:00:00', 35120, NULL, 1, NULL)
, (165521, 1, '65521', 184984, '1D07CF00-A7FA-489D-9D58-286C7E24A7A2', '2001-04-06 00:00:00', NULL, 35120, NULL, 1, NULL)
, (165522, 1, '65522', 185008, '896681F3-B452-410E-AD4A-97DCF4FE6722', '1994-05-11 00:00:00', NULL, 35120, NULL, 1, NULL)
, (165523, 0, '65523', 183993, 'DC5106A4-3649-4C12-ABF5-03CE21EC1679', '1994-05-06 00:00:00', NULL, 35124, NULL, 1, NULL)
, (165524, 1, '65524', 184146, 'A603E966-6F85-4FC1-BA06-40E391C7218E', '1995-03-22 00:00:00', NULL, 35111, NULL, 1, NULL);