[dba-SQLServer] Adding Records to Joined Tables

Thanks again Arthur.  Yes the code is from a stored procedure, and because
the actual number of records will be relatively small there shouldn't be a
problem with performance.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Wednesday, 16 April 2014 7:54 a.m.
To: Discussion concerning MS SQL Server
Subject: Re: [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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com