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 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|||One easy way is to use the DTS Import/Export Wizard. Once you specify your
query and destination table, select Transform and 'Enable identity insert'.
Test it with copies of your databases first, just in case.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Les Caudle" wrote:
> 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 select)
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:
>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 select)
>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.|||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:
>>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
>>select)
>>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.
>
![Smile [:)]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_vLkpl95TKqMIu41BMgYZVNGgD_ELLjHho1UxQRloXZiJjHlVi61V0uVrJ1PJGFA8X_kOZByl6kVrYg6V_lCGcZQ5s2t8d0lZYLVaEcKlVETC8_OQ8erQ=s0-d)