| Author |
Topic |
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
 Posted - 2006-03-08 : 09:44:24
|
| Hi All,What would be the best way to move data (in tables) only, from one SQL Server to another,both are on the same LAN. (The solution is enough assuming 2 DBs of same SQL Server, not necessarily 2 SQL Servers)- I want only the data in tables to be moved ( do not want to move any other object as Stored Proc, views etc) - I need to use the existing users of the Dev DB (don't want to recreate them one by one)- This is one time task and No need of Replication / DTS mechanisms |
|
|
propanecan
Yak Posting Veteran
60 Posts |
Posted - 2006-03-08 : 09:53:29
|
| If the dbs are on two different servers you could created a linked server and then bulk insert the data.For example INSERT INTO SERVERNAME2.DATABASEB.dbo.TABLEA SELECT * FROM TABLEA |
 |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-08 : 10:01:29
|
Hi propanecan,Thanks for the quick response.I might need some other way, since I have about 200 tables (with 4 GB of data)Its a tedious task to do, one by one. |
|
|
|
Tahsin
Starting Member
34 Posts |
Posted - 2006-03-08 : 11:05:26
|
| Another similar approach might be to create a duplicate environment, delete all of the unneeded tables (hopefully it's not as big as 200) and then create a SP to bulk insert all the data via a linked server as propanecan suggested. Why are you not leaning towards DTS/Export option BTW? |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-03-08 : 11:17:50
|
| It may be worth having a look at tools such as 'DB Ghost' (I don't have experience of this, but sit next to the creator on my current project!) and Red Gate SQL Data Compare. I realise that this is a one-time issue, but if you could see other uses for such a product, it may well be beneficial in the long term and could even be cost-effective depending upon how long it's going to take you to code and how expensive your time is!Mark |
|
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-03-08 : 11:20:52
|
| I'd consider a DTS, even though you say this is a one time thing. Refreshing development data could easily creep up again. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-03-08 : 11:22:32
|
| The red gate tools will definitely do it and they aren't too expensive. You could perhaps look at using DTS as a one off. If you do this you can schedule it to cause minimal impact elsewhere on the LAN.steve-----------Oh, so they have internet on computers now! |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-08 : 11:58:33
|
Tahsin & JoeNak---------------In DTS, is there a way to select all the tables at once? If not same tedious process of getting each table ...Thats what I'm worried about.I have to punish myself for troubling you while Import Export Wizard is there. (I forgot that  )Still I would like to have someway which is not a kind of a wizardmwjdavidson & elwoos--------------------I cannot go for any 3rd party tools (I need some way by SQL Server only) |
|
|
|
Tahsin
Starting Member
34 Posts |
Posted - 2006-03-08 : 12:17:33
|
| I think ultimately there will have to be some process where you have to choose the 200 tables that you want, whether it's DTS, Export Wizard, 3rd Party Tool ... there isn't going to be a way to automatize the selection that you need. This is why I suggested creating an environment where you can delete unwanted tables, simplifying the selection approach ... sort of a backwards approach. |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2006-03-08 : 12:51:41
|
| It's cheezy, but you can use the IMPORT/EXPORT wizard and just select to copy data from tables and views (not transfer all objects). This will let you select just the tables you want or all at once. I guess, you could use the transfer all objects objects and select the objects button and only select tables. Just a thought.JonWhen your forehead becomes flat, find another wall to hit it against.http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-03-08 : 14:23:04
|
| DTS (2000) has a "Copy SQL Server Objects" task. On the copy tab, uncheck Copy all objects and click on select objects. |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-08 : 15:24:21
|
| Thanks AllI did it with Import / Export WizardLearned "Copy SQL Server Objects" - Special Thanks to JoeNakIts Resolved |
|
|
|
|
[Resolved] Move Data From Prod To Dev