Select value for each insert on subselect

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select value for each insert on subselect

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-10-21 : 02:33:28

I'm trying to port over all records from table CateringOrder in a New Database from tbl_CateringOrder in an old database (xxProduction) to a new (xx). One of the fields NumberOfPeople in the new CateringOrder is no longer an ID, but just a number. But in order to port over that number from the old Database table, I need to grab the integer value by doing a join and selecting it from the xxProduction.NumberOfPeople table.

So I added a sub Select statement as part of the select statement in order to try to grab the integer value I need for NumberOfPeople, but it's not liking my syntax. I'm talking about this part that grabs the NumberOfPeople based on ID:

Code Snippet

,(Select ep.NumberOfPeopleName from [xxProduction].dbo.tbl_NumberOfPeople ep
where ep.NumberOfPeopleID = [xxProduction].dbo.tbl_CateringOrder.[CateringOrderID])

Here's the insert T-SQL I have so far that is bombing out:

Code Snippet

INSERT INTO [xx].[dbo].[CateringOrder]
([CateringOrderID]
,[UserID]
,[NumberOfPeople] (this is now just an integer field in the new CateringOrder table)
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID])
SELECT [CateringOrderID]
,[CustomerID]
,(Select ep.NumberOfPeopleName from [xxProduction].dbo.tbl_NumberOfPeople ep
where ep.NumberOfPeopleID = [xxProduction].dbo.tbl_CateringOrder.[CateringOrderID])
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID]
FROM [xxProduction].dbo.tbl_CateringOrder

how can I grab the NumberOfPeopleName by the current ID in the main select based off each xxProduction.NumberOfPeopleID?

CREATE TABLE [dbo].[CateringOrder](
[CateringOrderID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[BeanOptionID] [int] NULL,
[TortillaOptionID] [int] NULL,
[CreateDate] [datetime] NULL,
[Notes] [varchar](1000) NULL,
[EventDate] [datetime] NULL,
[EventTypeID] [int] NULL,
[EventTime] [datetime] NULL,
[DeliveryOptionID] [int] NULL,
[DeliveryOrPickupTime] [datetime] NULL,
[EventCompanyName] [varchar](150) NULL,
[NumberOfPeople] [int] NULL,
[DeliveryAddress1] [varchar](100) NULL,
[DeliveryCityID] [int] NULL,
[DeliveryStateID] [int] NULL,
[DeliveryZip] [char](10) NULL,
[DeliveryAddressSameAsShipping] [bit] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tbl_CateringOrder](
[CateringOrderID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NULL,
[NumberOfPeopleID] [int] NULL,
[BeanOptionID] [int] NULL,
[TortillaOptionID] [int] NULL,
[CreateDate] [datetime] NULL,
[Notes] [varchar](3000) NULL,
[NumberOfPeople] [int] NULL,
[EventDate] [datetime] NULL,
[DeliveryOptionID] [int] NULL
) ON [PRIMARY]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 02:39:02

ok. whats the question? Are you facing any problem? if yes, please illustrate with some sample data.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-21 : 07:07:29

If you are trying to get the ID field for each inserted row.
There are 2 ways - either use @@identity or SCOPE_IDENTITY and loop for each inserted row. A faster method is to use INSERTED table, e.g.

INSERT INTO xx.dbo.CateringOrder
([CateringOrderID]
,[UserID]
,[NumberOfPeople]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID])

OUTPUT INSERTED.[CateringOrderID] --this line retrieves all inserted [CateringOrderID]'s

--insert multiple rows
SELECT '1' ,'a','b','c','d','e','f','g','h'
UNION ALL
SELECT '2' ,'a','b','c','d','e','f','g','h'
UNION ALL
SELECT '3' ,'a','b','c','d','e','f','g','h'
UNION ALL
SELECT '4' ,'a','b','c','d','e','f','g','h'
UNION ALL
SELECT '5' ,'a','b','c','d','e','f','g','h'
GO

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-10-21 : 11:46:14

I'm not trying to get the identity. I'm trying to do an insert and one of the values NumberOfPeople depends on a lookup value based on NumberOfPeopleID in the NumberOfPeople Table. Take a look at it again...I'm inserting [NumberOfPeople] (int) and trying to grab the integer for the current CateringOrderID being inserted by adding a select (Select ep.NumberOfPeopleName from [xxProduction].dbo.tbl_NumberOfPeople ep
where ep.NumberOfPeopleID = [xxProduction].dbo.tbl_CateringOrder.[CateringOrderID])

in order to grab the int value for the insert. NumberOfPeopleName is also an int but is in the NumberOfPeople table. To grab it, I have to join on NumberOfPeopleID in my Select

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 11:53:09

[code]SELECT co.[CateringOrderID]
,co.[CustomerID]
,ep.NumberOfPeopleName
,co.[BeanOptionID]
,co.[TortillaOptionID]
,co.[CreateDate]
,co.[Notes]
,co.[EventDate]
,co.[DeliveryOptionID]
FROM [xxProduction].dbo.tbl_CateringOrder co
join [xxProduction].dbo.tbl_NumberOfPeople ep
on ep.NumberOfPeopleID = co.[CateringOrderID][/code]

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-10-21 : 11:56:26

ok, I've updated the original post, hopefully this is more clear to all now. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources