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
 Development Tools
 ASP.NET
 Inserting more than one value for a field

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:

EventID
WitnessID
(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, WitnessID
1,3
1,4
1,5

Only 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 NumberOfwitnesses
FROM EventWitnesses

and, of course, join to the Events table and the Witnesses table for more info in your queries.

I hope this helps.


- Jeff

Edited by - jsmith8858 on 04/19/2003 10:36:16
Go to Top of Page

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.
Event
EventID,
IncidentID,
WitnessID

But 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 table
witnessID,
witnessName1,
WitnessAddress1,
WitnessCity1,
WitnessState1,
WitnessZip1

witnessName2,
WitnessAddress2,
WitnessCity2,
WitnessState2,
WitnessZip2,

WitnessName3,
WitnessAddress3,
WitnessCity3,
WitnessState3,
WitnessZip3,

WitnessName4,
WitnessAddress4,
WitnessCity4,
WitnessState4,
WitnessZip4,

WitnessAddress5,
WitnessName5,
WitnessCity5,
WitnessState5,
WitnessZip5

The 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.

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-19 : 11:44:54
How about this:

http://www.sqlteam.com/item.asp?ItemID=2652

There's also a variation here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12538

That uses fixed-length strings. You can insert pretty much any number of rows with one call to the INSERT statement. There's also some code from AjarnMark that uses multiple CSV's but I can't remember which forum it's in. If I find it I'll post the link here.

Go to Top of Page

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.asp
This 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.

Go to Top of Page
   

- Advertisement -