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.

Multiple fields in where statement

In need to write a delete that checks to see if a record exists in which 3 specific fields match the same 3 fields in another table. If there is a match it deletes that record. Below was my first attempt, which of course doesn't work.

Delete from oop_test where acct_no, curr_seq, acct_type in (select acct_no, curr_seq, acct_type from oop_temp)What about this?

Delete from oop_test
where exists(select 'ok' from oop_temp i where i.acct_no=oop_test.acct_no and ...)|||YES! THAT IS IT! Thanks a TON! I am still a bit confused about the use of the variable "i" in the query. Explain it's use to me. Maybe it is my lack of SQL skills.|||This can also be done with a simple join:

Delete oop_test
from oop_test
inner join oop_temp
on oop_test.acct_no = oop_test.acct_no
and oop_test.curr_seq = oop_test.curr_seq
and oop_test.acct_type = oop_test.acct_type

blindman|||I will have to give this a shot. While the first method works like a champ, I had a difficult time figuring it out. This is real straight forward.

THANKS!|||The "i" in Snail's code is just an alias for the table name oop_temp. Many coders assign shorter alias name for tables in their queries to reduce the amount of typing required. Otherwise, Snail's code just checks each record in oop_test to see is a matching record "exists" in the oop_temp based on the three columns specified.

Both methods work. I think the join method might be more efficient, though for small-to-midsize tables probably not enough to be noticable. Go with whichever method you find easiest to read.

blindman|||Actually, the part I had a problem with was the select 'ok' part. Do you know what that means?|||All right, the 'OK" part is odd! Snail, what was your reasoning for the hard-coded text?

How it works is like this...

The inner query just needs to see whether a corresponding record exists. It doesn't really need to know what any of the record's values are. Therefore, Snail supplied a hard-coded string 'Ok', which will be returned for every matching record. If 'Ok' exists in the recordset, then there was a matching record. Using:

where exists(select * from oop_temp...

...accomplishes the same thing. The optimizer is smart enough to know that you only want to check for the existence of the record, and won't try to access all the columns in the record.

blindman|||Originally posted by blindman
All right, the 'OK" part is odd! Snail, what was your reasoning for the hard-coded text?

How it works is like this...

The inner query just needs to see whether a corresponding record exists. It doesn't really need to know what any of the record's values are. Therefore, Snail supplied a hard-coded string 'Ok', which will be returned for every matching record. If 'Ok' exists in the recordset, then there was a matching record. Using:

where exists(select * from oop_temp...

...accomplishes the same thing. The optimizer is smart enough to know that you only want to check for the existence of the record, and won't try to access all the columns in the record.

blindman

I agree - there is no difference in performance between select 'OK' and select * inside exists, but 'OK' looks much better for me ;)|||So SSchuler, its just a matter of style, and lookin' good! :cool:

blindman|||HEHEH! Good one. Ya learn something new everyday. I can't believe that tripped me up like it did. Now that you point it out, it is quite obvious. Don't let anyone say that us computer jockeys don't have style! ;-)|||* also has to be expanded by the optimizer into the field list, while 'ok' doesn't. i usually use if exists (select 1 from table_name)|||Originally posted by ms_sql_dba
* also has to be expanded by the optimizer into the field list, while 'ok' doesn't. i usually use if exists (select 1 from table_name)

I believe that's changed...Where SELECT * is actually optimized to perform better.|||Brett is correct. Select * is optimized, and the columns are not expanded.

blindman|||Originally posted by SSchuler
In need to write a delete that checks to see if a record exists in which 3 specific fields match the same 3 fields in another table. If there is a match it deletes that record.

To find "a record in which three specific fields match the same three fields in another table," you would use a JOIN clause in a SELECT query.

To delete those fields, you use this select query as a sub-select in a DELETE query, something like this:

DELETE FROM victim WHERE victim_id IN
(SELECT id FROM
table1 A JOIN table2 B USING
A.F1 = B.F1 AND A.F2 = B.F2 AND A.F3 = B.F3
)

(Sub-select italicized for emphasis. Caution: extemporaneous SQL coding... do not try this at home.) ;-)|||A little late, sundial. Read other member's posts first!

blindman

multiple field statment

I have a table that has 9 fields.
in some cases a record may almost be a duplicate of another record except
for one of the fields.
so I was thinking I need to write a statment to where I have to say what
each field equals, but I haven't had any luck
here is what I tried.
Select * from MyTable where field1 = "this" and field2 = "a" and field3 =
"test"
How do I write that statement correctly?
Thanks
Replace " with '. SQL uses a single quote to delimit literal strings rather
than a double quote. If that doesn't solve your problem then please tell us
exactly the wording of any error message you are getting.
David Portas
SQL Server MVP
sql

multiple field statment

I have a table that has 9 fields.
in some cases a record may almost be a duplicate of another record except
for one of the fields.
so I was thinking I need to write a statment to where I have to say what
each field equals, but I haven't had any luck
here is what I tried.
Select * from MyTable where field1 = "this" and field2 = "a" and field3 = "test"
How do I write that statement correctly'
ThanksReplace " with '. SQL uses a single quote to delimit literal strings rather
than a double quote. If that doesn't solve your problem then please tell us
exactly the wording of any error message you are getting.
--
David Portas
SQL Server MVP
--

Multiple dataset in a single report

Let's say we have two (logical) sections within a report, one that displays a record from table_A and on the other one displays a Barchart based on the values fetched from table_B.
Could we assign seperate queries (resultset) to above mentioned situation ?
p.s. Right now, we are doing it through JOIN between tables A and B. However, we would like to not use that.If you have different, tables, matrixes, etc within a report you may assign
a different result set to each one...Just define the multiple queries, and
make the assignment in the properties of the table/matrix
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:642C9A77-A4C2-44EE-B639-AC8C31E9776A@.microsoft.com...
> Let's say we have two (logical) sections within a report, one that
displays a record from table_A and on the other one displays a Barchart
based on the values fetched from table_B.
> Could we assign seperate queries (resultset) to above mentioned situation
?
> p.s. Right now, we are doing it through JOIN between tables A and B.
However, we would like to not use that.

multiple columns in details section

I have three fields in my report that make up each record. I would like to display these in detail section.

so instead of

1. alam Dept1 10000
2. khaiser dept2 20000
3. mujeeb dept2 20000

I would have

alam khaiser mujeeb ...
dept1 dept2 dept2 ...
10000 20000 20000 ..Use Crosstab Report|||1. Right-click on the dtails section, to open the Section Expert dialog box.

2. On the 'Sections' list, click the details section.

3. On the 'Common' tab, select 'Format with multiple columns' check box. A new tab called 'Layout' appears.

4. On the 'Layout' tab, specify the formatting for the columns:

Enter the width of the column in the 'Width' box. (you can set the height by dragging the section borders in the Design view of the report)

Enter the space between labels going across the page in the 'Horizontal gap' checkbox.

Enter the space between labels going down the page in the 'Vertical gap' box.

Select the printing direction, 'Across then Down' or 'Down then Across'.

Good Luck|||after using the Scetion expert i got mutliple columns but the sum of salary should be in right side but it is not coming ......