how to do this? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

how to do this?

Author

Topic

rosetulip
Starting Member

15 Posts

Posted - 2014-09-17 : 18:43:16

I have a master and a child table

StudentMaster table
ID is PK identity field

StuDetail table
ID is PK identitify field
StudentMasterID is FK

the relationship between table StudentMaster and StuDetail table is one to many by StudentMaster.ID ->StuDetail.StudentMasterID

I want to do the following

1. Insert a row in Master table using the row to be cloned
2. Save the NEW IDENTITY value
3. Insert a row in Child table using the row to be cloned and assign the NEW IDENTITY value saved in step 2.

the data should be from

StudentMaster
ID name address
1 Alex ABC
2 Eric DDD
....

StuDetail
ID StudentMasterID SourceCode
23 1 AZ345
24 1 SF345
....

to after running this procedure IF I choose input ID =1 from StudentMaster table

StudentMaster
ID name address
1 Alex ABC
2 Eric DDD
3 Alex ABC
....

StuDetail
ID StudentMasterID SourceCode
23 1 AZ345
24 1 SF345
25 3 AZ345
26 3 SF345
...

anyone has ideas how to do this in scripts?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-17 : 20:38:03

Maybe:

declare @fromid int;
declare @toid int;
set @fromid=1;

insert into StudentMaster
 ([name],[address])
 select [name]
       ,[address]
   from StudentMaster
  where id=@fromid
;

select @toid=scope_identity();

insert into StuDetail
 (StudentMasterID,SourceCode)
 select @toid
       ,SourceCode
   from StuDetail
  where StudentMasterID=@fromid
;

rosetulip
Starting Member

15 Posts

Posted - 2014-09-19 : 00:40:55

If I add one more detail table for Detail table

StudentMaster -> StuDetail -> MoreDetail
one -> many
one -> Many

Here is the data look like if I choose ID=1 from StudentMaster

------------Before Clone-----------------------

StudentMaster
ID name address
1 Alex ABC
2 Eric DDD
....

Header
ID StudentMasterID SourceCode
23 1 AZ345
24 1 SF345
....

HeaderDetail
ID HeaderID Source
345 23 ppp
346 23 UUU
347 23 POI
348 23 LKI

678 24 OIU
679 24 PYT
680 24 URE
...

-------------After Clone---------------------------

StudentMaster
ID name address
1 Alex ABC
2 Eric DDD
3 Alex ABC
....

Header
ID StudentMasterID SourceCode
23 1 AZ345
24 1 SF345
25 3 AZ345
26 3 SF345
...

HeaderDetail
ID HeaderID Source
345 23 ppp
346 23 UUU
347 23 POI
348 23 LKI

349 25 ppp
350 25 UUU
351 25 POI
352 25 LKI

678 24 OIU
679 24 PYT
680 24 URE

681 26 OIU
682 26 PYT
683 26 URE

How to improve your scripts?

Upendra Gupta
Starting Member

12 Posts

Posted - 2014-09-19 : 06:20:42

unspammed

rosetulip
Starting Member

15 Posts

Posted - 2014-09-19 : 11:15:23

Any one has ideas how to do this? It seems it needs a while loop to accomplish from 2th form to 3 form linking

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-19 : 13:32:13

Try this:

declare @fromid int;
declare @toid int;
set @fromid=1;

insert into StudentMaster
 ([name],[address])
 select [name]
       ,[address]
   from StudentMaster
  where id=@fromid
;

select @toid=scope_identity();

insert into Header
 (StudentMasterID,SourceCode)
 select @toid
       ,SourceCode
   from Header
  where StudentMasterID=@fromid
;

insert into HeaderDetail
 (HeaderId,Source)
 select c.ID
       ,b.Source
   from Header as a
        inner join HeaderDetail as b
                on b.HeaderID=a.ID
        inner join Header as c
                on c.StudentMasterID=@toid
               and c.SourceCode=a.SourceCode
  where a.StudentMasterID=@fromid
;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-19 : 13:37:45

You don't need to write a while loop. Just write a third INSERT statement similar to the second one but targeting the new HeaderDetail table. Give it a go and post your results, whether they work or not.

Note: Since this looks like a homework question, we expect that you'll want to work at writing the query, so that you can build your confidence.

rosetulip
Starting Member

15 Posts

Posted - 2014-09-19 : 23:28:33

This is not a homework question. All the table name and data I make up, not the real table and data I am working on for my project. table name like "Student" is makeup.

rosetulip
Starting Member

15 Posts

Posted - 2014-09-20 : 01:14:37

Here is what I wrote

DECLARE @NewHeaderID integer

SET @NewHeaderID = SCOPE_IDENTITY()

insert into HeaderDetail
(HeaderId,Source)
select @NewHeaderID,
,b.Source
from Header as a
inner join HeaderDetail as b
on b.HeaderID=a.ID
where a.StudentMasterID=@fromid --- I input 1 here
;

It is working. But, not satisfactory Why?

I clone the entire record set when I input 1

I expect the result look like blew

Header
ID StudentMasterID SourceCode
23 1 AZ345
24 1 SF345
25 3 AZ345
26 3 SF345
...

HeaderDetail
ID HeaderID Source
345 23 ppp
346 23 UUU
347 23 POI
348 23 LKI

349 25 ppp
350 25 UUU
351 25 POI
352 25 LKI

678 24 OIU
679 24 PYT
680 24 URE

681 26 OIU
682 26 PYT
683 26 URE

Now it comes like this

Header
ID StudentMasterID SourceCode
23 1 AZ345
24 1 SF345
25 3 AZ345
26 3 SF345
...

HeaderDetail
ID HeaderID Source
345 23 ppp
346 23 UUU
347 23 POI
348 23 LKI

678 24 OIU
679 24 PYT
680 24 URE

349 26 ppp
350 26 UUU
351 26 POI
352 26 LKI

681 26 OIU
682 26 PYT
683 26 URE

All become 26, I would like to see those 23 are 25 and those 24 are 26

How do you recommend improve the script?

rosetulip
Starting Member

15 Posts

Posted - 2014-09-22 : 00:34:12

How do you recommend improve the scripts?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources