mssqltips logo

More SQL command comparisons between SQL Server and MariaDB

By:   |   Updated: 2015-02-27   |   Comments   |   Related: More > Other Database Platforms

Problem

In part 1 and part 2 of this series, I compared SQL commands for MariaDB and SQL Server.  In this tip, we will show more examples such as concatenation, cursors, how to split comma separated values, how to import xml data and more.

Solution

In this tip we will look at various things you do in a database and the differences between SQL Server and MariaDB.

Requirements and setup

  1. SQL Server 2014 or earlier versions.
  2. The MariaDB should be installed. In this tip, I installed MariaDB and the SQL Server on the same Windows OS. You can find the MariaDB installer here.

Code Comparison for SQL Server vs. MariaDB 

How to concatenate strings

SQL Server

MariaDB

select concat('This is the way to','concatenate')

More information:

select concat('This is the way to','concatenate')

How to concatenate strings with numbers

SQL Server

MariaDB

select concat('This is the way to concatenate the number ',1)

select concat('This is the way to concatenate the number ',1);

How to work with cursors

SQL Server

MariaDB

/*The example creates a table with names and the cursor concatenates all the names of each row in a single row. Note that in SQL Server the use of cursors is not recommended for performance reasons.*/

create table name(name varchar(30));

insert into name values('John'),
('Peter'),('Jake')

DECLARE db_cursor CURSOR FOR
SELECT name
FROM name

DECLARE @name VARCHAR(100),@string VARCHAR(100)=''

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @[email protected]+',' [email protected]

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
select @string

 

More information:

 

create table name(name varchar(30));

insert into name values('John'),
('Peter'),('Jake')

DELIMITER $$

CREATE PROCEDURE p1 (INOUT string varchar(3000))
BEGIN

DECLARE counter INTEGER DEFAULT 0;
DECLARE namev varchar(100) DEFAULT "";

DECLARE db_cursor1 CURSOR FOR
SELECT name FROM name;

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET counter = 1;

OPEN db_cursor1;

get_names: LOOP

FETCH db_cursor1 INTO namev;

IF counter = 1 THEN
LEAVE get_names;
END IF;
SET string = CONCAT(namev,",",string);

END LOOP get_names;

CLOSE db_cursor1;

END$$

DELIMITER ;

SET @string = "";
CALL p1(@string);
SELECT @string;

 

How to splits rows with values separated by comas in multiple rows

SQL Server

MariaDB

Create table test(name varchar(2000));

insert into test values('Dennis,Joseph,Joel'),
('Dino,Peter');

SELECT
Split.n.value('.', 'VARCHAR(100)') AS String
FROM (SELECT
CAST ('' + REPLACE([name], ',', '') + '' AS XML) AS String
FROM test) AS n CROSS APPLY String.nodes ('/M') AS Split(n);

 

More information:

Create table test(name varchar(2000));

insert into test values('Dennis,Joseph,Joel'),
('Dino,Peter');

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.name, ',', n.n), ',', -1) name
FROM test t CROSS JOIN
(
SELECT a.N + b.N * 10 + 1 n
FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n
) n
WHERE n.n <= 1 + (LENGTH(t.name) - LENGTH(REPLACE(t.name, ',', '')))
ORDER BY name;

How to import data from a xml file to a table

SQL Server

MariaDB

In SQL Server it is harder to do than using MariaDB. For a complete tutorial, read our article: Importing and Processing data from XML files into SQL Server tables.

create table mssqltips
(name varchar(30),
lastname varchar(50));






LOAD XML INFILE 'filexml2.xml'
INTO TABLE mysql.mssqltips
ROWS IDENTIFIED BY '';

How to backup a database

SQL Server

MariaDB

BACKUP DATABASE database-name TO DISK = 'c:\SQL\backup-file.bak'

More information:

--Exit the mysql and in the mysql bin folder, execute this commands(the mysqldump command is used):

mysqldump -u loginname -p database_name > backup-file.sql

-- You will be prompted to enter your password

How to restore a database

SQL Server

MariaDB

RESTORE DATABASE database-name    
FROM DISK = 'c:\SQL\backup-file.bak'

More information:

mysqldump -u root -p mysql < backup-file.sql

How to backup all the databases

SQL Server

MariaDB

You can use the Maintenance Wizard, the SSIS Back UP Task or this article for a T-SQL solution:

Simple script to backup all SQL Server databases

mysqldump -u root -p --all-databases > all_databases.sql

How to send emails

SQL Server

MariaDB

EXEC msdb.dbo.sp_send_dbmail     
@profile_name = 'MyCustomProfile',     
@recipients = '[email protected]',     
@body = 'This is a test.',     
@subject = 'simple test' ;

More information:

By default, it is no longer supported. You may need to use other programming tools to integrate this functionality.

Next Steps

Read Part 1 and Part 2 of this series

For more information about T-SQL and MariaDB, refer to the following links:



Last Updated: 2015-02-27


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools