[dba-SQLServer] Adding Records to Joined Tables

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