SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Copy table with multiple SCOPE_IDENTITY()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lucsky8
Posting Yak Master

105 Posts

Posted - 08/20/2014 :  08:20:15  Show Profile  Reply with Quote
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]

Edited by - lucsky8 on 08/20/2014 08:29:24

gbritton
Flowing Fount of Yak Knowledge

1109 Posts

Posted - 08/20/2014 :  08:35:26  Show Profile  Reply with Quote
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.
Go to Top of Page

lucsky8
Posting Yak Master

105 Posts

Posted - 08/20/2014 :  08:49:50  Show Profile  Reply with Quote
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.


Edited by - lucsky8 on 08/20/2014 09:11:40
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1109 Posts

Posted - 08/20/2014 :  09:12:37  Show Profile  Reply with Quote
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.
Go to Top of Page

lucsky8
Posting Yak Master

105 Posts

Posted - 08/20/2014 :  09:22:27  Show Profile  Reply with Quote
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.

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/20/2014 :  13:39:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You should have a look at the OUTPUT operator.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000