multiple updates to table

Hello all,

I have a parallel process updating a single table from various sources using update table statements using a key column.

I'm afraid the process will fail when an update will occur to a record with the same key simultaneously.

Does anyone have a suggestion how to accomplish this? Is there a way timing the updates in queue?

Thanks.

There is no concurrent update in SQL Server, DML statement are queued and executed one by one (if they are not in a transaction). There are sure scenarios which would lead to concurrency conflicts, but this has to be handled by your frontedn application.

HTH, Jens Suessmeyer.'

http://www.sqlserver2005.de

|||

I assume you have multiple clients pumping data into a single table, right?

The next question is what you mean by key? Like an identity key? If so, that won't happen. SQL Server single threads identity key generation so that no two rows will get the same key.

Multiple updates on multiple conditions?

Hi,

I'm looking for a way, if possible, to update a field to different values based on different criteria. For example, Field X should be A if condition 1 is true, it should be B if condition 2 is true, etc. I'm not sure if there's a way to use a CASE in an UPDATE statement?

Thanks

Maybe something like:

update urTable
set X = case when condition_1 = target1 then A
when condition_2 = target2 then B
else C
end
where filterColumn = someCondition

Multiple Updates In Store Procedure?

I am a total newbie to SQL Server, but have a good idea about how things operate..

I have a "job" that runs multiple (10) update queries for me in different tables.

Can I create a stored procedure to do these so that I can make one call to them?

If not, how can I call the job to start external to MS SQL?>>Can I create a stored procedure to do these so that I can make one call to them?

ofcourse. if you have all the required parameters you definetely can.

hth

Multiple Updates at once?

I have an application that, upon exiting, has to update upwards of 300+
records in a SQL Server database.
There are issues with timeouts due to network traffic and server load.
I was wondering if there was a better procedure or method of transferring a
large group of simple sql tasks to the server that could perhaps be done in
one complete action. this may alleviate the timeout issue.
Thank you for your help.I take it you aren't making a single call to a stored proc? Or the 300
records can't be updated with a single UPDATE statement?
Bryce|||300 rows isn't that many to update - provided you don't do it as 300
separate UPDATE statements. Are you using stored procedures to perform the
updates? Where does the data come from?
David Portas
SQL Server MVP
--|||I am not familiar with stored procedures. How would I make a stored
procedure that would change multiple records in a database based on
different criteria, ie replace UPDATE queries like these ones.
UPDATE Movies SET MovieClassNo = 1 WHERE (MovieID = 'A1241234')
UPDATE Movies SET MovieClassNo = 2 WHERE (MovieID = 'F19892346')
UPDATE Movies SET MovieClassNo = 3 WHERE (MovieID = 'L34982423')
UPDATE Movies SET MovieClassNo = 1 WHERE (MovieID = 'Q98645949')
I have looked in the BOL, and I jsut get kinda lost when I do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IY6dnSoM4vCqM63fRVn-oA@.giganews.com...
> 300 rows isn't that many to update - provided you don't do it as 300
> separate UPDATE statements. Are you using stored procedures to perform the
> updates? Where does the data come from?
> --
> David Portas
> SQL Server MVP
> --
>|||Atley wrote:
> I am not familiar with stored procedures. How would I make a stored
> procedure that would change multiple records in a database based on
> different criteria, ie replace UPDATE queries like these ones.
> UPDATE Movies SET MovieClassNo = 1 WHERE (MovieID = 'A1241234')
> UPDATE Movies SET MovieClassNo = 2 WHERE (MovieID = 'F19892346')
> UPDATE Movies SET MovieClassNo = 3 WHERE (MovieID = 'L34982423')
> UPDATE Movies SET MovieClassNo = 1 WHERE (MovieID = 'Q98645949')
>
Are those movieID values hard-coded or are they parameters that need to
be passed into the procedure? What abou the MoveiClassNo? If they are
parameters and you have 300 updates to run, then I'd want to know what
this process is and why you need to update 300 rows in the Movies table
when the application terminates. Passing 300 (or possible 600)
parameters to a procedure is not ideal. In that case, it would probably
be easier to call the procedure 300 times, once for each movieID.
But I'd still like to understand better the reason for the updates and
why there are so many at application termination.
David Gugick
Imceda Software
www.imceda.com|||In principle you can do something like this:
UPDATE Movies
SET movieclassno =
CASE movieid
WHEN 'A1241234' THEN 1
WHEN 'F19892346' THEN 2
WHEN 'L34982423' THEN 3
WHEN 'Q98645949' THEN 1
END
WHERE movieid IN
('A1241234',
'F19892346',
'L34982423',
'Q98645949')
but it's better to use stored procedure parameters to pass those values
rather than construct the SQL dynamically at the client. The question
is, why are you batching up all 300 updates to execute them in one go?
David Portas
SQL Server MVP
--|||These are Movies that are being transfered from one location to another in a
bulk transfer
So the movies are being scanned in, and then the location ID has to be
changed in one fell sweep.
The parameters are not known until the waybill is made and the movies are
scanned in.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110536761.241514.182540@.g14g2000cwa.googlegroups.com...
> In principle you can do something like this:
> UPDATE Movies
> SET movieclassno =
> CASE movieid
> WHEN 'A1241234' THEN 1
> WHEN 'F19892346' THEN 2
> WHEN 'L34982423' THEN 3
> WHEN 'Q98645949' THEN 1
> END
> WHERE movieid IN
> ('A1241234',
> 'F19892346',
> 'L34982423',
> 'Q98645949')
> but it's better to use stored procedure parameters to pass those values
> rather than construct the SQL dynamically at the client. The question
> is, why are you batching up all 300 updates to execute them in one go?
> --
> David Portas
> SQL Server MVP
> --
>|||These are Movies that are being transfered from one location to another in a
bulk transfer
So the movies are being scanned in, and then the location ID has to be
changed in one fell sweep.
The parameters are not known until the waybill is made and the movies are
scanned in.
This has to be done before the application is closed or the waybill is
printed, or the waybill is closed.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uAWkxjdJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> Atley wrote:
> Are those movieID values hard-coded or are they parameters that need to
> be passed into the procedure? What abou the MoveiClassNo? If they are
> parameters and you have 300 updates to run, then I'd want to know what
> this process is and why you need to update 300 rows in the Movies table
> when the application terminates. Passing 300 (or possible 600)
> parameters to a procedure is not ideal. In that case, it would probably
> be easier to call the procedure 300 times, once for each movieID.
> But I'd still like to understand better the reason for the updates and
> why there are so many at application termination.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Atley wrote:
> These are Movies that are being transfered from one location to
> another in a bulk transfer
> So the movies are being scanned in, and then the location ID has to be
> changed in one fell sweep.
> The parameters are not known until the waybill is made and the movies
> are scanned in.
> This has to be done before the application is closed or the waybill is
> printed, or the waybill is closed.
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uAWkxjdJFHA.3960@.TK2MSFTNGP09.phx.gbl...
I would try writing a stored procedure to do the update and calling it
300 times from the application. It should happen very quickly. Pass the
MovieClassNo and MoveID to the procedure. This option keeps all the
intelligence in the database.
Before I would consider anything else, I would try that.
David Gugick
Imceda Software
www.imceda.com|||CREATE TABLE WayBills
(movieid CHAR (8) NOT NULL PRIMARY KEY,
movie_class_nbr INTEGER NOT NULL
CHECK (movie_class_nbr BETWEEN 1 AND 4));
Then use the collected data:
UPDATE Movies
SET movie_class_nbr
= (SELECT movie_class_nbr
FROM WayBills AS W1
WHERE W1.movie_id = Movies.movie-id)
WHERE EXISTS (SELECT *
FROM WayBills AS W1
WHERE W1.movie_id = Movies.movie_id);

Multiple updates and Identity fields

I have a table used by multiple applications. One column is an Identify field and is also used as a Primary key. What is\are the best practices to use get the identity value returned after an INSERT made by my code.. I'm worried that if someone does an INSERT into the same table a "zillionth" of a second later than I did, that I could get their Identity value.

TIA,

Barkingdog

Are you using SQL2k5 ? THen you can use the new OUTPUT clause. Otherwise you should have a look on SCOPE_IDENTITY() which should fit your needs. But the new OUTPUT function should be more straight forward in your case.

HTH, JEns K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

I looked up OUTPUT in sql 2k5 BOL: Here's an example that I found:

>>>
Copy Code
USE AdventureWorks;
GO
DECLARE @.MyTableVar table( ScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @.MyTableVar
VALUES (N'Operator error', GETDATE());

--1. Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @.MyTableVar;

--2. Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO
>>>>

So with OUTPUT the ScrapReasonID from @.MyTableVar will hold the identity of the row just inserted. Is this correct?

What is the difference in output between queries 1 and 2? Shouldn't they be the same?

TIA,

Barkingdog

Multiple Triggers Error: Nesting Level Exceeded (Limit 32). SQL 2005

I am running sql 2005 here is my issue I have 4 triggers on a table 2 inserts, and 2 updates, I can run the two inserts and only 1 update at one time or I get the Nesting Level 32 error. Each item was tested seperately and works fine also the group of 3 does exactly as it is suppose to, but the 2 update always triggers conflict with each other please if anyone has a suggestion im all ears.

Update Race:

IFNotUPDATE(Edited)

UPDATE Patient

SET Race =(SELECTCASE Racecode

WHEN'API'THEN'Asian, Pacific Islander'

WHEN'BH'THEN'Black, Hispanic'

WHEN'BNH'THEN'Black, Non-Hispanic'

WHEN'HIS'THEN'Hispanic'

WHEN'OTH'THEN'Other, Black & White'

WHEN'WH'THEN'White, Hispanic'

WHEN'WNH'THEN'White, Non-Hispanic'

END)

WHERE id# IN(SELECT id# FROM inserted)

END

Update Religion:

IF Not UPDATE (Edited)

Update Patient

SET Religion = (SELECT CASE Religioncode

WHEN 'C' THEN 'Catholic'

WHEN 'J' THEN 'Jewish'

WHEN 'O' THEN 'Other'

WHEN 'P' THEN 'Protestant'

WHEN 'U' THEN 'Unknown'

END)

WHERE id# IN (SELECT id# FROM Inserted)

END

why not combining them into one update.

IF Not UPDATE (Edited)
begin
UPDATE Patient
SET Race = (SELECT CASE Racecode
WHEN 'API' THEN 'Asian, Pacific Islander'
WHEN 'BH' THEN 'Black, Hispanic'
WHEN 'BNH' THEN 'Black, Non-Hispanic'
WHEN 'HIS' THEN 'Hispanic'
WHEN 'OTH' THEN 'Other, Black & White'
WHEN 'WH' THEN 'White, Hispanic'
WHEN 'WNH' THEN 'White, Non-Hispanic'
END)
,Religion = (SELECT CASE Religioncode
WHEN 'C' THEN 'Catholic'
WHEN 'J' THEN 'Jewish'
WHEN 'O' THEN 'Other'
WHEN 'P' THEN 'Protestant'
WHEN 'U' THEN 'Unknown'
END)
WHERE id# IN (SELECT id# FROM inserted)
end|||I am not really good at setting up my code thanks very much I have some classes coming up. That did the trick. Thanks again!!!

Multiple triggers

When a trigger on one table updates another table, does it activate
the update trigger on the updated table?

For ex:

1. Table A has a trigger for update
2. Table B has a trigger for update that updates table A

Will the trigger no table A run when table B trigger updates table A?

ThanksHi

If you update table B the trigger will update table A and the trigger on
table A will be activated. You will have to be careful that the actions you
perform within the do what you expect. There are 32 levels of recursion.

Check out Using Nested Triggers in Books Online.

John
"Yariv" <yariva@.mll.co.il> wrote in message
news:b4e534ce.0310080112.14d7aa18@.posting.google.c om...
> When a trigger on one table updates another table, does it activate
> the update trigger on the updated table?
> For ex:
> 1. Table A has a trigger for update
> 2. Table B has a trigger for update that updates table A
> Will the trigger no table A run when table B trigger updates table A?
> Thanks|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you update table B the trigger will update table A and the trigger on
> table A will be activated. You will have to be careful that the actions
> you perform within the do what you expect. There are 32 levels of
> recursion.

I'll add here, that is depends on the configuration option "nested
triggers". By defualt it is on. However, it is a little too easy to
turn it off in the Enterprise Manager. (Been there, done that.)

Note that if the trigger updates the table the trigger belongs to,
the default is that the trigger is *not* re-fired. This depends on
a database setting, RECURSIVE_TRIGGERS.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Multiple text updates to a single row

I have a table A that has related records in table B. I need to run an update to concatonate certian values in table B into a single value in table A.

Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?

No need to use cursor. simple update should be enough, but you need to watch out for the data types though. You should be able to use somthing like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, i int, j char(2), k bit)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.a select 3, NULL

insert @.a select 4, NULL

insert @.a select 5, NULL

insert @.b select 1, 55, 'AB', 0

insert @.b select 2, 66, 'CD', 1

insert @.b select 3, 77, 'EF', 1

insert @.b select 4, 88, 'GH', 0

insert @.b select 5, 99, 'IJ', 1

update a

set a.y = cast (b.i as varchar(10)) + b.j + cast (b.k as varchar(1))

from @.a a join @.b b

on a.x = b.x

select * from @.a

|||

Thank you for the response. Unfortunately, this is not my situation. I have multiple related records in table B that relate back to single records in table A. I'll update your code example to reflect the problem I have.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

--The Following select will fail to return

--concatonated values in a.y because the

--UPDATE can't update the same row value

--twice in the same UPDATE statement.

update a

set a.y = ISNULL(a.y,'') + b.j

from @.a a join @.b b

on a.x = b.x

--column y only holds the first value for

--group 1 and group 2

select * from @.a

|||

I am sure, there will be better ways than this code, you can do something like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

declare @.temp table (x int)

declare @.x int

declare @.y varchar(200)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

insert @.temp

select distinct x from @.a

while exists (select 1 from @.temp)

begin

select top 1 @.x = x from @.temp

select @.y = ISNULL(@.y, '') + j from @.b b where b.x = @.x

select @.y as 'the value'

update a

set y = ISNULL(y, '') + @.y

from @.a a

where a.x = @.x

delete from @.temp where x = @.x

set @.y = ''

end

select * from @.a

|||

Here is an old post that should help. Basically, you create a udf to concatenate the values.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/81da308504ac0e90

|||

Is this just for display? Or are you going to be storing the values like this? Best policy would be to use the UI to display the rows as you want. For display, you can use the techniques on the following page:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

But if you are doing this to store the values like this, it is a bad idea. Having each value in a different row is the best policy always. Far easier in SQL to build up a value than it is tear it apart.

set nocount on
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'

update a
set y = LEFT(o.list, LEN(o.list)-1)
FROM @.a a
CROSS APPLY
(
SELECT
CONVERT(VARCHAR(12), j) + ',' AS [text()]
FROM
@.b s
WHERE
s.x = a.x
ORDER BY
x
FOR XML PATH('')
) o (list)

select *
from @.a

Returns:

x y

-- -

1 AB,CD,EF

2 ZY,RX

|||

First, thanks to Sankar and oj for your assistance.

Louis, your solution is what I was looking for. I do need to store the value, but it is for a very specific reason. I am finishing a table and stored procedure solution to delivering an open ended number of report subscription records to an SSIS package to output between 1 and 76 reports to PDF with a potentially different set of input parameters and values per client, per report.

Using the code above, I will be able to populate a single table structure with all report subscription records regardless of input parameters. I will concatenate the parameters as you illustrated above as "&parm1=val1&parm2=val2" while the next record may have "&parm5=value5" only. It allows me to run all filtered reports based on scheduling values (i.e. daily, weekly, monthly) while accounting for the differences in reports and client requirements.

I have not spent enough time exploring the XML functionality in SQL Server, and it bit me this time.

Thanks again for the help.

Hugh

|||

oj,

On second glance I used your UDF route. Made it easy to integrate into an existing sql insert without adding any more code to my stored procedure.

Thanks.

Multiple text updates to a single row

I have a table A that has related records in table B. I need to run an update to concatonate certian values in table B into a single value in table A.

Since an UPDATE can't update the same row twice, is there any way I can do this other than use a Cursor?

No need to use cursor. simple update should be enough, but you need to watch out for the data types though. You should be able to use somthing like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, i int, j char(2), k bit)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.a select 3, NULL

insert @.a select 4, NULL

insert @.a select 5, NULL

insert @.b select 1, 55, 'AB', 0

insert @.b select 2, 66, 'CD', 1

insert @.b select 3, 77, 'EF', 1

insert @.b select 4, 88, 'GH', 0

insert @.b select 5, 99, 'IJ', 1

update a

set a.y = cast (b.i as varchar(10)) + b.j + cast (b.k as varchar(1))

from @.a a join @.b b

on a.x = b.x

select * from @.a

|||

Thank you for the response. Unfortunately, this is not my situation. I have multiple related records in table B that relate back to single records in table A. I'll update your code example to reflect the problem I have.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

--The Following select will fail to return

--concatonated values in a.y because the

--UPDATE can't update the same row value

--twice in the same UPDATE statement.

update a

set a.y = ISNULL(a.y,'') + b.j

from @.a a join @.b b

on a.x = b.x

--column y only holds the first value for

--group 1 and group 2

select * from @.a

|||

I am sure, there will be better ways than this code, you can do something like this.

Code Snippet

declare @.a table (x int, y varchar(200))

declare @.b table (x int, j char(2))

declare @.temp table (x int)

declare @.x int

declare @.y varchar(200)

insert @.a select 1, NULL

insert @.a select 2, NULL

insert @.b select 1, 'AB'

insert @.b select 1, 'CD'

insert @.b select 1, 'EF'

insert @.b select 2, 'ZY'

insert @.b select 2, 'RX'

insert @.temp

select distinct x from @.a

while exists (select 1 from @.temp)

begin

select top 1 @.x = x from @.temp

select @.y = ISNULL(@.y, '') + j from @.b b where b.x = @.x

select @.y as 'the value'

update a

set y = ISNULL(y, '') + @.y

from @.a a

where a.x = @.x

delete from @.temp where x = @.x

set @.y = ''

end

select * from @.a

|||

Here is an old post that should help. Basically, you create a udf to concatenate the values.

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/81da308504ac0e90

|||

Is this just for display? Or are you going to be storing the values like this? Best policy would be to use the UI to display the rows as you want. For display, you can use the techniques on the following page:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

But if you are doing this to store the values like this, it is a bad idea. Having each value in a different row is the best policy always. Far easier in SQL to build up a value than it is tear it apart.

set nocount on
declare @.a table (x int, y varchar(200))
declare @.b table (x int, j char(2))
insert @.a select 1, NULL
insert @.a select 2, NULL
insert @.b select 1, 'AB'
insert @.b select 1, 'CD'
insert @.b select 1, 'EF'
insert @.b select 2, 'ZY'
insert @.b select 2, 'RX'

update a
set y = LEFT(o.list, LEN(o.list)-1)
FROM @.a a
CROSS APPLY
(
SELECT
CONVERT(VARCHAR(12), j) + ',' AS [text()]
FROM
@.b s
WHERE
s.x = a.x
ORDER BY
x
FOR XML PATH('')
) o (list)

select *
from @.a

Returns:

x y

-- -

1 AB,CD,EF

2 ZY,RX

|||

First, thanks to Sankar and oj for your assistance.

Louis, your solution is what I was looking for. I do need to store the value, but it is for a very specific reason. I am finishing a table and stored procedure solution to delivering an open ended number of report subscription records to an SSIS package to output between 1 and 76 reports to PDF with a potentially different set of input parameters and values per client, per report.

Using the code above, I will be able to populate a single table structure with all report subscription records regardless of input parameters. I will concatenate the parameters as you illustrated above as "&parm1=val1&parm2=val2" while the next record may have "&parm5=value5" only. It allows me to run all filtered reports based on scheduling values (i.e. daily, weekly, monthly) while accounting for the differences in reports and client requirements.

I have not spent enough time exploring the XML functionality in SQL Server, and it bit me this time.

Thanks again for the help.

Hugh

|||

oj,

On second glance I used your UDF route. Made it easy to integrate into an existing sql insert without adding any more code to my stored procedure.

Thanks.

Multiple SQL statements in a stored procedure

Hi!

I got 2 stored procedure, proc1 executes proc2,
proc2 does some updates and inserts on different tables ...

proc1:

ALTER PROCEDURE
AS
execute proc2

SELECT * FROM tblFoo
______________________
my problem is, that when executing proc1, I receive the message:

"THE SP executed successfully, but did not return records!"

But I need the resultset from "SELECT * FROM tblFoo" that is executed
at the end of proc1.
I'm not sure, but I think that I solved a similira problem with "set
nocount on", I put it into both SP, but it's still the same ... no
resultset ...

How can I display "SELECT * FROM tblFoo" within a SP, where SQL
statements are executed before?!

Thank you!Err, stupid question but did you check that tblFoo actually has records in
it?

"Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
news:1117053332.823938.254990@.g14g2000cwa.googlegr oups.com...
> Hi!
> I got 2 stored procedure, proc1 executes proc2,
> proc2 does some updates and inserts on different tables ...
>
> proc1:
> ALTER PROCEDURE
> AS
> execute proc2
> SELECT * FROM tblFoo
> ______________________
> my problem is, that when executing proc1, I receive the message:
> "THE SP executed successfully, but did not return records!"
> But I need the resultset from "SELECT * FROM tblFoo" that is executed
> at the end of proc1.
> I'm not sure, but I think that I solved a similira problem with "set
> nocount on", I put it into both SP, but it's still the same ... no
> resultset ...
> How can I display "SELECT * FROM tblFoo" within a SP, where SQL
> statements are executed before?!
> Thank you!

Multiple row updates

Hello,
I am working on a web app and am at a point where I have multiple rows in my GUI that need to be sent to and saved in SQL Server when the user presses Save. We want to pass the rows to a working table and the do a begin tran, move rows from working table to permanent tables with set processing, commit tran. The debate we are having is how to get the data to the work table. We can do individual inserts to the work table 1 round trip for each row (could be 100's of rows) or concatenate all rows into 1 long (up to 8K at a time) string and make one call sending the long string and then parse it into the work table in SQL Server. Trying to consider network usage and overhead by sending many short items vs 1 long item and cpu overhead for many inserts vs string manipulation and parsing. Suggestions?

Thanks you
JeffHow about dump to Text File then Use Stored procedure to import then move files out of the way when complete.

May even remove the need for a work table cos U will already have a log & using DTS or BCP to import is pretty fast

just a thought

GW|||Yeah I was thinking that's what I would do...

But what happens when, let's say you have 10 users trying to do this at the same time...

Your work table seems problematic

Just dump the file...and use a sproc to bcp it in...