Copy table with multiple SCOPE_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 2008 Forums

Transact-SQL (2008)

Copy table with multiple SCOPE_IDENTITY()

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2014-08-20 : 08:20:15

Hi, i have 3 tables.
The user can create there on Pyramide and after they are able to duplicate. When they click duplicate there a popup asking them witch school they want to duplicate the Pyramide.
So i am able to copy tblPyramide and tblPyramideNiveau (with SCOPE_IDENTITY())
The problemes is with tblPyramideNiveau and tblPyramideNiveauCategorie
I need to find away when i insert tblPyramideNiveau it will insert all the category thats goes with it with the new id.

Is there away when i insert for tblPyramideNiveau to do the insert for tblPyramideNiveauCategorie with the new Id.
Maybe a way to do 2 insert at the same times.
Exemple :
In part 2
insert 1 Niveau then go insert all Categorie for that Niveau, move to second niveau then insert all second Categorie etc
I hope i explain my self ok :)

Here my stored procedure i have so far

Should i do the last part asp.net?


@intDistrictCd INT,
@intPyramideId INT,
@intEcoleProvenanceId INT,
@intEcoleToCopyeId INT

-- Part 1
-- Insert Pyramide To new @intEcoleToCopyeId
INSERT INTO tblPyramide (strTitre,strDescription,intDistrictCd,intEcoleId)
SELECT DISTINCT tp.strTitre,tp.strDescription,tp.intDistrictCd,@intEcoleToCopyeId
FROM	 tblPyramide as tp 
	 INNER JOIN tblPyramideNiveau as tpn ON tpn.intPyramideId = tp.intPyramideId 
	 INNER JOIN tblPyramideNiveauCategorie as tnc ON tnc.intPyramideNiveauId = tpn.intPyramideNiveauId
WHERE	 tp.intDistrictCd = @intDistrictCd AND tp.intEcoleId = @intEcoleProvenanceId
	 AND tp.intPyramideId = @intPyramideId

-- Part 2
-- Insert the Niveau with SCOPE_IDENTITY()
INSERT INTO tblPyramideNiveau (intNiveau,strTitre,strDescription,intPourcentage,intPyramideId)
SELECT DISTINCT  tpn.intNiveau,tpn.strTitre,tpn.strDescription,tpn.intPourcentage,SCOPE_IDENTITY()
FROM	 tblPyramide as tp 
	 INNER JOIN tblPyramideNiveau as tpn ON tpn.intPyramideId = tp.intPyramideId 
	 INNER JOIN tblPyramideNiveauCategorie as tnc ON tnc.intPyramideNiveauId = tpn.intPyramideNiveauId
WHERE	 tp.intDistrictCd = @intDistrictCd AND tp.intEcoleId = @intEcoleProvenanceId
AND tp.intPyramideId = @intPyramideId


-- Part 3
-- missing insert to get all category with the new link ID from Niveau
-- Not working 
-- I need to find away when to insert tblPyramideNiveau it will insert all the category thats goes with it.
INSERT INTO tblPyramideNiveauCategorie (strTitre,strDescription,intPyramideNiveauId)
SELECT   tnc.strTitre,tnc.strDescription,SCOPE_IDENTITY()
FROM	 tblPyramide as tp 
	 INNER JOIN tblPyramideNiveau as tpn ON tpn.intPyramideId = tp.intPyramideId 
	 INNER JOIN tblPyramideNiveauCategorie as tnc ON tnc.intPyramideNiveauId = tpn.intPyramideNiveauId
WHERE	 tp.intDistrictCd = @intDistrictCd AND tp.intEcoleId = @intEcoleProvenanceId
	 AND tp.intPyramideId = @intPyramideId

Here are my table
Table 1


[dbo].[tblPyramide](
[intPyramideId] [int] IDENTITY(1,1) NOT NULL,
[strTitre] [varchar](200) NULL,
[strDescription] [varchar](max) NULL,
[intDistrictCd] [int] NULL,
[intEcoleId] [varchar](50) NULL

Table 2

[tblPyramideNiveau](
[intPyramideNiveauId] [int] IDENTITY(1,1) NOT NULL,
[intNiveau] [int] NULL,
[strTitre] [varchar](200) NULL,
[strDescription] [varchar](max) NULL,
[intPourcentage] [int] NULL,
[intPyramideId] [int]

Table 3

[tblPyramideNiveauCategorie](
[intPyramideNiveauCategorieId] [int] IDENTITY(1,1) NOT NULL,
[strTitre] [varchar](200) NULL,
[strDescription] [varchar](max) NULL,
[intPyramideNiveauId] [int]

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-20 : 08:35:26

That's and odd way to use SCOPE_IDENTITY! However, when you say part 3 is not working do you mean that you get an error message or that you get incorrect results? If you get an error message, please post it. If you get incorrect results, then POST:

1. INSERT INTO statements to populate all three tables with initial data (so Part 1 will work)
2. The contents of all three tables you expect to see if all three inserts work the way you want.

lucsky8
Posting Yak Master

105 Posts

Posted - 2014-08-20 : 08:49:50

Part 3 is working but not in the wright way.
I will give an exemple of something i want to duplicate
The user will choose i want to duplicate intPyramideId (1) from intEcoleId(1555) to intEcoleId(2667)
So i need to take tblPyramide (1) and copy all is Niveau and categorie to the new school 2667.
So the school 2667 will be able to change the Pyramide like they want with out affecting the one from 1555

I am hopen to suggestion and tks :)

tblPyramide (intPyramideId,strTitre,strDescription,intDistrictCd,intEcoleId)

1 Luc Somehing 50 1555

Here i will need to insert the new intPyramideId

tblPyramideNiveau (intPyramideNiveauId,intNiveau,strTitre,strDescription,intPourcentage,intPyramideId)

1 1 aaa test 100 1
2 2 bbb test 20 1
3 3 ccc Test 10 1
4 4 ddd test 2 1

Here i will need to insert the new intPyramideNiveauId

tblPyramideNiveauCategorie (intPyramideNiveauCategorieId,strTitre,strDescription,intPyramideNiveauId)
1 réussissent 1
2 avec plans 1
3 avec temps et appui supplém 1
16 Observations / interventions 2
17 atteignent pas 2
20 Interventions 2
21 Cueillette de données 3
22 Rédaction 3
23 Interventions spécifiques 3
24 Équipe stratégique du District 4

So the new result for the new school would be :

tblPyramide (intPyramideId,strTitre,strDescription,intDistrictCd,intEcoleId)

2 Luc Somehing 50 2667

tblPyramideNiveau (intPyramideNiveauId,intNiveau,strTitre,strDescription,intPourcentage,intPyramideId)

5 1 aaa test 100 2
6 2 bbb test 20 2
7 3 ccc Test 10 2
8 4 ddd test 2 2

tblPyramideNiveauCategorie (intPyramideNiveauCategorieId,strTitre,strDescription,intPyramideNiveauId)
25 réussissent 5
26 avec plans 5
27 avec temps et appui supplém 5
28 Observations / interventions 6
29 atteignent pas 6
30 Interventions 6
31 Cueillette de données 7
32 Rédaction 7
33 Interventions spécifiques 7
44 Équipe stratégique du District 8

quote:
Originally posted by gbritton

That's and odd way to use SCOPE_IDENTITY! However, when you say part 3 is not working do you mean that you get an error message or that you get incorrect results? If you get an error message, please post it. If you get incorrect results, then POST:

1. INSERT INTO statements to populate all three tables with initial data (so Part 1 will work)
2. The contents of all three tables you expect to see if all three inserts work the way you want.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-20 : 09:12:37

Please repost your data as INSERT INTO statements. This is so we can copy and paste those statements into SSMS and execute them directly withoug formatting.

Also, please post the desired state of all three tables after the successful completion of a correct query.

lucsky8
Posting Yak Master

105 Posts

Posted - 2014-08-20 : 09:22:27

Tks for your help but since i am better with .net :) and part 1 et 2 work the way i want i will do the third part in .net i think it will be easier for me.

I will try it and let you know.

Many tks for your help
Lyc

quote:
Originally posted by gbritton

Please repost your data as INSERT INTO statements. This is so we can copy and paste those statements into SSMS and execute them directly withoug formatting.

Also, please post the desired state of all three tables after the successful completion of a correct query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-20 : 13:39:27

You should have a look at the OUTPUT operator.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources