Creating an insert statement - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Creating an insert statement

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-01-09 : 12:32:48

I have three tables called TaskRequests, TaskDetails and Issues.
Issues table is related to is related to TaskDetails table by IssueID
and TaskDetails table is related to TaskRequests table by TaskID.

The structure is like this:

TaskRequests table
TaskID IDENTITY pk,
other fields

TaskDetails table
DetailsID IDENTITY pk,
TaskID fk, to TaskRequest table
IssueID fk to Issue table
otherfields

Issue table
IssueID IDENTITY pk
other fields.

My question is since TaskID, DetailsID and IssueID are auto numbers,
How do create the insert statement that will ensure that:
1, has the same value in TaskDetails table as it does in TaskRequest table and
similarly how do ensure the same for Issue table and TaskDetails table so relationships between can be easily captured?
Thanks in advance

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2003-01-09 : 12:38:26

I'd use an insert trigger on the a table that you're inserting in to,
you can select the generated id from the "inserted" table (see BOL) and insert this value in to taskdetails. if it all goes wrong nothing will be commited and it'll stay in step. Have a play.

Rob

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-01-09 : 14:16:32

I don't want to have to select a generated number from one table to insert it into another table.
There is got to be a way to have that done automatically.
This becomes even more significant when you attempt to display records from joined tables - you know like
select * from table1, table2, table3 where table1.id = table2.id and table2.id = table3.id.
If anyone can help, this would GREATLY appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-09 : 14:21:51

quote:
I don't want to have to select a generated number from one table to insert it into another table.

This is utterly contrary to what you said here:

quote:
My question is since TaskID, DetailsID and IssueID are auto numbers,
How do create the insert statement that will ensure that:
1, has the same value in TaskDetails table as it does in TaskRequest table and
similarly how do ensure the same for Issue table and TaskDetails table so relationships between can be easily captured?

If you don't want the values to automatically generate, don't use identity.

quote:
There is got to be a way to have that done automatically....If anyone can help, this would GREATLY appreciated.

There is a way, and RobWhitaker described it already. Considering how little real information you provided on both the table structures and the actual insert process, you should be thankful he was able to provide as much as he did.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-01-09 : 16:39:53

robvolk:
I hope I did not say anything offensive to Robwhittaker for his help and kindness.
I am also apologetic to have provided limited info.
Last on this note, I am truly grateful whenever some kind soul like Robwhittaker or you offers your help.
Back to the table issue, what I would not want is to select an identity field that has been created for one table and manually insert it into another table.
I wanted to know if there is a way to say:
insert into table1
values (,,,,)
insert into table1 (id from table1, ,,,,)
values(@id from table1, ,,,,)
etc.
You see this way, whenever an id is created for table1, it is automatically inserted into table2 whenever a record is created for table2.
Sorry again if I am not clear

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-09 : 17:02:01

No offense taken, and sorry if I sounded harsh.

I'm not sure that I see a cause and effect here. Suppose you insert a row into TaskRequests and it generates a new TaskID, which you'd like to carry into TaskDetails. What about the other columns in TaskDetails? What do they get filled with? That part of the process needs some more explanation:

quote:
You see this way, whenever an id is created for table1, it is automatically inserted into table2 whenever a record is created for table2

...because even with the relationships you described it seems like you'd either be inserting incomplete data into related (but functionally separate) tables, or using multiple tables to store data that could possibly be stored in a single table (a one-to-one relationship)

If you can provide an example INSERT statement(s) with some sample data, and the actual results you want to achieve with all of the tables involved, I think it will help. Don't skimp on specifics, use as much info as you can. It would also help to provide some more background on what each table does.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-01-09 : 17:32:13

thanks again robvolk:

Here is a sample stored proc that I created.
What this does is it creates an auto number for the employee table and then moves the identity value of this new record into a variable in the next table called Priority.
the next one follows the same logic.
This way, once a record is created for one table it is automatically created for other tables AND the rest of the fields are then manually entered.
I am dumping the real stored proc that I created here so you can get a good sense of what I am trying to do here.
In short, I have a project that I am working on and we need to be able to say "show the project that an employee is working on.
Since an employee number is an auto number,and project number is an auto number, we have to find a way to determine the relationship between them.
I hope this is a better explanation.
thanks again for all the help

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-09 : 19:04:20

quote:
Here is a sample stored proc that I created.

???? I don't see the code.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-01-10 : 07:40:08

hi robvolk!
I pasted the code into the forum and went home, so I thought.
I did not realize it did get pasted till this morning.
I apologize for that.
Here it is:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spInsertProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spInsertProject]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create Procedure spInsertProject(
@projName varchar(50),
@ServiceType varchar(50),
@assignedTo varchar(50),
@submittedBy varchar(50),
@startDate datetime,
@dueDate datetime,
@completionDate datetime,
@dateUpdated datetime,
@status varchar(50),
@priority varchar(50),
@requestorFname varchar(50),
@requestorLname varchar(50),
@email varchar(50),
@phone varchar(50),
@details varchar(1000))
AS
Declare
@RequestorID int,
@status_id int,
@priority_id int

set @status_id = @@identity
set @priority_id = @@identity

/* Create and populate new Requestor record */
Insert into Employee
(efname,
elname,
email,
phone)
values(@requestorFname,
@requestorLname,
@email,
@phone)

/* Move the identity value of the new record into a variable */
SET @RequestorID = @@IDENTITY

/* Create and populate new Priority record */

INSERT INTO Priority
(
RequestorID,
Priority
)
VALUES
(
@RequestorID,
@Priority
)

/* Move the identity value of the new record into a variable */
SET @Priority_ID = @@IDENTITY

/* Create and populate new status record */

INSERT INTO status
(
priority_ID,
status
)
VALUES
(
@Priority_ID,
@status
)

/* Move the identity value of the new record into a variable */
SET @status_ID = @@IDENTITY

/* Create and populate new Project record */
Insert into project (status_id,
projName,
serviceType,
assignedTo,
submittedBy,
startDate,
dueDate,
dateUpdated,
completionDate,
details)
values(@status_id,
@projName,
@serviceType,
@assignedTo,
@submittedBy,
@startDate,
@dueDate,
@completionDate,
@dateUpdated,
@details)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-01-13 : 22:16:51

robvolk, what happened?
I posted the code and haven't heard from you since.
Can you, please

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-13 : 23:10:14

Sorry about that.

If you only need to know the name of the new employee and project numbers, couldn't you just add this to the end of the sproc:

SELECT @RequestorID AS EmployeeID, @Priority_ID AS ProjectID

You would then get a rowset from the sproc, something like:

EmployeeID    ProjectID
1234          9876

If I'm still missing it, can you provide a sample of the output you're looking for?

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-01-14 : 10:07:42

Thanks for responding!
Not only do I need to know which employees are working on which project, I also need to know the status of the project.

Example would be:
employee project status
simflex tracking in progress.

To do that, I think, we need to join ids from project, employee and status.
The difficult think is how to insert records that will have matching ids from all three giving that the ids are auto numbers.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-01-14 : 10:07:58

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-14 : 10:25:48

SELECT @RequestorID AS EmployeeID, @Priority_ID AS ProjectID, status
FROM status
WHERE Priority_ID=@Priority_ID

Does that work?

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-01-14 : 10:39:23

Hi robvolk!
What I am saying is that that stored proc is not correct.
It returns cartesian values.
I don't think I am handling the @@identity correctly in the insert statement.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-14 : 10:47:50

You'll have to provide an example of:

1. what you want to insert,
2. what you want the results to be,
3. what the results you're getting now look like

Provide ACTUAL OUTPUT from query analyzer, and some actual sample data from your tables. I'm having trouble seeing where cartesian results would occur, because you're only inserting one row into each of the tables.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-01-14 : 14:18:33

Okay, I'm coming to this party a little late, but let me interject a few ideas...

It appears that there are really two different questions going on here. One of them being, how to insert the data, which the stored procedure simflex wrote appears it will handle okay, assuming you're always inserting only one child record upon insertion of the project record. I further assume (dangerous as that is) that there must be some other way to have additional children added later.

The second question seems to be, after the data has been inserted, how do I query the tables to get a status report. Rob's proposals cover you if you're looking for the IDENTITY numbers that were just inserted. However, if you're trying to run this later and get the status of several projects, you need something like this:

SELECT tr.TaskID, td.DetailsID, i.IssueID, s.StatusValue -- add any other desired fields.
FROM TaskRequests tr
JOIN TaskDetails td on td.TaskID = tr.TaskID
JOIN Issue i on i.IssueID = td.IssueID
JOIN Status s on s.StatusID = i.StatusID -- assuming the status is applied by issue, otherwise join to whichever table appropriate
-- add any desired WHERE clause for filtering.

------------------------------------------------------
The more you know, the more you know you don't know.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-01-14 : 21:41:37

AjarnMark and rob, especially rob for your efforts and patience, I thank you too.
What was messing me up earlier was the fact that I have the project table that has multiple foreign keys which AjarnMark touched on with this statement, "I further assume (dangerous as that is) that there must be some other way to have additional children added later."
I did not know how to move the identity seeds from other tables with the the primary keys to the foreign keys in the project table.
I have removed those for now but i still need them.
example will be
project table
projectid pk,
taskid fk
statusid fk

in a situation like this, how do i move identity values of statusid from status table and taskid from task table to project successfully?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources