When developing an application using a database one may need to initialize tables with a few rows. For example, it is useful for a "Category" table to contain a few rows as soon as the application starts. So we create these lines in the database with the tool that we want (rarely a SQL INSERT command) then export these newly created lines so that other developers can add them on their computer.
In the rest of the article we will see multiple ways to export and import data: either by generating an SQL script or by using the BCP utility.
#Using Microsoft SQL Server Database Publishing Wizard
SQL Server Management Studio provides a wizard to generate a SQL script from a database:
It is possible to filter the elements to be generated. If you want to export data from one or more tables, just check the desired tables:
Many generation options are available in the advanced options:
By default only the schema is generated, so you have to change this option:
And here it is, the wizard generates a file like:
This method is simple thanks to the assistant. Many options are available to customize the final script.
#Using Visual Studio
Visual Studio 2015 can generate scripts from table data:
There you go!
Unlike the first solution, you can only export the data from one table at a time. So it's a little less convenient if you want to export the data from several tables but that can be enough in some cases…
#Using BCP
BCP (Bulk Copy) is a command-line utility for quickly exporting or importing data. Unlike previous solutions, the generated file is not a sequence of INSERT commands, but a text or binary file (depending on the options)
##Export
Shell
bcp Sample.dbo.Employee out "%USERPROFILE%\desktop\export.dat" –n -N -T -S (LocalDb)\MSSQLLocalDB
The generated file is a file that can not be read by a human but easy to import by SQL Server:
##Import
Shell
bcp Sample.dbo.Employee in "%USERPROFILE%\desktop\export.dat" –n -N -T -S (LocalDb)\MSSQLLocalDB
Or in SQL
SQL
BULK INSERT dbo.Employee
FROM 'C:\users\meziantou\desktop\export.dat'
WITH (DATAFILETYPE='widenative');
BCP has many options to customize the export and import. For example, -E preserves the values of an auto-incremented column.
#Conclusion
You now have the choice of 3 different methods to export rows from one or more tables. If you do not need to generate an INSERT-based SQL script, it is better to use BCP because it is faster. By searching a bit on the internet you can also find stored procedures to generate the INSERT (example), however, they are often complicated and it is difficult to know if they work properly with all the functionalities of SQL Server…
Do you have a question or a suggestion about this post? Contact me!
