Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Updated muliple rows into one column? Why is it hard? HELP Expand / Collapse
Posted Tuesday, May 7, 2013 2:35 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 6, 2016 4:59 AM
Points: 80, Visits: 681
Thanks c_o! it works perfectly

just a a few simply questions relating to my actual table which contains 200000+ rows and additional columns I did not include in the sample table.

If wanted to add more groups into EventRole, I would simply use the sql scrip below?
CREATE TABLE [dbo].[Registered]
[ParticipantID] INT IDENTITY(1,1) NOT NULL,
[RegisteredInfo] VARCHAR(50) NOT NULL,
INSERT INTO [dbo].[EventRole] VALUES ('Registered','4')

Do I also need to add every participant level manually? I have 200000+ participant in the table.
INSERT INTO [dbo].[Participants] VALUES ('Person1','1')
INSERT INTO [dbo].[Participants] VALUES ('Person2','1')
INSERT INTO [dbo].[Participants] VALUES ('Person3','2')
INSERT INTO [dbo].[Participants] VALUES ('Person4','2')

ParticipantMap table is simple matching the EventRole to the participant table but is this manually entry also?
INSERT INTO [dbo].[ParticipantMap] VALUES ('1','1')
INSERT INTO [dbo].[ParticipantMap] VALUES ('1','3')
INSERT INTO [dbo].[ParticipantMap] VALUES ('1','4')

Thanks for the help Again!
Post #1450334
Posted Thursday, May 9, 2013 4:06 AM


Group: General Forum Members
Last Login: Monday, April 25, 2016 3:18 AM
Points: 11, Visits: 61
Ya, see, if you already have a data set in your original schema, it makes it a bit more difficult, as you will have to migrate your data to the new schema, if the setup was for an app which will be receiving data for the first time, then it would almost be easier, as you can set up the schema and have the app populate it in a specific format,

To answer the first insert portion, no, the whole idea is to have all your event roles in one table, and to not have to create a table/column for each type, then assigning your new type to a participant,

it basically works like this:

Case 1
--New participant, existing role
----Add participant with id of role to which they belong to in participants table

Case 2
--New participant, new role
----Add role to eventrole table, with the id of the parent role if it has one (say like student, would have id of teacher in parentid, and teacher would have principal's id in parentid, but principal would have null)
----add participant with id of new role created
**This will ensure that you can always add new roles,without having to do any changes to your schema

in your case, you don't have to re-capture all the data, but to migrate may be a bit difficult, depending on your current data structure, there were a few different versions you mentioned previously in your post, so by now I am not sure which one you have currently, but what you basically need to do is to populate the specific fields of the new schema table by doing a select from your current schema table to be in the format of the new schema table

ex. say your current format is in the format where you have an event with different participants and then you have a column which identifies the role of the participant, (as I losely understand, this is how your current schema is), in this case you will do your insert into the new schema participant table as follows:

--say you want to add all the participants who are speakers
INSERT INTO Participants
SELECT ParticipantName
, 1
FROM OriginalTable
WHERE isSpeaker = -1

then from there on, you can do that to "map" each of the participants to a role and thus build your participants table, you will also have to find similar ways of populating the Event and Participant map tables,

I hope it makes sense, it makes it a bit difficult as currently I am not even sure if your original tables are in such a format so that you will be able to do extracts like these, but I am sure if you understand exactly how the new schema works, you will be able to do the mappings like this, and thus populate the tables from your original tables with only a little effort, please do not go and try to re-type everything, or something like that, there is always an easier way
Post #1450981
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse