asked
Need to update production Database
I have a table in my development database and I need to copy it to the production database. How do I do this?
Several ways 1. Create a linked server on the target that points to the source, then perform an INSERT with EXECUTE AT 2. BCP out the contents from source, BCP in on the target 3. BCP out on source, then BULK insert on target 4. Enable replication just for the object to be copied over
It depends. For example, what if you want to transfer 10K rows. You can try something like: INSERT INTO localtable EXECUTE ('SELECT column1, column2, ..., columnN from remotetable') AT [linkedserver]; This will execute the call at the remote, transfer over the network, then insert to the local server. In this particular scenario, there is no performance degradation IMO.
One way: Using the SQL Server Management Studio, navigate to the database, right-click, Tasks -> Generate Scripts. Select the table you want, and check the "Script data" option to make sure that the data is scripted along with the schema (EDIT - if that's what you want!). Have a play with the options here, to make sure you get what you want / need in terms of dependent objects, indexes etc. This will generate a script (either to the clipboard, or to a new query window, or to a file) that you can run against the appropriate production server.
There are many ways! - Generate script from the development server, run it on the production (as ThomasRushton said make sure that you had the script for your keys constraints etc)- - Use import/export method (will not have the Keys, constraints etc but have simple table structure) - Use SSIS packages to transfer the objects
You Can Use Export And Import Wizad to copy the Data from Development Database to Production Database.
Lots of options listed here, many of them good. Just to add a couple more: Red Gate SQL Compare combined with Red Gate SQL Data compare will do it quickly, easily, and entirely through guis. This is also quite easy to do using SMO if you like PowerShell or any of the DotNet languages.
