开发者

Insert a Join Statement - (Insert Data to Multiple Tables) - C#/SQL/T-SQL/.NET

I have a Winform that has fields need to be filled by a user. All the fields doesn't belong to one table, the data will go to Customer table and CustomerPhone table, so i decided to do multiple in开发者_如何学Goserts. I will insert appropriate data to CustomerPhone first then Insert the rest data to Customer table.

Is it possible to Join an Insert OR Insert a Join? If show me a rough sample, i will be grateful.

Many Thanks


Strictly speaking, you can chain inserts and updates in a single statement using the OUTPUT clause. For example, the code bellow inserts at once into two distinct tables:

create table A (
    id_a int not null identity(1,1) primary key,
    name varchar(100))
create table B (
    id_b int not null identity(1,1) primary key,
    id_a int null,
    name_hash varbinary(16));

insert into A (name)
output inserted.id_a, hashbytes('MD5', inserted.name)
into B (id_a, name_hash)
values ('Jonathan Doe')

select * from A
select * from B


If you're asking whether you can somehow insert into two tables with one SQL statement: No, you need to do the two separate insert statements.

If you're asking something else, please elaborate..


You can make a view which has those columns and do an insert to the view. Normally, a view which combines multiple tables will not be updateable - however, you can make an instead of trigger which inserts into both tables and/or enforces your business logic.

Here's another link.

This can be a very powerful tool if you use it in an organized way to make a layer of views for both selects and unserts/updates.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜