More SQL command comparisons between SQL Server and MariaDB

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

More information:

• New FORMAT and CONCAT Functions in SQL Server 2012
• Concatenate SQL Server Columns into a String with CONCAT()

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

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

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

/*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 @string=@string+',' +@name

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor
select @string

More information:

• SQL Server Cursor Example
• Iterate through SQL Server database objects without cursors
• Run same command on all SQL Server databases without cursors

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;

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:

• Splitting Delimited Strings Using XML in SQL Server

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;

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 '';

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

More information:

• Simple script to backup all SQL Server databases
• All SQL Server Backup Tips

--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

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

More information:

• All SQL Server Restore Tips

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

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

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

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

More information:

• All Database Mail Tips
  

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