Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    12

    Unanswered: Select form 1 table and insert into another

    Can someone point me in the right direction or show me a sample that may assist me in the following.

    Thanks,
    -dw


    I need to select some data from 1 table and insert it into another using a stored procedure.

    I also would like to select data from table 1 and insert a new record into table 1 modifying a field with new data passed by parameter.

    Is it something like:

    parameters passed = @selectJobNumber, @newJobNumber


    declare @DeliveryMethodID int

    SELECT
    @DeliveryMethodID=DeliveryMethodID,
    ... etc - more fields

    FROM
    jobDeliveryAddress

    WHERE
    (JobNumber= @selectJobNumber)


    INSERT INTO [jobDeliveryAddress]
    (
    [JobNumber],
    [DeliveryMethodID],
    ... etc - more fields

    )
    VALUES
    (
    @newJobNumber,
    @DeliveryMethodID,
    ... etc

    )
    -dw

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    INSERT INTO [jobDeliveryAddress]
    (
    [JobNumber],
    [DeliveryMethodID],
    ... etc - more fields

    )
    SELECT
    @selectJobNumber, DeliveryMethodID,
    ... etc - more fields

    FROM
    jobDeliveryAddress

    WHERE
    (JobNumber= @selectJobNumber)

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    But I am not sure where you're heading with it? Looks like a somewhat fishy design...

  4. #4
    Join Date
    Jan 2004
    Posts
    12
    Design, what design?

    No really, what I am looking to do is related to duplicating a particular job for reprocessing under a different job number.

    My idea (design!) is to pass a stored procedure the job number of the original along with the job number of the new job and let the stored proc 'copy' the job under the new job number. A couple of other fields will also be changed as part of the process.

    I thought letting the db do the work was better than writing code to do it within an application.
    -dw

  5. #5
    Join Date
    Jan 2004
    Posts
    12
    rdjabarov,

    in your post there is a one-to -one relationship between the INSERT fields and SELECT fields. Does this give me the ability to substitute parameters passed to the sp in place of fields selected in the SELECT statement?

    tnx for the prompt reply.
    -dw

  6. #6
    Join Date
    Jan 2004
    Posts
    35
    inset into jobdeliveryaddress
    (
    jobnumber,
    deliverymethodid,
    ...
    )
    select
    @newJobNumber,
    deliverymethodid,
    ...
    from
    jobdeliveryaddress
    where jobnumber = @selectjobnumber

  7. #7
    Join Date
    Jan 2004
    Posts
    12
    Excellent. Thank you very much.
    -dw

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •