identity value question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

identity value question

Author

Topic

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-02-12 : 05:32:56

I have a db that I deleted some rows from a couple of weeks ago.

I now find I need those rows. I have a backup with the rows in that I need, but loads more data is in the live db than is in the backup.

I have setup a new db and restored my backup to it so I now have the live one and the backup one.

I need all the values including the identity (pk) column copying from the backup into the live db.

If I alter the live table and set "is identity = no", then do an insert into 'backup' select * from 'live' then set "is identity = yes" back on the live table, would that cause any problems?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-12 : 05:42:25

You have to make sure that you can select only the needed rows from your source for the insert.
You can do in T_SQL:
SET IDENTITY_INSERT <table_name> ON
...insert-statement...
SET IDENTITY_INSERT <table_name> OFF

No, you're never too old to Yak'n'Roll if you're too young to die.

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-02-12 : 05:49:10

Yes I can because they all have a unique id, so my insert would be

INSERT INTO maindb.table
SELECT *
FROM backupdb.table where backupdb.table.uniquevalue = 234

My question is really that if I turn the pk column to NOT be an identity column, then do the insert, then set identity back to yes, would that cause any problems later on with new records etc?

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 05:50:47

You can copy rows between tables / databases preserving IDENTITY by using


SET IDENTITY_INSERT MyTable ON
GO

INSERT INTO MyLiveDB.dbo.MyTable
SELECT SRC.*
FROM MyTempDB.dbo.MyTable AS SRC
    LEFT OUTER JOIN MyLiveDB.dbo.MyTable AS DST
        ON DST.My_ID = SRC.My_ID
WHERE DST.My_ID IS NULL    -- Record does not exist

UPDATE DST
SET DST.Col1 = SRC.Col1,
    ...
FROM MyTempDB.dbo.MyTable AS SRC
    JOIN MyLiveDB.dbo.MyTable AS DST
        ON DST.My_ID = SRC.My_ID
WHERE SRC.SomeColumn <> DST.SomeColumn -- Critical data has changed
GO

SET IDENTITY_INSERT MyTable OFF -- Don't forget to turn it off! It can only be ON on one table at a time
GO

Backup the Live DB first

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 05:52:12

"My question is really that if I turn the pk column to NOT be an identity column, then do the insert, then set identity back to yes, would that cause any problems later on with new records"

No, don't do that - any new rows that are inserted whilst you are fixing things will not get an ID, and there will be a massive update to perform the "remove IDENTITY" and then another massive update to reapply it ...

Just use SET IDENTITY_INSERT

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-02-12 : 06:05:34

But I was going to take the live db down whilst I do it. I know there will be no inserts whilst I am doing it. The most rows in the tables is only a few hundred so it wont take long for the update...

Is your above code better to use then?

also, in your code example, I have to manually list all the colums, some tables have quite a few in them

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 06:14:31

"Is your above code better to use then"

Yes. I seem to be repeating myself:

Dropping and Re-applying IDENTITY on a column requires a whole heap of processing, logging, and so on for the database.

The only reason to take the database down would be to make it easier to restore in case you had an accident - prudent in fact (although personally I would feel confident to do it with the DB live)

Assuming you application(s) are not logging on as SYSADMIN then setting the database to "DBO ONLY", and preferably SINGLE_USER as well, should stop anything (including scheduled jobs) connecting to the LIVE DB.

Set LIVE DB to DBO-ONLY, Single user
Take a full backup
Run the SQL to make the transfer (maybe ~~connect~~ comment out the INSERT part so it is just a SELECT first, so you can review what it will do, then re-run with the INSERT in place)

Review the data so you are happy with it.

Restore from backup if anything has gone wrong, otherwise change the DB back to MULTI-USER + Unrestricted

For further safety wrap your INSERT in a transaction block:

BEGIN TRANSACTION
GO

INSERT ...

you will see the number of rows inserted, make sure that is reasonable.

Make any SELECTs etc. so you are happy with the outcome.

If you are happy use COMMIT if you see ANY errors or are unhappy with the data IMMEDIATELY use ROLLBACK (do not try other commands, then may run outside the scope of the transaction and update the database and you won't be able to ROLLBACK)

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 06:16:43

" in your code example, I have to manually list all the colums, some tables have quite a few in them"

If you are using INSERT you can use SELECT *

If you are using UPDATE you will have to list all columns (or DELETE the rows from LIVE first, and then INSERT will work, but if the table on LIVE is part of foreign-key relationship you won't be able to delete)

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-02-12 : 06:19:08

All I want to do is copy all records where 'uniqueid' = 234 from the backup tables into the live tables. There are about 10 tables where I have to do this and all the tables have a column that = 234. I want to keep the identity values too

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-02-12 : 06:57:41

Thanks for the suggestions everyone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources