Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Identity Column Inserts Issue. Expand / Collapse
Author
Message
Posted Wednesday, May 14, 2008 3:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 07, 2011 3:15 AM
Points: 375, Visits: 1,255
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.


--
Post #500265
Posted Wednesday, May 14, 2008 3:41 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
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
Post #500273
Posted Wednesday, May 14, 2008 3:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 07, 2011 3:15 AM
Points: 375, Visits: 1,255
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.


--
Post #500276
Posted Wednesday, May 14, 2008 4:46 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
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:

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

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
Post #500303
Posted Wednesday, May 14, 2008 4:51 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
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
Post #500308
Posted Wednesday, May 14, 2008 6:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 07, 2011 3:15 AM
Points: 375, Visits: 1,255
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.






--
Post #500382
Posted Wednesday, May 14, 2008 8:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 07, 2011 3:15 AM
Points: 375, Visits: 1,255
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.


--
Post #500532
Posted Wednesday, May 14, 2008 10:41 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
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
Post #500725
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse