doe doe - 4 months ago 18
SQL Question

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?

Answer
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
          )