June 10, 2011 at 4:36 am
Please help,
I have a two tables, customer and order.
Customer
----------
CustomerId, CustomerName
Order
------
OderId, customerId, Total Price
Customer Count = 1000
I want to insert four orders for each customer, how could I do that.
Thank you in advance.
Regards
Lamy
June 10, 2011 at 4:48 am
The same four orders for each customer? If not, what? Also, please provide table DDL in the form of CREATE TABLE statements.
John
June 10, 2011 at 4:51 am
Yes, the same four orders for all customers.
Thanks
June 10, 2011 at 4:53 am
Table DDL, please, including foreign key constraint.
John
June 10, 2011 at 5:01 am
Thanks John,
CREATE TABLE customers(
customerId GUID PRIMARY KEY,
customerName CHAR(50) NOT NULL,
);
CREATE TABLE orders(
orderId GUID PRIMARY KEY,
orderCustomerId GUID FOREIGN KEY,
orderTotalPrice Decimal(8,2),
);
Thank you
June 10, 2011 at 5:04 am
OK, we're getting closer. Now will you please send some DDL that will actually create the tables when I run it on my SQL server.
Thanks
John
June 10, 2011 at 5:53 am
John,
Try this.
USE [temp]
GO
/****** Object: Table [dbo].[Customer] Script Date: 06/10/2011 12:49:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customer](
[customerId] [uniqueidentifier] NOT NULL,
[customerName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[customerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [temp]
GO
/****** Object: Table [dbo].[Order] Script Date: 06/10/2011 12:39:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Order](
[orderId] [uniqueidentifier] NOT NULL,
[orderCustomerId] [uniqueidentifier] NOT NULL,
[orderTotalPrice] [decimal](8, 2) NOT NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[orderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_Customer] FOREIGN KEY([orderCustomerId])
REFERENCES [dbo].[Customer] ([customerId])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Customer]
GO
ALTER TABLE [dbo].[Order] ADD CONSTRAINT [DF_Order_orderTotalPrice] DEFAULT ((0)) FOR [orderTotalPrice]
GO
INSERT INTO [dbo].[Customer]
([customerId]
,[customerName])
VALUES
('D8155DAF-7CD2-4130-BD61-F81A05997702'
,'Lamine')
INSERT INTO [dbo].[Customer]
([customerId]
,[customerName])
VALUES
('79A8007B-C160-4E36-A798-4FFB0B910B1C'
,'John')
Thanks again.
June 10, 2011 at 5:54 am
Oops!!
remeber John this script is pointing to a Temp Database.
Regards
Lamine
June 10, 2011 at 6:59 am
WITH NewOrders AS ( -- change prices here as required
SELECT 10 AS orderTotalPrice UNION ALL
SELECT 20 UNION ALL
SELECT 30 UNION ALL
SELECT 40
)
INSERT INTO [Order] (
orderId
,orderCustomerId
,orderTotalPrice
)
SELECT
NEWID()
,c.customerId
,n.orderTotalPrice
FROM
Customer c
CROSS JOIN
NewOrders n
-- Check inserted data
SELECT
o.OrderId
,c.customerName
,o.OrderTotalPrice
FROM
[Order] o
JOIN
Customer c
ON
o.orderCustomerId = c.customerId
June 10, 2011 at 8:30 am
Hi John,
Million thanks.
Take care
Lamine
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply