Insert/Update statements or Stored Procs

When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.
thanksStored procs...but who's going to write them?|||Use ADO from VB for insert and update|||Originally posted by Brett Kaiser
Stored procs...but who's going to write them?

wouldn't i just code the Insert/Update statement within the stored proc, then pass the value's to the stored proc. That sounds like alot of parameters to be dealing with for larger tables.|||Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

Also you isolate all of the buseness rules to the server, not the code...

More control that way.|||Originally posted by Brett Kaiser
Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

Also you isolate all of the buseness rules to the server, not the code...

More control that way.

Thanks Brett, one more quick question. Within the stored proc, i need to check if the record already exists before inserting or updating. Can you paste a small code sample to give me an idea of how i would ideally do that.

thanks alot|||I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

-PatP|||USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C' UNION ALL
SELECT 4,'D'
GO

CREATE PROC mySproc99
@.Action Char(1)
, @.Col1 int
, @.Col2 char(1) = Null
AS
-- File: {\\tsstrv03\ESolutions}:
-- Date: May 1st, 2002
-- Author: Brett Kaiser
-- Server:
-- Database: TaxReconDB
-- Login: sa
-- Description: myTable99 Maint sproc
--
--
-- The stream will do the following:
--
-- 1.
--
-- Tables Used: myTable99
--
-- Tables Created: None
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- ------- ---- ------ ------ ------ ------
-- Ledger_Detail 76779 17160 KB 17040 KB 64 KB 56 KB
-- ATS_SignOff_Entity 3316 512 KB 504 KB 16 KB -8 KB
-- tblAcct_LedgerBalance 11691 3848 KB 3792 KB 8 KB 48 KB
--
--Change Log
--
-- UserId Date Description
-- ---- ----- ---------------------------
-- x002548 05/23/2002 1. Initial release
--
--
--

Declare @.error_out int, @.Result_Count int, @.Error_Message varchar(255), @.Error_Type int, @.Error_Loc int, @.RC int

SET NOCOUNT ON

SELECT @.rc = 0

BEGIN TRAN

IF @.Action NOT IN ('S','I','U','D')
BEGIN
SELECT @.Error_Loc = 1
SELECT @.Error_Message = 'Incorrect Request. Must be S,I,U or D. Paramter was: "' + @.Action + '"'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END

IF @.Action = 'S'
BEGIN
SELECT Col1, Col2 FROM myTable99 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 2
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 2
SELECT @.Error_Message = 'myTable99 Returned zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'D'
BEGIN
DELETE FROM myTable99 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 3
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 3
SELECT @.Error_Message = 'An Attempted DELETE from myTable99 affected zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'I'
BEGIN
INSERT INTO myTable99(Col1,Col2) SELECT @.Col1, @.Col2

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 4
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 4
SELECT @.Error_Message = 'An Attempted INSERT to myTable99 did not insert anything'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'U'
BEGIN
UPDATE myTable99 SET Col2=@.Col2 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 5
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 5
SELECT @.Error_Message = 'An Attempted UPDATE of myTable99 Affected zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

COMMIT TRAN

mySproc99_Exit:

SET NOCOUNT OFF

RETURN @.rc

mySproc99_Error:

ROLLBACK TRAN

IF @.Error_Type = 50001
BEGIN
Select @.error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' @.@.ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
+ ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @.error_out)
END
IF @.Error_Type = 50002

BEGIN
Select @.Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@.Error_Message)
END

SELECT @.rc = -1

RAISERROR @.Error_Type @.Error_Message

GOTO mySproc99_Exit
GO

DECLARE @.RC int

EXEC @.RC = mySproc99 'X',1,'A'

SELECT @.RC

EXEC @.RC = mySproc99 'S',4

SELECT @.RC

EXEC @.RC = mySproc99 'I',5,'E'

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'U',5,'F'

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'D',5

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'I',4,'F'

SELECT @.RC
GO

DROP PROC mySproc99
GO
DROP TABLE myTable99
GO|||Originally posted by Pat Phelan
I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

-PatP
Actually, any communication with the server should be done through stored procedure, including SELECT.|||Originally posted by rdjabarov
Actually, any communication with the server should be done through stored procedure, including SELECT. I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

-PatP|||here's the man of so many virtues|||Originally posted by ms_sql_dba
here's the man of so many virtues

You sure s/he's a man?

Pat, you lost me...

we're talking about an app right? Not ad-hoc/dba maint issues? right?|||Originally posted by ms_sql_dba
here's the man of so many virtues Are you accusing me of having virtues ? I may wear many hats, but that is due to having a huge head. It has nothing to do with virtues of any kind!

-PatP|||Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)|||Originally posted by rdjabarov
Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)

suave is the only word I can think of...

You must be a ladies man....

:D

Does anyone use anything like the template posted..or is it 1 sproc per operation?|||Originally posted by rdjabarov
Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?) Heck, I thought that confusion was a consequence of working with databases!

Nah, I don't really have any of those titles, but they sounded cool next to my actual titles (International super-spy, Bon-Vivant, and Ultra-cool geek about town). I'll try to behave better from now on!

-PatP|||Originally posted by Brett Kaiser
suave is the only word I can think of... Nope, Suave is one of our DataWarehousing consultants. He lives somewhere in Jersey and flies out to come play when we need him.
Originally posted by Brett Kaiser
You must be a ladies man.... Just one lady, although I do flirt outrageously. I used to send people around the bend when I'd dial our last TAM and ask "So how are you, other than obviously devastatingly gorgeous?" To which she'd often reply "Gee, you've just GOT to call more often."
Originally posted by Brett Kaiser
Does anyone use anything like the template posted..or is it 1 sproc per operation? Nope, to me that smacks of bad design. At least in my book, cross-tabs should be done on the client side or in the data warehouse, not from an OLTP system.

-PatP|||That's all VERY funny...

but what do you mean cross tabs?

And I definetly have to use that line...

Well, at least on the wife anyway...|||Originally posted by Brett Kaiser
but what do you mean cross tabs?
Whoops! Brain fart on my part, wrong thread!

-PatP|||International super-spy? You took my title!!! I demand it back...or a cig!|||You're really having trouble with the cig thing, but it is worth the fight. There aren't enough "bright boys" around, and we can't afford to loose any!

Anywho, the title isn't exclusive. When they awarded me the title, none of the previous users lost their permission to use it!

-PatP|||>For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand".

Well. You could use embedded SQL inside Powerbuilder and its a far superior tool among all the popular tools. You better get your facts right...pb8 > 9704 would "compile" and give SQL error codes whenever embedded sql is given in code and it adds to ease of use for developers...[no doubt agrees for SP approch being the better one].

moreover, PB supports all 4 levels of dynamic SQL superbly and I use them successfully in my code to query oracle dynamically even when i dont know table names or column list...

my 2 cents

WS [wizardofnet-at-yahoo]

Originally posted by Pat Phelan
I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

-PatP|||Originally posted by mell
I use them successfully in my code to query oracle dynamically even when i dont know table names or column list...


[as he types falling out of chair]
Really?
[/as he types falling out of chair]|||Sorry, Brett, but in the few projects I have had any control over, I went with a stored procedure per action. Makes for a ton of stored procedures, but the front end code seems to be more readable. Haven't gone for many updates, as yet, so I don't know how many stored procedures I will be touching then. But that is just my 0.02 USD=0.219430 MXN|||Originally posted by MCrowley
Sorry, Brett, but in the few projects I have had any control over, I went with a stored procedure per action. Makes for a ton of stored procedures, but the front end code seems to be more readable. Haven't gone for many updates, as yet, so I don't know how many stored procedures I will be touching then. But that is just my 0.02 USD=0.219430 MXN

Ya lost me on that one...you mean make 4 out of the one I posted?

It's all a matter of methodolgy...

Pick 1 and stick eith it...no thinking involved...same thing for naming comventions...

make it so you don't have to look anything up...

But I like the part about not knowing the names of columns or tables...

must make for some very interesting code...no?|||Originally posted by mell
Well. You could use embedded SQL inside Powerbuilder and its a far superior tool among all the popular tools.[wizardofnet-at-yahoo] 'splain dis one again for me... In the scenario I described, you don't have SELECT permissions, so you can't see any tables. You can't open the DataWindow painter. You can't generate any dynamic SQL...

What exactly can you do again?

-PatP|||Hey Lucy.....what did you did you do with the permissions this time?|||Yep. As near as I understand, when a procedure runs for the first time with it's first parameters, a query plan is born. SQL Server tries to use that query plan for each successive run of the stored procedure. Writing an all in one procedure is good if the procedure is not run very often, but for a website where a stored procedure can be run many many times, you don't want to wait around for the query optimizer to try to figure out it needs to recompile all of a sudden. Clear as mud?|||woooooosh...

and huh?

wouldn't the plan just stay in cache?

Got to get that internals book...|||Here is a classic example. Get on a server that has been around and been backing up databases regularly. Then make this stored procedure:

create procedure testproc (@.start int, @.end int)
as

select *
from msdb..backupset
where backup_set_id > @.start
and backup_set_id < @.end
go

Then run this:
testproc 1, 2

Get the execution plan, then run this:
testproc 1, 40000

and check out that execution plan

It should use the index for both, even though a table scan would be better for the second query.

EDIT: Hmm. Having trouble with the reverse of the logic in this example. I can not get the stored proc to do anything but use the index. Anyway, complex queries can get hit pretty hard by this fact. Something to keep in mind.|||Originally posted by Sammy_S
When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.

thanks

It's definitely better to use sprocs. In this way you separate the different layers in your application (something, which you may have missed to consider during the development). Later if you need changes you will need just to change the sprocs, without any modifications in the VB code. Also consider that the sprocs syntax is being validated during creation and they're compiled. So in all cases it's better to use them instead of raw hard-coded statements.

Martin Markov

INSERT...EXEC prevents parallelism?

Hello all. I have a, shall we say, "legacy" stored proc whose result
set I must manipulate in another stored proc. The legacy stored proc
is extremely complex, and the final select for the result set is about
2 pages of UNION statements. When this legacy SP runs in a standalone
fashion, it executes in less than 20 seconds, with the query plan
showing a high degree of parallelism.
The driver calls the legacy proc using INSERT ...EXEC, placing the data
into a pre-defined temp table and doing some simple manipulations to
it. Not complex. But it takes several minutes to run, and the query
plan shows basically the same execution plan for the legacy SP, but
with no parallelism.
What can be done to make the driver perform without the
single-processor bottleneck? I'm happy to provide the SP code, query
plans, etc if desired, but thought there might be a "Doh!" answer. :-)
TIA
CCinnabar (heynele@.cotse.net) writes:
> Hello all. I have a, shall we say, "legacy" stored proc whose result
> set I must manipulate in another stored proc. The legacy stored proc
> is extremely complex, and the final select for the result set is about
> 2 pages of UNION statements. When this legacy SP runs in a standalone
> fashion, it executes in less than 20 seconds, with the query plan
> showing a high degree of parallelism.
> The driver calls the legacy proc using INSERT ...EXEC, placing the data
> into a pre-defined temp table and doing some simple manipulations to
> it. Not complex. But it takes several minutes to run, and the query
> plan shows basically the same execution plan for the legacy SP, but
> with no parallelism.
> What can be done to make the driver perform without the
> single-processor bottleneck? I'm happy to provide the SP code, query
> plans, etc if desired, but thought there might be a "Doh!" answer. :-)
Hm, I trying to recall that whether I have heard of this issue before,
but I am not sure. In any case, when you are insertning data into a table
that can affect the query plan and the choice of parallelism.
I have an article on web site, that discusses INSERT-EXEC and a number
of alternatives to it. Maybe you should try any of the alternatives?
http://www.sommarskog.se/share_data.html.
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|||INSERT...EXEC causes the stored procedure to run within the context of a
transaction. It may be that parallelism isn't the problem, but rather
running it in a transaction.
Try executing the procedure from within a transaction, i.e.,
BEGIN TRAN
EXEC procName...
COMMIT
and see if things slow down. If they do, then don't use INSERT...EXEC,
instead rewrite the procedure or a copy of the procedure so that the insert
happens within the proc. If you create the temp table before executing the
procedure, it can be populated by the proc and will exist after the proc
returns.
"Cinnabar" <heynele@.cotse.net> wrote in message
news:1136847107.546724.48670@.o13g2000cwo.googlegroups.com...
> Hello all. I have a, shall we say, "legacy" stored proc whose result
> set I must manipulate in another stored proc. The legacy stored proc
> is extremely complex, and the final select for the result set is about
> 2 pages of UNION statements. When this legacy SP runs in a standalone
> fashion, it executes in less than 20 seconds, with the query plan
> showing a high degree of parallelism.
> The driver calls the legacy proc using INSERT ...EXEC, placing the data
> into a pre-defined temp table and doing some simple manipulations to
> it. Not complex. But it takes several minutes to run, and the query
> plan shows basically the same execution plan for the legacy SP, but
> with no parallelism.
> What can be done to make the driver perform without the
> single-processor bottleneck? I'm happy to provide the SP code, query
> plans, etc if desired, but thought there might be a "Doh!" answer. :-)
> TIA
> C
>|||Thanks Erland. Unfortunately, none of the alternatives is really a
possibility.
I knew that insert...exec would cause the called SP to execute within a
transaction (as Brian points out) but that in and of itself shouldn't
prevent parallelism, should it? I will plan to try executing the
legacy SP within a transaction tomorrow, to get proof that is the
issue.
This is a real drag, as my only recourse is to copy the guts of the
legacy SP into mine...which obviates the whole idea of reuse. This
legacy SP contains complex data business rules that are essentially
undocumented (and uncommented in the SP), and are maintained by another
group of developers.
thanks for your and Brian's replies.
C
Erland Sommarskog wrote:
> Cinnabar (heynele@.cotse.net) writes:
> Hm, I trying to recall that whether I have heard of this issue before,
> but I am not sure. In any case, when you are insertning data into a table
> that can affect the query plan and the choice of parallelism.
> I have an article on web site, that discusses INSERT-EXEC and a number
> of alternatives to it. Maybe you should try any of the alternatives?
> http://www.sommarskog.se/share_data.html.
>
> --
> 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|||Brian, I did the test you suggested and that's exactly it.
Do you know why the explicit transaction could prevent parallelism?
And a related question: I might also have to use this legacy SP as the
source on a data pump DTS task that inserts the result set into a
staging table on another server. Am I likely to run into the same
issue, i.e. poor performance due to lack of parallelism, which in turn
is due to an implicit or explicit transaction?
Thanks!
C
Brian Selzer wrote:
> INSERT...EXEC causes the stored procedure to run within the context of a
> transaction. It may be that parallelism isn't the problem, but rather
> running it in a transaction.
> Try executing the procedure from within a transaction, i.e.,
> BEGIN TRAN
> EXEC procName...
> COMMIT
> and see if things slow down. If they do, then don't use INSERT...EXEC,
> instead rewrite the procedure or a copy of the procedure so that the inser
t
> happens within the proc. If you create the temp table before executing th
e
> procedure, it can be populated by the proc and will exist after the proc
> returns.
> "Cinnabar" <heynele@.cotse.net> wrote in message
> news:1136847107.546724.48670@.o13g2000cwo.googlegroups.com...|||Cinnabar (heynele@.cotse.net) writes:
> Brian, I did the test you suggested and that's exactly it.
> Do you know why the explicit transaction could prevent parallelism?
Very interesting.
But there is something which smells fishy to me. I find it bit difficult
to believe that a transaction scope in general would prevent parallelism.
I mean, in such case I should have heard of it before. Or at least I think
so.
It could of course be something particular particular with this procedure,
although I have no idea of what that could be.
Out of the blue, you are not running with a transactiun isolation
level of REPEATABLE READ or SERIALIZABLE?
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|||I didn't think that the explicit transaction would prevent parallelism. I
suggested on the other hand that the problem may not be parallelism at all,
but the increased lock duration and overhead caused by executing the entire
procedure within the context of a transaction.
It sounds like the problem is the procedure itself. Consider that it may
cost less in time, resources, and performance to fix it. If this is a
procedure that must remain in production, then that should carry more weight
in your decision process. I'm usually hold the opinion: if it ain't broke,
don't fix it! But if the performance is unmanagable, then in this case it's
clearly broken.
"Cinnabar" <heynele@.cotse.net> wrote in message
news:1136924932.520579.29290@.f14g2000cwb.googlegroups.com...
> Brian, I did the test you suggested and that's exactly it.
> Do you know why the explicit transaction could prevent parallelism?
> And a related question: I might also have to use this legacy SP as the
> source on a data pump DTS task that inserts the result set into a
> staging table on another server. Am I likely to run into the same
> issue, i.e. poor performance due to lack of parallelism, which in turn
> is due to an implicit or explicit transaction?
> Thanks!
> C
> Brian Selzer wrote:
>|||Brian, I wish this SP were in my purview, I would certainly rewrite it.
But in a sense it isn't "broken", since it is working fine in its
standalone execution. So those who maintain it will not be eager to
rewrite it to help the performance of my driver proc.
(Erland, neither the driver nor the legacy SP is running with the
isolation levels you asked about.)
Some background I did not mention earlier: my driver SP that filters
the legacy SP's result set is part of a DTS package that moves data
between two servers. (However, this problem is repeatable outside the
context of the DTS package.) One of the alternatives I'm considering
is to modify the package to dump the legacy SP's result set directly
into a staging table on the destination server. Then I'd do the
filtering and transformations at the destination. But I'm not sure
that this design won't result in the same problem; seems like it would
not, since the destination table is on a different server and I can
control the transaction context. I'm banging out a prototype to
determine if this will work.
again, thanks for all your comments!
C

insert statement script/stored proc.

I have a table that has 3 columns that I need to split up the data in
each column and insert into another table as rows.
As well for each record inserted the Status is set to "O".
I am thinking I stored procedure would be the way to go? But trying to
determine the best way to go about this.
Below is an example of the table structures.
I have 2 options, delete all the data from table2 and do a bunch of
inserts, or perform updates?
I am assuming the first would be better but having trouble with the sql
statement.
Any ideas?
table1
ID Name1 Name2 CITY
500 John Jeff TO
501 Sheila Rose TO
502 Barb Jen TO
503 Tom Jerry TO
504 Alan Scott TO
505 Steve John TO
506 Pat Cathy TO
table2
ID Name Status
500 John O
500 Jeff O
501 Sheila O
501 Rose O
502 Barb O
502 Jen O
503 Tom O
503 Jerry O
504 Alan O
504 Scott O
505 Steve O
505 John O
506 Pat O
506 Cathy O
One way is to use an UNION like:
SELECT id, name1 AS "Name" FROM tbl
UNION
SELECT id, name2 AS "Name" FROM tbl
Another option is to use a CASE expression like:
SELECT id, CASE seq WHEN 1 THEN name1 ELSE name 2 END
FROM tbl, ( SELECT 1 UNION SELECT 2 ) D ( seq )
Make sure you have a composite key on ( id, name ) to prevent potential
duplication of names.
Anith
|||<pisquem@.hotmail.com> wrote in message
news:1160507284.442954.181820@.i3g2000cwc.googlegro ups.com...
>I have a table that has 3 columns that I need to split up the data in
> each column and insert into another table as rows.
> As well for each record inserted the Status is set to "O".
> I am thinking I stored procedure would be the way to go? But trying to
> determine the best way to go about this.
> Below is an example of the table structures.
> I have 2 options, delete all the data from table2 and do a bunch of
> inserts, or perform updates?
> I am assuming the first would be better but having trouble with the sql
> statement.
> Any ideas?
>
> table1
> ID Name1 Name2 CITY
> 500 John Jeff TO
> 501 Sheila Rose TO
> 502 Barb Jen TO
> 503 Tom Jerry TO
> 504 Alan Scott TO
> 505 Steve John TO
> 506 Pat Cathy TO
>
> table2
> ID Name Status
> 500 John O
> 500 Jeff O
> 501 Sheila O
> 501 Rose O
> 502 Barb O
> 502 Jen O
> 503 Tom O
> 503 Jerry O
> 504 Alan O
> 504 Scott O
> 505 Steve O
> 505 John O
> 506 Pat O
> 506 Cathy O
>
I might not be following, but this should do what you wish.
INSERT table2 (ID, Name, Status)
SELECT ID, Name1, 'O'
FROM Table1
UNION ALL
SELECT ID, Name2, 'O'
FROM Table1
Rick Sawtell
|||Thanks for the posts.
Which is the most effective and efficient way?
Rick Sawtell wrote:
> <pisquem@.hotmail.com> wrote in message
> news:1160507284.442954.181820@.i3g2000cwc.googlegro ups.com...
> I might not be following, but this should do what you wish.
> INSERT table2 (ID, Name, Status)
> SELECT ID, Name1, 'O'
> FROM Table1
> UNION ALL
> SELECT ID, Name2, 'O'
> FROM Table1
>
> Rick Sawtell
|||Darn, I forgot to include something...
In the second table I need to have another column inserted with the
value of either 001 or 002 depending on if its Name1 being inserted or
Name2. So the table should be outputed as follows.
ID Name Code Status
500 John 001 O
500 Jeff 002 O
501 Sheila 001 O
501 Rose 002 O
Any ideas?
pisq...@.hotmail.com wrote:[vbcol=seagreen]
> Thanks for the posts.
> Which is the most effective and efficient way?
>
> Rick Sawtell wrote:

insert statement script/stored proc.

I have a table that has 3 columns that I need to split up the data in
each column and insert into another table as rows.
As well for each record inserted the Status is set to "O".
I am thinking I stored procedure would be the way to go? But trying to
determine the best way to go about this.
Below is an example of the table structures.
I have 2 options, delete all the data from table2 and do a bunch of
inserts, or perform updates?
I am assuming the first would be better but having trouble with the sql
statement.
Any ideas?
table1
ID Name1 Name2 CITY
500 John Jeff TO
501 Sheila Rose TO
502 Barb Jen TO
503 Tom Jerry TO
504 Alan Scott TO
505 Steve John TO
506 Pat Cathy TO
table2
ID Name Status
500 John O
500 Jeff O
501 Sheila O
501 Rose O
502 Barb O
502 Jen O
503 Tom O
503 Jerry O
504 Alan O
504 Scott O
505 Steve O
505 John O
506 Pat O
506 Cathy OOne way is to use an UNION like:
SELECT id, name1 AS "Name" FROM tbl
UNION
SELECT id, name2 AS "Name" FROM tbl
Another option is to use a CASE expression like:
SELECT id, CASE seq WHEN 1 THEN name1 ELSE name 2 END
FROM tbl, ( SELECT 1 UNION SELECT 2 ) D ( seq )
Make sure you have a composite key on ( id, name ) to prevent potential
duplication of names.
--
Anith|||<pisquem@.hotmail.com> wrote in message
news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
>I have a table that has 3 columns that I need to split up the data in
> each column and insert into another table as rows.
> As well for each record inserted the Status is set to "O".
> I am thinking I stored procedure would be the way to go? But trying to
> determine the best way to go about this.
> Below is an example of the table structures.
> I have 2 options, delete all the data from table2 and do a bunch of
> inserts, or perform updates?
> I am assuming the first would be better but having trouble with the sql
> statement.
> Any ideas?
>
> table1
> ID Name1 Name2 CITY
> 500 John Jeff TO
> 501 Sheila Rose TO
> 502 Barb Jen TO
> 503 Tom Jerry TO
> 504 Alan Scott TO
> 505 Steve John TO
> 506 Pat Cathy TO
>
> table2
> ID Name Status
> 500 John O
> 500 Jeff O
> 501 Sheila O
> 501 Rose O
> 502 Barb O
> 502 Jen O
> 503 Tom O
> 503 Jerry O
> 504 Alan O
> 504 Scott O
> 505 Steve O
> 505 John O
> 506 Pat O
> 506 Cathy O
>
I might not be following, but this should do what you wish.
INSERT table2 (ID, Name, Status)
SELECT ID, Name1, 'O'
FROM Table1
UNION ALL
SELECT ID, Name2, 'O'
FROM Table1
Rick Sawtell|||Thanks for the posts.
Which is the most effective and efficient way?
Rick Sawtell wrote:
> <pisquem@.hotmail.com> wrote in message
> news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
> >I have a table that has 3 columns that I need to split up the data in
> > each column and insert into another table as rows.
> > As well for each record inserted the Status is set to "O".
> > I am thinking I stored procedure would be the way to go? But trying to
> > determine the best way to go about this.
> > Below is an example of the table structures.
> > I have 2 options, delete all the data from table2 and do a bunch of
> > inserts, or perform updates?
> > I am assuming the first would be better but having trouble with the sql
> > statement.
> > Any ideas?
> >
> >
> > table1
> >
> > ID Name1 Name2 CITY
> > 500 John Jeff TO
> > 501 Sheila Rose TO
> > 502 Barb Jen TO
> > 503 Tom Jerry TO
> > 504 Alan Scott TO
> > 505 Steve John TO
> > 506 Pat Cathy TO
> >
> >
> >
> > table2
> >
> > ID Name Status
> > 500 John O
> > 500 Jeff O
> > 501 Sheila O
> > 501 Rose O
> > 502 Barb O
> > 502 Jen O
> > 503 Tom O
> > 503 Jerry O
> > 504 Alan O
> > 504 Scott O
> > 505 Steve O
> > 505 John O
> > 506 Pat O
> > 506 Cathy O
> >
> I might not be following, but this should do what you wish.
> INSERT table2 (ID, Name, Status)
> SELECT ID, Name1, 'O'
> FROM Table1
> UNION ALL
> SELECT ID, Name2, 'O'
> FROM Table1
>
> Rick Sawtell|||Darn, I forgot to include something...
In the second table I need to have another column inserted with the
value of either 001 or 002 depending on if its Name1 being inserted or
Name2. So the table should be outputed as follows.
ID Name Code Status
500 John 001 O
500 Jeff 002 O
501 Sheila 001 O
501 Rose 002 O
Any ideas?
pisq...@.hotmail.com wrote:
> Thanks for the posts.
> Which is the most effective and efficient way?
>
> Rick Sawtell wrote:
> > <pisquem@.hotmail.com> wrote in message
> > news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
> > >I have a table that has 3 columns that I need to split up the data in
> > > each column and insert into another table as rows.
> > > As well for each record inserted the Status is set to "O".
> > > I am thinking I stored procedure would be the way to go? But trying to
> > > determine the best way to go about this.
> > > Below is an example of the table structures.
> > > I have 2 options, delete all the data from table2 and do a bunch of
> > > inserts, or perform updates?
> > > I am assuming the first would be better but having trouble with the sql
> > > statement.
> > > Any ideas?
> > >
> > >
> > > table1
> > >
> > > ID Name1 Name2 CITY
> > > 500 John Jeff TO
> > > 501 Sheila Rose TO
> > > 502 Barb Jen TO
> > > 503 Tom Jerry TO
> > > 504 Alan Scott TO
> > > 505 Steve John TO
> > > 506 Pat Cathy TO
> > >
> > >
> > >
> > > table2
> > >
> > > ID Name Status
> > > 500 John O
> > > 500 Jeff O
> > > 501 Sheila O
> > > 501 Rose O
> > > 502 Barb O
> > > 502 Jen O
> > > 503 Tom O
> > > 503 Jerry O
> > > 504 Alan O
> > > 504 Scott O
> > > 505 Steve O
> > > 505 John O
> > > 506 Pat O
> > > 506 Cathy O
> > >
> >
> > I might not be following, but this should do what you wish.
> >
> > INSERT table2 (ID, Name, Status)
> > SELECT ID, Name1, 'O'
> > FROM Table1
> > UNION ALL
> > SELECT ID, Name2, 'O'
> > FROM Table1
> >
> >
> > Rick Sawtell

insert statement script/stored proc.

I have a table that has 3 columns that I need to split up the data in
each column and insert into another table as rows.
As well for each record inserted the Status is set to "O".
I am thinking I stored procedure would be the way to go? But trying to
determine the best way to go about this.
Below is an example of the table structures.
I have 2 options, delete all the data from table2 and do a bunch of
inserts, or perform updates?
I am assuming the first would be better but having trouble with the sql
statement.
Any ideas?
table1
ID Name1 Name2 CITY
500 John Jeff TO
501 Sheila Rose TO
502 Barb Jen TO
503 Tom Jerry TO
504 Alan Scott TO
505 Steve John TO
506 Pat Cathy TO
table2
ID Name Status
500 John O
500 Jeff O
501 Sheila O
501 Rose O
502 Barb O
502 Jen O
503 Tom O
503 Jerry O
504 Alan O
504 Scott O
505 Steve O
505 John O
506 Pat O
506 Cathy OOne way is to use an UNION like:
SELECT id, name1 AS "Name" FROM tbl
UNION
SELECT id, name2 AS "Name" FROM tbl
Another option is to use a CASE expression like:
SELECT id, CASE seq WHEN 1 THEN name1 ELSE name 2 END
FROM tbl, ( SELECT 1 UNION SELECT 2 ) D ( seq )
Make sure you have a composite key on ( id, name ) to prevent potential
duplication of names.
Anith|||<pisquem@.hotmail.com> wrote in message
news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
>I have a table that has 3 columns that I need to split up the data in
> each column and insert into another table as rows.
> As well for each record inserted the Status is set to "O".
> I am thinking I stored procedure would be the way to go? But trying to
> determine the best way to go about this.
> Below is an example of the table structures.
> I have 2 options, delete all the data from table2 and do a bunch of
> inserts, or perform updates?
> I am assuming the first would be better but having trouble with the sql
> statement.
> Any ideas?
>
> table1
> ID Name1 Name2 CITY
> 500 John Jeff TO
> 501 Sheila Rose TO
> 502 Barb Jen TO
> 503 Tom Jerry TO
> 504 Alan Scott TO
> 505 Steve John TO
> 506 Pat Cathy TO
>
> table2
> ID Name Status
> 500 John O
> 500 Jeff O
> 501 Sheila O
> 501 Rose O
> 502 Barb O
> 502 Jen O
> 503 Tom O
> 503 Jerry O
> 504 Alan O
> 504 Scott O
> 505 Steve O
> 505 John O
> 506 Pat O
> 506 Cathy O
>
I might not be following, but this should do what you wish.
INSERT table2 (ID, Name, Status)
SELECT ID, Name1, 'O'
FROM Table1
UNION ALL
SELECT ID, Name2, 'O'
FROM Table1
Rick Sawtell|||Thanks for the posts.
Which is the most effective and efficient way?
Rick Sawtell wrote:
> <pisquem@.hotmail.com> wrote in message
> news:1160507284.442954.181820@.i3g2000cwc.googlegroups.com...
> I might not be following, but this should do what you wish.
> INSERT table2 (ID, Name, Status)
> SELECT ID, Name1, 'O'
> FROM Table1
> UNION ALL
> SELECT ID, Name2, 'O'
> FROM Table1
>
> Rick Sawtell|||Darn, I forgot to include something...
In the second table I need to have another column inserted with the
value of either 001 or 002 depending on if its Name1 being inserted or
Name2. So the table should be outputed as follows.
ID Name Code Status
500 John 001 O
500 Jeff 002 O
501 Sheila 001 O
501 Rose 002 O
Any ideas?
pisq...@.hotmail.com wrote:[vbcol=seagreen]
> Thanks for the posts.
> Which is the most effective and efficient way?
>
> Rick Sawtell wrote:

Insert Statement Fails

All,

Trying to format some data before I drop it into a grid. I have this in a stored proc but it fails

CREATE TABLE dbo.tmpSummary (
AE NVARCHAR(50)
, PRODUCT_LINE NVARCHAR(20)
, ANNUAL_REV NUMERIC (9)
, [GRWTH/ACQ] NUMERIC (9)
, RETENTION NUMERIC (9)
, CATEGORY NVARCHAR(20)

)

INSERT INTO dbo.tmpSummary (
[AE]
, [PRODUCT_LINE]
, [ANNUAL_REV]
, [GRWTH/ACQ]
, RETENTION
, CATEGORY
)
SELECT
A.AE
, A.PRODUCT_LINE
, A.ANNUAL_REV
, A.[GRWTH/ACQ]
, A.RETENTION
, B.PRODUCT_CATEGORY AS CATEGORY

FROM
tmpSummary A RIGHT OUTER JOIN PRODUCT B
On A.PRODUCT_LINE=B.PRODUCT_CATEGORY

I keep getting an error "Invalid Column name CATEGORY" Anyone know why? Thanks

Never mind. Maybe if I learn to read i could see that i am trying to insert data BACK into the same table. It should have been something else.

|||

Only reason why you would get that error is that you don't have a column in your table named CATEGORY. Check your table defenition again to make sure you have the spelling of the column name correct. I know that gets me alot

Insert records in multiple tables via store proc

Any help will be appreacited
I need to insert records into multiple tables via store proc. I wrote a query statement that does that, but I need to carry one value to the next piece of the script, which is easy via query analizer, but I do not know how to pass that value to the next step in the store proc. Please see the query I am using to give me some light. The case sample is 9731285 and needs to be carry out to each step in the store proc. Thank you!
DECLARE @.Casenumber as char(20
SET @.CASENUMBER = '9731285
INSERT INTO tblCaseDat
(CaseNumber, DisplayCaseNumber
VALUES (@.CASENUMBER, (left(@.casenumber, 2))+'-'+rtrim(Right(@.casenumber,18))
G
declare @.casenumber char(20
select @.casenumber = '9731285
INSERT INTO tblname (longname
values (@.casenumber+' '+ 'Debtor1'
g
declare @.casenumber char (20
select @.casenumber = '9731285
INSERT INTO tblCasename (caseid, NameID, NameTypeID
(select caseid, (Select NameI
from tblnam
where longname =(@.casenumber+' '+ 'Debtor1')), '5
from tblcasedat
where casenumber = @.casenumber
G
declare @.casenumber char(20
select @.casenumber = '9731285
INSERT INTO tblname (longname
values (@.casenumber+' '+ 'Debtor2'
g
declare @.casenumber char (20
select @.casenumber = '9731285
INSERT INTO tblCasename (caseid, NameID, NameTypeID
(select caseid, (Select NameI
from tblnam
where longname =(@.casenumber+' '+ 'Debtor2')), '6
from tblcasedat
where casenumber = @.casenumber
Gthe batch separator (GO) resets any variable declarations.
hence, if you remove the 'GO'
remove the additional DECLARE / SET CaseNumber,
you can execution the entire set of statments as one
batch, which can be put into a stored proc,
also, an explicit BEGIN TRAN , COMMIT TRAN around the
entire set of inserts statements is probably warranted
>--Original Message--
>Any help will be appreacited.
>I need to insert records into multiple tables via store
proc. I wrote a query statement that does that, but I need
to carry one value to the next piece of the script, which
is easy via query analizer, but I do not know how to pass
that value to the next step in the store proc. Please see
the query I am using to give me some light. The case
sample is 9731285 and needs to be carry out to each step
in the store proc. Thank you!!
>DECLARE @.Casenumber as char(20)
>SET @.CASENUMBER = '9731285'
>INSERT INTO tblCaseData
> (CaseNumber, DisplayCaseNumber)
>VALUES (@.CASENUMBER, (left(@.casenumber, 2))+'-'+rtrim
(Right(@.casenumber,18)))
>GO
>declare @.casenumber char(20)
>select @.casenumber = '9731285'
>INSERT INTO tblname (longname)
> values (@.casenumber+' '+ 'Debtor1')
>go
>declare @.casenumber char (20)
>select @.casenumber = '9731285'
>INSERT INTO tblCasename (caseid, NameID, NameTypeID)
> (select caseid, (Select NameID
> from tblname
> where longname =(@.casenumber+' '+ 'Debtor1')), '5'
> from tblcasedata
> where casenumber = @.casenumber)
>GO
>declare @.casenumber char(20)
>select @.casenumber = '9731285'
>INSERT INTO tblname (longname)
> values (@.casenumber+' '+ 'Debtor2')
>go
>declare @.casenumber char (20)
>select @.casenumber = '9731285'
>INSERT INTO tblCasename (caseid, NameID, NameTypeID)
> (select caseid, (Select NameID
> from tblname
> where longname =(@.casenumber+' '+ 'Debtor2')), '6'
> from tblcasedata
> where casenumber = @.casenumber)
>GO
>.
>

Insert Question.

Hello. I am extending an existing proc and encountering a strange issue. I
hope someone can shed some light on this. Here's my problem :
insert into : table with decimal(14,2) columns ( where you see math )
sql statement :
TRUNCATE TABLE dbo.tbl_met_wos
INSERT INTO dbo.tbl_met_wos
SELECT
b.TopDivision as Division,
b.TopDivisionName as DivisionName,
b.Div as Department,
b.DivName as DepartmentName,
b.Dept as Class,
b.DeptName as ClassName,
a.fponhandTW,
a.fponhandMTD,
a.fponhandTQ,
a.fponhandTS,
a.fponhandTY,
c.mdonhandTW,
c.mdonhandMTD,
c.mdonhandTQ,
c.mdonhandTS, --STD
c.mdonhandTY,
d.FPSalesTW,
d.FPSalesMTD,
d.FPSalesTQ,
d.FPSalesSTD,
d.FPSalesTY,
e.MDSalesTW,
e.MDSalesMTD,
e.MDSalesTQ,
e.MDSalesSTD,
e.MDSalesTY,
--RH Added.
--cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in (0,null)
then 1 else d.FPSalesTW end) AS decimal(14,2)),
'1.25',
Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW in(0,null)
then 1 else e.MDSalesTW end) AS varchar),
(a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null) then 1
else d.FPSalesMTD end,
(c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null) then 1
else e.MDSalesMTD end,
Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ in(0,null)
then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
(c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then 1
else e.MDSalesTQ end,
(a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null) then 1
else d.FPSalesSTD end,
--(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null) then 1
else e.MDSalesSTD end,
'1.37',
(a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then 1
else d.FPSalesTY end,
(c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then 1
else e.MDSalesTY end
FROM #HIERARCHY b
LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND b.Department =
a.Department
LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND b.Department =
c.Department
LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND b.Department =
d.Department
LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND b.Department =
e.Department
ORDER BY
b.Div,
b.Dept
as you can see, this is not the hottest proc in the world, but support sucks
sometimes :) anyhow, when this insert statement runs, It will round off to
whole numbers where you see math in the fields. Where you see '1.25' and
'1.37' they insert fine as a decimal(14,2) when you select from the table. I
have tryed converting and casting the entire field like :
Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null)
then 1 else e.MDSalesTQ end AS Decimal(14,2)
but it still rounds off to a whole number. if it were trying to insert
100.65 I get 101.00 when I select from the table.
Any IDEAS? I'm really with this one.
Robert H"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:%23KHG44LrFHA.2588@.tk2msftngp13.phx.gbl...
> Hello. I am extending an existing proc and encountering a strange issue. I
> hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
>
...

> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
>
The sum or product of two decimals requires more precision than the
individual operands. As the required precision approaches 36, scale is
trimmed. So after a few operations your results quickly become converted to
decimal(36,0) unless you are diligent about converting intermediate results
to remove unneeded precision.
If you are sticking with decimal(14,2), just convert each intermediate
result to decimal(16,4), or something, to preserve your scale.
See:
http://msdn.microsoft.com/library/d...br />
8rc5.asp
David|||Robert H wrote:
> Hello. I am extending an existing proc and encountering a strange
> issue. I hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
> TRUNCATE TABLE dbo.tbl_met_wos
> INSERT INTO dbo.tbl_met_wos
> SELECT
> b.TopDivision as Division,
> b.TopDivisionName as DivisionName,
> b.Div as Department,
> b.DivName as DepartmentName,
> b.Dept as Class,
> b.DeptName as ClassName,
> a.fponhandTW,
> a.fponhandMTD,
> a.fponhandTQ,
> a.fponhandTS,
> a.fponhandTY,
> c.mdonhandTW,
> c.mdonhandMTD,
> c.mdonhandTQ,
> c.mdonhandTS, --STD
> c.mdonhandTY,
> d.FPSalesTW,
> d.FPSalesMTD,
> d.FPSalesTQ,
> d.FPSalesSTD,
> d.FPSalesTY,
> e.MDSalesTW,
> e.MDSalesMTD,
> e.MDSalesTQ,
> e.MDSalesSTD,
> e.MDSalesTY,
> --RH Added.
> --cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in
> (0,null) then 1 else d.FPSalesTW end) AS decimal(14,2)),
> '1.25',
> Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW
> in(0,null) then 1 else e.MDSalesTW end) AS varchar),
> (a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null)
> then 1 else d.FPSalesMTD end,
> (c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null)
> then 1 else e.MDSalesMTD end,
> Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
> convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ
> in(0,null) then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
> (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then
> 1 else e.MDSalesTQ end,
> (a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null)
> then 1 else d.FPSalesSTD end,
> --(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null)
> then 1 else e.MDSalesSTD end,
> '1.37',
> (a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then
> 1 else d.FPSalesTY end,
> (c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then
> 1 else e.MDSalesTY end
>
> FROM #HIERARCHY b
> LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND
> b.Department = a.Department
> LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND
> b.Department = c.Department
> LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND
> b.Department = d.Department
> LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND
> b.Department = e.Department
> ORDER BY
> b.Div,
> b.Dept
>
> as you can see, this is not the hottest proc in the world, but
> support sucks sometimes :) anyhow, when this insert statement runs,
> It will round off to whole numbers where you see math in the fields.
> Where you see '1.25' and '1.37' they insert fine as a decimal(14,2)
> when you select from the table. I have tryed converting and casting
> the entire field like :
> Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
> e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
> cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ
> in(0,null) then 1 else e.MDSalesTQ end AS Decimal(14,2)
> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
> Any IDEAS? I'm really with this one.
> Robert H
Don't use integer type values in the calculation unless you want an
integer result. Make sure all integer values are first converted/cast to
decimal (14, 2) first (you can do this inline). If it's a hard-coded
value, then use "0.00" instead of "0".
Also, the TRUNCATE TABLE operation can only be run by system
administrators, table owner, db_owner, and db_ddladmin. So you won't be
able to grant execute on the procedure to users without those rights.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks Guys ! I now know a little more about sql precision! The solution
to the problem based on what you guys have said is to convert
each field to a real data type. This solved all problems. that truncate
table was allready in the proc, and was in production so Someone gave it the
proper access.
Thanks!
Robert H
"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:%23KHG44LrFHA.2588@.tk2msftngp13.phx.gbl...
> Hello. I am extending an existing proc and encountering a strange issue. I
> hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
> TRUNCATE TABLE dbo.tbl_met_wos
> INSERT INTO dbo.tbl_met_wos
> SELECT
> b.TopDivision as Division,
> b.TopDivisionName as DivisionName,
> b.Div as Department,
> b.DivName as DepartmentName,
> b.Dept as Class,
> b.DeptName as ClassName,
> a.fponhandTW,
> a.fponhandMTD,
> a.fponhandTQ,
> a.fponhandTS,
> a.fponhandTY,
> c.mdonhandTW,
> c.mdonhandMTD,
> c.mdonhandTQ,
> c.mdonhandTS, --STD
> c.mdonhandTY,
> d.FPSalesTW,
> d.FPSalesMTD,
> d.FPSalesTQ,
> d.FPSalesSTD,
> d.FPSalesTY,
> e.MDSalesTW,
> e.MDSalesMTD,
> e.MDSalesTQ,
> e.MDSalesSTD,
> e.MDSalesTY,
> --RH Added.
> --cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in
> (0,null) then 1 else d.FPSalesTW end) AS decimal(14,2)),
> '1.25',
> Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW in(0,null)
> then 1 else e.MDSalesTW end) AS varchar),
> (a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null) then 1
> else d.FPSalesMTD end,
> (c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null) then 1
> else e.MDSalesMTD end,
> Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
> convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ in(0,null)
> then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
> (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then 1
> else e.MDSalesTQ end,
> (a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null) then 1
> else d.FPSalesSTD end,
> --(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null) then
> 1 else e.MDSalesSTD end,
> '1.37',
> (a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then 1
> else d.FPSalesTY end,
> (c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then 1
> else e.MDSalesTY end
>
> FROM #HIERARCHY b
> LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND b.Department =
> a.Department
> LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND b.Department =
> c.Department
> LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND b.Department
> = d.Department
> LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND b.Department =
> e.Department
> ORDER BY
> b.Div,
> b.Dept
>
> as you can see, this is not the hottest proc in the world, but support
> sucks sometimes :) anyhow, when this insert statement runs, It will round
> off to whole numbers where you see math in the fields. Where you see
> '1.25' and '1.37' they insert fine as a decimal(14,2) when you select from
> the table. I have tryed converting and casting the entire field like :
> Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
> e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
> cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null)
> then 1 else e.MDSalesTQ end AS Decimal(14,2)
> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
> Any IDEAS? I'm really with this one.
> Robert H
>
>|||Robert H wrote:
> Thanks Guys ! I now know a little more about sql precision! The
> solution to the problem based on what you guys have said is to convert
> each field to a real data type. This solved all problems. that
> truncate table was allready in the proc, and was in production so
> Someone gave it the proper access.
No. Not a REAL/FLOAT data type. Use DECIMAL/NUMERIC instead.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eySIpGYrFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Robert H wrote:
> No. Not a REAL/FLOAT data type. Use DECIMAL/NUMERIC instead.
>
Actually, using floating point in a calculation is a very reasonable thing
to to. It frees you from having to manually adjust the precision and scale
after each operation. At the end you can round off and convert back to a
decimal type, knowing that th accumulation of error in the calculation was
minimized by the floating point arithmetic.
David

Insert Question

I have found a proc that reads as follows:

CREATE PROCEDURE InsertArea
@.AreaDescr as varchar(50)
as
insert into tblAreas
select isnull(max(AreaID)+1,1), @.AreaDescr
from tblAreas
GO

My question: tblAreas is a simple lookup table but why would I use this method of managing the AreaID instead of just inserting the record using an Identity column and being done with it? So I am confused on whether there is a hidden secret here or if the person wasn't aware of the Identity field = not knowing what they were doing? All of the tables in this database are like this as well, not just an isolated few.

Thanks for any input.
GregJust use an identity column, if you can. This kind of logic is leftover from either an old database engine, or an old database designer.|||Generally speaking, I agree with blindman. The only time I would use this type of process is when the "business logic" dictates that there can not be missing numbers, which as we all know can happen with an identity column.|||Any logic that relies upon the relative values of a surrogate key is not business logic. It's just bad logic.|||I respect your opinion. How would you populate a field that needed to increment by one, and could not skip numbers? Say, a check number field or something like that.|||How would you populate a field that needed to increment by one, and could not skip numbers? Say, a check number field or something like that.if that is indeed a requirement, then i would pre-populate the numbers manually

in other words, if you issue a cheque book with cheques numbered 001 through 150, then insert those numbers into (some) table and indicate that they have been issued but not disbursed|||if that is indeed a requirement, then i would pre-populate the numbers manually

in other words, if you issue a cheque book with cheques numbered 001 through 150, then insert those numbers into (some) table and indicate that they have been issued but not disbursed

Huh? I am not a big fan of identity either but can you explain the rationale for this for us lesser mortals. Pre-populate? How do you make this scalable? A really big table with empty records waiting to be filled in? Do you add so many empty records a day? What if you go over your set amount. It's early, am I misreading this?|||Huh? I am not a big fan of identity either but can you explain the rationale for this for us lesser mortals.please, sean, that's inflammatory language, let's not get into another flamefest

buddy asked a question, i told him one way how, and you're questioning why

it's not a "why" question, just accept the requirement to generate a sequence of numbers with absolutely no gaps, and let's see your suggestion for this|||I did not think it was inflamatory at all. I was asking a question.

~"The IT world can be harsh" - Rudy from the other day.

Any time you poke fun at me it can be justified. When I ask a legit question, it's a flame war.

And you did not answer me.

First I would ask this guy why it is a requirement that there are no missing numbers. I suspect deep down there is some suspect logic here. Then I would ask him how this works out if one of these rows are deleted. Does he repopulate this column and re-establish his relationships each time? The whole thing is a bad idea.|||I think it was more of an inphlegmatory statement.

I like Rudy's idea of prepopulating check numbers when they are issued, and then complete them when they are disbursed. Remember, even check numbers these days are not always sequential. Sometimes I write checks from two different sets alternately, and then there are counter-checks and electronic checks too.
But I also suspect, like Sean, that if there is a need for a gapless incrementing value then there is a flaw in the application concept.|||Get the hose...

Damn.

us mere mortals...Sean, you still smarting from the pad left with zeroes thread?

The answer to this thread is "Wake up and rework your model"

Plus the sproc he "found" is a poor construction to replace identity. It show lack of forsight in a multiuser environment, and can get hosed easily, and rather quickly.

And the check analogy, what if I cancel a check..bad analogy too, but is a "missing" number in a sequence, at least from the banks perspective.

And if you want a non-identity, Identity column

Try this

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx|||And the check analogy, what if I cancel a check..bad analogy too, but is a "missing" number in a sequence, at least from the banks perspective.no, it isn't, it's an excellent example

and no, it isn't missing at all, it's just cancelled

it's the same situation joe celko refers to in several of his examples -- what did we do in the old days of pre-printed order forms, when you spilled coffee on an order form, you couldn't use that one, but you couldn't just destroy it, you have to account for that order number in the order number sequence somehow

just saying "rework the model" does not really address the business requirement adequately

rework it how, exactly?|||just saying "rework the model" does not really address the business requirement adequatelySo when the business requirement is flawed, then we fix it with code? I usually go back to the Business Analyst and send them back to the project sponsor to fix the underlying problem... I rarely fix a bad business process by adding code to it.

-PatP|||I rarely fix a bad business process by adding code to it.geez you guys are stubborn

okay, let's suppose neil diamond has personally autographed a series of 100 gold-plated cds of "margaritaville" and these are now up for sale

your job is to record who purchased each one

you need to assign a "record number" (pun partially intentional) numbered 001 through 100 to each of these discs

you're going to go back to the sponsor and tell them this is a bad business decision? tell them you can't assign a sequential number because it might have gaps and you don't know how to do handle them?

sheesh!!!|||Ok what happens when one of the records (pun partially intended again) turns out to be non readable and the purchaser rejects the record.|||then you update the record (heh) and mark it as non-readable or other such status

the point is, you cannot delete it, you must account for that number|||What if I don't use a sequential number and I use a GUID?

I think you're mixing apples and oranges here....

And if I was worried about the sequence of things, I think I'd rather use datetime

Has the original posters head exploded yet?

And remember, yesterday was hump day, so if you missed out, it's your own fault...|||What if I don't use a sequential number and I use a GUID?

I think you're mixing apples and oranges here....no, i think you are missing the point

your client says "i want these 100 discs numbered as 001 through 100"

and you come back with "okay, let's use a GUID for that!"

i feel like asking "have you ever actually worked with clients?" but that would be rude and demeaning

the point is not what you would rather use, the point is what would you actually use to record the numbers 001 through 100

i don't see why everybody's having such a hard time with that concept|||In this situation the number of the record would be field in the record or property of my object but it would not be my primary key or record identifier.|||In this situation the number of the record would be field in the record or property of my object but it would not be my primary key or record identifier.excellent answer, and now we are getting somewhere|||no, i think you are missing the point

your client says "i want these 100 discs numbered as 001 through 100"

and you come back with "okay, let's use a GUID for that!"

i feel like asking "have you ever actually worked with clients?" but that would be rude and demeaning

the point is not what you would rather use, the point is what would you actually use to record the numbers 001 through 100

i don't see why everybody's having such a hard time with that concept

Well you'd have to try harder to get me pissed off.

I beleive the discussion was about the relative offset of a number and it's relationship to the other numbers in the sequence. Which is meaningless.

What for example, does the fact that record 5 is before record 6, except that?

Or that record 7 was destroyed in a fire and no longer exist?

The numbers themselves hold no special meaning. And we all know this. It is about the number only that we are discussing, not the other stuff that you are alluding to, that would require other, specific information, which of course would have to be recorded in the database. Which is not what this thread is about.

It about the poster trying to derive valuable information, using a sequence number, alone, by itself, with no other facts. Period.

Damn hangover.|||In this situation the number of the record would be field in the record or property of my object but it would not be my primary key or record identifier.

The above plus...

Deny delete permissions on the table, use IDENTITY or SCOPE_IDENTITY or some nonsense to populate.

That is my final answer. Sorry for the wait, been busy writing documentation.|||Wow, this thread exploded overnight (my overnight anyway). I'm not the OP, but I did raise the issue of some applications requiring sequential numbers with no gaps. I'll avoid the debate about whether you should use that field as PK or not. The fact is, as anyone who has worked in an accounting, gaming or other environment that gets audited knows, there are situations that have this requirement.

In the earlier example of the records numbered 1-100 and one gets destroyed, it's already been pointed out that the destroyed record must still be accounted for. An auditor is going to come in and ask where number XX is, and you'd better be able to tell him, not just say "the system skipped that number". Missed numbers equal possible theft to an auditor.|||arggghhh. accounting. the natural enemy of every dba. buy something of the shelf like Great Pains I mean Great Plains, ACCPAC, MAS90... The rules... the pain... the spreadsheets... the humanity...|||The pile. The only natural enemy of the hole.

More out of curiosity than to prolong the conversation, do banks actually care if I void a check in order to set up a direct deposit? Or do they let that check go as MIA?|||While I can't cite the exact section and paragraph, under GAAP (http://cpaclass.com/gaap/gaap-us-01a.htm) rules any documents that need to be serialized must be serialized before use. In other words, it has to be done while the forms are blank, with no data on them at all. Once any data has been recorded on the documents, any serialization is unacceptable under GAAP (it might be convenient, it is certainly permitted, but not admissable for any kind of GAAP compliance).

I work with bean counters every day. I've had this stuff drilled into my very soul. If you are adding serial numbers after the form has been filled out (or worse yet already stored in the database), the auditors had better cite that as a GAAP failure on any opinion they issue.

-PatP|||More out of curiosity than to prolong the conversation, do banks actually care if I void a check in order to set up a direct deposit? Or do they let that check go as MIA?Most banks don't even insist that check numbers be unique or even provided. I don't know of any that care if checks are AWOL, although they often report missing checks for customer convenience.

-PatP|||While I can't cite the exact section and paragraph, under GAAP (http://cpaclass.com/gaap/gaap-us-01a.htm) rules any documents that need to be serialized must be serialized before use. In other words, it has to be done while the forms are blank, with no data on them at all. Once any data has been recorded on the documents, any serialization is unacceptable under GAAP (it might be convenient, it is certainly permitted, but not admissable for any kind of GAAP compliance).

I work with bean counters every day. I've had this stuff drilled into my very soul. If you are adding serial numbers after the form has been filled out (or worse yet already stored in the database), the auditors had better cite that as a GAAP failure on any opinion they issue.

-PatP

And who, pray tell, does this serialization?

Why not serialize on inception?

You don't pre-serialize a trade do you?

I trust what you say, but I don't understand, or have ever seen it, even with all the gov work I've done, or street work.|||Hi Guys

I'm not sure I want to get involved in this thread as some of you seem a little tetchy but ......

a) Purchase Order No's - Goods Despatch Numbers (GDN's) & Goods Receipt Numbers (GRN's) are often serialized for very good business reasons.

b) I have always found it annoying that new Developers automatically use Identity fields without thinking about the fact that Primary Keys should be used for LOGICAL uniqueness not just a method of achieving system Ref Integrity and trying to improve performance by using a single Int field, especially on small databases
ie. I say use a real world Logicaly unique composite primary key (even if they're VarChars) IF POSSIBLE.

c) An exclusive lock on the previous max(id) would prevent problems if you think 2 people may run this statement at the exact same time (takes milliseconds to run).

d) please don't shout at me it's all IMHO ;-)

GW|||When Auditors worry about serial numbers, they are looking to be able to guarantee something. Specifically, that all of the serialized items can be accounted for throughout an entire process.

In order to make that guarantee about forms, those forms need to be uniquely identifiable in some way that has nothing to do with the way the form is used. The most commonly accepted way to do this is to pre-print serial numbers on those forms.

If you know that a particular branch/office/business/whatever started with forms X through Y, you can ensure that all of the forms are either present or properly accounted for... If these numbers are added after the fact (once the forms have been under control of the party(s) you want to be able to make that guarantee about, the numbers are useless for the purpose of making the guarantee.

If you add the serialization after the data is transcribed (copied from the original form to another media such as a database), that makes the serial numbers even less useful because at that point they have no relationship to the physical (controlled) forms at all.

This would be a textbook case of a serial number that was meaningless from a GAAP standpoint.

-PatP|||Exactly

This is why I would use the preprinted Hardcopy GDN/GRN number as the PrimaryKey and ask the input clerk to enter it into the system (with a bit of business logic to check format etc).

Purchase Orders are usually generated by the system so can be generated at point of creation. As far as I concerned the PO does not exist till it's commited to the system, at point of commit it's serialized, i imagine this method then complys with GAAP.

GW|||I don't think that GAAP rules apply outside of the United States, so you probably don't need to worry about them. I know there is an organization like the AICPA in the United Kingdom (I think it is the SBA, but I'm not sure) and I'm sure that they have similar rules.

Because the PO numbers are generated for internal purposes only, there is no point in auditing them. Who would care if PO #1234567 was missing? That wouldn't hurt anyone that I can imagine.

An auditor only cares about the things that they need to prove or certify. They might only be engaged to prove one specific thing, or they might be engaged for a full systemic audit (an awful thing).

-PatP|||Let me pitch in with my two cents...

If you want a running sequence number without using identity column, there are two ways this can be achieved: 1) use max as said before 2) use a separate table to hold a value and increment it as required. E.g. if you have multiple such requirements, you can have each row containing a number. I know 'Blindman' will jump in and try to get me with something here. What the heck? There is no such thing as old style and new style. Use the one that is convenient as long as that does not hurt the system. By the way, Windows has lot of VAX VMS concepts built-in anyway.|||:o :o :o :o|||I know 'Blindman' will jump in and try to get me with something here.Of course. I can't let bad advice slip by, since beginners tend to rely upon the information they get from these forums.
There is no such thing as old style and new style. Sure there is. That's a really uninformed statement. If you insist there is no difference between old style and new style, you can "Goto" heck. And there is also a difference between good programming and sucky programming, and using either of the methods you suggest is both slow from a processing standpoint, and needlessly complex from a programming standpoint.|||Go back to your own advice. Reading BOL... If you do not understand then don't think that it is uninformed statement. You stated that database cannot be shrunk... Go and check the BOL under DBCC SHRINKFILE... By the way, before you jump and attack me, you better get your side straight. You talk about programming...

Of course. I can't let bad advice slip by, since beginners tend to rely upon the information they get from these forums.
Sure there is. That's a really uninformed statement. If you insist there is no difference between old style and new style, you can "Goto" heck. And there is also a difference between good programming and sucky programming, and using either of the methods you suggest is both slow from a processing standpoint, and needlessly complex from a programming standpoint.|||I never stated that databases can't be shrunk. I said they can't be shrunk below their original size. Books Online:
You cannot shrink an entire database to be smaller than its original size.
Therefore, if a database was created with a size of 10 megabytes (MB) and
grew to 100 MB, the smallest the database could be shrunk to, assuming all
the data in the database has been deleted, is 10 MB. You are a noob.|||Here is your famous BOL for your reference...

--

DBCC SHRINKFILE
Shrinks the size of the specified data file or log file for the related database.

Syntax
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)

Arguments
file_name

Is the logical name of the file shrunk. File names must conform to the rules for identifiers. For more information, see Using Identifiers.

file_id

Is the identification (ID) number of the file to be shrunk. To obtain a file ID, use the FILE_ID function or search sysfiles in the current database.

target_size

Is the desired size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size.

If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.

--

Call yourself whatever you want and it can be shrunk below 10MB if required. Don't pat yourself. Will you?

I never stated that databases can't be shrunk. I said they can't be shrunk below their original size. Books Online:
You cannot shrink an entire database to be smaller than its original size.
Therefore, if a database was created with a size of 10 megabytes (MB) and
grew to 100 MB, the smallest the database could be shrunk to, assuming all
the data in the database has been deleted, is 10 MB. You are a noob.|||In this situation the number of the record would be field in the record or property of my object but it would not be my primary key or record identifier.

wtf, so now there are 2 keys ?
the number does happen to be unique and immutable, what 's the point in adding yet another key ?|||wtf, so now there are 2 keys ?
the number does happen to be unique and immutable, what 's the point in adding yet another key ?some people just cannot sleep at night unless they have assigned the key|||some people just cannot sleep at night unless they have assigned the key

Haha, and a table must have at least one identity!

Won't start that key debate.
Redundancy is meaningless.|||some people just cannot sleep at night unless they have assigned the key Guilty as charged... :(

Insert Proc With Both Select And Values

I'm trying to write a Stored Proc to Insert records into a table in SQL Server 2000. The fields in the records to be inserted are from another table and from Parameters. I can't seem to figure out the syntax for this.

I created a test in MS Access and it loooks like this:

INSERT INTO PatientTripRegionCountry_Temp ( CountryID, RegionID, Country, PatientTripID )
SELECT Country.CountryID, Country.RegionID, Country.Country, 2 AS PatientTripID
FROM Country

This works great in Access but not in SQL Server. In SQL Server 2 = @.PatientTripID

ANY SUGGESTIONS ON HOW TO HANDLE THIS?Hey, I tested your script. It works for me. Could you specify the error message and under what circumstance you are running this command and fail?|||Are you looking for something more like:CREATE PROCEDURE dbo.s2164
@.piPatientTripID INT
AS

INSERT INTO PatientTripRegionCountry_Temp (
CountryID, RegionID
, Country, PatientTripID)
SELECT Country.CountryID, Country.RegionID
, Country.Country, @.PatientTripID
FROM Country

RETURN-PatP|||This is my Stored Proc. It executes but the field PatientTripID is set to <Null>

CREATE PROCEDURE [dbo].[sp_PatientTripRegionCountryTemp_Insert_ForRegionID ]
@.RegionID int,
@.PatientTripID int,
@.PatientID int
AS
INSERT INTO PatientTripRegionCountry_Temp ( CountryID, Country, RegionID, PatientTripID )
SELECT C.CountryID, C.Country, C.RegionID, @.PatientTripID
FROM Country C
WHERE (RegionID=@.RegionID)
GO

Any Suggestions?|||When you execute it from Query Analyzer, it should show "N row(s) affected" when it executes. Zero would be a bad thing in this case.

-PatP|||?? How are you calling the procedure? Can you give a couple examples?|||Thanks for all your help

Don't ask me why, but I retried the versions shown in #4 above and this time it worked.|||Way more gooder yet even! Glad you are back in business.

-PatP