Post reply

Identity Column Inserts Issue.

  • May 14, 2008 at 3:30 am

    #207319

    Hi friends,

    I have 3nos. of table in my database (DB1) like Users, Groups and Goup_Users

    In Users table, UserID is Identity

    In groups table, GroupID is Identity.

    In Group_Users table, UserId and GroupID columns are presents.

    Now I have another database (DB2) having the same structure.

    I have to copy the Users, Groups, and Group_Users from the DB2 to DB1.

    Here my problem is the Identity column in both database, I can remove the Identity and copy the data. But for DB2 Group_Users combination should be same after transfer to DB1.

    Can you please give me the idea how can I achieve it?

    Cheers!

    Sandy.

    --

  • May 14, 2008 at 3:41 am

    #815014

    So, if you have empty tables on SQL side, there is no problem.

    You have to

    drop FK constraint on Users_Groups

    set identity_insert Users on

    --populate your data from the other db

    set identity_insert Users off

    set identity_insert Groups on

    --populate your data from the other db

    set identity_insert Groups off

    populate Users_Groups.

    create FK constraints on Users_Groups

    HTH

    Piotr

    ...and your only reply is slàinte mhath

  • May 14, 2008 at 3:46 am

    #815016

    hey Piotr,

    I am not facing problem copying the master data like users and groups,

    My issue is the combination of UserId and GroupID in Group_Users table present in DB2 need to be same combination for DB1 after moving the UserID and GroupID in the Group_Users Table.

    hope you are clear to my query now?

    Cheers!

    Sandy.

    --

  • May 14, 2008 at 4:46 am

    #815032

    Hi are you looking to copy from Database 1 (has the data) to Datbase 2 (which is blank) but wish to retain the Parent/Child relationships?

    If so then from Database 1 migrate the date:

    [Code]

    SET IDENTITY_INSERT YourDatabase2.dbo.YourTable ON

    INSERT INTO [YourDatabase2].[dbo].[YourTable]

    ([Ident Column]

    ,[Field1]

    ,[etc....])

    SELECT

    IdentColumn,

    Field1,

    etc.......

    FROM [YourDatabase1].[dbo].[YourTable]

    SET IDENTITY_INSERT YourDatabase.dbo.YourTable OFF

    [/Code]

    Repeat for the child table.

    Hope I understood your issue correctly.

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • May 14, 2008 at 4:51 am

    #815036

    Hi Sandy,

    If you enable identity_insert on a table, you can insert the values from the other databases. Since they maintain referential integrity there, they will on SQL side as well. The trick is not to generate new ids for records inserted but reuse those generated on DB2 side

    Regards

    Piotr

    ...and your only reply is slàinte mhath

  • May 14, 2008 at 6:23 am

    #815096

    hey Philip Horan,

    Nope, I am trying to copy data from DB2 to DB1 and both the database is having data. more over to that in my DB1 and Db2 the structure are same.

    and As I specified when i am copying the data. the Identity changes in Db1 for Db2 data for Example

    ===================

    Database: DB1

    ----------------------

    Table : Users

    =========

    UserID

    ------

    1

    2

    -----------------------------

    Table : Groups

    =========

    GroupID

    ------

    1

    2

    ----------------------------

    Table: Group_Users

    ===============

    UserID GroupID

    ------ -------

    1 2

    2 1

    ==========================================

    Database: DB2

    ----------------------

    Table : Users

    =========

    UserID

    ------

    1

    2

    -----------------------------

    Table : Groups

    =========

    GroupID

    ------

    1

    2

    ----------------------------

    Table: Group_Users

    ===============

    UserID GroupID

    ------ -------

    1 2

    2 1

    ============================

    If I copy data user and Group Data from Db2 to Db1

    then My result will be like this

    =============================

    Database: DB1

    ===============

    Table : Users

    =========

    UserID

    ------

    1

    2

    3

    4

    -----------------------------

    Table : Groups

    =========

    GroupID

    ------

    1

    2

    3

    4

    ----------------------------

    Table: Group_Users

    ===============

    UserID GroupID

    ------ -------

    1 2

    2 1

    ===============

    3 --

    4 --

    ===============

    My Issue is here, how can i keep track of which userid is belongs to which groupid because all the ID will be new in Db1

    Can you please look into my Issue?

    hey Piotr,

    Can u look into above data?

    Cheers!

    Sandy.

    --

  • May 14, 2008 at 8:12 am

    #815229

    hey Piotr,

    I resolved the issue by using dynamic crosstab query concept.

    by checking each userid with respect to groupid.

    Anyways thanks a lot.

    Cheers!

    Sandy.

    --

  • May 14, 2008 at 10:41 am

    #815395

    Hi Sandy. Do you have an example as I could put that to good use.

    Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

Viewing 8 posts - 1 through 7 (of 7 total)

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