Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 6th, 2007, 04:25 AM
Authorized User
 
Join Date: Oct 2006
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Default INSERT INTO with parameterized values and SELECT

Hi All,

has anybody tried Inserting records to a table using combination of parameter and SELECT? Below is my stored proc:

create procedure mySP
(
  @ds_id int,
  @product varchar(30),
  @system varchar(6)
)
as


begin tran

    insert into map
    (
        ds_id, --VALUE WILL COME FROM THE PARAMETER
        counterparty_id,
        counterparty_name,
        swift_code,
        related,
        rba,
        country_code,
        cp_code,
        comment,
        who_created,
        when_created,
        who_modified,
        when_modified,
        product -- VALUE FROM THE PARAMETER
    )

        VALUES (@ds_id,
       (SELECT
        counterparty_id,
        counterparty_name,
        swift_code,
        related,
        rba,
        country_code,
        cp_code,
        comment,
        who_created,
        when_created,
        who_modified,
        when_modified FROM map_r),
                @product)

commit tran



Here, I would like all the fields of map be replaced with the records from map_r, except the column of ds_id and product, which will be filled by the supplied parameter @ds_id and @product. However Im receiving errors . Is there a way to INSERT records to a table using combination of SELECT and parameterized values?

Thank you so much!

 
Old September 6th, 2007, 04:48 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Get rid of the VALUES part and add the parameters to the SELECT:
Code:
 insert into map
    (
        ds_id, --VALUE WILL COME FROM THE PARAMETER
        counterparty_id,
        counterparty_name,
        swift_code,
        related,
        rba,
        country_code,
        cp_code,
        comment,
        who_created,
        when_created,
        who_modified,
        when_modified,
        product -- VALUE FROM THE PARAMETER
    )
       (SELECT
        @ds_id,
        counterparty_id,
        counterparty_name,
        swift_code,
        related,
        rba,
        country_code,
        cp_code,
        comment,
        who_created,
        when_created,
        who_modified,
        when_modified,
        @product
        FROM  map_r)


--

Joe (Microsoft MVP - XML)





Similar Threads
Thread Thread Starter Forum Replies Last Post
insert into....select problem timbal25 SQL Server 2005 1 October 24th, 2007 08:28 PM
A select and then an insert in a stored proc smacks SQL Server 2005 3 July 21st, 2007 07:31 PM
SELECT/ORDER/INSERT Stuart Stalker SQL Server DTS 1 July 24th, 2005 01:00 AM
INSERT & SELECT FROM....? No idea Seb_soum Classic ASP Databases 3 May 14th, 2004 07:06 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.