How to insert data from same set of tables selecting different id from the same

Question

How to insert data from same set of tables selecting different id from the same

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
October 6, 2022 at 2:02 pm

#4096242

I am trying to insert same set of records with student id 1234 (All 4 records with student id 1234 needs to be inserted into same table but instead of
student id 1234. i want the id to be selected 9876)
Below is the sample data Student ID 1234 below points to specific case from casetype table where type = 'HOLL' and Subtype = 'SUXX'
Student ID 9876 below points to specific case from casetype table where type = 'HOLL' and Subtype = 'GEN'
StudentID StudentName StudentKey StudentAccKey StartDate EndDate
1234 john 12455666 875755656 2022-10-06 NULL
1234 jim 12455666 875755656 2022-10-06 NULL
1234 Sam 12455666 875755656 2022-10-06 NULL
1234 Jack 12455666 875755656 2022-10-06 NULL
9876 Rap 12455666 567776665 2022-10-06 NULL
9876 Swer 12455666 567776665 2022-10-06 NULL
I am selecting columns from student table. where type = 'HOLL' and Subtype = 'SUXX' , in that case only those 4 records with student id 1234 will be inserted.
but how i can change or update student id to different 9876 in the select caluse. I tried to use case statement in select but it didnt worked
like case when ct.type = 'HOLL' and ct.Subtype = 'GEN' then ct.studentid end as studentid
insert into Student (studentid,
StudentName,
StudentKey,
StudentAccKey,
StartDate,
EndDate)
SELECT studentid,
StudentName,
StudentKey,
StudentAccKey,
StartDate,
EndDate
FROM Student std
join casetype ct on std.studentid = ct.studentid
where ct.type = 'HOLL' and ct.Subtype = 'SUXX' and std.StudentKey = 12455666

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76996 More actions · Answer 1

This is as clear as mud. You've been around long enough to know that you should post sample data and expected results using (temp) table creates and inserts.

Also, what is the logic for substituting 9876 for 1234?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

If this is a one off, then instead of the studentid in the SELECT, then put the scalar value instead.

As Drew noted, however, make your question easier for people to answer with code (formatted).

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

You have 1 common StudentAccKey with 1 common StudentKey and 1 common StartDate.

And yet there are two StudentID's and six different StudentName's.

The code that you're using to get the data has a massive problem and you need to fix that before doing anything else.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 4

ID Grpkey StudentKey StudentLockKey StudentPeriodKey BeginDate EndDate Type SubType

5799 1234 1224567 8876535 1298777 2020-1-20 NULL HOLL SUX

5799 4355 1224567 8876535 1298785 2020-1-20 NULL HOLL SUX

5799 2677 1224567 8876535 1298789 2020-1-20 NULL HOLL SUX

5799 7645 1224567 8876535 1298790 2020-1-20 NULL HOLL SUX

5799 2456 1224567 8876535 1298791 2020-1-20 NULL HOLL SUX

9876 6788 1224567 8876535 1298793 2020-1-20 NULL HOLL GEN

9876 8664 1224567 8876535 1298796 2020-1-20 NULL HOLL GEN

9876 5777 1224567 8876535 1298791 2020-1-20 NULL HOLL GEN

Here is sample Data.

For mockup Data, We are trying to insert same set of records with ID 5799 (total 5 records) into same table , but i dont want all 5 records with same ID 5799.

it shoudl be different caseid 9876. for those first 5 records. When ever student detail records gets created on UI it creates with an ID and associated to Type and SUb type.

pietlinden SSC Guru Points: 63506 More actions · Answer 5

Didn't Steve answer your question? Seemed pretty clear to me. you just overwrite the ID = 1234 with another value.

insert into Student (studentid,

StudentName,

StudentKey,

StudentAccKey,

StartDate,

EndDate)

SELECT 9786,

StudentName,

StudentKey,

StudentAccKey,

StartDate,

EndDate

FROM Student std

join casetype ct on std.studentid = ct.studentid

where ct.type = 'HOLL' and ct.Subtype = 'SUXX' and std.StudentKey = 12455666

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 6

Yes he answered, i got it. But the question is we cannot hardcode 9786 in select. because there are tons of records with different student keys. i Just took sample student key. We have to insert the records for each student key based on type and sub type. but in select we have to use id as with type HOLL and Subtype GEN. for each student key.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 7

You haven't explained how you use the other keys. What you need to do is help develop an algorithm. Explain for 1234 how you derive this? Or for 9786, how do you use other values to get this.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 8

mcfarlandparkway wrote:

We have to insert the records for each student key based on type and sub type. but in select we have to use id as with type HOLL and Subtype GEN. for each student key.

What do you mean by "based on"? Be specific, because it is not clear.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel