Multiple Inserts

Hello I am trying to optimize a Stored Procedure and am having no luck
It comes from using a Cursor Inserting a record then using that records
ID to insert another record. Here is the code
OPEN _cursor
FETCH NEXT FROM _cursor
INTO @.Program, @.Year, @.JobID, @.EmpID
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM _cursor
INTO @.Program, @.Year, @.JobID, @.EmpID
SET @.msg = 'Job# : ' + CAST(@.JobID as nvarchar(20)) + ' Has no
Installation Rate for the Following Combination. '
SET @.msg = @.msg + 'Program : ' + @.Program + ', Year : ' + @.Year
SELECT @.isNEW = WFTID FROM tblWorkFlowTasks
WHERE ARID = @.AREA AND WFTTID=@.TT AND ID = @.JobID
-- Only Insert New Records
if(isnull(@.isNEW, 0) = 0)
BEGIN
INSERT INTO tblWorkFlowTasks(ARID, ID, StatusID, Description, WFTTID)
VALUES (@.AREA, @.JobID, @.STOpen, @.msg, @.TT)
SET @.WFTID = @.@.IDENTITY
-- Assign To User
INSERT INTO tblWorkFlowTaskAssignees (WFTID, UserID) VALUES
(@.WFTID,@.EmpID)
END
END
CLOSE _cursor
DEALLOCATE _cursor
I was wondering I could use an insert statement inside of an insert
statement
or specify SP VAlues from a SELECT Statement?What exactly is the business requirement here? Inserting master and detail
rows in one step?
BTW: that piece of code is actually made up from examples of not bad, but
*terrible* practices.
ML|||Any Help would be nice thanks.
Yes I know there are bad practices there. Hence trying to optimize it
takes
Over 3 minutes to run but if run each separately by hand it takes 20
secs.
I am trying to Insert a Master Records based on a Query/Temp Table.
And then Insert a Detail record for the Inserted Records. All In one
step with out cursors
Evil little devils.
Thanks Again|||How about using an Insert trigger to populate your second table. That way
you could remove the inefficient cursor. If this sounds like a viable optio
n
for you I'll supply some example code if required.
--
Adam J Warne, MCDBA
"EzraB" wrote:

> Hello I am trying to optimize a Stored Procedure and am having no luck
> It comes from using a Cursor Inserting a record then using that records
> ID to insert another record. Here is the code
>
> OPEN _cursor
> FETCH NEXT FROM _cursor
> INTO @.Program, @.Year, @.JobID, @.EmpID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM _cursor
> INTO @.Program, @.Year, @.JobID, @.EmpID
>
> SET @.msg = 'Job# : ' + CAST(@.JobID as nvarchar(20)) + ' Has no
> Installation Rate for the Following Combination. '
> SET @.msg = @.msg + 'Program : ' + @.Program + ', Year : ' + @.Year
>
> SELECT @.isNEW = WFTID FROM tblWorkFlowTasks
> WHERE ARID = @.AREA AND WFTTID=@.TT AND ID = @.JobID
> -- Only Insert New Records
> if(isnull(@.isNEW, 0) = 0)
> BEGIN
> INSERT INTO tblWorkFlowTasks(ARID, ID, StatusID, Description, WFTTID)
> VALUES (@.AREA, @.JobID, @.STOpen, @.msg, @.TT)
> SET @.WFTID = @.@.IDENTITY
>
> -- Assign To User
> INSERT INTO tblWorkFlowTaskAssignees (WFTID, UserID) VALUES
> (@.WFTID,@.EmpID)
> END
> END
> CLOSE _cursor
> DEALLOCATE _cursor
> I was wondering I could use an insert statement inside of an insert
> statement
> or specify SP VAlues from a SELECT Statement?
>|||Hi EzraB
You can avoid using the cursor to increase the performance.
You can re-write whole thing as:
INSERT INTO tblWorkFlowTasks(ARID, ID, StatusID, Description, WFTTID)
SELECT @.AREA, @.JobID, @.STOpen, 'Job# : ' + CAST(@.JobID as nvarchar(20)) + '
Has no Installation Rate for the Following Combination. ' + 'Program : ' +
@.Program + ', Year : ' + @.Year, @.TT
FROM <condition in cursor>
where isnull(@.isNEW, 0) = 0
INSERT INTO tblWorkFlowTaskAssignees (WFTID, UserID)
SELECT @.IDENTITY, @.EmpID
FROM <condition in cursor>
where isnull(@.isNEW, 0) = 0
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"EzraB" wrote:

> Hello I am trying to optimize a Stored Procedure and am having no luck
> It comes from using a Cursor Inserting a record then using that records
> ID to insert another record. Here is the code
>
> OPEN _cursor
> FETCH NEXT FROM _cursor
> INTO @.Program, @.Year, @.JobID, @.EmpID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM _cursor
> INTO @.Program, @.Year, @.JobID, @.EmpID
>
> SET @.msg = 'Job# : ' + CAST(@.JobID as nvarchar(20)) + ' Has no
> Installation Rate for the Following Combination. '
> SET @.msg = @.msg + 'Program : ' + @.Program + ', Year : ' + @.Year
>
> SELECT @.isNEW = WFTID FROM tblWorkFlowTasks
> WHERE ARID = @.AREA AND WFTTID=@.TT AND ID = @.JobID
> -- Only Insert New Records
> if(isnull(@.isNEW, 0) = 0)
> BEGIN
> INSERT INTO tblWorkFlowTasks(ARID, ID, StatusID, Description, WFTTID)
> VALUES (@.AREA, @.JobID, @.STOpen, @.msg, @.TT)
> SET @.WFTID = @.@.IDENTITY
>
> -- Assign To User
> INSERT INTO tblWorkFlowTaskAssignees (WFTID, UserID) VALUES
> (@.WFTID,@.EmpID)
> END
> END
> CLOSE _cursor
> DEALLOCATE _cursor
> I was wondering I could use an insert statement inside of an insert
> statement
> or specify SP VAlues from a SELECT Statement?
>|||Adam -
I know how to do cursors, But if I took that route how would I get the
UserID Values without requerying the Database? Thanks I'll keep it in
mind.
I do not claim to be the best sql Programmer. I can do what need to be
done. But I would like to now the best ways to do things so feel free
to pick apart my code. Thanks once agian|||Chandra-
Will that put the inserted ID from the Tasks Table into the Assignees
Table?
I doesn't look like it would but I'll Give it a try.
Thanks for all the help so far people. First time in groups never
expected responses this fast.|||That's the way to go!
Insert the master row, then in an appropriate way (of which "set @.master_id
= @.@.identity" is the worst) get the master key (be it primary key or any
other kandidate key), then use that key when inserting detail rows.
To improve data integrity you can wrap it all up into a transaction, too.
We could provide more help, but you'll have to provide more data. Post your
DDL, DML and sample data and we can come up with a solution.
ML|||No problem Ezra, I've copied some code in below. Just paste this in a test
db and run the code in. Then execute the proc to see it work. You can stil
l
use @.@.identity within a trigger.
--CODE START
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TRG_INS_TEST]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TRG_INS_TEST]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[InsertMasterAndChild]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[InsertMasterAndChild]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TableChild]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TableChild]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TableMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TableMaster]
GO
CREATE TABLE [dbo].[TableChild] (
[id] [int] NOT NULL ,
[col2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TableMaster] (
[col1] [int] IDENTITY (1, 1) NOT NULL ,
[col2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE InsertMasterAndChild AS
insert into TableMaster (col2)
values ('a')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER TRG_INS_TEST ON [dbo].[TableMaster]
FOR INSERT
AS
INSERT INTO TableChild
values(@.@.IDENTITY,'Anything you want')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--CODE END
--
Adam J Warne, MCDBA
"EzraB" wrote:

> Adam -
> I know how to do cursors, But if I took that route how would I get the
> UserID Values without requerying the Database? Thanks I'll keep it in
> mind.
> I do not claim to be the best sql Programmer. I can do what need to be
> done. But I would like to now the best ways to do things so feel free
> to pick apart my code. Thanks once agian
>|||Chandra - ML -
THanks for your help so far. But When I execute the Above Code In the
Assignees/Detials
I just get the Same ID for All the Records. So I'm going to give you a
really striped down version okay. here we go.
FROMTABLE
PKID [int]
Field1 [int]
FIeld2 [int]
MASTERTABLE
PKID [int]
Field1 [int] -- This should match Field1 from FROMTABLE
DETAILTABLE
PKID [int]
ParentID [int] -- This should match the PKID Field from MASTERTABLE
Field2 [int] -- This should match Field2 from FROMTABLE
I wish to insert all records from FROMTABLE INTO MASTERTABLE
ONLY Field Field1
THEN CREATE A DETAILS FOR THE MASTERTABLE in DETAILTABLE
With Field2 FROM FROMTABLE and PKID from MASTERTABLE.
The reason this is not in one table is so I can insert other details at
a later time.
Did I make that a bit easier to understand? Thanks.
I'm really starting to dig this groups.