Home » Asp.netRSS

ASP.NET insertion and joining data between tables

I have the following tables:

1.) PRODUCT_INFORMATION

2.) CUSTOMERS

3.) DISTRIBUTORS

4.) PRODUCT_NAME

5.) CONTROLLER

Each table has a primary key assigned. The customers table has an ID column and a CUSTOMER_NAME column with unique index property set to avoid any duplication. How do I insert values in the product_information table that has a foreign key column from the CUSTOMERS table if we insert a value in the CUSTOMER_NAME field of the CUSTOMERS table. I want to know how can I create a join between all the tables so that when I insert values in individual tables they are joined with a serial number column in the PRODUCT_INFORMATION  table?

Please suggest!!

 

1 Answer Found

 

Answer 1

abhishruts:
The customers table has an ID column and a CUSTOMER_NAME column with unique index property set to avoid any duplication. How do I insert values in the product_information table that has a foreign key column from the CUSTOMERS table if we insert a value in the CUSTOMER_NAME field of the CUSTOMERS table.

you can do this way: make ID column in the Customers Table a primary and identity. When you insert any value into the Customers table, you can get the latest CustomerID and insert a record in ProductName.

Declare @Customer as table
       (ID int primary key identity, 
        customer_Name varchar(10) unique);

Declare @ProductName 
as table(ID int identity,Product_Name varchar(100), 
         customer_Name varchar(10) unique
 );

-- Insert a new customer
insert into @Customer values('My Name 1');

-- Insert Latest Customer into Product Names Table
insert into @ProductName
    select 'My Product Name 1',
           customer_Name     
  from @Customer 
where ID=@@IDENTITY

select * from @Customer;

select * from @ProductName

That inserts a row into Customer and then inserts a new record into product name with the newly inserted Customer Name. You can apply same logic to other tables  as well.

abhishruts:
how can I create a join between all the tables so that when I insert values in individual tables they are joined with a serial number column in the PRODUCT_INFORMATION  table?

Are you taking about a trigger to insert a record into Product Information table when a new customer is created?

http://www.sqlteam.com/article/an-introduction-to-triggers-part-i


 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter