Tuesday, October 23, 2007
Literally you can copy and paste from one SQL Server to another
Let's say you want to copy data from one server to another. Assume you have a table called MyTable on ServerA and the exact same table on ServerB.
Basically all you have to do is simply copy and paste the rows from one screen to another. Here are the details.
Here are step by step instructions:
1. Open MS SQL Server Management Studio
2. Browse to your favorite table using the Object Explorer.
3. Right click on the table and select "Open Table"
4. Shift or control click the rows you want to copy.
5. Type control-C.
Repeat steps 1 - 4 on the other server that has this same table (no data though).
UPDATE: Be sure to select the last row that has all nulls, otherwise the paste may not work as expected.
Lastly, type Control-V to paste the data.
This tip does not work in Enterprise Manager that comes with SQL Server 2000 (at least that I know of).
Subscribe to:
Post Comments (Atom)
4 comments:
Just as a side note, in order to paste into sql server you must first select the last row that contains null values in order to paste correctly. If you don't do this that the data will not paste correctly.
paul alkema,
Thank you for the tip. I have updated the blog to include your tip. Thank you for helping make my blog more accurate.
Thank you,
Brent
Also when doing this, you should always make sure that the table you are pasting into is a dummy table as this can be a dangerous method of getting data from one table to another. From there you can pull the data that you need from the dummy table.
I have pasted data into dummy tables and had the identity keys duplicated completely messing up the table.
I would recommend if possible when sending data from one table to another just using the insert into method.
EXAMPLE:
INSERT INTO table1
select column1,column1,column1
from Table2
Paul,
Thank you for the feedback and tips. Assuming all your primary keys are setup properly on both tables it works well I think. In particular, I use it when copying new rows from a development database to a production database. I would highly recommend using your technique or the select into SQL statement to make a backup of your tables first.
I would like to point out as well that this method really works well to copy data from server to server. If you are doing just database to database on the same server, I would agree and usually use SQL to do so.
Some options would be to create insert statements and run on the target database. You could also do SSIS, or Import / Export data, or setup a linked server. All these work, but require much more setup.
Chose your poison carefully. :)
Brent
Post a Comment