Read .sql file into a database

Question

I created a sql database and filled it with some good data. Now I'm trying to move all the stored procedure over to another database instance. I exported the entire source database, including beloved stored procedures to a .sql file. Now I'm looking for a way to read that data into the other computer's sql server installation.

So far, I've tried looking through the Visual Studio UI for hints, and using sqlcmd with an input file. After using sqlcmd with the .sql file, some rows were affected, but they didn't show up in the server explorer.

• Mark Storey-Smith: On re-reading your question, it's not clear whether your problem is export/import of data (which I originally thought, hence my answer below) or whether you are just unable to execute your .sql script on the new server. Which is it?
• Zelda: If the script has the insert/create procedure/ect statements, can't you just run it against the new database in SQL Sever Management studio or via the command line?
• lowerkey: @MarkStorey-Smith: Both, actually. First, I'm trying to export and import date from one sql server to another. Second, my first attempt at doing so failed. Thanks for the answer.

Answer 1

Do you want to move data as a one off exercise or do you want to add the data to source control?

• SSMS Tools Pack includes a script generator for resultsets, tables or databases.
• SSMS has table level data script generation built-in.
• If the datasets are large, consider using BCP.
• SSIS Import/Export wizard