Tag Archives: mysql

mysqldump equivalent in SQL Server

This is frustrating for a mysql user to take a database dump when they try to do the same in sql server. Here is the one of the optimal way for a mysql user working for sql server.

Right Click on database -> Tasks -> Generate Scripts..
Next -> Select the database name from a list
Under advanced scripting option-> Types of data to Script
You have 3 options to be selected here

  1. Schema only
  2. Data only
  3. Schema and data

sqldump
Later on give the path and file name for generating the script.

Now question is how to run this script?

Two ways:
1. run the below command at DOS command prompt

OSQL -U <userName> -P <password> -S <serverName> -i  <completepathSQLFile>

OR
2. Open the generated script file in a query window and just run them in one go. Just like you run a simple query. But you are advised to do provided its a small sized file.

MySQL Query for a Programmer

I am writing some useful queries collected in past specially when I was working with Ruby & Java projects.
They are more than SIMPLE and lesser than COMPLEX and often used by a programmer. This post is more for my self reference. Good if somebody else is benefited…
To make it simple, I have wrote them with examples only rather than having a complete syntax.
You can do a browser search(Ctrl+F) within this post based on few keywords:
mysqldump, alter, add index, add column, csv to database, group concat, rename etc.

1. Copy a table : Copies a table along with data structure and data into another table:
create table trackedi_duplicate as select * from trackedi

2. Insert multiple rows using single query
INSERT INTO “table1” (“column1”, “column2”)
SELECT “column1”, “column2” FROM table2

3. Repeating the same character:
SELECT REPEAT(‘a’,5)
Outputs aaaaa

4. Trimming the \n from a long description like column
select trim(both ‘\n’ from description) from base;

5. Position of a char or string in a column :  Give me all the rows containing the word “pro” in status column
select * from trackedi where LOCATE(‘pro’,status) > 0

6. Size of each table
SELECT TABLE_SCHEMA AS ‘Database’, TABLE_NAME AS ‘Table’,
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH – DATA_FREE) / 1024 / 1024),15),” Mb”) AS Size FROM INFORMATION_SCHEMA.TABLES;

7. Loading data from CSV to a table
LOAD DATA LOCAL INFILE ‘C:\\dbs_dump\\mytable1.csv’ INTO TABLE myTable1 FIELDS  TERMINATED BY ‘,’ LINES TERMINATED BY ‘\r\n’;

8. Dump a database
mysqldump -u root -p testDb  > c:\dbs_dump\testDb.sql

9. Dump few tables only
mysqldump -u root -p testDb table1 table2 > c:\dbs_dump\fewTables.sql // table structure along with data
mysqldump -u root -p -d testDb table1  table2 > c:\fewTablesStructure.sql // table structure without data

 10. Storing/Running a sql script – Make sure that you are at the prompt of mysql
mysql> source fewTables.sql

11. Renaming a table
rename table analytical_charts to data_charts;

 12. Dropping/Adding a foreign key
alter table data_analysis drop foreign key chart_id;
alter table data_analysis add foreign key(`chart_id`) REFERENCES `data_charts` (`id`);

13. Adding a new column
alter table data_charts add column `chart_type` varchar(80);

 14. Adding a new index
alter table data_charts add index `chart_type` (`chart_type`);

15. Updating a column by string replacement
update [table_name] set [field_name] = replace([field_name],'[string_to_find]’,'[string_to_replace]’);
update myTable set myColm1 = replace(address,’\”,”);

16. Group Concat – Give me the list of all names grouped by status – MySQL specific, you don’t have in SQLServer
SELECT status, GROUP_CONCAT(name) FROM trackedi group by status