** help, need to restore specific records from backup copy of database

I've got an SQL 2000 database where some rows were killed by running:
dbcc CHECKDB ('theDb', repair_allow_data_loss)
The primary key is an auto incrementing integer.
I have an older copy of the database, with all the missing rows, located on
an
SQL 2005 box.
I can connect to both databases (located on different machines) using SQL 20
05
Server Management Console.
How can I write some sql to quickly move the records (keeping the same prima
ry
key) from the good database to the bad one.
I have a list of all the primary keys of the records that need to be restore
d
(and the sql to generate that list, so I can handle that part of the query t
o
move the data).
Thanks in advance, Les CaudleThe easiest method might be to query the SQL Server 2005 box via a linked
server or OPENQUERY. You would want to query the rows that you want to
insert.
You would use SET IDENTITY_INSERT tablename ON before inserting into the
table with the identity value as the primary key.
Hopefully that gives you enough information to start working on a solution.
Keith Kratochvil
"Les Caudle" <very@.tiredofspam.com> wrote in message
news:kcep72dcjesf6ss1ebcvuc1676em7ltopn@.
4ax.com...
> I've got an SQL 2000 database where some rows were killed by running:
> dbcc CHECKDB ('theDb', repair_allow_data_loss)
> The primary key is an auto incrementing integer.
> I have an older copy of the database, with all the missing rows, located
> on an
> SQL 2005 box.
> I can connect to both databases (located on different machines) using SQL
> 2005
> Server Management Console.
> How can I write some sql to quickly move the records (keeping the same
> primary
> key) from the good database to the bad one.
> I have a list of all the primary keys of the records that need to be
> restored
> (and the sql to generate that list, so I can handle that part of the query
> to
> move the data).
> --
> Thanks in advance, Les Caudle|||Keith - I copied the backup table into a new database on the server with the
problem. Then I tried to run this query (wiht a few more fields in the sele
ct)
SET IDENTITY_INSERT dbo.Customers ON
GO
INSERT INTO dbo.Customers
SELECT CustomerID,
fName,
lName,
EmailAddress
FROM Olddb.dbo.Customers
WHERE Olddb.dbo.Customers.customerId in
(117,
236,
238
)
GO
SET IDENTITY_INSERT dbo.Customers OFF
GO
I keep getting this error msg:
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'Customers' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
I don't understand. I have both a column list and IDENTITY_INSERT is ON
Thanks, Les Caudle
On Tue, 30 May 2006 16:59:39 -0500, "Keith Kratochvil"
<sqlguy.back2u@.comcast.net> wrote:

>The easiest method might be to query the SQL Server 2005 box via a linked
>server or OPENQUERY. You would want to query the rows that you want to
>insert.
>You would use SET IDENTITY_INSERT tablename ON before inserting into the
>table with the identity value as the primary key.
>
>Hopefully that gives you enough information to start working on a solution.|||MS books on line seem to be incorrect, I followed their example and got that
error msg.
To get it to work, I needed to modify the code as follows:
INSERT INTO dbo.Customers (CustomerID,
fName,
lName,
EmailAddress)
etc. etc.
so that I had a column list for the source as well as destination tables
On Tue, 30 May 2006 18:38:27 -0500, Les Caudle <very@.tiredofspam.com> wrote:
[vbcol=seagreen]
>Keith - I copied the backup table into a new database on the server with th
e
>problem. Then I tried to run this query (wiht a few more fields in the sel
ect)
>SET IDENTITY_INSERT dbo.Customers ON
>GO
>INSERT INTO dbo.Customers
> SELECT CustomerID,
> fName,
> lName,
> EmailAddress
> FROM Olddb.dbo.Customers
> WHERE Olddb.dbo.Customers.customerId in
> (117,
> 236,
> 238
> )
>GO
>SET IDENTITY_INSERT dbo.Customers OFF
>GO
>
>I keep getting this error msg:
>Msg 8101, Level 16, State 1, Line 1
>An explicit value for the identity column in table 'Customers' can only be
>specified when a column list is used and IDENTITY_INSERT is ON.
>I don't understand. I have both a column list and IDENTITY_INSERT is ON
>Thanks, Les Caudle
>On Tue, 30 May 2006 16:59:39 -0500, "Keith Kratochvil"
><sqlguy.back2u@.comcast.net> wrote:
>|||Correct. I always specify column names within the insert statement. That
keeps things working even if somebody changes the order of the columns in a
table. It also makes this insert statement work. I am glad you discovered
the correct T-SQL.
Keith Kratochvil
"Les Caudle" <very@.tiredofspam.com> wrote in message
news:8amp72t9gpecjbgheki1h0h89v7b3hpkia@.
4ax.com...
> MS books on line seem to be incorrect, I followed their example and got
> that
> error msg.
> To get it to work, I needed to modify the code as follows:
> INSERT INTO dbo.Customers (CustomerID,
> fName,
> lName,
> EmailAddress)
> etc. etc.
> so that I had a column list for the source as well as destination tables
> On Tue, 30 May 2006 18:38:27 -0500, Les Caudle <very@.tiredofspam.com>
> wrote:
>
>