biotec
asked on
Stored procedure to insert into table each night
I'm trying to do something like below where I schedule a big query to run each night and do an insert into a table. The table will have the same name so it has to overwrite. I'm thinking a stored procedure can do this and be scheduled but not sure how or if that's the best way.
select [LastName],[FirstName],[Sex],[Language],[Ethnicity],[Race],[DOB],[ResultDescription],[A1cResultValue],[enc_id],[person_id] into PIP_Q1_2016
from blah_blah_PIP_Q1_2016SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott Pletcher
You should consider putting the TRUNCATE and INSERT into a single transaction and checking for errors during the INSERT, so that if the INSERT goes wrong at least you can roll back the truncate and the table won't be empty.
ASKER
I've got that working but what I need to do once that finishes is actually take that table and do a select from it to create another table because the data can't be manipulated properly until it is all combined.
Once this is done inside the same stored procedure can I have it take the result and actually populate another table (truncate that as well) so the whole thing is done in one sp or do I have to create two stored procedures to run one after the other so once one finishes then run the other?
This needs to be in the second part of the stored procedure.
select * from
(select [LastName],[FirstName],[Se
ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY A1cDate DESC)rowno2
from A1c_PIP_Q1_2016 )A1c
where rowno2 = 1
Once this is done inside the same stored procedure can I have it take the result and actually populate another table (truncate that as well) so the whole thing is done in one sp or do I have to create two stored procedures to run one after the other so once one finishes then run the other?
This needs to be in the second part of the stored procedure.
select * from
(select [LastName],[FirstName],[Se
ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY A1cDate DESC)rowno2
from A1c_PIP_Q1_2016 )A1c
where rowno2 = 1
ASKER
Scott that is a good point. I'm not sure how to do that and actually that plays into what I just mentioned above in that I need to do this job/stored procedure in stages so once one part completes it moves on to the next part.
> ... but what I need to do once that finishes is ...
@biotec - Please do us a favor and include all of your requirements in the original question, and not in a stream of follow-on comments, so that we can get you answers faster without multiple iterations of comments, and running the risk of losing experts between comments. Top 10 Ways to Ask Better Questions, Number Ten. Thanks in advance.
@biotec - Please do us a favor and include all of your requirements in the original question, and not in a stream of follow-on comments, so that we can get you answers faster without multiple iterations of comments, and running the risk of losing experts between comments. Top 10 Ways to Ask Better Questions, Number Ten. Thanks in advance.
ASKER
Sorry for the additional comments guys. I figured out how to do what I needed thanks to your comments. Appreciate it.