CODEWITHSUNDEEP
sqlsql-server
doe
doe

Reputation: 148

How to insert into multiple tables from a stored procedure

I have the following code that inserts data into a column however its not loading into the correct columns.what do you think is wrong?

ALTER PROCEDURE [dbo].[spTESt]  
AS

DECLARE @Report TABLE (
   c1 int, c2 int, c3 int, c4 int, c5 int  
)


INSERT INTO @Report  (c1)
SELECT mass as c1 FROM other_sales
WHERE id='17501' order by mass

INSERT INTO @Report (c2)
SELECT mass as c2  FROM other_sales
WHERE id='17154' order by mass 


INSERT INTO @Report  (c3)
SELECT mass FROM other_sales
WHERE id='17156' order by mass


INSERT INTO @Report  (c4)
SELECT mass FROM other_sales
WHERE id='17500' order by mass


INSERT INTO @Report  (c5)
SELECT mass FROM other_sales
WHERE id='17501' order by mass

it needs to go into separate columns based on its condition. Should I structure it differently?

Upvotes: 0

Views: 1146

Answers (3)

Ash
Ash

Reputation: 6035

Assuming all the select statements returnsingle values, you could try a single statement as follows:

ALTER PROCEDURE [dbo].[spTESt]  
AS
--......
DECLARE @Report TABLE (c1 int, c2 int, c3 int, c4 int, c5 int )

INSERT INTO @Report  (c1, c2, c3, c4, c5) values (
(SELECT mass FROM other_sales WHERE id='17501'),
(SELECT mass FROM other_sales WHERE id='17154'),
(SELECT mass FROM other_sales WHERE id='17156'),
(SELECT mass FROM other_sales WHERE id='17500'),
(SELECT mass FROM other_sales WHERE id='17501') ) 
--....

Upvotes: 2

Jesse Petronio
Jesse Petronio

Reputation: 723

DECLARE 
      @Report TABLE (

c1 int ,
c2 int ,
c3 int ,
c4 int , 
c5 int

      )

DECLARE @_C1 INT
DECLARE @_C2 INT
DECLARE @_C3 INT
DECLARE @_C4 INT
DECLARE @_C5 INT

SELECT @_C1 = mass FROM other_sales
WHERE id='17501' order by mass

SELECT @_C2 = mass  FROM other_sales
WHERE id='17154' order by mass 

SELECT @_C3 = mass other_sales
WHERE id='17156' order by mass

SELECT @_C4 = mass other_sales
WHERE id='17500' order by mass

SELECT @_C5 = mass other_sales
WHERE id='17501' order by mass

INSERT INTO @Report
        ( c1, c2, c3, c4 , c5 )
VALUES  ( @_C1, -- c1 - int
          @_C2, -- c2 - int
          @_C3, -- c3 - int
          @_C4,  -- c4 - int
          @_C5  -- c5 - int
          )

Upvotes: 1

Jesse Petronio
Jesse Petronio

Reputation: 723

Remove the comma after your last column declaration 

DECLARE 
      @Report TABLE (

c1 int ,
c2 int ,
c3 int ,
c4 int 

      )

Upvotes: -1

Related Questions