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.
Author |
Topic |
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2003-04-19 : 10:21:47
|
Ok, I am not if I should post this thread in T-SQL forum but having visited this forum deveral times, I am confident there is enough talent to guide with the best way to solve this problem.I am trying to write an insert statement that will insert up to 5 values for the same field.For instance, I have a table called Witness table.According to our specs, whenever an incident occurs, there could be 0 to 5 witnesses on the scene of the incident.My question is, how can I construct the insert statement so that the same fied can be used to capture all the witnesses at the scene of the incident.Here is my table structure:Witness table:witnessId int pk, identity,witnessName varchar(50),WitnessAddress varchar(50),WitnessCity varchar(50),WitnessState varchar(50),WitnessZip varchar(50),WitnessPhone varchar(50)If it is easier to write with a stored procedure, please help me with this and I can then use ASP to call the stored proc.If it is easier to embed it in asp code, that would be great too as long as it gives me the ability to insert all the witness records.I have other tables to insert but this is the one that gives me the problem.Thanks in advance |
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-19 : 10:35:24
|
You need a table that links all events to the witnesses that were there; you are correct in that you want to store all of the witnesses in the same column, but you want to do it over a series of rows.You showed us your Witness table (looked good), and I assume you have a table of Events.In that case, to "link them up", you create a table of "EventWitnesses" with this structure:EventIDWitnessID(any other info about this event/witness combo you wish to store)The primary key of this table should be a composite key of eventID and WitnessID.So, if you had an event 1 and it was witnessed by witnesses 3,4, and 5, your table would like this:EventID, WitnessID1,31,41,5Only store data in this table that relates to BOTH the witness and the event; for example, don't store event information or witness infomation, but maybe info like "how well did they see it" or "how well do they recall it", etc.Once that table is set up, it should be really easy for you to maintain. To add a witness to the event, you just add a row. To find out how many witnesses witnessed an event:Select EventID, count(*) as NumberOfwitnessesFROM EventWitnessesand, of course, join to the Events table and the Witnesses table for more info in your queries.I hope this helps.- JeffEdited by - jsmith8858 on 04/19/2003 10:36:16 |
 |
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2003-04-19 : 11:29:53
|
hi Jeff!Thanks for the response.I understand what you are saying but I am not sure it will solve my problem.I do have an event table. I also have an incident table.The structure is more like.EventEventID,IncidentID,WitnessIDBut the thing is there is got to be a way to insert multiple records for same field.The unormalized witness table would look like this:Witnesses tablewitnessID,witnessName1,WitnessAddress1,WitnessCity1,WitnessState1,WitnessZip1witnessName2,WitnessAddress2,WitnessCity2,WitnessState2,WitnessZip2,WitnessName3,WitnessAddress3,WitnessCity3,WitnessState3,WitnessZip3,WitnessName4,WitnessAddress4,WitnessCity4,WitnessState4,WitnessZip4,WitnessAddress5,WitnessName5,WitnessCity5,WitnessState5,WitnessZip5The way I did it when I used to be an oracle person is declare all my parameters like:create procedure insertWitness(witnessName1 IN witnesses.witnessName%type,witnessName2 IN witnesses.witnessName%type''''As you can see from this example, you can insert multiple values for any field.Then a cursor will be used to loop through them inserting all values for any field.I have been looking for the code.If I can find it, I am sure that if I can't convert that to sql server, I am sure one of you gurus will. |
 |
|
robvolk
Most Valuable Yak
15732 Posts |
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2003-04-19 : 12:43:00
|
hi robvolk!thanks for the response!Please take a moment to look at this link.http://www26.brinkster.com/simflex/kenig/accident_Report.aspThis is what I am currently working on.Although it is in its infancy stage, as you can see, there will be a total of 5 witnesses (I currently have it setup to accept 3).So for every accident, there is a likelyhood of 0 to 5 witnesses being on the scene.I have other similar situations as this but once I figure out the witness thing, I can apply that method to the rest of them. |
 |
|
|
|
|
|
|