[dba-SQLServer] Adding Records to Joined Tables

David,

That appears to be an elegant solution. Since your variables use the @
prefix, I assume this code is clipped from a stored procedure, which should
give you great performance despite the nested Select. Should performance
become an issue, you might gain some by turning the nested Select into a
table variable. But I wouldn't bother if performance is not a problem.


On Tue, Apr 15, 2014 at 2:51 PM, David Emerson <newsgrps at dalyn.co.nz> wrote:

> Arthur,
>
> In my case the form had a couple of filter fields used to filter the
> recordset used for the form.  I didn't want a form/subform setup because
> the
> filters would have been for different main tables.  Here is the solution I
> came up with:
>
> SELECT dbo.tlkpFunderServiceCodeRate.*
> FROM dbo.tlkpFunderServiceCodeRate
> WHERE dbo.tlkpFunderServiceCodeRate.FunderServiceCodeIDNo IN
>         (SELECT dbo.tlkpFunderServiceCode.FunderServiceCodeID FROM
> dbo.tlkpFunderServiceCode
>                 WHERE (dbo.tlkpFunderServiceCode.FunderIDNo = @FunderIDNo
> OR
> @FunderIDNo = 0) AND
>                  (dbo.tlkpFunderServiceCode.FunderServiceCodeID =
> @FunderServiceCodeIDNo OR @FunderServiceCodeIDNo = 0))
> ORDER BY dbo.tlkpFunderServiceCodeRate.FunderServCodeRateStartDate
>
> The rates link back to a code which links back to a funder.  The two
> filters
> mean I could select all rates, all rates for a funder, or all rates for a
> code.
>
> Regards
>
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
>
> Regards
>
> David
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
> Fuller
> Sent: Tuesday, 15 April 2014 10:50 p.m.
> To: Discussion concerning MS SQL Server
> Subject: Re: [dba-SQLServer] Adding Records to Joined Tables
>
> David,
>
> I'm guessing that you want to do this with code rather than via a form,
> since the latter makes it so easy -- just your standard form+subform. Is
> that correct? If so, I still don't quite understand why you want to use a
> view. Your code will need to determine the parent PK first, then use a
> standard INSERT statement, passing the parent PK in, either by calling a
> stored procedure that accepts a parameter for the parent PK, or by building
> the INSERT statement in code and then executing. My preference would be the
> stored procedure.
>
> Please clarify why you're using a view to accomplish this.
>
> Arthur
> _______________________________________________
> 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
>
>


-- 
Arthur