SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identity Column Inserts Issue.


Identity Column Inserts Issue.

Author
Message
IN_Sandeep
IN_Sandeep
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1379 Visits: 1255
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.

--
Piotr.Rodak
Piotr.Rodak
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2752 Visits: 1761
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
IN_Sandeep
IN_Sandeep
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1379 Visits: 1255
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.

--
2Tall
2Tall
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3020 Visits: 1203
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

Piotr.Rodak
Piotr.Rodak
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2752 Visits: 1761
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
IN_Sandeep
IN_Sandeep
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1379 Visits: 1255
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.

--
IN_Sandeep
IN_Sandeep
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1379 Visits: 1255
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.

--
2Tall
2Tall
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3020 Visits: 1203
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search