通过存储过程插入/更新多个表 [英] inserting/updating multiple tables through stored procedures

问题描述

大家好，


我无法编写存储过程来插入和更新sql server 2005中的2个表。这是我的问题。

我有2个表格，包括以下列。

TableName：BENEFIT_PDF

1）DOCUMENT_CK

2）EFFECTIVE_DATE

3）PDF_FILE_NAME

4）PDF_DISPLAY_NAME

5）PDF_IMAGE


另一个TableName：PLAN_PDF

1）DOCUMENT_CK

2）PLAN_ID

3）状态


这里document_ck是自动生成的标识列，请参阅PLAN_PDF。

EFFECTIVE_DATE和PDF_FILE_NAME形成独特的索引。


第一个表将详细信息保存为pdf文件。（在PDF_IMAGE列中）


不止一个州与利益细节相关联。（这就是为什么我们有plan_pdf表）


就像2个州可以有相同的好处details.PLAN_ID在表中是唯一的。


我需要提供通过存储过程可以通过客户端填充这两个表的方式。

i我想知道我应该写一个sp来做这个还是我需要2个sp来完成这个任务。


我还需要随时提供用现有数据更新这些字段的方法。

我是存储过程的新手，我很困惑映射这些表并填充它们。


请帮帮我。


谢谢

Srini

解决方案

嗨Shrinivas。

你通常不会使用存储过程，虽然它当然可以完成。

存储可能会或可能不是您的最佳选择

很大程度上取决于您使用的是用户的前端。


要做到这一点存储过程，您的前端需要调用它并通过参数将值传递给存储过程。

展开 | 选择 | Wrap | 行号

尝试创建一个触发器，为你做级联插入...


- CK

你好Delerna，


如果我不使用存储过程，我需要通过我的应用程序更新这些表吗？


如果是这样我需要加入这两个表，并从我的应用程序中插入/更新。


你能发送sql查询加入这两个表。就像我提到的不止一个州可以有相同的福利细节。


我该怎么办？通过加入或任何其他方式？


请帮帮我。

Hi all,

I am having trouble to writing stored procedure to insert and update the 2 tables in sql server 2005. Here is my problem.

I have 2 tables with following columns.
TableName:BENEFIT_PDF
1)DOCUMENT_CK
2)EFFECTIVE_DATE
3)PDF_FILE_NAME
4)PDF_DISPLAY_NAME
5)PDF_IMAGE

another TableName: PLAN_PDF
1)DOCUMENT_CK
2)PLAN_ID
3)STATE

Here document_ck is the identity column which is autogenerated and refer to the PLAN_PDF.
EFFECTIVE_DATE and PDF_FILE_NAME FORM THE UNIQUE INDEX.

The first table stores the benefit detais as pdf file.(in the PDF_IMAGE column)

more than one state is associate with the benefitdetails.(thts why we have plan_pdf table)

like 2 states can have the same benefit details.PLAN_ID is unique in the table.

I need to provide a way to the client that he can able to populate these 2 tables through stored procedures.
i am wondering that should I write a single sp to do this or I need 2 sp''s to acomplish this task.

I aslo need to provide the way to update these fields with the existing data at any time.
I am new to storedprocedures and I am confusing in mapping these tables and populate them.

Please help me.

thanks
Srini

解决方案

Hi Shrinivas.
You wouldn''t normally use a stored procedure, although it certainly can be done.
Stored procs may or may not be your best choice
A lot depends on what you are using as the front end for your users.

To do it with a stored proc, your front end will need to call it and pass the values to the stored proc through parameters.

Expand|Select|Wrap|Line Numbers

Try creating a trigger that will do the cascade insert for you...

-- CK

Hi Delerna,

If I do not use the stored procedure, I need to update these tables through my application right?

If so I need to join these 2 tables and do the insert/update from my application.

Can you send me sql query to join these 2 tables. Like I mentioned more than one state can have the same benefit details.

How can I do it? through join or any other way?

Please help me.

这篇关于通过存储过程插入/更新多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！