tring to restore data from table

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Blogs QotD Books SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » tring to restore data from table - HELP

tring to restore data from table - HELP

Rate Topic

Display Mode

Topic Options

Author

Message

george sibbald

Posted Friday, February 29, 2008 12:19 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 1,561, Visits: 3,451

HI, data has been accidentally removed from a table by a user and I am tryng to recover just that data from another database recovered to just before deletion.

Strange problem with duplicate key on a column user_attr_id defined as unique. When I try to insert a row where user_attr_id = 303 fails on duplicate key, but select on target table where user_attr_id = 303 returns 0 rows. Do not understand that

What is best code to insert only non duplicates from recovered table to source table.?

Post #462617

Steve Jones - Editor

Posted Friday, February 29, 2008 12:40 PM

SSChampion

Group: Administrators
Last Login: Yesterday @ 6:11 AM
Points: 19,447, Visits: 5,032

Perhaps you have duplicates in the source data.

Post #462631

george sibbald

Posted Friday, February 29, 2008 12:50 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 1,561, Visits: 3,451

good point steve and I wish it were so, but select on 'where user_attr_id =303' returns only 1 row.

I am thinking I need some sort of outer join on the source and target tables to insert all but duplicates?

Post #462638

Matt Miller

Posted Friday, February 29, 2008 1:02 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:35 AM
Points: 5,734, Visits: 6,917

Are you sure the message is originated by your install operation (as opposed to an insert trigger on the table?) I've fallen for the duplicate row message only to realize it meant duplicate "in the history table", not the main table I was inserting into.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #462645

george sibbald

Posted Friday, February 29, 2008 1:11 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 1,561, Visits: 3,451

don't think so matt, how can I be 100% sure there are no insert triggers on this table?

Post #462650

george sibbald

Posted Monday, March 03, 2008 3:18 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 1,561, Visits: 3,451

guys, thanks for your help. turns out there was another unique index defined on the table I missed because I was so concentrated on the primary key (to be fair to me it was late here............)

Post #463034

« Prev Topic | Next Topic »

Permissions