stored procedure to run query and then insert rows

[charlesg]

I have a query that select rows from the employees,salary_head and salary_group tables
this is the query
SELECT TOP 100 PERCENT dbo.salary_head.salary_group_id, dbo.salary_group.salary_group, dbo.salary_head.amount, dbo.grade_level.[level],
dbo.employees.employ_name, dbo.employees.work_id, dbo.employees.company_id, dbo.employees.designation, dbo.salary_head.level_id,
dbo.employees.terminate, dbo.employees.banks_id, dbo.employees.bank_account_no
FROM dbo.employees INNER JOIN
dbo.salary_head INNER JOIN
dbo.salary_group ON dbo.salary_head.salary_group_id = dbo.salary_group.salary_group_id ON
dbo.employees.level_id = dbo.salary_head.level_id INNER JOIN
dbo.grade_level ON dbo.employees.level_id = dbo.grade_level.level_id
ORDER BY dbo.grade_level.level_no DESC, dbo.salary_head.salary_group_id
i also have a table called payrollers1 with the following fields
payroll_id int auto
payperiod_id int
employee_id
level_id
designation_id
banks_id
bankaccount_no
salarygroup_id
Amount
I am trying to write a stored procedure that will run the above query and then insert the values of the employee_id,level_id,designation_id,salary_group_id,amount rows into the payroller table.
As for the payperiod_id i want the Stored procedure to look up the max payperiod value.
I am totally new to stored procedure and do not know how to write this code.
Can somebody help me with this code.

 

[pdx_man]

I'm guessing you have this query defined as a VIEW since you are returning the TOP 100 PERCENT with an ORDER BY clause. Lets call it YourQView.

You are going to have to add the fields you need in the payrollers1 table to the SELECT part of YourQView. Those being the ID fields.

to look up the max payperiod value

From where? payrollers1? And do what with it? Isn't it defined as an IDENTITY field in payrollers1?

CREATE PROCEDURE dbo.proc_YourProc As

DECLARE @MaxPayID INT

SELECT @MaxPayID = MAX(payperiod_id) FROM payrollers1

INSERT INTO payrollers1 (payroll_id 
,payperiod_id 
,employee_id
,level_id
,designation_id
,banks_id
,bankaccount_no
,salarygroup_id
,Amount)
SELECT 
The
, @MaxPayID  
, Fields
, From
, YourQView
, That
, Match
, Above
, Order
FROM YourQView