Using the recordset from SELECT statement in an INSERT statement, all in one Stored Procedure

• joshua__lim

  SSC Rookie

  Points: 27

  More actions

  March 27, 2008 at 4:59 am

  #183907

  Hi, i'm an SQL newbie. I'm trying to figure out if there's an easy way to take a single field record set from a SELECT statement and then do an INSERT using that record set, all in one single Stored Procedure.

  Here's what i tried to do, but this returns an error "The name "phonenumber" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.".

  The common field in both SELECT and INSERT is phonenumber.

  PROCEDURE [dbo].[usp_select_and_insert]

  @name varchar(20),

  AS

  SELECT phonenumber FROM USERLIST where OWNERNAME LIKE @name

  INSERT INTO LOGLOG (destination,content) values(phonenumber,'hello world');

  GO

  Hope that one of you can be kind enough to give me some guidance. Appreciate in advance. 🙂

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 27, 2008 at 5:23 am

  #794517

  Something like this:

  INSERT INTO LOGLOG (destination, [content])

  SELECT phonenumber, 'hello world'

  FROM USERLIST

  where OWNERNAME LIKE @name

  John