April 1, 2009 at 2:20 pm
Hi,
I have a view that has records that I want to insert into another table. Everytime I execute the stored procedure, it only moves over one row everytime I run this stored procedure. I want the stored procedure to move over all the records in this view to the new table. Any suggestions? Is there a way I can make this stored procedure execute until the view is empty? Here is my stored procedure below :
CREATE procedure TP__CreateProgressNotes
(
@staffkey int
,@Userid int)
as
DECLARE @datesvc datetime
DECLARE @fullname varchar(41)
DECLARE @name_l varchar(75)
DECLARE @op__folderid int
DECLARE @descr varchar(100)
DECLARE @notechargekey int
DECLARE @chargekey int
DECLARE @clientkey int
--DECLARE @staff varchar(75)
DECLARE @notecount int
---------------------------
DECLARE @NewdocIDINT
DECLARE @op__cabinetid INT
DECLARE @op__parentclassid INT
DECLARE @ClientBaseCabinet INT
DECLARE @NewFolderID INT
DECLARE @FormName varchar(100)
DECLARE @OP__ParentID INT
DECLARE @ProcessName varchar(100)
DECLARE @OP__STATUSORD smallint
select @datesvc=datesvc,@fullname=fullname,@name_l=name_l,@op__folderid=op__folderid,
@descr=descr, @notechargekey=notechargekey, @chargekey=chargekey,@clientkey=clientkey
--@staff=staff
from cnotestocreate
where staffkey=@staffkey
insert into FD__Patient_note
( op__docid
,op__folderid
,op__parentid
,dos
,fullname
,visitdesc
,chargekey
,clientkey
--,clinician
,staffkey_init
--,staffname
,ordertype
,folderid)
Values
(@newdocid
,@Op__folderid
,null
,@datesvc
,@fullname
,@descr
,@chargekey
,@clientkey
--,@staff
,@staffkey
--,@staff
,'New'
,@op__folderid)
April 1, 2009 at 3:15 pm
The reason why your proc just processes one line is because you requested to do so:
CREATE procedure TP__CreateProgressNotes
(
@staffkey int
,@Userid int)
...
select ... where staffkey=@staffkey
If you want to insert all lines of the view (?) cnotestocreate, just change your code to
insert into FD__Patient_note
[target_columns]
select [source_columns]
from cnotestocreate
You won't even have to declare all those variables 🙂
Btw: What is the purpose of @userid? I can't see where it's used.
April 2, 2009 at 7:24 am
Thanks for the feedback. I am a rookie and still in the process of learning how to create store procedures.
This stored procedure is executed in the front end of the database which is an Electronic Health Record. The userid is just a field that identifies who currently is using the program.
April 2, 2009 at 10:44 am
Or you could change the code to be ALL or parmeter
from cnotestocreate
where staffkey=@staffkey -- 1 record only
OR @staffkey IS NULL -- copies all records, if you pass NULL parameter
April 2, 2009 at 12:17 pm
That actually moved over all of the rows, but the field Staffkey was null in the tables that I inserted from the source table. Is there another parameter that I can use that will keep executing the stored procedure until all of the rows from the source table is empty? As I run the stored procedure, the number of records in my row decrease one by one. I want the stored procedure to execute all rows until the view becomes 0. Thanks :unsure:
April 2, 2009 at 2:12 pm
marvin.tumasar (4/1/2009)
I want the stored procedure to move over all the records in this view to the new table.
Marvin, can you post the DDL for the tables involved and the view? It sounds like you are needing to insert records by virtue of their not existing in another table. If the view is showing you all the records you need to insert into the new table, and if when they are inserted in the new table, the WHERE criteria in the view excludes them from subsequent runnings of the view, I think you might be able to just insert the entire view as a set rather than one at a time, and you may not need all the variables.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply