exporting data from ms sql server 2005 ?

Question

I have a table in my ms sql server database with more than 50,000 records. I want to export this data as INSERT query to a text file, so I can run it on another server to insert all the records there. How can I export the data as SQL statements ?

Answer 1

I'm not quite sure I get your question. Why don't you just export the data as a text file, and then import it into the db on the other server? Is there an access or permission issue? Or, you could always create an on-the-fly down and dirty DTS to do the job...

Answer 2

Using SSIS would work.



You can also try using the bcp utility. Look it up on SQL Books Online help for 2005. It's pretty easy, but it IS a command line utility rather than transact SQL Assuming you have privs, you can run the cmdshell from transact SQL to run it...

Answer 3

Try BULK INSERT statement.