Inserting a select with an additional static field

[Scott]

I have a stored procedure where I want to select all fields matching
the query into another table. In addition, I want to add a common
groupID to each of the records that are being inserted into the second
table.

I can get the results that I want by using a temporary table but need
to know if there is a way to do it directly..

below is the code that uses the temporary table..

CREATE TABLE #tempStore_DeliveryAddress (
[AddressId] [int] IDENTITY (1, 1) NOT NULL ,
[UserId] [int] NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[SpouseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[MiddleName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[SpouseMiddleName] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[SpouseLastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Suffix] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SpouseSuffix] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Company] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Address1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Address2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Address3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PostalCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ForeignFlag] [int] NULL CONSTRAINT
[DF_Store_DeliveryAddress_ForeignFlag] DEFAULT (0),
[email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Greeting] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FullName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ShortName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[OptOut] [int] NULL CONSTRAINT [DF_Store_DeliveryAddress_OptOut]
DEFAULT (0),
[Modified] [datetime] NULL CONSTRAINT
[DF_Store_DeliveryAddress_Modified] DEFAULT (getdate()),
[Modifer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_Store_DeliveryAddress_Modifer] DEFAULT ('DBA'),
[Created] [datetime] NULL CONSTRAINT
[DF_Store_DeliveryAddress_Created] DEFAULT (getdate()),
[Creator] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_Store_DeliveryAddress_Creator] DEFAULT ('DBA'),
[MailListID] [int] NULL ,
CONSTRAINT [PK_Store_DeliveryAddress] PRIMARY KEY CLUSTERED
(
[AddressId]
) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO #tempStore_DeliveryAddress([UserId], [Title], [FirstName],
[SpouseName], [MiddleName], [SpouseMiddleName], [LastName],
[SpouseLastName], [Suffix], [SpouseSuffix], [Company], [Address1],
[Address2], [Address3], [City], [State], [PostalCode], [Country],
[ForeignFlag], [email], [Greeting], [FullName], [ShortName], [OptOut],
[Modified], [Modifer], [Created], [Creator])

(SELECT [UserId], [Title], [FirstName], [SpouseName], [MiddleName],
[SpouseMiddleName], [LastName], [SpouseLastName], [Suffix],
[SpouseSuffix], [Company], [Address1], [Address2], [Address3], [City],
[State], [PostalCode], [Country], [ForeignFlag], [email], [Greeting],
[FullName], [ShortName], [OptOut], [Modified], [Modifer], [Created],
[Creator]
FROM [ntmportal].[dbo].[Store_AddressBook]
WHERE [AddressID] in (Select AddressID From Store_AddressesForGroup
where AddressGroupID = 322))
UPDATE #tempStore_DeliveryAddress set MailLISTID = 422

INSERT INTO Store_DeliveryAddress([UserId], [Title], [FirstName],
[SpouseName], [MiddleName], [SpouseMiddleName], [LastName],
[SpouseLastName], [Suffix], [SpouseSuffix], [Company], [Address1],
[Address2], [Address3], [City], [State], [PostalCode], [Country],
[ForeignFlag], [email], [Greeting], [FullName], [ShortName], [OptOut],
[Modified], [Modifer], [Created], [Creator], [MailListID])
(Select [UserId], [Title], [FirstName], [SpouseName], [MiddleName],
[SpouseMiddleName], [LastName], [SpouseLastName], [Suffix],
[SpouseSuffix], [Company], [Address1], [Address2], [Address3], [City],
[State], [PostalCode], [Country], [ForeignFlag], [email], [Greeting],
[FullName], [ShortName], [OptOut], [Modified], [Modifer], [Created],
[Creator], [MailListID]
FROM #tempStore_DeliveryAddress)

[David Portas]

INSERT INTO Store_DeliveryAddress
(userid, title, firstname, spousename, middlename, spousemiddlename,
lastname, spouselastname, suffix, spousesuffix, company, address1,
address2, address3, city, state, postalcode, country, foreignflag,
email, greeting, fullname, shortname, optout, modified, modifer,
created, creator, maillistid)
SELECT userid, title, firstname, spousename, middlename, spousemiddlename,
lastname, spouselastname, suffix, spousesuffix, company, address1,
address2, address3, city, state, postalcode, country, foreignflag,
email, greeting, fullname, shortname, optout, modified, modifer,
created, creator, 422
FROM Store_AddressBook
WHERE addressid IN
(SELECT addressid
FROM Store_AddressesForGroup
WHERE addressgroupid = 322)

Do you really need to duplicate the data in a second table
(Store_DeliveryAddress)? Maybe you could just create a view based on the
above SELECT statement.

--
David Portas
SQL Server MVP
--

[Serge Rielau]

You can use a qualified asterisk. Note that asterisk is often frowned
upon as bad style.

INSERT INTO Store_DeliveryAddress
(userid, title, firstname, spousename, middlename, spousemiddlename,
lastname, spouselastname, suffix, spousesuffix, company, address1,
address2, address3, city, state, postalcode, country, foreignflag,
email, greeting, fullname, shortname, optout, modified, modifer,
created, creator, maillistid)
SELECT Store_AddressBook.*, 422
FROM Store_AddressBook
WHERE addressid IN
(SELECT addressid
FROM Store_AddressesForGroup
WHERE addressgroupid = 322)
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab