Coping data from one SQL table to enother table

I found a data table on the prodcution SQL 2000 db blanked out for unknown
reason. I'd like to restore the data from a SQL 2000 test database which was
restored from a previous day's complete backup of the production db. Both
db's are residing on the same server under the Enterprise Manager. Is there
a way to do the restoring from within the Enterprise Manager, or T-SQL via
Query Analyzer?
Thanks.In Query Analyzer:
INSERT INTO production_database..some_table (<column_list> )
SELECT <column_list>
FROM test_database..some_table
Jacco Schalkwijk
SQL Server MVP
"Eli Feng" <efeng@.kerisys.com> wrote in message
news:OoXS$eOKFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I found a data table on the prodcution SQL 2000 db blanked out for unknown
> reason. I'd like to restore the data from a SQL 2000 test database which
> was
> restored from a previous day's complete backup of the production db. Both
> db's are residing on the same server under the Enterprise Manager. Is
> there
> a way to do the restoring from within the Enterprise Manager, or T-SQL via
> Query Analyzer?
> Thanks.
>|||If Neither Table has Identity Column,
Insert DatabaseA..TableName (<List of Columns> )
Select <Same List of columns>
From DatabaseB..TableName
-- ----
If Tables have Auto-incrementing surrogate PKs, (Identity PK) Column,
you might have slightly more difficult issue.
-- ----
A) If you want the same values for IDent Column,
(And they are not already in use in Dest Table) you can
Set Identity_Insert DatabaseA..TableName On
Insert DatabaseA..TableName (<List of Columns> )
Select <Same List of columns>
From DatabaseB..TableName
Set Identity_Insert DatabaseA..TableName Off
-- ----
B) If you don't care what the PKs are in the new Table,
Just insert all the columns except the Identity PK,
and the Insert will generate new PK values
"Eli Feng" wrote:

> I found a data table on the prodcution SQL 2000 db blanked out for unknown
> reason. I'd like to restore the data from a SQL 2000 test database which w
as
> restored from a previous day's complete backup of the production db. Both
> db's are residing on the same server under the Enterprise Manager. Is ther
e
> a way to do the restoring from within the Enterprise Manager, or T-SQL via
> Query Analyzer?
> Thanks.
>
>|||I find enterprise mangler useful for this kind of thing, if you add a new
column into the middle of the table and script the changes instead of
applying, it will generate you the rename, recreate and drop scripts that
you can then tweak in query analyser and use to copy the data over from the
backup.
Alternatively you can run through the DTS wizard and do it that way.
Mr Tea
"Eli Feng" <efeng@.kerisys.com> wrote in message
news:OoXS$eOKFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I found a data table on the prodcution SQL 2000 db blanked out for unknown
> reason. I'd like to restore the data from a SQL 2000 test database which
> was
> restored from a previous day's complete backup of the production db. Both
> db's are residing on the same server under the Enterprise Manager. Is
> there
> a way to do the restoring from within the Enterprise Manager, or T-SQL via
> Query Analyzer?
> Thanks.
>|||LOFL He said "Enterprise MANGLER"
Y'know, that says so much more than anything I could have come up with.
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:6OoZd.943$6P4.5@.newsfe2-gui.ntli.net...
>I find enterprise mangler useful for this kind of thing, if you add a new
>column into the middle of the table and script the changes instead of
>applying, it will generate you the rename, recreate and drop scripts that
>you can then tweak in query analyser and use to copy the data over from the
>backup.|||Based on all your query scripts and advices, I have copied the data
successfully.
Thanks you all. I really appreciate that.
Best regards,
Eli
"Michael C#" <xyz@.yomomma.com> wrote in message
news:%23woMj8OKFHA.4092@.tk2msftngp13.phx.gbl...
> LOFL He said "Enterprise MANGLER"
> Y'know, that says so much more than anything I could have come up with.
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:6OoZd.943$6P4.5@.newsfe2-gui.ntli.net...
the
>