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)