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_2016

Open in new window

biotecAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
A Stored Procedure will work fine, something like..

CREATE PROC PIP_Q1_2016_INS AS 

-- Meaningful code comments here

TRUNCACTE TABLE PIP_Q1_2016

INSERT INTO PIP_Q1_2016 (
   LastName, FirstName, Sex, Language Ethnicity, Race,
   DOB, ResultDescription, A1cResultValue, enc_id, person_id) 
SELECT 
   LastName, FirstName, Sex, Language Ethnicity, Race,
   DOB, ResultDescription, A1cResultValue, enc_id, person_id
FROM blah_blah_PIP_Q1_2016

GO

Open in new window

Then schedule this SP to execute using SQL Server Agent or whatever scheduling tool your company uses.

If you need this to do anything else then you'll have to provide us more details.

Hope this helps.
Jim
Preston CooperDatabase AdministratorCommented:
I recommend that you truncate the table and use INSERT SELECT.  You can add indexing to the table as desired and have a primary key.

TRUNCATE TABLE  dbo.PIP_Q1_2016
INSERT INTO dbo.PIP_Q1_2016 ([LastName],[FirstName],[Sex],[Language],[Ethnicity],[Race],[DOB],[ResultDescription],[A1cResultValue],[enc_id],[person_id])
SELECT [LastName],[FirstName],[Sex],[Language],[Ethnicity],[Race],[DOB],[ResultDescription],[A1cResultValue],[enc_id],[person_id]
from blah_blah_PIP_Q1_2016

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

biotecAuthor Commented:
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],[Sex],[Language],[Ethnicity],[Race],[DOB],[ResultDescription],[A1cResultValue],[A1cDate],[enc_id],[person_id],
      ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY A1cDate DESC)rowno2
      from A1c_PIP_Q1_2016 )A1c
      where rowno2 = 1
biotecAuthor Commented:
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.
Jim HornSQL Server Data DudeCommented:
>  ... 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.
biotecAuthor Commented:
Sorry for the additional comments guys. I figured out how to do what I needed thanks to your comments. Appreciate it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.