CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresinsert
ruedi
ruedi

Reputation: 5545

How to Insert Select Query Results into a Table with a Stored Procedure

I want to add the result of a query into another Table that is only for collecting some statistics by stored procedure.

This is what I have achieved: https://sqltest.net/#926688

But the resultset is empty and I do not know why.

Table: SurveyData
ID|Variable1|Variable2
 1|        3|        4
 2|        2|        4
 3|        5|        1

With a stored procedure I want to count the values of the variable in case they are 4 or 5 and put the result into another table calles Upper_Values

The result should look like this

Table: Upper_Values
ID|Var1count|Var2Count
1 |        1|        2
2 | ...

The SP I have written for this is:

GO
CREATE PROCEDURE Put_Values
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO Upper_Values(Var1Count, Var2Count)
    Select count(Variable1) as Var1Count, count(Variable2) as Var2Count from surveyData
END

Anyone who can help me get this done?

Upvotes: 0

Views: 78

Answers (1)

JMabee
JMabee

Reputation: 2300

I modified your code so it would run. 

CREATE TABLE sql_server_test_a 
( 
    ID         NVARCHAR(4000), 
    FIRST_NAME NVARCHAR(200), 
    LAST_NAME  NVARCHAR(200) 
); 

INSERT INTO sql_server_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('1', 'Paris', 'Hilton'); 


CREATE TABLE sql_server_test_b
( 
    FIRST NVARCHAR(200),
    LAST NVARCHAR(200)
);
GO
CREATE PROCEDURE Put_First
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO sql_server_test_b(FIRST, LAST)
    Select FIRST_NAME as FIRST, LAST_NAME as LAST from sql_server_test_a
END
GO

EXEC Put_First;

SELECT * from sql_server_test_b

Upvotes: 1

Related Questions