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

sql

import-data

sql-server

command-line-tool

byNikita Barsukov·Dec 9, 2024

Dependent on type of your data file, the method to import it into SQL Server varies:

For CSV-like text data files, BULK INSERT will serve you right:

BULK INSERT YourTable
FROM 'path_to_your_file.csv'  
WITH   
(  
    FIELDTERMINATOR =',',  -- Ah, the magic comma! 🎩 
    ROWTERMINATOR ='\n'  -- Don't forget about '\n', it ends the journey for each row!
);

In case you have a .bak backup file, you'll need RESTORE DATABASE:

RESTORE DATABASE YourDB 
FROM DISK = 'path_to_your_file.bak'
WITH MOVE 'DataFile' TO 'YourDB.mdf', MOVE 'LogFile' TO 'YourDB.ldf';  -- Careful, heavy lifting! 💪

For .sql script files, several routes can take you to your destination, which we explore next.

Command-line import with sqlcmd

The sqlcmd command-line tool is your trusty companion for importing bigger .sql files:

sqlcmd -S serverName\instanceName -i inputFilePath.sql -o outputFilePath.txt

Here we're saying -S as our Server name and instance, -i as input file location, and -o denotes output file for logging.

Note: Log files do an excellent job as eye witness. 👀

Keeping an eye on the import

Monitoring import progress is, as they say, for keeping your sanity intact. Use -o in sqlcmd to get a nice log. For huge files, use tools like readfileonline.com to peek contents without waking up the whole memory.

Choosing import method based on file size

Size matters when choosing your import method. For smaller files, SQL Server Management Studio (SSMS) is a champ, with its Attach command. For larger adversaries, go with sqlcmd.

Error-proofing your script

Check your .sql script for any syntax blunders before you enter the execution arena. If you're new to this, it's helpful to do a few test runs with a smaller data subset.