Update table with result from stored procedure/query?

[Kirsten Østerga]

I have a table that I insert data into using the following stored procedure:

CREATE PROCEDURE [dbo].[usp_insertTotaler] 
AS
BEGIN
	SET NOCOUNT ON;

    	DECLARE @Periode DateTime, @NyPeriode DateTime
	SET @NyPeriode = (SELECT Max(Periode) FROM InstPeriode)

	IF EXISTS 
		(SELECT DISTINCT ip.Periode FROM InstPeriode ip LEFT OUTER JOIN Totaler t ON ip.Periode = t.Periode 
		WHERE t.Periode Is Null)
	BEGIN
	INSERT INTO Totaler(iNavn, Periode, SubBrutSum, BrutTotal)

	EXEC dbo.usp_displayThemAll
	@Periode = @NyPeriode
	SELECT iNavn, Periode, SubBrutSum, BrutTotal
	FROM Totaler
	END

My question is: Is it possible to make an updatequery that executes a stored procedure (usp_displayThe mAll) and then update my table (Totaler) with these data? And if it is - then how?

[ck9663]

Yes you can...

If you execute the display before the update, you're going to display the un-updated records.

Also, displaying the records will not do anything if you don't push it to your front-end. SQL Server is a Database, not a front-end tool...

Good Luck!!!

~~CK

[Kirsten Østerga]

It doesn't seem to me like I'm using SQL as a front-end tool? What's wrong with creating a stored procedure, and then push it to my front end? (Which is what I do - don't worry).

Perhaps I misunderstand you, but your answer seems a bit unkind...

[ck9663]

The comment was based on the assumption that your stored procedure is called DisplayAll. Which to me says the intention is to display the record.

If you want to update a table based on the resultset returned by that stored procedure, there are a number of options.

1. Read the stored proc, retrieve the query and use it to update your table.

2. Convert the stored proc into a table-valued function and then call it to insert to your table.

3. If you really want to use that stored procedure, you still can. Here, read this...

Happy Coding!!!

~~ CK