Copy rows into the same table with new primary key

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 2005 Forums

Transact-SQL (2005)

Copy rows into the same table with new primary key

Author

Topic

andrea8877
Starting Member

3 Posts

Posted - 2008-12-10 : 04:09:55

I think this question have been posted before, but i have searched a lot without answers...

I have a TableB with
ID - primary key
SCHEDA - foreign key
Col1, Col2, ecc... columns

And the rows are like:
ID-SCHEDA-Col1
1 - 1 - aaa
2 - 2 - bbb
3 - 2 - ccc
4 - 2 - ddd
5 - 3 - eee

Now i want to duplicate all the rows that are associated with a SCHEDA and associate them with a new SCHEDA so for example

Insert TableB (SCHEDA,Col1,Col2)
Select 99,Col1,Col2
From TableB

but how to "automatically" generate new primary keys?

UPDATE: see my reply below for further explanation

NOTE: the ID column is not and IDENTITY column. Could i change it now? What problems could arise? And if i can change it now, then i have to rewrite my asp code that makes INSERT into TableB?

Thanks

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-10 : 04:25:00

You can do it by joining table to itself but why would you want to? This breaks normalisation rules. Consider breaking table into 2 tables to remove need for duplication.

andrea8877
Starting Member

3 Posts

Posted - 2008-12-10 : 04:57:59

Excuse my poor english, i think i've not explained well my problem...

I don't want to break normalization rules, because the "duplicates" must have a new unique ID.

For example, i'd like to duplicate all the rows associated to the SCHEDA=2 with a new SCHEDA=99, and so the result TableB should then be:
ID-SCHEDA-Col1
1 - 1 - aaa
2 - 2 - bbb
3 - 2 - ccc
4 - 2 - ddd
5 - 3 - eee
6 - 99 - bbb
7 - 99 - ccc
8 - 99 - ddd new rows copied from existing ones with all Cols equals, but with a new unique primary key

I know how to add a single new row, something like:

@newId = select (max(ID)+1) from TableB

Insert into TableB (ID, SCHEDA, Col1)
Select @newId, 99, Col1
From Table1

In my work i have to add like 50 rows in this manner, and i dont want to write it manually...

I could write a stored procedure like
SP_CopyRow(SCHEDA_FROM,SCHEDA_TO, ID_FROM)
which executes the code above

and then write down 50 sentences like...

Sp_CopyRow(2,99,2)
Sp_CopyRow(2,99,3)
Sp_CopyRow(2,99,4)
.....
but in this case i must manually check all the TableB ID's that are associated with the SCHEDA=2 and i could miss something or make errors. Moreover i must copy the values from many more SCHEDA's so i was thinking if there isn't a more compact and intelligent way!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 10:03:42

seems like what you need is this


CREATE PROC CopyData
@SchedaFrom int,
@SchedaTo int
AS

CREATE TABLE #Temp
(
ID int identity(1,1),
Scheda int,
Col1 varchar(100)
)
DECLARE @MaxID int

SELECT TOP 1 @MaxID=ID
FROM YourTable
ORDER BY ID DESC

INSERT INTO #Temp (Scheda,Col1)
SELECT @SchedaTo,Col1
FROM YourTable
WHERE Scheda=@SchedaFrom

INSERT INTO YourTable
SELECT @MaxID+ID,Scheda,Col1
FROM #Temp

--see inserted values
SELECT * FROM YourTable WHERE SCHEDA=@SchedaTo
GO

andrea8877
Starting Member

3 Posts

Posted - 2008-12-11 : 08:09:46

Great visakh,
it is exactly what i was looking for!

I have thought to create a new table with the "copied" rows and then transfer them
into MyTable, but it wasn't so clear...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 09:59:48

No problem...glad that i could help you out
You're welcome

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-12 : 05:06:18

To meet 1st Normal Form you must remove repeating values - you have done the opposite.
You have added repeating values.
If one row is updated, will the other row with DUPLICATE data also need updating?

To get to 1st Normal Form, repeating columns should be in tableA and tableB has a column which references TableA. TableB would then have multiple rows pointing to a single row in TableA.

http://articles.techrepublic.com.com/5100-10878_11-5034716.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources