Insert related data from 2 seperate tables into 1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert related data from 2 seperate tables into 1

Author

Topic

drenard
Starting Member

7 Posts

Posted - 2007-04-12 : 17:17:19

G'Day,

Can anyone show me an example of how to do the following.

I have 3 table "Customer", "Employee" & "Request"

I am having my users fill out the customer records
first with all the correct information needed, Name,
address, phone, etc. The employee table has 3 fields
and all the records have been populated with employee
information, Email, Name, Ext.

My goal is to have the user fill in 4 fields in the request
table, Customer, email, salesnum, amount and upon hittin the
save button not only create the new record in the request table
but match up the customer table and employee table and write
into the new record that information as well. This way
all the related information will be saved as 1 new record
in the request table.

I would really be thankful for any help or pointers on this..

Thanx.

drenard
Starting Member

7 Posts

Posted - 2007-04-12 : 17:57:05

Is the following even close to the ballpark ?

-set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pMyApp1Silly1Add]
@p_sono varchar(50),
@p_email varchar(50),
@p_status varchar(50),
@p_linkid nchar(10),
@p_test uniqueidentifier,
@p_grid_out int output
AS
BEGIN
INSERT
INTO [dbo].[silly1]
(
[sono],
[email],
[status],
[linkid],
[test]
)
VALUES
(
@p_sono,
@p_email,
@p_status,
@p_linkid,
@p_test
)

Select * from silly2
where silly1.customer = silly2.customer
union
select * from silly2
where silly1.email = silly3.email
union

SET @p_grid_out = SCOPE_IDENTITY()

END

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-12 : 22:08:23

Let's start from scratch.

3 tables; I put together a small script to hopfully assist. It didn't seem like you were using the right appoach. Let me know if you need further clarification.

[code]
create table tbl_Customer
(
CustomerID int not null identity(1,1),
CustName varchar(50) ,
Address varchar(50),
City varchar(50),
state varchar(25),
ZIP varchar(12),
Phone varchar(20)
)

create table tbl_Employee
(
EmployeeID int not null identity(1,1),
EmployeeInfo varchar(100) ,
EmpName varchar(50),
Ext varchar(20)
)

create table tbl_Request
(
RequestID int not null identity(1,1),
CustomerID int,
Email varchar(60) ,
SalesNo varchar(50),
EmployeeID int
)

declare @CustID int, @Email varchar(60),@SalesNo varchar(50),@EmployeeID Int

set @Email = 'test@test.com'
set @SalesNo = '123456'

--Insert data into a table
--This is just to show you the relation of data, you customer and Employee
--Info will already be in the table when using in a live application

insert tbl_Customer(CustName,Address,City,state,ZIP,Phone)
values('customer 1234','34343 no where st.', 'Bev hills','CA','90210','555-555-5555')

set @CustID = Scope_Identity()

insert into TBL_Employee(EmployeeInfo,EmpName,Ext)
values('test info','John Doe','12345')

set @EmployeeID = Scope_Identity()

Insert into Tbl_Request(CustomerID,Email,SalesNo,EmployeeID)
values (@CustID,@Email,@SalesNo,@EmployeeID)

--Retrieve data and relation, substitute the actual colum names for * to be selective
Select a.*,b.*,c.* from
Tbl_Request a inner join TBl_Employee b
on a.EmployeeID = b.EmployeeID
inner join TBL_Customer c
on a.CustomerID = c.CustomerID
[/Code]

drenard
Starting Member

7 Posts

Posted - 2007-04-12 : 23:35:41

Thank you,

I understand the structure..

Thanks very much..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources