Monday, March 12, 2012

How to import data into development database from productions data

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.
>
>

No comments:

Post a Comment