select...insert
-
Can someone please tell me how to get this to work.
I have 2 tables:
actions and actionType.
ActionType has prepopulated data.
actionTyeID int pk
actionType varchar(50)
the sample values are:
actionTypeID ActionType
1 MoveForward
2 MoveBackwards
3 Pause
4 Play
5 Stop
Actions has following:
ActionID int pk identity
ActionTypeID int (from actionType table)
ActionDesc varchar(50),
actionDate datetime,
actionReport varchar(50).
Since actionType table has pre-populated data,
I need to select the id and insert it into Actions table.
At the same time, I want to insert the rest of the data into Actions table.
Here is an example of what I am talking about.
Insert into Actions(
ActionTypeID ...(this is from actionType table),
ActionDesc,
actionDate,
actionReport
)
values(
value (from actionType table),
@ActionDesc, @actionDate,@actionReport
)
How do I get the value for actionTypeID from actionType table and insert that value into actions table with the rest of data
from actions table?
I hope this question is clear.
Thanks a lot
-
Unless you can associate the actionTypeID with something to derive the desc, date and report or simply use constants to default these columns you can't.
Unless I'm missing something obvious you have not got enough information to proceed.
-
If you want to insert five rows for every @ActionDesc, @ActionDate, and @ActionReport then in would be as simple as
insert into Actions (ActionTypeID,ActionDesc,ActionDate,ActionReport)
select at.ActionTypeID,
@ActionDesc,
@ActionDate,
@ActionReport
FROM ActionType at
I am not sure this is what you are looking for but with what you have provided, it is the only thing I could come up with.
-
Hi I believe I understand what you are saying and I am associating actionTypeID with desc to another table called Spinoff.
the spinoff table also has a prepopulated data.
which looks like this:
spinOffID int pk,
spinOffName varchar(50),
actionTypeID int (from actionType table).
Then spinOff looks like
spinOffID spinOffName actionTypeID
1 2minSpinoff 1
2 Oldies 1
and it goes on from 1111, 2222, etc
So since these two tables are prepopulated, and since spinoff is associated with actions based on actiontype, there is got to be a way to select actionTypeID and insert it int actions table while at the same time inserting the rest of the records for actions table.
Please let me know if it is clear enough.
It is kind of cray names, I know.
-
tkbr0wn,
sorry I have already posted a response to amelvin before I saw your post.
Yes what you provided is exactly what I was looking for.
I was just wondering if it would work if I did it the way you have it.
I will test that.
If it works, it is exactly I what I needed.
thank you !
-
insert into Actions (ActionTypeID,ActionDesc,ActionDate,ActionReport)
select at.ActionTypeID,
@ActionDesc,
@ActionDate,
@ActionReport
FROM ActionType at
I didn't think the above would work and it didn't.
-
If the variables are declared and properly populated, it will work.
-
I am sorry tkbr0wn, you are right it worked.
I did a research last night and found an example almost exactly as what you posted now, but one of the sql server experts on the forum said it was not possible to code it that way.
I didn't bother trying it.
It compiled but the one thing that is not happening, though is that actionType from actionType table is not being derived which is what amelvin aluded to.
On the user's input form, they have a drop down menu of actionTypes that are coming from the actionType table.
So a user would select an actionType and insert the record into action table but since actionType table is pre-populated with values for actionType field, there is got to be a way for the user to tie all of these together.
So right now, even thoug the store proc is compiling, it is not working.
-
Hi simflex,
You sure know how to turn someone's brain to mush with your questions.
You say tkbr0wn's solution is what you are looking for but it doesn't work! In your first post you specified two tables and data, where does SpinOff come into it? Can u post proc and any additional data and more inportantly what output you expect.
Far away is close at hand in the images of elsewhere.
Anon. -
hi David!
I apologize twice!.
First for just getting your response to my threaded question.
I checked a few times and gave up hope of getting any response.
Second, I apologize for not knowing how to ask a well thought-out and clearly defined question.
That contributes a lot to my questions not being answered.
In any case, my boss decided to let someone else deal with that stored proc.
I did however get something similar.
I am attaching the entire code.
Basically, what I am asking for here is similar to the question here and that is.
I have two tables that have pre-populated data.
These tables are tblEmployee and tblDept.
tblDept has deptid and deptname.
tblEmp has empid,fname, lname, address, city, state, zip, phone, email
I am trying to populate the main table called accidentMain by selecting information from tblDept and tblEmp.
As you can see from the code I am attaching, if I was to insert data into the two tables in question, I can do so and then move their identity value into the accidentMain table but that is not the case.
I am hope it isn't terribly confusing.
Please take a look.
Always good to hear from you.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertAccidentRep]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertAccidentRep]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Procedure InsertAccidentRep(
@AccidentDesc varchar(3000),
@AccidentDay datetime,
@LocationDesc nvarchar(50),
@FactorsDesc nvarchar(50),
@CollisionDesc nvarchar(50),
@WeatherDesc nvarchar(50),
@OfficersName nvarchar(50),
@HeadQuarters nvarchar(50),
@TimeOfAccident nvarchar(50),
@TimeOfAccidentAMPM nvarchar(50),
@BadgeID int,
@caseNumber int,
@TagNumber nvarchar(50),
@CarYr int,
@carType nvarchar(50),
@CarMake nvarchar(50),
@CountyVehicleNo nvarchar(50),
@DamageCost money,
@InsuranceCo nvarchar(50),
@DamageDesc nvarchar(50),
@vin nvarchar(50),
@carModel nvarchar(50),
@DriverLicNumber nvarchar(50),
@LName nvarchar(50),
@FName nvarchar(50),
@DriverGender nvarchar(50),
@PhoneHome nvarchar(50),
@PhoneWork nvarchar(50),
@Address nvarchar(50),
@City nvarchar(50),
@State nvarchar(50),
@ZipCode nvarchar(50),
@Email_Add nvarchar(50),
@Age nvarchar(50),
@AtFault nvarchar(50),
@DrugTest nvarchar(50),
@Result nvarchar(50),
@TestType nvarchar(50),
@ConditionDesc nvarchar(50),
@InjuryDesc nvarchar(4000)
)
AS
Declare @AccidentID int
Declare @DeptID int
Declare @VehicleID int
Declare @DriverID int
Declare @InsuranceID int
Declare @EmpID int
Begin
/* Create and populate new AccidentMain Record */
INSERT INTO AccidentMain(
EmpID,
DeptID,
AccidentDesc,
AccidentDate,
TimeOfAccident,
TimeOfAccidentAMPM,
Loc_Desc,
FactorsDesc,
CollisionDesc,
WeatherDesc,
AtFault,
DrugTest,
Result,
TestType,
VehicleID,
ConditionDesc,
InjuryDesc
)
VALUES (
@EmpID,
@DeptID,
@AccidentDesc ,
@AccidentDay ,
@TimeOfAccident,
@TimeOfAccidentAMPM,
@LocationDesc ,
@FactorsDesc ,
@CollisionDesc ,
@WeatherDesc,
@AtFault,
@DrugTest,
@Result,
@TestType,
@VehicleID,
@ConditionDesc,
@InjuryDesc
)
/* Move the identity value of the new record into a variable */
SET @AccidentID = @@IDENTITY
/* Create and populate new Police table record */
INSERT INTO thePolice(
AccidentID,
OfficersName,
HeadQuarters,
BadgeID,
CaseNumber
)
VALUES(
@AccidentID,
@OfficersName,
@HeadQuarters,
@BadgeID,
@CaseNumber
)
/* Create and populate new Insurance table record */
INSERT INTO theInsurance(
InsuranceCo
)
VALUES(
@InsuranceCo
)
/* Move the identity value of the new record into a variable */
SET @InsuranceID = @@IDENTITY
/* Create and populate new theVehicle record */
INSERT INTO theVehicle(
AccidentID,
InsuranceID,
TagNumber,
CarYr,
carmodel,
CarMake,
CountyVehicleNo,
DamageCost,
DamageDesc,
VIN,
DriverLicNumber
)
VALUES (
@AccidentID,
@InsuranceID,
@TagNumber,
@CarYr,
@carType,
@CarMake,
@CountyVehicleNo,
@DamageCost,
@DamageDesc,
@vin,
@DriverLicNumber
)
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
No need to apologize, it's easy to understand your own systems and problems but trying to explain them to someone else!!
That's in the lap of the gods
As to your problem. I assume (nasty pratice) that you do not want to create emp or dept records (is there a link between emp and dept tables?) but find their values before inserting AccidentMain. Only two ways I know of, either pass the values to the proc (therefore calling app needs to get them) or set them in this proc before the insert like
select @EmpID = e.EmpID,@DeptID = d.DeptID
from tblEmployee e
inner join tblDept d on d.deptid = e.deptid
where e.lname = @LName
and e.fname = @FName
if (@EmpID IS NULL) or (@DeptID IS NULL)
... error processing here
This assumes link between tables and that the name passed to the proc is the employee name.
Hope this helps.
Far away is close at hand in the images of elsewhere.
Anon. -
hi David!
Thanks again.
Please take a look here:
http://www26.brinkster.com/simflex/kenig/FrmAccident.asp
So yes you are right.
The ideal situation would be to pick the names and have all data related to employee automatically populate text fields related to employee but your feedback is a good start.
-
Based on the form and assuming the Employee and Department are generated from a database table I would change the drop downs to include the id's and pass the return values to the proc
<select name="ename">
<option value="1">simflex</option>
<option value="2">janny</option>
<option value="3">mat</option>
<option value="4">ryan</option>
</select>
<select name="Driverdept">
<option value="1">Administration</option>
<option value="2">Safety</option>
<option value="3">Transportation Svcs</option>
<option value="4">Water Services</option>
<option value="5">Pruchasing</option>
</select>
Far away is close at hand in the images of elsewhere.
Anon. -
hi David!
I think your comment about passing id to stored proc is based having viewed the source of the form I gave a link to.
Well, I coded the dropdown to pull the information dynamically from the database.
It would have been easier to code everything using asp but I have been getting an error.
That is why I turned to stored proc.
Given the fact that I am dynamically populating the dropdown from database, will your original suggestion still work?
I mean this suggestion:
select @EmpID = e.EmpID,@DeptID = d.DeptID
from tblEmployee e
inner join tblDept d on d.deptid = e.deptid
where e.lname = @LName
and e.fname = @FName
-
Maybe I confused you. Basically to use your proc you need to derive EmpID and DeptID. All I was suggesting was since you already populate the drop downs from a database you already know the corresponding id's. If you change the form to put the id from the db as the option value as per my example then Request.Form("ename") and Request.Form("Driverdept") will get you the id's. Add two parameters to your proc
@EmpID int,
@DeptID int,
before @AccidentDesc
and remove the two declares for @EmpID & @DeptID
then you can pass the values of the request to the proc.
Hope this makes sense!
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply