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

Question

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 inserts. 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

Answer 1

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..

Answer 2

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

Answer 3

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.