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.
精彩评论