Stored procedure help

• MacDaddy

  SSC Eights!

  Points: 993

  More actions

  July 1, 2010 at 8:10 am

  #387259

  I'm not at all experienced with stored procedures so I'm probably struggling with something very basic here, apologies if this is so simple as to be idiotic:

  I am trying to use the following stored procedure to update a table from a table or view but I'm getting it all bassackwards when it comes to passing parameters and using cursors:

  Create sp_tableinsert

  AS

  @Code varchar(7),

  @data varchar(4000),

  @Ref varchar(50) = '',

  @Source varchar(100) = 'Stored Procedure'

  AS

  DECLARE @ID int

  INSERT INTO dbo.TableDestination

  (

  Code,

  Data,

  Ref,

  Source

  )

  VALUES(

  @Code,

  @data,

  @Ref,

  @Source

  )

  IF @@RowCount = 0

  RETURN 0

  ELSE

  RETURN SCOPE_IDENTITY()

  My source table from which I'm trying to take data from looks like this:

  Create table TableSource

  (

  Code Nvarchar(10),

  Data Nvarchar(1000)

  )

  Insert Into TableSource

  SELECT N'Code001' AS Code, N'XX001ABC|Fine white sand|Other' AS Data

  UNION ALL

  SELECT N'Code001' AS Code, N'XX002ABC|Silicone tube 5mm ID, 7mm OD X 50mtr|Tubes and hose'

  UNION ALL

  SELECT N'Code003' AS Code, N'XX003ABC|Pnumatic Pump|Pumps' AS Data

  UNION ALL

  SELECT N'Code001' AS Code, N'XX004ABC|Hydraulic Pump|Pumps' AS Data

  UNION ALL

  SELECT N'Code005' AS Code, N'XX005ABC|Support Beam|Bearings' AS Data

  UNION ALL

  SELECT N'Code010' As Code, N'XX006ABC|Supprt Beam Heavy Duty|Bearings' AS Data

  What's the correct way to execute sp_tableinsert to get it to pass the columns Code and Data from my source table as the parameters @Code and @data and have it scroll through the source table until it's finished?

  Incidentally, the stored procedure comes supplied with a system that I need to populate with data, the last two parameters, @Ref and @Source are optional.

  Many thanks in advance,

  Paul

  Keep the rubber side down and the shiny side up.

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  July 1, 2010 at 8:57 am

  #1187709

  It is a bad practice to use cursor or any kind of loop to do what you are trying to do in T-SQL.

  It will be better if you will create dedicated stored proc which will insert data from one table to another. You will be able to do it in one INSERT statement, something along the lines of:

  INSERT INTO dbo.TableDestination

  (

  Code,

  Data,

  Ref,

  Source

  )

  SELECT Code

  ,Data

  ,@Ref

  ,@Source

  FROM dbo.TableSource

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737382

  More actions

  July 1, 2010 at 9:31 am

  #1187734

  Eugene's code should help you. You basically write a query that you want to insert (can be columns or scalars) and then add that to an insert clause.