Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
5  
Sounds very much like wrong tool for the wrong job. Can you not make a case for extract/import of just the data with an appropriate tool e.g. bcp/bulk insert/SSIS? –  Mark Storey-Smith Dec 23 '13 at 15:51
1  
You'll want to save the output to a text file, and have your DBA run that text file using sqlcmd.exe. –  Max Vernon Dec 23 '13 at 15:51
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
1  
Thanks all for your comments (and marked answer) - I am going to go back to the DBAs and explain that this is the wrong approach –  Mike Dec 23 '13 at 15:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.