duplicating all related data with new IDENTITY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

duplicating all related data with new IDENTITY

Author

Topic

iori
Starting Member

20 Posts

Posted - 2008-10-24 : 17:53:35

I would like to know whats the best way to do this, say i have one table which has 30+ columns called ORGANIZATION and a related ORGANIZATION_MEMBERS table.

ORGANIZATION (ID[AUTO INCREMENTED],info1, info2.....info30)
ORGANIZATION_MEMBERS (ORGANIZATION_ID, MemberID)

Now i have make a copy each organization in ORGANIZATION table, get the new ID of the organization and make this new organization to have the same members as the old one.

I can only think of using cursors and temporary tables (with 30 columns) to do this. Is there a better/faster way of doing this? thinking of fetching those 30 columns in 30 different variables and inserting them using cursors make me lazy! :(

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 18:49:42

Let me rephrase...

You want to create a new table: NEW_ORGANIZATION
and you want to copy ORGANIZATION and ORGANIZATION_MEMBER information into this new table???

Please clarify.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 18:56:02

WILL this help:


select NEWORGID bigint identity(1,1), O.info1, o,info2, ... info30
INTO NEW_ORGANIZATION
from ORGANIZATION o, ORGANIZATION_MEMBERS m
where o.ID = m.ORGANIZATION_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 02:52:05

[code]select identity(int,1,1) AS NewOrganisationID,O.OrgainisationID O.info1, o,info2, ... info30
INTO NEW_ORGANIZATION
from ORGANIZATION

INSERT INTO ORGANIZATION_MEMBERS
SELECT no.NewOrganisationID,om.MemberID
FROM ORGANIZATION_MEMBERS om
INNER JOIN NEW_ORGANIZATION no
ON no.Organisation_ID=om.OrganisationID [/code]

iori
Starting Member

20 Posts

Posted - 2008-10-25 : 16:03:54

no its not a different ORGANIZATION TABLE, its the same table with new ORGANIZATION id. here is a sample:

ORGANIZATION(id, name, location.................)
-------------------------------------------------
1 organization1 TX

and ORGANIZATION_MEMBERS (ORGANIZATION_ID, MemberID) would be
------------------------------
1 1
1 2
1 3
1 4
1 5
1 6
1 7

NOW if i make a new organization based on an exisitng one the tables would look like:
ORGANIZATION:
----------------------------------
1 organization1 TX
2 organization2 TX

------------------------------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
2 1
2 2
2 3
2 4
2 5
2 6
2 7

I am thinking of approaching it with views so the view would be

vw_OrgDeails(orgID, orgName,.....OrgMembers)
-----------------------------------------------
1 organization1 1,2,3,4,5,6,7

now using cursors i can loop through it generate a new id and copy members using UDF that will split the string i pass to it and insert it for the new organisation

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 01:23:52

no need of view. you can do this

INSERT INTO ORGANIZATION (name,location,...)
select name,location,...
from ORGANIZATION 

INSERT INTO ORGANIZATION_MEMBERS
SELECT no1.OrganisationID,om.MemberID
FROM ORGANIZATION_MEMBERS om
INNER JOIN ORGANIZATION no
ON no.Organisation_ID=om.OrganisationID
INNER JOIN ORGANIZATION no1
ON no1.Name=no.Name
AND no1.Location=no.Location
....
AND no1.Organisation_ID<> no.Organisation_ID

keeping comma seperated values is against normalisation. Also it will make manipulations complex than storing as individual values as you will need UDF to parse the comma seperated list each time.

iori
Starting Member

20 Posts

Posted - 2008-10-26 : 12:49:44

thanks visakh16, that should work :) Also I am not saving comma separated values in the table, its a view that i have show user on the UI which is why i was thinking of taking that route.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-26 : 13:55:59

quote:
Originally posted by iori

thanks visakh16, that should work :) Also I am not saving comma separated values in the table, its a view that i have show user on the UI which is why i was thinking of taking that route.

ok. that sounds better. But it might be much easier to do this concatenation for UI in your front end application. Try that way too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources