[dba-SQLServer] tsql help - propogate that pk

Billy Pang tuxedo_man at hotmail.com
Fri Jul 16 16:24:58 CDT 2004 

Hi Shamil:
I didn't think it was possible anyways.
I was really racking my brain trying to figure out it out.
I had first concluded it was not possible.
However, it is always more prudent to ask peers in same field of work for 
second opinion.
This was a good thread :)

Sincerely,
Billy


>From: "Shamil Salakhetdinov" <shamil at users.mns.ru>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] tsql help - propogate that pk
>Date: Sat, 17 Jul 2004 00:23:32 +0400
>
> > If SQL  Server is able to immitate this behavior (without
> > using scope_identity(), of course :) ), then I am would
> > like to know.
>Billy,
>
>I think it can't - sorry I can't help here.
>
>Shamil
>
>----- Original Message -----
>From: "Billy Pang" <tuxedo_man at hotmail.com>
>To: <dba-sqlserver at databaseadvisors.com>
>Sent: Saturday, July 17, 2004 12:05 AM
>Subject: Re: [dba-SQLServer] tsql help - propogate that pk
>
>
> > Hi Shamil:
> >
> > The sentence from BOL is absolutely correct.  But it does not give me
>what I
> > want; I need to have "identity" values moved from one table to another
>after
> > a view insert.  There is no doubt that a view can update multiple base
> > tables; there is no question about that; and it is possible to
>reference
> > them unambigiously; however, the fact that it is an "instead of
>insert", it
> > is not possible to reference any identity values generated from the
>view
> > (without using scope_identity()).  This would not be a problem if it
>was
> > possible to create an "After" trigger on a view.
> >
> > And yes, it works eloquently in Access.  For demonstration purposes,
>within
> > Access, create two tables with following schema:
> >
> > tablea
> > tablea_id autonumber
> > tablea_value text(50)
> >
> > tableb
> > tableb_id autonumber
> > tableb_tablea_id number long
> > tableb_value text(50)
> >
> > then create a query called qryInsert with the following source:
> >
> > SELECT tablea.tablea_id, tableb.tableb_id, tablea.tablea_value,
> > tableb.tableb_value, tableb.tableb_tablea_id, tablea.tablea_id
> > FROM tablea INNER JOIN tableb ON tablea.tablea_id =
>tableb.tableb_tablea_id;
> >
> > then create and execute the following query:
> >
> > insert into qryInsert(tablea_value, tableb_value) values('a','b')
> >
> > followed by:
> >
> > insert into qryInsert(tablea_value, tableb_value)
> > select tablea_value, tableb_value
> > from qryInsert
> >
> > and again..
> >
> > insert into qryInsert(tablea_value, tableb_value)
> > select tablea_value, tableb_value
> > from qryInsert
> >
> > and you will find four records in tablea as expected, four records in
>tableb
> > as expected and all four records in tablea have related records in
>tableb.
> >
> > Access was able to do this without any database triggers.  And Access
>was
> > able to accommodate "view inserts" that have SELECT as source.  If SQL
> > Server is able to immitate this behavior (without using
>scope_identity(), of
> > course :) ), then I am would like to know.
> >
> > Kindest regards,
> > Billy
> >
> > >From: "Shamil Salakhetdinov" <shamil at users.mns.ru>
> > >Reply-To: dba-sqlserver at databaseadvisors.com
> > >To: dba-sqlserver at databaseadvisors.com
> > >Subject: Re: [dba-SQLServer] tsql help - propogate that pk
> > >Date: Fri, 16 Jul 2004 22:22:25 +0400
> > >
> > >Billy,
> > >
> > >What about that sentence from BOL?:
> > >
> > >"SQL Server 2000
> > >Updatable views can modify more than one table involved in the view.
>The
> > >DELETE, INSERT, and UPDATE statements can reference a view as long as
> > >SQL Server can translate the user's update request unambiguously to
> > >updates in the base tables referenced in the view's definition.
> > >Expect differences in behavior when working with updatable views with
> > >more than one table involved in the DELETE, INSERT, or UPDATE
> > >statements."
> > >
> > > > I was actually hoping for a solution that is
> > > > quite simple
> > >For me the solution looks simple. I called it tricky only because
>it's
> > >not obvious when starting to work with instead of triggers.
> > >You can also use a simple stored procedure to fullfill your task...
> > >
> > > > the view insert (where it
> > > > affects multiple tables) works eloquently in MS Access but not in
>Sql
> > > > Server.
> > >Are you sure it will work in MS Access for your task?  (This question
>is
> > >not an invitation to MS Access vs. MS SQL2000 discussion)
> > >
> > >Shamil
> > >
> > >----- Original Message -----
> > >From: "Billy Pang" <tuxedo_man at hotmail.com>
> > >To: <dba-sqlserver at databaseadvisors.com>
> > >Sent: Friday, July 16, 2004 3:18 AM
> > >Subject: Re: [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&S
>U=http://hotmail.com/enca&HL=Market_MSNIS_Taglines
> > > >
> > > > _______________________________________________
> > > > dba-SQLServer mailing list
> > > > dba-SQLServer at databaseadvisors.com
> > > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > > http://www.databaseadvisors.com
> > > >
> > >
> > >_______________________________________________
> > >dba-SQLServer mailing list
> > >dba-SQLServer at databaseadvisors.com
> > >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > >http://www.databaseadvisors.com
> > >
> >
> > _________________________________________________________________
> > MSN Premium helps eliminate e-mail viruses. 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
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
STOP MORE SPAM with the MSN Premium and 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

More information about the dba-SQLServer mailing list