Question :
I am currently creating a stored procedure that populates a table with a list of insert statements so that my DBAs can export the contents of the table to a text file and then paste the statements into SSMS and run (it is to copy data from test environment to live enviroment – I am constricted by the fact the 2 environments cannot see each other, bcp is not configured on the environment
My question is that the table I am going to export could have up to 2,000,000 insert statements. Is there a limit to what can be pasted in and run?
Answer :
bcp is not configured on the environment
BCP is an exe that comes with SQL Server. The default path is C:Program FilesMicrosoft SQL Server100ToolsBinnbcp.exe
You can refer to my BCP and BULK INSERT script here.
I am going to export could have up to 2,000,000 insert statements.
SSMS wont be able to support this. You have to use SQLCMD.
Highly suggest you to use BCP or SSIS to do the task.