How to insert more then one order into customers orders table

Hi there,
How do I insert into Customers and Orders table in Northwind database like
One customer and three orders at the same time like
Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
Insert into Orders (CustomerID,OrderDate)
VAULES
('abc','1/1/2006')
('abc','2/1/2006')
('abc','3/1/2006')
somthing like that -> multiple insert statement to one to many relationship
table
Thanks,
Oded DrorOded, shalom
Create a stored procedure and issue
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060101')
Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060102')
Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060103')
COMMIT TRANSACTION
"Oded Dror" <odeddror@.cox.net> wrote in message
news:OK9DoDKTGHA.4608@.tk2msftngp13.phx.gbl...
> Hi there,
> How do I insert into Customers and Orders table in Northwind database like
> One customer and three orders at the same time like
> Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
> Insert into Orders (CustomerID,OrderDate)
> VAULES
> ('abc','1/1/2006')
> ('abc','2/1/2006')
> ('abc','3/1/2006')
> somthing like that -> multiple insert statement to one to many
> relationship table
> Thanks,
> Oded Dror
>
>|||I did something like this
Insert into Orders (CustomerID,OrderDate)
select 'abc','1/1/2006' union
select 'abc','2/1/2006' union
select 'abc','3/1/2006'
not tested but it should work.
Grant
Who gives a {censored} if I am wrong.
"Oded Dror" <odeddror@.cox.net> wrote in message
news:OK9DoDKTGHA.4608@.tk2msftngp13.phx.gbl...
> Hi there,
> How do I insert into Customers and Orders table in Northwind database like
> One customer and three orders at the same time like
> Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
> Insert into Orders (CustomerID,OrderDate)
> VAULES
> ('abc','1/1/2006')
> ('abc','2/1/2006')
> ('abc','3/1/2006')
> somthing like that -> multiple insert statement to one to many
> relationship table
> Thanks,
> Oded Dror
>
>|||Uri,
The problem is when you enter a new Customer with Identity Key set to yes
How do you assign the CustomerID (you don't know what is it!) to the
Orders.CustomerID table
What I did is after finishing inserts command I added
update tblOrders set CustomerID =(select top 1 CustomerID from tblCustomers
order by CustomerID Desc)
where CustomerID IS NULL
That way I'm assigning the tblCustomer.CustomerID to tblOrders.CustomerID
It work but I'm not sure if is it the right solution?
Thanks,
Oded
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e%23vaqvKTGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Oded, shalom
> Create a stored procedure and issue
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
> Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060101')
> Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060102')
> Insert into Orders (CustomerID,OrderDate) VAULES ('abc','20060103')
> COMMIT TRANSACTION
>
>
> "Oded Dror" <odeddror@.cox.net> wrote in message
> news:OK9DoDKTGHA.4608@.tk2msftngp13.phx.gbl...
>|||Hi, Oded
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
DECLARE @.new_custid INT
BEGIN TRANSACTION
Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
SELECT @.new_custid=SCOPE_IDENTITY()
Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES
(@.new_custid,'abc','20060101')
Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES
(@.new_custid,'abc','20060102')
Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES (@.new_custid
,'abc','20060103')
COMMIT TRANSACTION
"Oded Dror" <odeddror@.cox.net> wrote in message
news:OjOoLxOTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Uri,
> The problem is when you enter a new Customer with Identity Key set to yes
> How do you assign the CustomerID (you don't know what is it!) to the
> Orders.CustomerID table
> What I did is after finishing inserts command I added
> update tblOrders set CustomerID =(select top 1 CustomerID from
> tblCustomers order by CustomerID Desc)
> where CustomerID IS NULL
> That way I'm assigning the tblCustomer.CustomerID to tblOrders.CustomerID
> It work but I'm not sure if is it the right solution?
> Thanks,
> Oded
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e%23vaqvKTGHA.1728@.TK2MSFTNGP11.phx.gbl...
>|||Uri,
When I did
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
DECLARE @.new_custid int
BEGIN TRANSACTION
Insert into tblCustomers (FirstName,LastName,State)
Values('Bill','Gates','2')
SELECT @.new_custid = SCOPE_IDENTITY()
Insert into tblCards (StoreID,CustomerID,CardNumber)
Values('2','@.new_custid','111')
Insert into tblCards (StoreID,CustomerID,CardNumber)
Values('4','@.new_custid','222')
Insert into tblCards (StoreID,CustomerID,CardNumber)
Values('23','@.new_custid','333')
COMMIT TRANSACTION
I got an error message:
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '@.new_custid' to data
type int.
Thanks,
Oded
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Oxqp06OTGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi, Oded
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> DECLARE @.new_custid INT
> BEGIN TRANSACTION
> Insert into Customers(ComapnyName,ContactName) Values('abc','xyz')
> SELECT @.new_custid=SCOPE_IDENTITY()
> Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES
> (@.new_custid,'abc','20060101')
> Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES
> (@.new_custid,'abc','20060102')
> Insert into Orders (CustomerID,ComapnyName,OrderDate) VAULES (@.new_custid
> ,'abc','20060103')
> COMMIT TRANSACTION
>
> "Oded Dror" <odeddror@.cox.net> wrote in message
> news:OjOoLxOTGHA.5500@.TK2MSFTNGP12.phx.gbl...
>|||Yes, sure .

> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('2','@.new_custid','111')
Why did you put an quotation on '@.new_custid'? Is it VARCHAR(n) or INTEGER?
Insert into tblCards (StoreID,CustomerID,CardNumber)
Values('2',@.new_custid,'111')
"Oded Dror" <odeddror@.cox.net> wrote in message
news:u404$kVTGHA.4520@.TK2MSFTNGP10.phx.gbl...
> Uri,
> When I did
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> DECLARE @.new_custid int
> BEGIN TRANSACTION
> Insert into tblCustomers (FirstName,LastName,State)
> Values('Bill','Gates','2')
> SELECT @.new_custid = SCOPE_IDENTITY()
> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('2','@.new_custid','111')
> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('4','@.new_custid','222')
> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('23','@.new_custid','333')
> COMMIT TRANSACTION
> I got an error message:
> Msg 245, Level 16, State 1, Line 5
> Conversion failed when converting the varchar value '@.new_custid' to data
> type int.
> Thanks,
> Oded
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oxqp06OTGHA.1688@.TK2MSFTNGP11.phx.gbl...
>|||Uri
It works!
Thanks you very much
Oded
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uzbR9oXTGHA.5908@.TK2MSFTNGP14.phx.gbl...
> Yes, sure .
>
> Why did you put an quotation on '@.new_custid'? Is it VARCHAR(n) or
> INTEGER?
> Insert into tblCards (StoreID,CustomerID,CardNumber)
> Values('2',@.new_custid,'111')
>
> "Oded Dror" <odeddror@.cox.net> wrote in message
> news:u404$kVTGHA.4520@.TK2MSFTNGP10.phx.gbl...
>sql