Using INSERT INTO in a Stored procedudre

• simplyit_kingsley

  Grasshopper

  Points: 11

  More actions

  February 20, 2011 at 2:38 pm

  #147136

  I have a stored procedure that I would like to always append new data/values into the table created as new data becomes available.

  Question 1- Do I have to create a Temporary table?

  Question 2- Is there another way doing this?

  Question 3- This is Ansi SQl and the procedure is getting hung up with the INSERT INTO statement. What am I doing wrong here?

  Please see my script below- The stored procedure is calling 2 views. (WellStatus, Thersholddepth) in this example.

  CREATE PROCEDURE "user1"."DB1" (odb VARCHAR(30), otbl VARCHAR(30), IndexDepth FLOAT, Value FLOAT, LOGHDRID CHAR(20))

  BEGIN

  DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' BEGIN END;

  IF "DB1" Is NOT NULL

  THEN

  INSERT INTO "DB1",

  ELSE

  MAIN: BEGIN

  CREATE MULTISET TABLE "..sproc_otbl_78683750" as

  (select

  ThresholdDepth.WellID ,

  ThresholdDepth.WellName ,

  ThresholdDepth.CURVENAME,

  ThresholdDepth. IndexDepth,

  ThresholdDepth. LogHdrID,

  ThresholdDepth. SequenceID,

  DB.ACTIVITY_T.ACTIVITY_ID,

  DB.ACTIVITY_T.ACTIVITY_CODE_DESC,

  DB.ACTIVITY_T.time_from,

  DB.ACTIVITY_T.time_to,

  DB.ACTIVITY_T.MD_to,

  DB.ACTIVITY_T.MD_From,

  USER1.WellStatusView.WELL_STATUS_DESC,

  USER1.WellStatusView.WELL_STATUS_DATE,

  CAST(DB.ACTIVITY_T.Activity_Memo as Varchar(1000)) as Memo,

  COUNT(ACTIVITY_ID) as ActivityFrequency

  from

  DB.ACTIVITY_T,

  USER1.ThresholdDepth,

  USER1.WellStatusView

  where

  DB.ACTIVITY_T.well_id = USER1.ThresholdDepth.WellID

  and USER1.WellStatusView.well_id=USER1.ThresholdDepth.WellID

  and DB.ACTIVITY_T.TIME_FROM = CAST(USER1.ThresholdDepth.IndexTime as DATE) and

  DB.ACTIVITY_T.TIME_TO >= CAST(USER1.ThresholdDepth.IndexTime as DATE)and

  ThresholdDepth.Value > 'n'

  ThresholdDepth.IndexDepth > 'n'

  GROUP BY

  DB.ACTIVITY_T.ACTIVITY_ID,

  ThresholdDepth.WellID ,

  ThresholdDepth.WellName ,

  ThresholdDepth.CURVENAME,

  ThresholdDepth. IndexDepth,

  ThresholdDepth. LogHdrID,

  ThresholdDepth. SequenceID,

  DB.ACTIVITY_T.ACTIVITY_CODE_DESC ,

  DB.ACTIVITY_T.time_to,

  DB.ACTIVITY_T.time_from ,

  DB.ACTIVITY_T.MD_to ,

  DB.ACTIVITY_T.MD_From ,

  USER1.WellStatusView.WELL_STATUS_DESC,

  USER1.WellStatusView.WELL_STATUS_DATE,

  Memo) with data

  --ORDER By ThresholdDepth.SequenceID,ThresholdDepth.IndexDepth

  Primary Index(ACTIVITYID, WELLID);

  CALL DBC.SysExecSQL('DROP TABLE "' || :odb || '"."' || :otbl || '"');

  CALL DBC.SysExecSQL('CREATE MULTISET TABLE "' || :odb || '"."' || :otbl || '", NO FALLBACK AS (SELECT * FROM "_twm_sproc_otbl_78683750") WITH DATA ');

  END IF;

• LutzM

  SSC Guru

  Points: 107049

  More actions

  February 20, 2011 at 5:04 pm

  #1289274

  Are you sure you'er talking about SQL SERVER 2005 syntax?

  If not, it would be very helpful if you would at least name the software and version you're dealing with...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]