[dba-SQLServer] tsql help - propogate that pk

Thanks Shamil for your time.  I was actually hoping for a solution that is 
quite simple (perhaps I was overlooking something very obvious).  I thought 
that there was a more simple solution because the view insert (where it 
affects multiple tables) works eloquently in MS Access but not in Sql 
Server.

Regards,
Billy




>From: "Shamil Salakhetdinov" <shamil at users.mns.ru>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: "dba-SQLServer" <dba-SQLServer at databaseadvisors.com>
>Subject: Re: [dba-SQLServer] tsql help - propogate that pk
>Date: Fri, 16 Jul 2004 02:32:23 +0400
>
>Hi Billy,
>
>You didn't ask about inserting several rows using instead of insert
>trigger, did you? :)
>
>Well, I think it can be done without cursors but we have to assume that
>there is no UNIVERSAL solution for such a case - I mean that for each
>certain case the cursor-less solution with instead of triggers could
>have quite different implementation - as always in programming an
>"optimal" solution is a balance of interests, a compromise:
>
>So if we assume that when several rows are inserted against my sample
>view then this means that:
>
>1. they all belong to one order
>2. product name is unique
>
>Then one of the the solutons is:
>
>- insert all rows into tblProduct from inserted if there is no yet such
>product,
>- insert one row into tblOrder from inserted,
>- insert all rows into tblOrdItem from inserted joined to tblProduct on
>ProdName
>
>See SQL script in P.S. of this message.
>
>If you need something more complicated - e.g. inserting several orders
>against view like in the current sample avoiding cursors' usage by all
>means - if you need that - then you're welcome to hire me to do that and
>other tricky work! :)
>
>HTH,
>Shamil
>
>P.S. Solution:
>
>
>CREATE TRIGGER [qselInsert] ON [dbo].[qselOrder]
>INSTEAD OF INSERT
>AS
>declare @ordID int
>
>Set NoCount On
>
>-- /////////////////////////////////////////////////
>Insert Into [dbo].[tblProduct]
>(
>    [prodName]
>  , [prodCurPrice]
>)
>select
>    prodName
>  , prodCurPrice
>from inserted where (prodName not in (select prodName from tblProduct))
>
>-- /////////////////////////////////////////////////
>Insert Into [dbo].[tblOrder]
>(
>    [ordDate]
>  , [custId]
>  , [ordCurTotal]
>)
>select top 1
>    ordDate
>  , custId
>  , ordCurTotal
>from inserted
>
>Set @ordID = Cast(SCOPE_IDENTITY() As [int])
>-- /////////////////////////////////////////////////
>Insert Into [dbo].[tblOrderItem]
>(
>    [ordId]
>  , [prodId]
>  , [oitAmount]
>)
>select
>    @ordId
>  , p.prodId
>  , oitAmount
>from inserted i inner join tblProduct p on i.prodName = p.prodName
>-- /////////////////////////////////////////////////
>
>Set NoCount Off
>----- Original Message -----
>From: "Billy Pang" <tuxedo_man at hotmail.com>
>To: <dba-sqlserver at databaseadvisors.com>
>Sent: Friday, July 16, 2004 1:18 AM
>Subject: Re: [dba-SQLServer] tsql help - propogate that pk
>
>
> > Hi Shamil:
> >
> > Thanks for your assistance.  Your solution will only work based on the
> > assumption that records can only be inserted into tblOrder one at a
>time; if
> > insert statement for tblOrder table has "select" as source, then the
> > tblorderid fk in tblorderitem table may contain incorrect value.
> >
> > Using below example as example, execute the following insert
>statements:
> >
> > <--- cut here --->
> > insert into qselOrder(ordDate, custId, ordCurTotal, prodName,
>prodCurPrice,
> > oitAmount)
> > values(getdate(),1,2,'product name',4,5)
> >
> > insert into qselOrder(ordDate, custId, ordCurTotal, prodName,
>prodCurPrice,
> > oitAmount)
> > select ordDate, custId, ordCurTotal, prodName, prodCurPrice, oitAmount
>from
> > qselOrder
> >
> > insert into qselOrder(ordDate, custId, ordCurTotal, prodName,
>prodCurPrice,
> > oitAmount)
> > select ordDate, custId, ordCurTotal, prodName, prodCurPrice, oitAmount
>from
> > qselOrder
> > <--- cut here --->
> >
> > assuming tblorder table is empty from very beginning, it will insert
>four
> > records into tblorder table as expected and four records into
>tblorderitems
> > table as expected.  however, the fourth record in tblorder table does
>not
> > have any related records in tblorderitems.
> >
> > One way around this problem is to load the values within the
>"inserted"
> > pseudo table into a table variable (assuming ss2k) or temporary table
>and
> > loop through them using database cursor.  However, I am wondering if
>there
> > is another way.
> >
> > sincerely,
> > Billy
> >
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
Add photos to your e-mail with MSN Premium. Get 2 months FREE*  
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines