Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I import a sql data file into SQL Server?

I have a .sql file and I am trying to import it into SQL Server 2008. What is the proper way to do this?

like image 641
Amen Ra Avatar asked Oct 19 '11 21:10

Amen Ra


People also ask

How do I open a SQL file in SQL?

In the Connect to Server dialog box, in Server name, select the name of your SQL server instance, and then click Connect. In Microsoft SQL Server Management Studio, on the menu, select File > Open > File. In the Open File dialog box, browse for the script file, and then click OK.

How do I import a large SQL file into SQL Server?

Here comes in handy the sqlcmd command line tool. It can import large . sql files, can be run from a batch script, installer, etc. How to use sqlcmd command-line tool to import large .


2 Answers

If your file is a large file, 50MB+, then I recommend you use sqlcmd, the command line utility that comes bundled with SQL Server. It is easy to use and it handles large files well. I tried it yesterday with a 22GB file using the following command:

sqlcmd -S SERVERNAME\INSTANCE_NAME -i C:\path\mysqlfile.sql -o C:\path\output_file.txt 

The command above assumes that your server name is SERVERNAME, that you SQL Server installation uses the instance name INSTANCE_NAME, and that windows auth is the default auth method. After execution output.txt will contain something like the following:

... (1 rows affected) Processed 100 total records  (1 rows affected) Processed 200 total records  (1 rows affected) Processed 300 total records ... 

use readfileonline.com if you need to see the contents of huge files.

UPDATE

This link provides more command line options and details such as username and password:

https://dba.stackexchange.com/questions/44101/importing-sql-server-database-from-a-sql-file

like image 95
oabarca Avatar answered Sep 28 '22 02:09

oabarca


If you are talking about an actual database (an mdf file) you would Attach it

.sql files are typically run using SQL Server Management Studio. They are basically saved SQL statements, so could be anything. You don't "import" them. More precisely, you "execute" them. Even though the script may indeed insert data.

Also, to expand on Jamie F's answer, don't run a SQL file against your database unless you know what it is doing. SQL scripts can be as dangerous as unchecked exe's

like image 43
Neil N Avatar answered Sep 28 '22 01:09

Neil N