Looping columns in instead of trigger

I have the following view (vProcurementPlan)

SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
dbo.tblRequisition.ROS AS ROS,
dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID AS
ProjectID
FROM dbo.tblProcurementPlan INNER JOIN
dbo.tblRequisition ON
dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID

If I try inserting a record from Access it complains about multiple base
tables, I'm happy to write an "instead of" trigger and handle the 5 columns
from tblRequisition but as it contains all columns from tblProcurementPlan I
don't want to have to list them separately in any insert or update
statement.

The idea is that a record will be inserted into both tables simultaneously
upon insert to the view.Trev@.Work (bouncer@.localhost) writes:
> I have the following view (vProcurementPlan)
> SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
> ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
> dbo.tblRequisition.ROS AS ROS,
> dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID
> AS ProjectID
> FROM dbo.tblProcurementPlan INNER JOIN
> dbo.tblRequisition ON
> dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID
> If I try inserting a record from Access it complains about multiple base
> tables, I'm happy to write an "instead of" trigger and handle the 5
> columns from tblRequisition but as it contains all columns from
> tblProcurementPlan I don't want to have to list them separately in any
> insert or update statement.

I am afraid you don't have much choice.

Besides, in my opinion SELECT * does not belong in production code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try not using the * and referencing all the column names explicitly.
It should insert without a problem.
eg
SELECT pp.RequisitionID ppreqid, pp.ReqNo ppReqNo, pp.Am AS Am, ppROS
AS ROS, pp.ActivityID ppAcID, pp.ProjectID ppProjID,rq.RequisitionID
rqReqId, rq.ReqNo rqReqNo, rq.Am Am, rqROS ROS, rq.ActivityID rqAcID,
rq.ProjectID rqProjID
FROM dbo.tblProcurementPlan pp INNER JOIN dbo.tblRequisition rq ON
pp.RequisitionID = rq.RequisitionID

Pachydermitis

"Trev@.Work" <bouncer@.localhost> wrote in message news:<3fddeed0$0$13894$afc38c87@.news.easynet.co.uk>...
> I have the following view (vProcurementPlan)
> SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
> ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
> dbo.tblRequisition.ROS AS ROS,
> dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID AS
> ProjectID
> FROM dbo.tblProcurementPlan INNER JOIN
> dbo.tblRequisition ON
> dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID
> If I try inserting a record from Access it complains about multiple base
> tables, I'm happy to write an "instead of" trigger and handle the 5 columns
> from tblRequisition but as it contains all columns from tblProcurementPlan I
> don't want to have to list them separately in any insert or update
> statement.
> The idea is that a record will be inserted into both tables simultaneously
> upon insert to the view.

Loop through table using column id instead of name

I need to generate a comma delimited file that is a copy of a row in a table
.
I would like to be able to use the column id instead of the column name to d
o
this. i would like to be able to pass a table name to the stored procedure
and not have to have the column names hard coded.
Is this even possible?
I know it is possible to do in C++ or VB, but can it be done as a stored
procedure.
Thanks for your help,
KenIt seems to me it's much more efficient to do it in the calling application.
String and file handling isn't T-SQL's strong suit.
Ken Holzer wrote:
> I need to generate a comma delimited file that is a copy of a row in a tab
le.
> I would like to be able to use the column id instead of the column name to
do
> this. i would like to be able to pass a table name to the stored procedure
> and not have to have the column names hard coded.
> Is this even possible?
> I know it is possible to do in C++ or VB, but can it be done as a stored
> procedure.
> Thanks for your help,
> Ken

Looking For Examples Of Stored Procedures in C# or VB.Net.

Hi,

Does anyone have any links to examples of stored procedures written in C# or VB.Net instead of T-Sql. I'd like to see how it looks and works. Thanks! Ken.Hi Ken,

Here are some links for writing stored procedures in .Net compatible language.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlclrguidance.asp
http://www.sqlteam.com/item.asp?ItemID=21927

If you have any concerns please revert back, i will send you the sample code.

Best Regards,
Swamy|||The SQL Server Hands On Labs have examples:

http://msdn.microsoft.com/sql/2005/2005labs/default.aspx

See the SQL CLR lab.|||There are several examples available if you installed the samples and documentation. They are located in the C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR by default.