14

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)

jarlh
  • 42,561
  • 8
  • 45
  • 63
Matt
  • 1,893
  • 11
  • 33
  • 57
  • 3
    Connect to your server, then `Menu > File > Open > File...`, pick your `.sql` file and then run it - doesn't that work? – marc_s Mar 18 '14 at 19:27
  • 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 – Matt Mar 18 '14 at 19:32
  • 3
    But it's generated by **MySQL** which is not **100% compatible** with T-SQL/SQL Server ... all SQL's aren't created entirely identical .... – marc_s Mar 18 '14 at 19:48
  • If you are trying to use a `.sql` file, [this answer](https://stackoverflow.com/a/7828099/7196681) will be helpful! – Curiosity Sep 20 '17 at 04:31
  • If its a large sql file, checkout this solution https://stackoverflow.com/questions/431913/how-do-you-import-a-large-ms-sql-sql-file – Dan Csharpster Nov 22 '17 at 20:24
  • 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. – Alex Dec 21 '17 at 14:06

4 Answers4

2

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.

a lead alcove
  • 305
  • 1
  • 14
0

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.

K4M
  • 1,030
  • 3
  • 11
-1

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

SCJohnson243
  • 141
  • 5
  • 1
    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 – Matt Mar 18 '14 at 19:25
  • I found this - not sure if it will help you: http://stackoverflow.com/questions/7828060/how-do-i-import-a-sql-data-file-into-sql-server – SCJohnson243 Mar 18 '14 at 19:37
-1

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.

CadSquad
  • 39
  • 4