Post reply

Merging user data

  • January 4, 2018 at 11:45 am

    #342277

    I got a problem where due to the bug user got created duplicated accounts, so now some users have two accounts with the same logins but different ID's because of that they do have now some data related to both accounts and I have to merge it to one.
    To get my duplicated user list:
    Create table #userDuplications(
        [USER_ID] [int] IDENTITY(1,1),
        [LOGIN] [nvarchar](16),
    )
    Insert into #userDuplications (USER_ID,LOGIN) select USER_ID,LOGIN from USER_TABLE;
    delete from #userDuplications where LOGIN in (select LOGINLOGIN  from USER_TABLE group by LOGIN  having count(*)= 1)

    Now my result contains list of tables containing USER_ID
    Select * from #userDuplications 
    Above result contains pairs of duplicates:
    id:login
    1: user1
    2: user1
    3:user2
    4:user3

    The problem is that I'm not sure which tables contains USER related data so I figured I could use below query to get the list of tables with foreign keys to find the tables that needs to be the part of merge:
    EXEC sp_fkeys 'USER_TABLE'

    Above gives me the list of tables(FKTABLE_NAME) that contains users ID so I would have to iterate through each table and look up for user ids... i.e: if table contains user id = 1 or 2 then replace it with smaller id  but again not sure how to write this query... 
    Not sure if my thinking about this problem is correct. Hopefully you guys understand my issue and can help somehow.
    Thanks

  • January 4, 2018 at 3:50 pm

    #1974569

    If you have FK relationships, you don't have to do the data changes yourself.

    Script out the FKs, "DROP and CREATE", and change the "ON UPDATE CASCADE" to "ON UPDATE NO ACTION" before recreating them.  Then when you change the key, SQL will automatically propagate the change thru related tables.

    You can use sp_keys or view sys.foreign_keys (and maybe sys.foreign_key_columns??) to help.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply