Stored procedure to insert into table each night

biotec
biotec used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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
Database Administrator
Commented:
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
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

Author

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 Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>  ... 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.

Author

Commented:
Sorry for the additional comments guys. I figured out how to do what I needed thanks to your comments. Appreciate it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial