SQL Server Management Studio 2012 - Export/Import data from/to table

Question

I have table with more than 3 000 000 rows. I have try to export the data from it manually and with SQL Server Management Studio Export data functionality to Excel but I have met several problems:

• when create .txt file manually copying and pasting the data (this is several times, because if you copy all rows from the SQL Server Management Studio it throws out of memory error) I am not able to open it with any text editor and to copy the rows;

• the Export data to Excel do not work, because Excel do not support so many rows

Finally, with the Export data functionality I have created a .sql file, but it is 1.5 GB, and I am not able to open it in SQL Server Management Studio again.

Is there a way to import it with the Import data functionality, or other more clever way to make a backup of the information of my table and then to import it again if I need it?

Thanks in advance.

Solution

I am not quite sure if I understand your requirements (I don't know if you need to export your data to excel or you want to make some kind of backup).

In order to export data from single tables, you could use Bulk Copy Tool which allows you to export data from single tables and exporting/Importing it to files. You can also use a custom Query to export the data.

It is important that this does not generate a Excel file, but another format. You could use this to move data from one database to another (must be MS SQL in both cases).

Examples: Create a format file:

Bcp [TABLE_TO_EXPORT] format "[EXPORT_FILE]" -n -f "[ FORMAT_FILE]" -S [SERVER] -E -T -a 65535

Export all Data from a table:

bcp [TABLE_TO_EXPORT] out "[EXPORT_FILE]" -f "[FORMAT_FILE]" -S [SERVER] -E -T -a 65535

Import the previously exported data:

bcp [TABLE_TO_EXPORT] in [EXPORT_FILE]" -f "[FORMAT_FILE] " -S [SERVER] -E -T -a 65535

I redirect the output from hte export/import operations to a logfile (by appending "> mylogfile.log" ad the end of the commands) - this helps if you are exporting a lot of data.

Comments

[gotqn]

thank you for the quick response. I have read about the BCP utility and enable it. When I try to do the export it says to me "Access denied". I have read that if you log with the SA account it should work, but i get "Access denied" again. Have you met the same issue?

[Bernhard Kircher]

Unfortunately I do not know the permissins your Useraccount has on the server nor do I know the permissions on the database. It could be a Filepermission problem on the exported file/directory, or something else. Did you take a look at the msdn link? Maybe you need to add another option to the commandline (like username/password - depending on your configuration). If this does not work - maybe you are able to post more information (exact errormessage, the command you are using etc)

[gotqn]

Yes, i have looked trough the link. I use user and password options too. My account is administrator with full rights.

[Bernhard Kircher]

are you execuing bcp on the database server directly? Please post the complete errormessage. Did you try to execute the commandline via "run as administrator"?

[gotqn]

Thank you very much for your help. When I run the commandline via "run as administrator" it successfully exported the data. Then I successfully imported it back too. It is really faster way to import/export data.