CODEWITHSUNDEEP
sqlsql-serverstored-procedures
boombox2014
boombox2014

Reputation: 65

STORED PROC - select then insert

I'm new in coding stored procedures. Basically I would just like to transfer top 10 records of table 1 to table 2(which I created). My code can't insert the records I selected from table 1. I'm not sure if I'm missing something or I'm doing it wrong. Please help. Thanks in advance!

Note:

1.) Table 1 and Table 2 are with the same datatypes.

2.) I need parameters for each column for me to be able to call them in my c#.net graph

Here's my SP:

**

USE [GDW]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_BI_Get_AsiaTopTen]

AS BEGIN
  DECLARE 
  @fyname nvarchar(120),
  @fmonth nvarchar(120),
  @fmonth_num int = null,
  @world_area nvarchar(120) = null

    SELECT TOP 10 
  @fyname= [FYName], 
  @fmonth= [FMONTH], 
  @fmonth_num= [FMONTH_NUM],
  @world_area= [World_AREA]
  FROM [dbo].[tbl_BI_FastReport1_Temp]
  WHERE [World_AREA] = 'Asia'
  AND [FMONTH_NUM] = '201401'

  INSERT INTO [dbo].[tbl_BI_AsiaTopTen]
  (
  [FYName],
  [FMONTH],
  [FMONTH_NUM],
  [World_AREA]
  )
  VALUES
  (
  @fyname,
  @fmonth,
  @fmonth_num,
  @world_area
  )

  END

**

Upvotes: 3

Views: 3117

Answers (3)

Atul Dravid - White Pvt. Ltd.
Atul Dravid - White Pvt. Ltd.

Reputation: 311

You don't need stored procedure for this... As I remember, you can do

insert into table2 Select top 10 col1, col2, col3 from table1 where col1= ....

checkout this msdn link...

http://msdn.microsoft.com/en-us/library/ms174335.aspx

Upvotes: 0

Jason
Jason

Reputation: 621

Try something like this instead:


    INSERT INTO [dbo].[tbl_BI_AsiaTopTen]
    (
    [FYName],
    [FMONTH],
    [FMONTH_NUM],
    [World_AREA]
    )
    SELECT TOP 10 
    [FYName], 
    [FMONTH], 
    [FMONTH_NUM],
    [World_AREA]
    FROM [dbo].[tbl_BI_FastReport1_Temp]
    WHERE [World_AREA] = 'Asia'
    AND [FMONTH_NUM] = '201401'

Upvotes: 0

M.Ali
M.Ali

Reputation: 69514

ALTER PROCEDURE [dbo].[sp_BI_Get_AsiaTopTen]
AS 
BEGIN
  SET NOCOUNT ON;

      INSERT INTO [dbo].[tbl_BI_AsiaTopTen]
                      ([FYName],[FMONTH],[FMONTH_NUM],[World_AREA])
    SELECT TOP 10 [FYName] 
                , [FMONTH] 
                , [FMONTH_NUM]
                , [World_AREA]
    FROM [dbo].[tbl_BI_FastReport1_Temp]
    WHERE [World_AREA] = 'Asia'
    AND [FMONTH_NUM] = '201401'
 -- ORDER BY SomeCOlumn 
END

Upvotes: 1

Related Questions