[Tips] Microsoft SQL Server Large Data Import and Export Problem Summary

Question 1:

Today I got an Excel with nearly a million pieces of data to import into the database, I first used my own Excel locally (2014), and then generated a script file to execute on the server (2008); The file SQL cannot be opened.

Workaround:

Use the built-in sqlcmd tool

Step 1: Win+R type: cmd command to open the command line tool;

Step 2: Type: cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn (the specific directory path is related to the location of the SQL you installed)

Step 3: Type: sqlcmd -S . -U sa -P 123 -d test -i data.sql

Parameter description:-S server address -u username -P password -d database name -i script file path

Question 2:

Today I got a database backup file, restore it on the server, a million pieces of data, to export to Excel, a set of tasks => export data => SQL->Excel 2007 is very good to export to 65536 errors, a check is Excel limit. If you still can't try it locally, then write a stored procedure to export multiple subtables with paging, each with 65,536 pieces of data. It's so annoying that I don't want to write. Use bcp.

Workaround:

Step 1: Run the following statement to set the security configuration

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

No setting will give an error "SQL Server blocked access to the process 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component was shut down as part of the security configuration for this server. System administrators can enable 'xp_cmdshell' by using sp_configure. For more information about enabling xp_cmdshell, see Peripheral App Configurator in the SQL Server Online Books series. ”

Step 2: EXEC master: xp_cmdshell 'bcp "use -B select * from -T queryout -E -c -S -U -P

Parameter description:-B database name  -T table name  -E Excel path  -S server address -U username -P password

Example: EXEC master: xp_cmdshell 'bcp "use LJ_PD_IPR_INTERFACE select * from PatentInformation" queryout D:\11.xls -c -S"." -U"sa" -P"123456"'

Question 3:

If the exported data contains special characters such as "[" and "]" with bcp, the final Excel style will be messed up. I found another tool: SQL Prompt

Download Address:The hyperlink login is visible.

The method of use is very simple, and the speed is also very fast, first query the required data, and right-click Open in Excel in the upper left corner