70

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amen Ra
  • 2,843
  • 8
  • 47
  • 85

8 Answers8

116

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

JohnB
  • 18,046
  • 16
  • 98
  • 110
dev4life
  • 10,785
  • 6
  • 60
  • 73
  • 1
    Even though I don't think it answers the question, it's usefull info, so "big thanks" – MVCDS Apr 29 '15 at 18:54
  • 1
    This worked, except you might need to separate the database using `-d` like so: `sqlcmd -S SERVERNAME -d INSTANCE_NAME -i C:\path\mysqlfile.sql -o C:\path\output_file.txt` – EliSquared Jan 19 '21 at 18:59
  • @EliSquared Could you pls tell me? Whether Should we need to delete the existing tables before we run the command. – Venkata Shivaram Oct 24 '21 at 17:34
34

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

Joshua Pinter
  • 45,245
  • 23
  • 243
  • 245
Neil N
  • 24,862
  • 16
  • 85
  • 145
24
  1. Start SQL Server Management Studio
  2. Connect to your database
  3. File > Open > File and pick your file
  4. Execute it
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 3
    SQL Server takes a dump when opening/executing files with non-trivial amount of statements. You would think this is something it would be good at. – Marcelo Mason Aug 30 '14 at 19:16
  • 1
    Hii @MarceloMason I want to know how much time does it take to open a .sql file that has more than 4.5k to 5k records in a table. – Venkata Shivaram Oct 24 '21 at 17:56
4

Try this process -

Open the Query Analyzer

Start --> Programs --> MS SQL Server --> Query Analyzer

Once opened, connect to the database that you are wish running the script on.

Next, open the SQL file using File --> Open option. Select .sql file.

Once it is open, you can execute the file by pressing F5.

Rohit Suthar
  • 3,528
  • 1
  • 42
  • 48
4

In order to import your .sql try the following steps

  1. Start SQL Server Management Studio
  2. Connect to your Database
  3. Open the Query Editor
  4. Drag and Drop your .sql File into the editor
  5. Execute the import
TsTeaTime
  • 881
  • 1
  • 13
  • 34
Parag Satpute
  • 41
  • 1
  • 1
    you may want to add a bit more detail to this. Screen shots could help as well if you think it is necessary. – TsTeaTime May 04 '19 at 06:12
  • Basically, run the content of the `.sql` file in the query editor. I did that in my data base, and I got all my tables (like 10 of them). Just be sure your are not dropping something you already have and you may want to keep. – carloswm85 Sep 07 '21 at 16:38
2

A .sql file is a set of commands that can be executed against the SQL server.

Sometimes the .sql file will specify the database, other times you may need to specify this.

You should talk to your DBA or whoever is responsible for maintaining your databases. They will probably want to give the file a quick look. .sql files can do a lot of harm, even inadvertantly.

See the other answers if you want to plunge ahead.

Jamie F
  • 23,189
  • 5
  • 61
  • 77
0

  1. Get the names of the server and database in SSMS:

    Screenshot of SSMS highlighting the server name and database name

  2. Run the following command in PowerShell or CMD:

    sqlcmd -S "[SERVER NAME]" -d [DATABASE NAME] -i .\[SCRIPT].sql

    Here is a screenshot of what it might look like:

    Screenshot of Windows Terminal with a command to run a SQL script

Daniel Jonsson
  • 3,261
  • 5
  • 45
  • 66
-2

There is no such thing as importing in MS SQL. I understand what you mean. It is so simple. Whenever you get/have a something.SQL file, you should just double click and it will directly open in your MS SQL Studio.

hannani zainal
  • 1
  • 3