Multiple INSERT's INTO temp table w/ Primary Key

Hi all -
I need to create a temp table containing all Items with activity for the
last five years. These records will be coming from a few different tables.
So I have the following . . .
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
INSERT INTO #tt_item
SELECT dbo.item.item
FROM Orders
. . . . which I'll have to repeat for each table in question.
My question is, with the PRIMARY KEY on the #tt_item table, how do I check
for the existence of an Item in #tt_item as I'm doing the INSERT?This depends on what you want to do when there is a conflict (Duplicate key
value)...
Do you want to ignore the dupe, (not insertt it)? If so, then
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
-- --
INSERT INTO #tt_item(item)
SELECT O.item
FROM Orders O
Where Not Exists (Select * From #tt_item
Where item = O.item)
If otoh, you want to insert them anyway, then you cannot use item as the
primary key of the temp table... You will need to make a composite key
consisting of, say, (Sourcetable, item)
"Michael.Fisher" wrote:

> Hi all -
> I need to create a temp table containing all Items with activity for the
> last five years. These records will be coming from a few different tables
.
> So I have the following . . .
> CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
> INSERT INTO #tt_item
> SELECT dbo.item.item
> FROM Orders
> . . . . which I'll have to repeat for each table in question.
> My question is, with the PRIMARY KEY on the #tt_item table, how do I check
> for the existence of an Item in #tt_item as I'm doing the INSERT?|||This depends on what you want to do when there is a conflict (Duplicate key
value)...
Do you want to ignore the dupe, (not insertt it)? If so, then
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
-- --
INSERT INTO #tt_item(item)
SELECT O.item
FROM Orders O
Where Not Exists (Select * From #tt_item
Where item = O.item)
If otoh, you want to insert them anyway, then you cannot use item as the
primary key of the temp table... You will need to make a composite key
consisting of, say, (Sourcetable, item)
"Michael.Fisher" wrote:

> Hi all -
> I need to create a temp table containing all Items with activity for the
> last five years. These records will be coming from a few different tables
.
> So I have the following . . .
> CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
> INSERT INTO #tt_item
> SELECT dbo.item.item
> FROM Orders
> . . . . which I'll have to repeat for each table in question.
> My question is, with the PRIMARY KEY on the #tt_item table, how do I check
> for the existence of an Item in #tt_item as I'm doing the INSERT?|||INSERT INTO #tt_item (item)
SELECT O.item
FROM Orders AS O
LEFT JOIN #tt_item AS I
ON O.item = I.item
WHERE I.item IS NULL
OR:
INSERT INTO #tt_item (item)
SELECT item
FROM Orders
UNION
SELECT item
FROM Foo
UNION
SELECT item
FROM Bar
UNION
..
David Portas
SQL Server MVP
--|||Many thanks David, as well as CBretana, for providing these timely, helpful
examples. Looks like any of these will work.
"David Portas" wrote:

> INSERT INTO #tt_item (item)
> SELECT O.item
> FROM Orders AS O
> LEFT JOIN #tt_item AS I
> ON O.item = I.item
> WHERE I.item IS NULL
> OR:
> INSERT INTO #tt_item (item)
> SELECT item
> FROM Orders
> UNION
> SELECT item
> FROM Foo
> UNION
> SELECT item
> FROM Bar
> UNION
> ...
> --
> David Portas
> SQL Server MVP
> --
>

multiple inserts in transaction

I am trying to insert into multiple tables and if any of the inserts fail, I
would like to rollback the entire thing.
This approach works but is it a good design'
Please let me know.
declare @.err int
declare @.id1 int
declare @.id2 int
set @.err = -1
begin tran
insert into tbl1(c1,c2) values (1,2)
if @.@.error = 0
set @.err = 0
else
set @.err = -1
SELECT @.id1 = SCOPE_IDENTITY()
if @.err = 0
begin
insert into tbl2(c1,c2) values (1,2)
if @.@.error = 0
set @.err = 0
else
set @.err = -1
SELECT @.id2 = SCOPE_IDENTITY()
end
if @.err = 0
begin
insert into tbl3(c1,c2) values (1,2)
if @.@.error = 0
set @.err = 0
else
set @.err = -1
end
if @.err = 0
begin
commit tran
end
else
begin
rollback tran
RAISERROR('Please check bla bla bla',16,1)
endWhy continue attempting to do work if you know you are going to roll back?
BEGIN TRAN;
INSERT INTO foo(a) SELECT 1;
IF @.@.ERROR <> 0
BEGIN
RAISERROR('foo', 11, 1);
ROLLBACK;
RETURN;
END;
INSERT INTO bar(a) SELECT 2;
IF @.@.ERROR <> 0
BEGIN
RAISERROR('bar', 11, 1);
ROLLBACK;
RETURN;
END;
COMMIT TRAN;
Some people use labels and GOTO, but I'm not overly fond of that structure,
personally... reminds me too much of QBasic.
For a much more thorough treatment of the topic, Erland has a couple of
great articles:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
"sqlster" <nospam@.nospam.com> wrote in message
news:A62E348D-7F82-4FD6-8F91-B54C8371351E@.microsoft.com...
>I am trying to insert into multiple tables and if any of the inserts fail,
>I
> would like to rollback the entire thing.
> This approach works but is it a good design'
> Please let me know.
> declare @.err int
> declare @.id1 int
> declare @.id2 int
> set @.err = -1
>
> begin tran
> insert into tbl1(c1,c2) values (1,2)
> if @.@.error = 0
> set @.err = 0
> else
> set @.err = -1
> SELECT @.id1 = SCOPE_IDENTITY()
> if @.err = 0
> begin
> insert into tbl2(c1,c2) values (1,2)
> if @.@.error = 0
> set @.err = 0
> else
> set @.err = -1
> SELECT @.id2 = SCOPE_IDENTITY()
> end
>
> if @.err = 0
> begin
> insert into tbl3(c1,c2) values (1,2)
> if @.@.error = 0
> set @.err = 0
> else
> set @.err = -1
>
> end
> if @.err = 0
> begin
> commit tran
> end
> else
> begin
> rollback tran
> RAISERROR('Please check bla bla bla',16,1)
> end|||Aaron,
Thank you VERY MUCH..
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why continue attempting to do work if you know you are going to roll back?
> BEGIN TRAN;
> INSERT INTO foo(a) SELECT 1;
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR('foo', 11, 1);
> ROLLBACK;
> RETURN;
> END;
> INSERT INTO bar(a) SELECT 2;
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR('bar', 11, 1);
> ROLLBACK;
> RETURN;
> END;
> COMMIT TRAN;
> Some people use labels and GOTO, but I'm not overly fond of that structure
,
> personally... reminds me too much of QBasic.
> For a much more thorough treatment of the topic, Erland has a couple of
> great articles:
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
>
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:A62E348D-7F82-4FD6-8F91-B54C8371351E@.microsoft.com...
>
>|||I agree that GOTO isn't the most elegant solution, but SQL 2000 doesn't have
TRY/CATCH logic, so that's what we're stuck with. I dislike the solution
you've suggested because the error handling code is distributed throughout
the procedure. For two or three inserts, that may not seem to be a problem,
but for a complicated process like posting a batch of inventory
transactions, distributing the error handling introduces a lot of redundant
code. Another reason to consolidate the error handling is that it's much
simpler to unit test the procedure--that is, to step through the error
handling logic--if it exists in a single place. Finally, consistency
simplifies development and maintenance and reduces bugs, so it's better to
use the same mechanism for all procedures--even those with only two or three
inserts.
By the way, there are a couple problems with your code:
(1) change "ROLLBACK" to "IF @.@.TRANCOUNT > 0 ROLLBACK" Of course, this
doesn't take into account transaction savepoints.
(2) change "RETURN" to "RETURN -1"
It should be clear even with this simple example that the changes I
mentioned must be applied throughout the procedure, illustrating the
maintenance problems that can and will occur.
The bottom line is that it cost less to code, test, and maintain procedures
with consolidated error handling code. I use GOTO because it yields better
code for less time and money. I haven't done any production code in SQL
2005 yet, but the TRY/CATCH logic sounds like a promising alternative.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:usuTkajEGHA.2380@.TK2MSFTNGP12.phx.gbl...
> Why continue attempting to do work if you know you are going to roll back?
> BEGIN TRAN;
> INSERT INTO foo(a) SELECT 1;
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR('foo', 11, 1);
> ROLLBACK;
> RETURN;
> END;
> INSERT INTO bar(a) SELECT 2;
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR('bar', 11, 1);
> ROLLBACK;
> RETURN;
> END;
> COMMIT TRAN;
> Some people use labels and GOTO, but I'm not overly fond of that
> structure, personally... reminds me too much of QBasic.
> For a much more thorough treatment of the topic, Erland has a couple of
> great articles:
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
>
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:A62E348D-7F82-4FD6-8F91-B54C8371351E@.microsoft.com...
>|||>I agree that GOTO isn't the most elegant solution, but SQL 2000 doesn't
>have TRY/CATCH logic, so that's what we're stuck with. I dislike the
>solution you've suggested because the error handling code is distributed
>throughout the procedure. For two or three inserts, that may not seem to
>be a problem, but for a complicated process like posting a batch of
>inventory transactions, distributing the error handling introduces a lot of
>redundant code.
In my defense, my suggestion was based on the code provided, not for every
situation you could come up with, and was only meant as pseudo-code. My
main thrust was to go read the articles, because Erland has shed a lot more
light on this topic than you or I could in a single thread.
A|||I didn't think your suggestion needed defending. Your code provided a
convenient example to illustrate the benefits of using GOTO. If it's any
consolation, most of the examples in Erland's articles don't include "IF
@.@.TRANCOUNT > 0" either, and the error handling code is also distributed.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23$srncmEGHA.1312@.TK2MSFTNGP09.phx.gbl...
> In my defense, my suggestion was based on the code provided, not for every
> situation you could come up with, and was only meant as pseudo-code. My
> main thrust was to go read the articles, because Erland has shed a lot
> more light on this topic than you or I could in a single thread.
> A
>|||>I didn't think your suggestion needed defending.
Well, you said you disliked it, and attacked it. So... to avoid that
perception in the future, my advice to you is to reply to the original
poster and just provide the helpful part of the advice, instead of adding
the beating to the respondant(s) and peppering that with your own take.

> Your code provided a convenient example to illustrate the benefits of
> using GOTO.
I don't think my code used GOTO at all...

> Erland's articles don't include "IF @.@.TRANCOUNT > 0" either
Well, to be honest, the only time I've ever seen the following type of
example require a @.@.TRANCOUNT check:
BEGIN TRAN
INSERT ...
IF @.@.ERROR <> 0
..
Is if the act of beginning a transaction itself had failed. In which case,
won't the batch abort anyway (at least under default conditions)? How do
you force a BEGIN TRAN to fail anyway? Aside from trying to commit a
transaction involving a linked server, where DTC is not running or
misconfigured, I can't think of one off the top of my head (and I don't
think that would fail on the BEGIN TRAN anyway, but it's too late for me to
bother checking right now anyway). The only time I have ever seen the above
kind of example error out due to a bad @.@.TRANCOUNT is if there are logic
errors in the code (e.g. redundant BEGIN TRAN statements).
I've seen this @.@.TRANCOUNT check used so heavily, and in such simplistic
examples, in the code of a very pedantic co-worker which, ly, I no longer
have to maintain -- because we replaced his slow and bloated codebase with a
completely revamped version (the @.@.TRANCOUNT checks weren't the slow and
bloated part I'm talking about). This anal retentive guy spent so much time
triple-bulletproofing every single line of code that, come delivery time,
much of the functionality was either broken, missing, or so horribly scraped
together that it was useless. He left us with little alternative except a
complete re-write. But he sure checked @.@.TRANCOUNT right after every BEGIN
TRAN, ROLLBACK, and COMMIT, even though the statements surrounding them were
rubbish! :-(
In any event, TRY/CATCH is a very welcome addition to SQL Server 2005,
though it still leads to what you complain about, distributed error
handling. Personally, I think it makes more sense to place error handling
right with the part of the code that errors out, instead of trying to shovel
it off to some common location at the bottom of the code. For one, when I
call a raiserror, the resulting line number lets me go right to the line of
code, instead of havint to retrace my steps. But that's just a preference
and there is no point in attacking it.
A|||Look, if you really want to turn this into a slug fest, then I'm game.

> I don't think my code used GOTO at all...
I didn't say that it did. The distributed nature of the error handling in
your code provided the perfect example of what NOT to do. Any error
handling change requires modifying every block of error handling code
throughout the procedure. Each of those modifications must then be tested.
MY point is that without TRY/CATCH, GOTO is the next best thing because it
serves to consolidate error handling into a single block of code.
I didn't say that you needed to check @.@.TRANCOUNT AFTER any statement. You
SHOULD, however, check it BEFORE a ROLLBACK to avoid raising the error:
Server: Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
I most certainly didn't make the asinine suggestion that @.@.TRANCOUNT should
be checked after a BEGIN TRAN. Where did that come from?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OvXE%23ZpEGHA.2912@.tk2msftngp13.phx.gbl...
> Well, you said you disliked it, and attacked it. So... to avoid that
> perception in the future, my advice to you is to reply to the original
> poster and just provide the helpful part of the advice, instead of adding
> the beating to the respondant(s) and peppering that with your own take.
>
> I don't think my code used GOTO at all...
>
> Well, to be honest, the only time I've ever seen the following type of
> example require a @.@.TRANCOUNT check:
> BEGIN TRAN
> INSERT ...
> IF @.@.ERROR <> 0
> ...
> Is if the act of beginning a transaction itself had failed. In which
> case, won't the batch abort anyway (at least under default conditions)?
> How do you force a BEGIN TRAN to fail anyway? Aside from trying to commit
> a transaction involving a linked server, where DTC is not running or
> misconfigured, I can't think of one off the top of my head (and I don't
> think that would fail on the BEGIN TRAN anyway, but it's too late for me
> to bother checking right now anyway). The only time I have ever seen the
> above kind of example error out due to a bad @.@.TRANCOUNT is if there are
> logic errors in the code (e.g. redundant BEGIN TRAN statements).
> I've seen this @.@.TRANCOUNT check used so heavily, and in such simplistic
> examples, in the code of a very pedantic co-worker which, ly, I no
> longer have to maintain -- because we replaced his slow and bloated
> codebase with a completely revamped version (the @.@.TRANCOUNT checks
> weren't the slow and bloated part I'm talking about). This anal retentive
> guy spent so much time triple-bulletproofing every single line of code
> that, come delivery time, much of the functionality was either broken,
> missing, or so horribly scraped together that it was useless. He left us
> with little alternative except a complete re-write. But he sure checked
> @.@.TRANCOUNT right after every BEGIN TRAN, ROLLBACK, and COMMIT, even
> though the statements surrounding them were rubbish! :-(
> In any event, TRY/CATCH is a very welcome addition to SQL Server 2005,
> though it still leads to what you complain about, distributed error
> handling. Personally, I think it makes more sense to place error handling
> right with the part of the code that errors out, instead of trying to
> shovel it off to some common location at the bottom of the code. For one,
> when I call a raiserror, the resulting line number lets me go right to the
> line of code, instead of havint to retrace my steps. But that's just a
> preference and there is no point in attacking it.
> A
>|||> I didn't say that it did. The distributed nature of the error handling in
> your code provided the perfect example of what NOT to do.
I think you're talking about a preference here, not an everyone-must-do-it
best practice.

> I didn't say that you needed to check @.@.TRANCOUNT AFTER any statement.
> You SHOULD, however, check it BEFORE a ROLLBACK to avoid raising the
> error:
> Server: Msg 3903, Level 16, State 1, Line 1
> The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
In the example I provided, please tell me how you can reach that path.
Because remember, you said you didn't like *my* example because I didn't use
@.@.TRANCOUNT. The logic in the piece of code I posted was so simple that the
above error message would be impossible to reach.|||You may be free to do as you please. I have to minimize development and
maintenance cost. Since development projects generally have budgets
attached, there are few who are free to do as they please. I think that
we'll both agree that it is a universal BAD practice to release code that
has not been thoroughly tested. This means that every path in the code must
be touched during testing. Your solution--distributing error handling
code--requires more test cases, more programming time, and more testing
time. In fact, the amount of development time for distributed error
handling code increases linearly along with the number of instances of
duplicate code, whereas the amount of development time when GOTO is used
remains flat. In addition, a change to the error handling code (for
example, if the need arises to rollback to a savepoint) requires that every
instance of duplicate code must be modified and tested--and again cost
increases linearly. The bottom line is that it costs less to use GOTO.
Because it costs less, I would consider that an everyone-must-do-it best
practice.
I said that I dislike your SOLUTION: distributing error handling code. I
acknowledge the point that "IF @.@.TRANCOUNT > 0" may not be needed in your
simplistic example. Since I consolidate error handling code, I always check
@.@.TRANCOUNT before issuing a ROLLBACK. It may add a few unnecessary CPU
cycles, but if I later add a call to another stored procedure, I don't have
to worry about forgetting to change the block of error handling code. All I
need to do is to make sure that control is redirected there whenever an
error occurs. That's one of the benefits of code reuse.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ueOfQx$EGHA.472@.TK2MSFTNGP12.phx.gbl...
> I think you're talking about a preference here, not an everyone-must-do-it
> best practice.
>
> In the example I provided, please tell me how you can reach that path.
> Because remember, you said you didn't like *my* example because I didn't
> use @.@.TRANCOUNT. The logic in the piece of code I posted was so simple
> that the above error message would be impossible to reach.
>

Multiple Inserts in the Same Stored Procedure

Hey,
I have a project that I have been working on and I need to insert a
record into multiple tables and I if any one of the inserts fails I
need to rollback all of the previous inserts that were done. To
illustrate, I have ten tables that need to have a record inserted into
them and if it errors out on table six, then I want to rollback the
previous five inserts. I'd appreciate any advice I can get. Thanks.bradley.d.walker@.gmail.com wrote:
> Hey,
> I have a project that I have been working on and I need to insert a
> record into multiple tables and I if any one of the inserts fails I
> need to rollback all of the previous inserts that were done. To
> illustrate, I have ten tables that need to have a record inserted into
> them and if it errors out on table six, then I want to rollback the
> previous five inserts. I'd appreciate any advice I can get. Thanks.
>
BEGIN TRANSACTION
INSERT Table1 .....
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
INSERT Table2 .....
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
INSERT Table3 .....
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
INSERT Table4 .....
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
...
...
...
...
...
...
COMMIT TRANSACTION|||With SQL 2000 -SQL 2005 is a bit 'simplier' in manifestation - but the idea
is the same.
BEGIN TRANSACTION
INSERT INTO into table1 (ColList) VALUES (ValList)
IF @.ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
INSERT INTO into table2 (ColList) VALUES (ValList)
IF @.ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
{etc.}
COMMIT TRANSACTION
RETURN
There are variations to the idea, using GOTO to the end of the sproc and ROL
LBACK from there.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<bradley.d.walker@.gmail.com> wrote in message news:1151077294.100001.43410@.g10g2000cwb.goog
legroups.com...
> Hey,
>
> I have a project that I have been working on and I need to insert a
> record into multiple tables and I if any one of the inserts fails I
> need to rollback all of the previous inserts that were done. To
> illustrate, I have ten tables that need to have a record inserted into
> them and if it errors out on table six, then I want to rollback the
> previous five inserts. I'd appreciate any advice I can get. Thanks.
>|||To add to the other responses, consider adding SET XACT_ABORT ON to the
beginning of your proc. This will ensure a transaction rollback occurs in
the case of a client-initiated attention event (e.g. query timeout or
cancel).
Hope this helps.
Dan Guzman
SQL Server MVP
<bradley.d.walker@.gmail.com> wrote in message
news:1151077294.100001.43410@.g10g2000cwb.googlegroups.com...
> Hey,
> I have a project that I have been working on and I need to insert a
> record into multiple tables and I if any one of the inserts fails I
> need to rollback all of the previous inserts that were done. To
> illustrate, I have ten tables that need to have a record inserted into
> them and if it errors out on table six, then I want to rollback the
> previous five inserts. I'd appreciate any advice I can get. Thanks.
>|||>> I have a project that I have been working on and I need to insert a record [si
c] into multiple tables and I if any one of the inserts fails Ineed to rollb
ack all of the previous inserts that were done. <<
Easy enough; set up a series of INSERT INTO's in a single transaction,
trap each insertion's error and do a ROLLBACK and return if you have a
failure. Do not commit until the end of the whole thing.
The scope of transactions in T-SQL is independent of the block
structure of the language. Think of a "transaction guy" with a bucket
of data looking at a house. The house pumps data into his bucket. He
does not care what is happening inside; he is waiting to see a COMMIT
or ROLLBACK flag come out of the window of the house. At that point,
he either throws the data out or throws it in the database.
But a better question why do you want to store the same data in
multiple tables? The major reason we moved from files to RDBMS was to
get rid of redundancy -- the mantra is "one fact, one time, one way,
one place!" and not "Let's make ten copies and try to keep them all the
same!" Instead of making ten copies of a mag tape with the same data,
we use VIEWs, CTE, and derived tables in SQL.
You did know that a row is not a record in your posting or understand
transactions, makes me wonder if your schema is messed up because you
are mimicing files.|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> To add to the other responses, consider adding SET XACT_ABORT ON to the
> beginning of your proc. This will ensure a transaction rollback occurs in
> the case of a client-initiated attention event (e.g. query timeout or
> cancel).
Very interesting! I did not know about this. This can be a quick fix
for applications that suffers from unhandled query timeouts.
Why did you not tell me this before? :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Very interesting! I did not know about this. This can be a quick fix
> for applications that suffers from unhandled query timeouts.
We did exactly that recently for one of our ADO middle-tier database
application with persistent db connections. All transactions were done in
stored procedure code (no nested procs) and the application performed no
explicit transaction handling and no special database exception handling.
Database errors were logged but the app moved on, oblivious to the
ramifications.
Everything was fine until a nightly job was accidentally run during the day
and that caused blocking and subsequent command timeouts. Because the app
performed no connection cleanup after the exception, a transaction that was
in progress remained open after the timeout. All subsequent work done on a
problem connection was done in the context the open transaction and was
never committed!
In addition to adding XACT_ABORT ON quick fix, I asked the developers to
close and re-open persistent connections following any type of database
exception.

> Why did you not tell me this before? :-)
I got the idea to try XACT_ABORT ON after perusing your error handling
articles. Somehow, I got it in my mind that this technique was covered
there ;-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97ED135AF6FCYazorman@.127.0.0.1...
> Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> Very interesting! I did not know about this. This can be a quick fix
> for applications that suffers from unhandled query timeouts.
> Why did you not tell me this before? :-)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> Everything was fine until a nightly job was accidentally run during the
> day and that caused blocking and subsequent command timeouts. Because
> the app performed no connection cleanup after the exception, a
> transaction that was in progress remained open after the timeout. All
> subsequent work done on a problem connection was done in the context the
> open transaction and was never committed!
A really fine mess! Yes, we have experienced this in our application as
well, although that's many years behind us now. I wonder how much the
default timeout of 30 seconds have cost enterprises over the worldin
money and misery.

> In addition to adding XACT_ABORT ON quick fix, I asked the developers to
> close and re-open persistent connections following any type of database
> exception.
While better than nothing, it's not optimal, unless you already have
turned off connection pooling. Of course, if you close and reconnect
directly, and get back the same physical connection directly, everything
will be cleaned up on the spot. But if the pool gives you a different
connection, it could take 60 seconds before the rollback occurs, when
the API actually closes the connection.

> I got the idea to try XACT_ABORT ON after perusing your error handling
> articles. Somehow, I got it in my mind that this technique was covered
> there ;-)
In that case, I don't know that I write in my articles myself. But I
will have to add it!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Multiple inserts against single insert from temp table

Hi,

I have table (AttributeID INT NOT NULL, ValueID INT NOT NULL), both of them are foreign keys. I need replace several rows which have same AttributeID and various ValueID with new values. So I must delete all rows WHERE AttributeID=@.AttributeID and then insert new values. What is better,

for every row make one call to SP which will do single INSERT;

or supplies all ValueIDs as single parameter to SP and then extracts ValueIDs them to temp table and then do INSERT MyTable SELECT * FROM #TempTable.

Which solution will be faster ? Or do you have idea for other solution which will outperform those two ?

Much thanks for your replies.

In general, a set base solution (i.e. single dml that affects more than one row at a time) is faster and better than row-per-row. So, if you have lots of rows to be processed, putting the data into a temp table (i.e. staging table) then invoking a single dml command would give you the most bang for your buck.

Multiple inserts

Hi Everyone -

I am trying to perform multiple inserts into a database -

At the moment, I have each insert on its own process -

But i would like to consolodate them into one statement...

Right now it is setup like this...

insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);

is there a way to streamline this operation?

thanks
tonyHi Tony,

Sure. You can concatenate them together into a single round trip to the db, something like this:

insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);insert into trip_sample_table (trip_table_id, sample_speed) values (165,37); and so on.

You can also read them from a temp table, for example, using a SELECT INTO, if you're running the code in a sproc.

How are you running the insert statements?

Don|||Hi Don -

that is exactly what i tried as well -

joining them all into a single insert with semi-colons to seperate
the statements -

and agin - this has caused a failure -
although the failure happeded a LOT faster... :-)

I'm beginning to think the web server itself is causing the
failure on the desktop program.

I'm using the SQLXML URL as the means to post the data -

but i'm leaning twords the updates are posting too fast for the server
to handle them...

Next week i'm going to go ahead and remove 2.0 of the SQLXML
and replace it with the SQLXML 3.0 and see if that solves the problem.

BTW -

I've also created a stored proc to handle the insert -
but the same thing is happening - using these methods

single line insert - one at a time
multiple line inserts - all at once, one long concatenated line -
single line insert using the stores proc

I've also put a thread.sleep(1000) between each
of the inserts, and although it runs MUCH slower -
it does get past the point of failure for the single line inserts.

I've also noticed the program just crashed out to the desktop -
even with a VERY large amount of try catches -

it just blows right out of the application.

thanks for responding.

take care
tony|||Hi Tony,

Yeah, SQLXML 2.0 (actually called something different: Web Release for SQL or something like that) had some issues. Make sure you install the SP1 of SQLXML 3.0. That version fixes a lot of stuff, and I'd say that's our prime suspect right now.

If it doesn't solve the problem, we can dig further. You hadn't mentioned you were using SQLXML, so there may be something about how you're using it that's causing a problem. Still kinda weird, though.

Keep us posted!
Don

Multiple insert with the same ID

Here is my problem:

I would like to execute 10 inserts and every insert in this batch should have the same ID. The next batch would have the next sequential number (ID) and so on...

I can do this by using a secondary table where I keep track of my IDs.

Is there another way of doing it?

Thanks.

dgYou could dynamically generate something based off something like the customer ID and the milliseconds of the date, for example. Not guaranteed to be unique, but some sort of thing based on your data that might be. You could also use the NEWID function to create a guaranteed unique value, but then you'd be using more space to store it than if using an int. Or, just select the max existing value before you do the insert, but you might get a collision there depending on activity. Some ideas

multiple insert delays

I am trying to work on a java application that does multiple inserts to the informix database. The data is actually obtained from an online screen. I see that the performance is very poor when ever the user enteres more than 20 records. This is caused because of the 20 inserts performed with in the program. Is there anyway I could do this in one insert.

I know that the bulk insert could be done using INSERT into ..select from.
I cannot use this as I do not have my data in a table.

Please advice.Hello,

I dont know Informix very well, but when you want to insert 20 records in a database, the execution time must be less than 1 second. For example 500-1000 insert on an oracle database will take 1 second (depends on the machine and network and so on). This is the execution time that we reach in various projects.

So - there must be a problem with your application or your network. Did you trace your application ? So that you can see what is happening ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Multiple insert command

Is it possible to insert a row, where you want to insert the same data in the row, but just change a number?

In vb i would use this for example (not actually the code, just an example)

For x=1 to 10

sql = "Insert into (Number, .... .. ) values (" & x & ", ......)

execute command

Next

Is it possible to make one command where i could something like this?

There are many ways of doing it depending on your requirements. Below is a simple example that assumes that you have variables with the data and you want to insert say 10 rows:

Code Snippet

insert into your_table (id, ....)

select top(10) row_number() over(order by object_id) as seq, @.col1, @.col2, @.col3

from sys.objects

order by object_id

-- You can also generate more rows on the fly using cross join of several tables like

insert into your_table (id, ....)

select top(1000) row_number() over(order by o1.object_id) as seq, @.col1, @.col2, @.col3

from sys.objects as o1 cross sys.objects as o2

order by o1.object_id

multiple insert call for a table having insert trigger

Hi

I am trying to use multiple insert for a table T1 to add multiple rows.

Ti has trigger for insert to add or update multiple rows in Table T2.

When I provide multiple insert SQL then only first insert works while rest insert statements does not work

Anybody have any idea about why only one insert works for T1

ThanksLooks like SQL Server is treating these multiple inserts as a batch and therefore only assuming one insert.

Try using the GO statement between the inserts and this will cause your trigger to fire with every insert.

INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO

Cheers|||Thanks for reply me back..
I am using cursor to call multiple insert inside that curser.. here is the code..

OPEN DestinationIDList
FETCH NEXT FROM DestinationIDList INTO @.DestinationID

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO Table(ID, DestinationID) VALUES(@.ID, @.DestinationID)

FETCH NEXT FROM DestinationIDList INTO @.DestinationID
END
CLOSE DestinationIDList
DEALLOCATE DestinationIDList

What should I have to do so that trigger fire for each insert ?

Thanks

Originally posted by aldo_2003
Looks like SQL Server is treating these multiple inserts as a batch and therefore only assuming one insert.

Try using the GO statement between the inserts and this will cause your trigger to fire with every insert.

INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO

Cheers|||What you could do is to write the Insert as a stored procedure.

Then call this proc from your cursor passing the variables that you have defined in your cursor

something like :

create proc Insert_T1
(@.id as int , @.DestinationID as int)
as

INSERT INTO T1(ID, DestinationID) VALUES(@.ID, @.DestinationID)
go

All you do call this proc in your cursor land this should make your trigger fire for every insert you perform

Give it a go and see if it works

Cheers|||No Luck :-(|||Can you post the code for your trigger

Cheers|||Here is the trigger's code

CREATE TRIGGER TR_TD
ON dbo.TRHistory FOR INSERT

AS

DECLARE @.ID int
DECLARE @.DSTID int
DECLARE @.RT decimal (18,4)
DECLARE @.Time datetime
DECLARE @.GTID int
DECLARE @.GTExist int
DECLARE @.intErrorCode INT
SET @.GTID = 0
SET @.GTExist = 0
SET @.ID = 0

-- CHECK ID' S VALIDITY

SELECT @.ID = i.ID, @.DSTID= i.DSTID,
@.RT = i.RT, @.Time = i.Time
FROM Inserted i
INNER JOIN ITSPS ON ITSPS.ID = i.ID

IF @.ID <> 0

BEGIN

-- FIND OUT NO OF GTS FOR SPECIFIC DSTS TO UPDATE FOR CORRESPONDING GT
DECLARE GTList CURSOR FOR
-- SELECT GTS FOR DSTID
SELECT GTs.GTID
FROM GTs INNER JOIN
GTDSTs ON GTs.GTID = GTDSTs.GTID INNER JOIN
ITSPs ON GTs.ID = ITSPs.ID
Where GTs.ID = @.ID AND DSTID = @.DSTID

OPEN GTList

FETCH NEXT FROM GTList INTO @.GTID

-- IF NO GT FOUND OF SPECIFIC DST FOR ITSP THEN REJECT
IF (@.GTID = 0)
BEGIN
SELECT @.intErrorCode = 1
CLOSE GTList
DEALLOCATE GTList
GOTO PROBLEM

END
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0

BEGIN

-- CHECK WIEHTER GT ENTERY EXISTS IN TR TABLE
-- IF SO THEN UPDATE ELSE INTER NEW FOR GT

SELECT @.GTExist = GTid
FROM TR
WHERE GTid = @.GTid AND DSTID = @.DSTID

IF @.GTExist > 0

UPDATE TR
SET RT = @.RT,
Time = getdate()

WHERE GTID = @.GTid


ELSE

INSERT INTO TR (GTID, DSTID, RT)
VALUES (@.GTID,@.DSTID,@.RT)
-- IN CASE FOR ANY EXCEPTION GO TO PROBLEM PARA AND CLOSE & DEALLOCATE CURSOR
SELECT @.intErrorCode = @.@.ERROR
IF (@.intErrorCode <> 0)
BEGIN
CLOSE GTList
DEALLOCATE GTList
GOTO PROBLEM
END
FETCH NEXT FROM GTList INTO @.GTID


END
CLOSE GTList
DEALLOCATE GTList
COMMIT TRAN
END
ELSE
-- INVALID ID
BEGIN
RAISERROR (' Invalid ID ', 16, 1)
ROLLBACK TRAN
END


PROBLEM:
IF (@.intErrorCode <> 0)
BEGIN
IF (@.intErrorCode = 1)
RAISERROR (' Insert is rejected due to invalid GT DST info', 16, 1)
ELSE
RAISERROR ('Error occured to udated info for tr' s. Please contact administrator ', @.intErrorCode, 1)
ROLLBACK TRAN


END|||Hi aldo_2003

I am Waiting.. Only problem is that if i use multiple insert with GO then multiple insert works.. but when i call is in cursor for multiple then it add only first insert..|||Just got back from lunch mate

Am goiung to try a couple if things and get back to you

Cheers|||Had a quick look at the trigger

Don't think it is the GO statement as I managed to get a test trigger to fire twice without the GO.

I'm not sure why your trigger is only firing once.

What you might want to do is to try and reduce the complexity of the code within the trigger by putting that logic within the stored proc and calling this from your trigger.

You have cursors within cursors when you take the trigger into account and this may be causing you issues that you are not aware of.

Get the trigger to fire properly without any logic in it i.e

Get the first cursor inserting into your table and the trigger firing a simple insert into a test table. Once you have that then implement your trigger logic in a stored proc and call that from the trigger.

I hope this helps, let me know how you get on.

Cheers|||Well there are just so many things...

first you don't need a cursor...collapse the cursor and the insert in to 1...

second a cursor in a trigger can't be a good idea performance wise...but like I said collapse them

third

-- IF NO GT FOUND OF SPECIFIC DST FOR ITSP THEN REJECT
IF (@.GTID = 0)

isn't a check for existance...

Look at @.@.ROWCOUNT

fourth...never mind...fixe the insert first...

multiple insert

hi friends,

i am having a problem here. I am using msde and i have a database with 3 tables.

Here is what i want to do.

1- I want to insert posted data to table 1 ( i can do this step)

2- I want to insert posted data to table 2 ( i can do this step, too)

3- I want to insert the primary keys of the rows inserted to table 1 and table 2 , to table 3 . I dont know anything about stored procedures . Can i do this in my code-behind using C# ? If you can show me the way i will be glad.
Thanks in advance

Using a stored procedure would be best. Assuming that you'reusing IDENTITY columns for your primary keys, your basic logic will besomething along the lines of:

DECLARE @.tbl1ID INT
DECLARE @.tbl2ID INT
INSERT Tbl1
(column list)
VALUES
(values)
SET @.tbl1ID = SCOPE_IDENTITY()

INSERT Tbl2
(column list)
VALUES
(values)
SET @.tbl2ID = SCOPE_IDENTITY()
INSERT Tbl3
(Tbl1ID, Tbl2ID)
VALUES
(@.tbl1ID, @.tbl2ID)
|||

thank you adam for you quick reply,
stored procedure way seems not that difficult but i dont know anythink about them. Where do you write the stored procedures, how do you run them , how do you call them from aspx.cs file, how do you reach the output variables from aspx.cs file ?
if you can tell me the steps i will be glad.
thanks in advance

|||I think you need to Google for some SQL Server introductoryarticles. Try www.sqlteam.com and www.sqlservercentral.com. It's a bit much to discuss in a forum post.

Multiple INSERT

I have been using a Select and Insert into statement,

INSERT INTO DetailsData(DetailsData_FundID, DetailsData_FundCountryID, DetailsData_FieldID, DetailsData_Data)
SELECT @.NewFund_ID, @.NewFund_CountryID, DetailsData_FieldID, DetailsData_Data FROM DetailsData WHERE
DetailsData_FieldID in (311, 814, 819, 820, 821, 822, 823, 824, 830, 831, 832, 833, 834, 826, 825, 841, 840, 843, 842, 813, 847, 848)
AND DetailsData_FundID = @.Fund_ID
AND DetailsData_FundCountryID = @.Fund_CountryID

that inserts the result set of a Select statement into a table. In our database for each transaction performed a record is kept

e.g.
INSERT INTO TranDetailsData VALUES (1, GETDATE(), NULL, 1, @.NewFund_ID, @.NewFund_CountryID, 845, @.GroupId);

Is it possble to perform them both in one statement?no because they insert into different tables|||1. use trigger on insert into DetailsData for the second insert (i would use this approach if you must keep track of inserts).
2. there is a special sql code constructions in the latest version of db2 udb, but it seems like you are using ms sql server. may be ms sql server has someting similar, ask in corresponding group.
3. why do you need one statement, the same transaction for both sqls should be ok (either both inserts go in or both are rejected).

regards,
dmitri|||well thanks for your suggestions.

I worked it out like this by using the data type table to store the data from the selectect statement.

DECLARE @.tblDetailData TABLE(DetailsData_FundID int, DetailsData_FundCountryID varchar(3), DetailsData_FieldID int, DetailsData_Data varchar(512))

INSERT @.tblDetailData SELECT @.NewFund_ID, @.NewFund_CountryID, DetailsData_FieldID, DetailsData_Data
FROM DetailsData WHERE DetailsData_FieldID IN (311, 814, 819, 820, 821, 822, 823, 824, 830, 831, 832, 833, 834, 826, 825, 841, 840, 843, 842, 813, 847, 848)
AND DetailsData_FundID = @.Fund_ID
AND DetailsData_FundCountryID = @.Fund_CountryID

INSERT INTO DetailsData(DetailsData_FundID, DetailsData_FundCountryID, DetailsData_FieldID, DetailsData_Data) SELECT * FROM @.tblDetailData

INSERT INTO TranDetailsData(TranSessionID, TranTime, TranSequence, TranType, DetailsData_FundID, DetailsData_FundCountryID, DetailsData_FieldID, DetailsData_Data) SELECT 1, GETDATE(), NULL, 1, * FROM @.tblDetailData

chuzhoi
"3. why do you need one statement, the same transaction for both sqls should be ok (either both inserts go in or both are rejected)."

I kinda guessed performing the statement twice would be an in efficient way of going about it.sql

Multiple insert

Hi, I'm trying to insert multiple rows with the following statment and
am getting a syntax error on line 3. The datatypes are varchars except
for status which is numeric.

insert S (S#, SNAME, STATUS, CITY)
values
('S2', 'Jones', 10, 'Paris'),
('S3', 'Blake', 30, 'Paris'),
('S4', 'Clark', 20, 'London'),
('S5', 'Adams', 30, 'Athens');

What's wrong?
Thanks,
SashiYou have to use the INSERT... SELECT form to insert multiple rows.

INSERT INTO S (s#, sname, status, city)
SELECT 'S2', 'Jones', 10, 'Paris' UNION ALL
SELECT 'S3', 'Blake', 30, 'Paris' UNION ALL
SELECT 'S4', 'Clark', 20, 'London' UNION ALL
SELECT 'S5', 'Adams', 30, 'Athens' ;

--
David Portas
SQL Server MVP
--|||The real problem is that SQL Server does not yet support SQL-92 syntax,
in spite of having the power to do so. Your choices are:

1) use a series of INSERT INTO statements (notice that INSERT is a
proprietary shorthand, not Standard SQL).

2) use a proprietary SELECT ..UNION ALL chain to build a table the same
way that the VALUES table constructor would.

Multiple Dynamic Inserts with SQL

I'm try to a multiple insert from one database to another by using this code:

insert into [mpis].[dbo].[Residents] (acno,surname,name,ID,type)
(selecttop 30 acno,surname,name,id,type
from [PretoriaDB].[dbo].[WorkingDB])

but I keep on getting this error:

Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Can any one help!!

If the target table will always be empty, you can just drop the table before the insert and use a "Select Into" statement. Otherwise, you will probably have to open a cursor for the "select top 30 ..." statement and iterate over it to insert each record to the table.

|||

tmametja:

String or binary data would be truncated.

the Error says that...

One of the fields on table [mpis].[dbo].[Residents] of type varchar/char is beingfed data that is too long. i.e. One of the Firleds in [PretoriaDB].[dbo].[WorkingDB] that you are usingto populate the [mpis].[dbo].[Residents] probably contains a string value that is too large for the field thatyou are trying to plug it into.

hope it helps to solve you problem./.

|||

i didn't know 2005 had an "INSERT...SELECT" statement... cool

Anyhow, looks like the syntax is INSERT [table_name] SELECT [colA], [colB] ... FROM [table_name_or_join]

I didn't see any "INTO" in the examples, or any mention of the target column list on the target table as in a tradional insert statement.

|||

tmametja:

Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Make sure data types (in INSERT and SELECT) are match and field length as well.

Good luck.

|||

thanx

|||

thanxkaushalparik27

|||

tmametja:

thanxkaushalparik27

well pleasure to help you, and dont forget to mark the answers which helped you and resolve the thread. Thanx

Multiple Dynamic Insert Statements in one stored procedure?

Hello, I'm having a little trouble, and need a little direction. I

have a stored procedure with a transaction that creates a contract for

a customer in a Contract table. After the contract record is created, I need

to create the contract items/products with their properties (price,

notes, etc), which belong in a linked table, so that they're created

all within the same transaction (the contract, and the products), so

that if either the contract or any of the items cannot be inserted into

their correct tables, it will rollback and undo the creation of the

contract. Now, I understand transactions and how they work, but my

trouble is that I'm not sure if I can use a dynamic insert

statement to create the contract items/products, within the same

transaction where the contract is created.

I'm new to sp_executesql, which seems to be the recommended method for dynamic sql in a stored proc, but if I understand it correctly, I'd have to call a stored proc for each item i wanted to insert something into the ContractItem table with a dynamic insert statement. Is there any way to have multiple dynamically created insert statements in one stored procedure? Help! I hope I explained my problem well enough. thanks in advance!

You can execute sp_executesql as many times as you like in a stored procedure. Simply begin a transaction at the start of the proc then either rollback or commit the transaction when appropriate.

Incidentally, is there a particular reason that you are choosing to use dynamic SQL over defining stored procedures that parameters can be passed into?

Chris

|||

Chris Howarth wrote:

You can execute sp_executesql as many times as you like in a stored procedure. Simply begin a transaction at the start of the proc then either rollback or commit the transaction when appropriate.

I understand this, I guess my problem really is this: the number of rows I will have to insert into the ContractItem table will vary each time this stored procedure to create a Contract and it's ContractItems is called. So how do I pass that (variable amount of) data into the stored prodedure to create the dynamic insert statements?

Chris Howarth wrote:

Incidentally, is there a particular reason that you are choosing to use dynamic SQL over defining stored procedures that parameters can be passed into?

Not really, I'm still sort of new to stored procedures though, if that is any kind of an excuse :) If this would be a better way to do it, then my plan should probably change slightly...

|||

How you choose to do this very much depends on your application's architecture.

If you have a web server or application server that is executing your stored procedures then it would probably make sense to create a single INSERT stored proc (designed to insert one row at a time) per table and then get your web/app server to execute the stored procedure once per row, passing in the parameters as required. Any iteration that needs to take place can then be performed by the web/app server.

You can start a transaction at the connection level, so as long as your web/app server uses the same connection to execute all stored procs that are part of the 'business transaction' then all of the changes will be rolled back should an error be raised.

Does this fit your scenario?

Chris

|||Chris,

This does fit my scenario... a transaction at the connection level sound just like what I need to get this to work properly. Now I just have to go dig up how to do that. Thank you so much for your help!

Multiple Databases?

Is it possible to retrieveDatafromSQL Database #1and insert it intoSQL Database #2using aStored Procedure?Thanks. If so Can you show example. ThanksYou could do something like this perhaps, I'm not sure on how efficient it would be. This is untested but maybe it will give you enough info to go on, be sure that the dbo has access to the second db you are wanting to insert data into. The other option is a trigger, but you asked how to do it from a SPROC.

CREATE PROCEDURE dbo.YourSPROCName
(
@.Whatever INT --I assume you have a UID your're passing in
)
AS
DECLARE @.Val1 VARCHAR
DECLARE @.Val2 VARCHAR
DECLARE @.Val2 VARCHAR
SELECT @.Val1 =(SELECT Col1 FROM Database1.dbo.Table1 WHERE Whatever=@.Whatever)
SELECT @.Val2 =(SELECT Col2 FROM Database1.dbo.Table1 WHERE Whatever=@.Whatever)
SELECT @.Val2 =(SELECT Col3 FROM Database1.dbo.Table1 WHERE Whatever=@.Whatever)
INSERT INTO Database2.dbo.Table1 (Whatever,Col1,Col2,Col3) VALUES (@.Whatever,@.Val1,@.Val2,@.Val3)
GO

Good luck.|||Thanks PD_Goss

I'm Trying to get it to work.
Question:?
How do you check to see if dbo has access to the second db?

---

Here's what I have so far but its not working. Any Ideas? Thanks.

Create Procedure spDatabaseExport

AS
BEGIN
SELECT MyDatabase1.dbo.Inventory
SELECT QtyInStock
WHERE
ProductID = @.6
GO

BEGIN
DECLARE @.SalesCount INT
INSERT INTO MyDatabase2.dbo.counter3
SELECT SalesCount FROM counter3
WHERE
ID = 2
END
GO|||If you are using dbo it should already have access.

Can you explain what it is you are trying to accomplish?

Here's a quick run down.

AS
 --if you are wanting to query a value from one db and insert it into another this is how:
DECLARE @.SalesCount INT
--Grab what you want
SELECT @.SalesCount = (SELECT SalesCount FROM MyDatabase1.dbo.counter3 WHERE
ID = 2 ) --is this supposed to be a static variable?
--put it in the other table
INSERT INTO MyDatabase2.dbo.counter3 (SalesCount) VALUES (@.SalesCount)
GO

|||Thanks PD_Goss,
What I'm Trying to do is set up a SaleHitCounter to keep track of the amount of individual items sold & store the values into a Different Database. Maybe increment the value by + 1
everytime an item is sold.

I have an OrderItems table Created with the following Columns

uid, OrderID, ProductID, AddressID, Quantity, ProName, Price,

I would like to create a Stored Procedure that's able to update (or) transfer into another Database the total values for each individual items sold. Maybe use the (ProductID, & Quantity, ) Columns? What would be the best way to do this?

TheProductID Column - contains all the Item Numbers e.g. 25, 52, 12, etc.

Is there a way (Stored Procedure) to tap into theProductID & Quantityand somehow get an accumulated total for each individual item - then transfer values to a different database.
But I'm confused on how to go about writing a Stored Procedure to accomplish this. Thanks Again for the help.|||Is the OrderItems table very large. If not, it will be wise idea to run the query directly on OrderItems table as following

select
 ProductID,
 sum (Quantity)
from
 OrderItems
--where -- Need the following 2 lines only if you want to filter
-- ProductID in (25, 52)
group by
 ProductID

If you want to dump this to another database, Run this query on the second database as following

Insert into
 tblCounts
select
 ProductID,
 sum (Quantity)
from
 Database1.dbo.OrderItems
--where -- Need the following 2 lines only if you want to filter
-- ProductID in (25, 52)
group by
 ProductID

Hope this helps

Anil|||This is how I would do it (personal preference I guess). It's important to have constraints (ProductID) so you need to place this after the procedure that updates the OrderItems table, hopefully you have a SPROC that is doing this so you can grab the ProductID parameter for the following code.

--After the Order Items Update
DECLARE @.QtySold INT
SELECT @.QtySold =(SELECT SUM(Quantity) FROM OrderItems WHERE ProductID=@.ProductID)
IF ((SELECT COUNT(ProductID) FROM Database2.dbo.SalesHitCounter WHERE ProductID=@.ProductID)=1)
BEGIN
UPDATE Database2.dbo.SalesHitCounter SET QtySold=@.QtySold WHERE ProductID=@.ProductID
END
ELSE
BEGIN
INSERT INTO Database2.dbo.SalesHitCounter (ProductID,QtySold) VALUES (@.ProductID,@.QtySold)
END

|||Thanks Guys for all the help!

But Database2.dbo.tblCounts is not receiving any inserts from OrderItems (via) Stored Procedure.

Here's where I'm at so far.
TableOrderItems is located inDatabase1
And TabletblCountsis Located inDatabase2

Both Tables are the same (except) tblCounts has nothing in it.
Both tables have the following Columns:(uid, OrderID, ProductID, Quantity, ProductName)
Here is how I have my Stored Procedure set up for now:

CREATE PROCEDURE webcounter4

AS
BEGIN
Select
ProductID,
sum (Quantity)
from
OrderItems
where
ProductID in (1)
group by
ProductID
Insert into
tblCounts
select
ProductID,
sum (Quantity)
from
Database2.dbo.tblCounts
where
ProductID in (1)
group by
ProductID
END
GO

---

Here is my the code from my .ASPX page that fire the Stored Procedure

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@. Page Language="VB" Debug="true" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SQLClient" %
<script runat="server"
 Sub Page_Load(Source as Object, E as EventArgs)
 Dim objCon As New SQLConnection("server=MyServer\InstanceName;User id=SA;password=Password;database=Database1")
 Dim cmd As SQLCommand = New SQLCommand("EXEC dbo.webcounter4", objCon)
 objCon.Open()
 Dim r as SQLDataReader
 r = cmd.ExecuteReader()
 r.read()
 strtblcounts.text = "Sale Hits : " & r.item(0)
 end sub
</script>

---

<BR>
Also I'm not sure how to incorperate this procedure:</B

DECLARE @.Quantity INT
DECLARE @.ProductID INT
DECLARE @.QtySold INT

SELECT @.Quantity =(SELECT SUM(Quantity) FROM OrderItems WHERE
ProductID=@.ProductID)

IF ((SELECT COUNT(PRODUCTID) FROM Database2.dbo.tblcounts WHERE
ProductID=@.ProductID)=1)

BEGIN

UPDATE Database2.dbo.tblCounts SET Quantity=@.Quantity WHERE
ProductID=@.ProductID

END

ELSE

BEGIN

INSERT INTO Database2@..dbo.tblCounts (ProducrID,Quantity) VALUES
(@.ProductID,@.Quantity)
|||This SPROC would insert a count or update the count of each item sold when an order is created, each item having its own row. It would need to be in the SPROC that handles order creation. I assume you are passing in the ProductID so the declaration is not necessary. The tblcounts structure would need to be more like UID, ProductID, QtySold.

--DECLARE @.Quantity INT --Not needed
--DECLARE @.ProductID INT --Not needed
DECLARE @.QtySold INT
--Get the Qty Sold of an item
SELECT @.QtySold =(SELECT SUM(Quantity) FROM OrderItems WHERE
ProductID=@.ProductID)
--Check if the items exists in the table
IF ((SELECT COUNT(ProductID ) FROM Database2.dbo.tblcounts WHERE
ProductID=@.ProductID)=1)
BEGIN
--If a record exists... update it
UPDATE Database2.dbo.tblCounts SET QtySold=@.QtySold WHERE
ProductID=@.ProductID
END
ELSE
BEGIN
--If a record does not exist... insert one
INSERT INTO Database2.dbo.tblCounts (ProductID,QtySold) VALUES
(@.ProductID,@.QtySold)
END

Your webcounter4 SPROC would only need this:

SELECT ProductID,QtySold FROM Database2.dbo.tblcounts WHERE ProductID IN (1)
GROUP BY ProductID

|||Thanks PD_Goss,

I don't have a SPROC that handles the Order Creation, I have aOrderDetails.aspx & OrderDetails.aspx.vb pages I believe that handles Order Creation. If you need to see the OrderDetails.aspx.vb Class file -- I can post it.|||I would definitely use SPROC's for your data work. I started out using inline SQL and when I made the transition it was much easier to manage. You can post the code and I will whip you up a SPROC.|||HI PD_Goss

I was wondering if it would be possible just toemail the class files to you. I have 3 rather large & lenghty class files & I'm not sure which one exactly you need to see --So I'd like to send you all three Class files. But rather than post, could I just Email the files. Thanks.|||Sure, PublicDispAcct@.hotmail.com

Multiple data insertion with For clause

Suppose I have a table named test with two fields(sl int,age int).
I want to insert 10 records all at a time.The records are in an incremental manner.I like to insert 1,2,3,4,5,6,7,8,9,10 for sl column and
22,23,24,25,26,27,28,29,30,31 for age column.But the procedure should follow C protype using for (j=1;j<10,j++) clause.
Is it possible to insert in SQL SERVER following c protype?
What is the fastest way for inserting multiple sequevcial data?
SubhasishFor relatively small sequential sets, do something like this...

INSERT INTO TEST(sl, age)
SELECT a.i, a.i+21
FROM (
SELECT i = 1 UNION
SELECT i = 2 UNION
SELECT i = 3 UNION
SELECT i = 4 UNION
SELECT i = 5 UNION
SELECT i = 6 UNION
SELECT i = 7 UNION
SELECT i = 8 UNION
SELECT i = 9 UNION
SELECT i = 10 ) as a

You can build up the derived table query quickly with cut-and-paste, then go back and fix the values

For bigger sequential sets, build yourself a temporary table of sequential integers like this:

CREATE TABLE #i
(x INT IDENTITY(1,1),
y INT)

INSERT INTO #i
VALUES(NULL)

INSERT INTO #i
SELECT y FROM #i

Running the last statement over and over will populate table #i with sequential integers in the x column. 11 executions gets you 1K rows, 21 gets you 1M rows, ... Then use the temporary table to drive your insert.

INSERT INTO test(sl, age)
SELECT #i.x, #i.x+21
FROM #i
WHERE #i.x < 100 -- for 100 rows|||I think s/he's looking for a loop as well...

USE Northwind
GO

CREATE TABLE myTable99 (sl int,age int)
GO

DECLARE @.x int, @.y int

SELECT @.x = 1, @.y = 1

WHILE @.x < 100
BEGIN
INSERT INTO myTable99 (sl, age)
SELECT @.X, 1*@.y UNION ALL
SELECT @.X, 2*@.y UNION ALL
SELECT @.X, 3*@.y UNION ALL
SELECT @.X, 4*@.y UNION ALL
SELECT @.X, 5*@.y UNION ALL
SELECT @.X, 6*@.y UNION ALL
SELECT @.X, 7*@.y UNION ALL
SELECT @.X, 8*@.y UNION ALL
SELECT @.X, 9*@.y UNION ALL
SELECT @.X, 10*@.y

SELECT @.x = @.x + 1, @.y = @.y + 1
END

SELECT COUNT(*) FROM myTable99
GO

DROP TABLE myTable99
GO

Multiple connection type query

Hi everyone,
Is it possible to perform a SELECT/INSERT statement with two different connection types? I want to do the "SELECT" statement with data from SQL Server and "INSERT" it into an Access database all in one query.
Sanctosuse linked tables to sql server in Access and create your insert query in access.