Import database (SQL file) in SQL Server Management Studio

Question

I've created the structure of my database first in PhpMyAdmin and exported it to a .sql file. Now I'm looking everywhere in SQL Server Management Studio where I can import/add the data in a new database.

Does anybody where to look or what to click? I'm using the 2014 version (CTP2)

Pled

Connect to your server, then Menu > File > Open > File..., pick your .sql file and then run it - doesn't that work? – Lydell

I get some syntax errors, but that's weird, because it's just a generated sql file, not a self made one. And I haven't edited it myself – Pled

But it's generated by MySQL which is not 100% compatible with T-SQL/SQL Server ... all SQL's aren't created entirely identical .... – Lydell

If you are trying to use a .sql file, this answer will be helpful! – Onfre

If its a large sql file, checkout this solution #432413 – Lycanthrope

If it is a full structure of DB, then you can simply copy-paste everything to studio from that file and run it. You don't need special tools to do it. – Ripe

Answer 1

If you have a .sql file which contains SQL statements, you can just copy and paste the contents (or open the file in a query window) and run it. This assumes it has all of the create table etc. statements to create the schema/structure and not just insert statements for the data.

Check the top of the file to make sure that it is first selecting the correct database, if not add a USE statement to select the correct database.

You didn't say how big the file was, but if it is quite large and has the insert statements (data as well as schema), then you'll probably want to run by CLI using sqlcmd command. Much faster and SSMS won't freak out.

Another alternative option to running the .sql file/code is to set up a data source for mysql and just use odbc to access the database itself.

Bear in mind that there are real and very annoying differences between mysql and t-sql that can make migration a pain. If you're just creating a few tables, it may not be an issue, but if there are a ton of tables with lots of fields of different data types, you may run into issues.

Rudolf

I'd love to know why my answer was downvoted 2 years later with no explanation. Any particular reason, downvoter? – Rudolf

Answer 2

If you are looking to import table structure, you can copy-paste the content and run inside SSMS in a query window. Beware of syntax differences with MySQL and SQL Server. You will most likely get errors. You need to convert your SQL script from MySQL dialect to SQL Server dialect (or just add them manually if they are not too many). If you set the databases to a SQL standard-compatibility mode at the very beginning, you will have much less trouble.

If you are ONLY looking just to import the data into existing tables inside the SQL Server only, you can do the same (i.e. copy-paste and run in query window). You will have less trouble with that.

Hippodrome

Answer 3

Open the server, open "Databases" and right click the database, go to "Tasks" and then Import Data...

Ephah

I've made it that far, but what option do I choose at "Data source"? The most logical one is "SQL Server Native Client 11.0" but that requires another database – Pled

I found this - not sure if it will help you: #7828560 – Ephah

Answer 4

I have had the most 'trouble free' success importing to SQL via a flat file method (comma delimited .txt file), the only stipulation when creating a flat file (i.e from Access) make sure the text identifier is set to {none} and not "". To import the file: in the SQL Server Management Studio right click on Databases and create a new database. Then right click on the new database -> Tasks -> Import Data... The import window opens: in the DATA SOURCE option select Flat File Source and select the .txt file...click NEXT. In the DESTINATION field select SQL Server Native Client 11.0 and go through the import process. This worked very well for me.

Embitter