What is the limit on the number of INSERT statements you can paste into SSMS and run?

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 1

bcp is not configured on the environment

BCP is an exe that comes with SQL Server. The default path is C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.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.