Easy SQL Question

Radjammin2

Active Member
Ok This isn't the hardest question but I guess for me it just never came up. I need to copy a table from 1 productions SQL server to another. Now I have done DTS Database copys but that grabs all the tables, and I have done insert into select * from source table.

But What about a single table from one SQL server to another? So simple it's hard? I know I could use R98403
but I guess I just wanted to know the SQL solution for my own Edification..!
grin.gif
 
I just had the opportunity to assist one of our developers with this
same question. If both servers are MS SQL (which they sound like they
are) you can create a server link using the system stored procedure
sp_addlinkedserver. With that link in place you can use a fully
qualified object name ([server].[database].[owner].[object]) to
access/create object from one SQL Server to another.



You can also just use DTS for one single table.



Let me know if that helps, or if you need any further assistance.





Peter Douglas

Voice: 208.542.8253

Email: [email protected]
 
You can use DTS to copy a single table, a list of tables, or an entire database.
 
You really SHOULD use OneWorld to copy over oneworld tables - it ensures that the table is created correctly, and everything comes over in one piece. As a quickie, you can do single table copies directly in OMW under Design/Table Operations (Copy Table).

If you try and do it any other way, you're going to have issues with indexes not being generated and owner issues - and that takes a lot longer to work out than just clicking "copy table" in omw !
 
Ya I could see Table copy in OMW to be the best solution. I actually got this questions from a developer so I guess a little suprised he didn't notice this already. Had emptied a smaller table. So to be certain, copy table from another environment, as long as I got it some wheres else!, that's a pretty quick fix.

To the Query analyzer link server, I knew about that option, but it seemed extensive. Ya if I link server I could just do an insert into from source to target using server qualified table name. But you know I really should do this now that we have separate Dev/Prod sql.


But hey thanks everyone that replied, I will try to post some more answer to be a contributor instead of just an answer leech.
 
Back
Top