April 30, 2008 at 6:41 am
Dear All,
I want to copy a table and its data from PROD environment to DEV environment.
Note: I dont want to use BCP or DTS package utilities.
I want to do the same with sql query.
I am using sql2000.
Inputs are welcome!
karthik
April 30, 2008 at 8:18 am
You will have to script out the table to set up the table in the new database. Then use OPENQUERY to get the data to be transferred to the new table.
Dave Novak
April 30, 2008 at 8:24 am
easiest way is to define the DEV server as a linked server. And using the four name convention
insert into PROD.DB.dbo.tablex
select * from DEV.DB.dbo.tablex
( DEV is the name of the linked server defined for DEV server )
April 30, 2008 at 8:28 am
ygulas,
i have tried out the above method.
but it failed.
karthik
April 30, 2008 at 1:38 pm
Did you create a linked server named DEV ? you can do it in 2 ways.
1. using sp_Addlinkedserver procedure ( Look at the books online for detail )
2. from Enterprise Manager
April 30, 2008 at 3:14 pm
ygulas (4/30/2008)
easiest way is to define the DEV server as a linked server. And using the four name conventioninsert into PROD.DB.dbo.tablex
select * from DEV.DB.dbo.tablex
( DEV is the name of the linked server defined for DEV server )
Actually, it's easier to "pull" it than it is to "push" it:
On the PROD server:
USE [prodDB]
Select *
Into [dbo].[TableX]
From DEV.[devDB].[dbo].[TableX]
This also creates the table for you. (Indexes, triggers, etc. still have to be scripted and applied though).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply