How to import data into development database from productions database

I have two different setup, one for development and other for production.
Two different SQL server 2000 installed on two different Windows 2000
server. I want to import all data of selected tables from production server
to development server. Databases name on both seerver are different. Before
I import, I want to remove all information from selected tables from
development database. Can anyone suggest me TSQL code?
Thanks.You might want to script the tables out en masse and build them that way,
rather than "import" anything (since you're not really importing any data).
See http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||Would it be easer to backup and restore production database on the
development server under different name?
"Sunny" wrote:
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production server
> to development server. Databases name on both seerver are different. Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||Or you can do something like this, with is not the best solution:
DECLARE @.name SYSNAME
DECLARE rs CURSOR
READ_ONLY
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
--
OPEN rs
--
FETCH NEXT FROM rs INTO @.name
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
-- find your tables you need
IF @.name = 'Table1'
BEGIN
PRINT @.name
--
DELETE
FROM OPENQUERY([ServerName], 'SELECT * FROM
DevelopDatabase.dbo.Table1')
--
INSERT INTO OPENQUERY([ServerName], 'SELECT * FROM
DevelopDatabase.dbo.Table1')
SELECT * FROM TABLE1
--
END
END
FETCH NEXT FROM rs INTO @.name
END
--
CLOSE rs
DEALLOCATE rs
GO
But before that you have to add linked server and linked server login
"Sunny" wrote:
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production server
> to development server. Databases name on both seerver are different. Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||Thanks Aaron and Sergey.
Backup restore works, but sometime I just want to few tables and not all
tables from production data. what I would like to do is store all tables
name in array and for each array element first delete all records from table
(name in array) of development server and copy all records from production
server. Any idea? or any better solution?
Thanks.
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> I have two different setup, one for development and other for production.
> Two different SQL server 2000 installed on two different Windows 2000
> server. I want to import all data of selected tables from production
server
> to development server. Databases name on both seerver are different.
Before
> I import, I want to remove all information from selected tables from
> development database. Can anyone suggest me TSQL code?
> Thanks.
>
>|||DTS?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Thanks Aaron and Sergey.
> Backup restore works, but sometime I just want to few tables and not all
> tables from production data. what I would like to do is store all tables
> name in array and for each array element first delete all records from
table
> (name in array) of development server and copy all records from production
> server. Any idea? or any better solution?
> Thanks.
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > I have two different setup, one for development and other for
production.
> > Two different SQL server 2000 installed on two different Windows 2000
> > server. I want to import all data of selected tables from production
> server
> > to development server. Databases name on both seerver are different.
> Before
> > I import, I want to remove all information from selected tables from
> > development database. Can anyone suggest me TSQL code?
> >
> > Thanks.
> >
> >
> >
>|||Yes, I think that would be the best option. I have not used DTS before and
SQL server is new to me, do you have any suggestion where to start?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...
> DTS?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > Thanks Aaron and Sergey.
> >
> > Backup restore works, but sometime I just want to few tables and not all
> > tables from production data. what I would like to do is store all tables
> > name in array and for each array element first delete all records from
> table
> > (name in array) of development server and copy all records from
production
> > server. Any idea? or any better solution?
> >
> > Thanks.
> >
> >
> > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > > I have two different setup, one for development and other for
> production.
> > > Two different SQL server 2000 installed on two different Windows 2000
> > > server. I want to import all data of selected tables from production
> > server
> > > to development server. Databases name on both seerver are different.
> > Before
> > > I import, I want to remove all information from selected tables from
> > > development database. Can anyone suggest me TSQL code?
> > >
> > > Thanks.
> > >
> > >
> > >
> >
> >
>|||www.sqldts.com
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:OfQ3oCvqEHA.3244@.tk2msftngp13.phx.gbl...
> Yes, I think that would be the best option. I have not used DTS before and
> SQL server is new to me, do you have any suggestion where to start?
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u5XFP7uqEHA.1160@.tk2msftngp13.phx.gbl...
> > DTS?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > news:Ohb0dRuqEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > > Thanks Aaron and Sergey.
> > >
> > > Backup restore works, but sometime I just want to few tables and not
all
> > > tables from production data. what I would like to do is store all
tables
> > > name in array and for each array element first delete all records from
> > table
> > > (name in array) of development server and copy all records from
> production
> > > server. Any idea? or any better solution?
> > >
> > > Thanks.
> > >
> > >
> > > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > > news:ucwJE2lqEHA.1668@.TK2MSFTNGP14.phx.gbl...
> > > > I have two different setup, one for development and other for
> > production.
> > > > Two different SQL server 2000 installed on two different Windows
2000
> > > > server. I want to import all data of selected tables from production
> > > server
> > > > to development server. Databases name on both seerver are different.
> > > Before
> > > > I import, I want to remove all information from selected tables from
> > > > development database. Can anyone suggest me TSQL code?
> > > >
> > > > Thanks.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>