How can I export data from my table to a sql file?

Question

I'm using SQL Server 2005 and I want to export all records from my Employee table to a .sql file. But when I run the export data option, it only generated the script of the structure of the Employee table, it does not contain the records on it.

What i want is to generate a script that will contain an INSERT statement containing the values from the Employee table. (Like in PHP admin).

Is that possible in SQL Server 2005? Please help me how to do it..

Thanks....

Answer 1

There are no tools in SQL Server 2005 that will export your data as SQL statements, which I assume you mean by ".sql file". The best way to export the data using a SQL Server tool is probably bcp, which can bulk copy the data to a file. The file contains data only, no SQL command. The bcp command and the BULK INSERT SQL command can be used to insert data into the Employee table.

If you want to create your own SQL INSERTs, you can create a statement like this:

SELECT 'INSERT INTO Employee (EmployeeID, LastName, FirstName) VALUES ('
    + CAST(EmployeeID as varchar) + ', ''' + LastName + ''', ''' + FIRSTNAME + ''')'
FROM Employee

You can save the results to your .sql file. However, you can probably see that this takes a little work to get it right. I'm not sure if the syntax in my example is perfect (did I get the right number of single-quotes?)

Answer 2

This blog solved my problem. I just exported the records into a text file and format it in excel file with its column and imported Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy.