Inserting datas from one table to another table

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 2000 Forums

Transact-SQL (2000)

Inserting datas from one table to another table

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-01-31 : 19:16:41

Hi,

I'am doing a project website,It has 2 sections namely recruiters and employers.For these 2 sections I have created 6 Tables each.I have 2 tables called recruiterinfo and employer_recruiterinfo for section 1 and section2 respectively.These Two are my base tables for those sections.Recruiterinfo and employer_recruiterinfo has a column called recruiter_id which is an identity column with increment seed 1.I have relationship with the other 5 tables through this identity column.If the I feel the user registered in section 1 should have registered in section 2 then I want to transfer his details to section 2 tables from section 1 tables.So I used the following query for transfering the details of the recruiterinfo table to employer_recruiterinfo table

Query 1:

insert employerinfo (salutation,recruiter_name,lastname,recruiter_email,recruiter_pwd,recruiter_designation,
enabled,hold,splitpurchased,dateofregistration,fresh,junior,middle,senior,rcxmailers)select salutation,recruiter_name,lastname,recruiter_email,recruiter_pwd,recruiter_designation,
enabled,hold,splitpurchased,dateofregistration,fresh,junior,middle,senior,rcxmailers
from recruiterinfo where recruiter_id=1033

This worked very much fine. Then I have to insert the values of companyinfo table to employer_companyinfo table.Since the rest of tables are related through the recruiter_id column I used the following query to know the recruiter_id value for the result of Query 1.I used the following Query for that.

Query 2:

select employerinfo.recruiter_id from employerinfo,recruiterinfo
where recruiterinfo.recruiter_email=employerinfo.recruiter_email
and recruiterinfo.recruiter_id=1033

The Above query gave me the result as 1370.Now to insert the datas of employers_companyinfo from companyinfo table,I thought of using the following query

Query 3:
insert employer_companyinfo (recuiter_id,company_name,address,city,state,pincode,phone,phone2,
fax,url,companyprofile)select recruiter_id,company_name,address,city,state,pincode,phone,phone2,
fax,url,companyprofile from companyinfo where recruiter_id=1033

If I use the above query then my employer_companyinfo table will have the recruiter_id value as 1033, But I want it to be 1370.Now what can I do so that I should be able to insert the values of companyinfo table to employer_companyinfo table and the employerinfo.recruiter_id should have the value fetched by Query 2.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-31 : 20:33:36

Karuna,

This is a perfect example for a Stored Procedure....

It should look something like this


Create Proc upNameYourSP
@RecruiterID int
as
Set NoCount On
declare @NewRecruiterID int

insert employerinfo (salutation,recruiter_name,lastname,recruiter_email,recruiter_pwd,recruiter_designation,
enabled,hold,splitpurchased,dateofregistration,fresh,junior,middle,senior,rcxmailers)select salutation,recruiter_name,lastname,recruiter_email,recruiter_pwd,recruiter_designation,
enabled,hold,splitpurchased,dateofregistration,fresh,junior,middle,senior,rcxmailers 
from recruiterinfo where recruiter_id=@RecruiterID

--This Assumes that there will be only 1 record returned
select @NewRecruiterID  = employerinfo.recruiter_id from employerinfo,recruiterinfo 
where recruiterinfo.recruiter_email=employerinfo.recruiter_email 
and recruiterinfo.recruiter_id=@RecruiterID

--Use the NewrecuiterID for the next query..
insert employer_companyinfo (recuiter_id,company_name,address,city,state,pincode,phone,phone2,
fax,url,companyprofile)select recruiter_id,company_name,address,city,state,pincode,phone,phone2,
fax,url,companyprofile from companyinfo where recruiter_id=@NewRecruiterID

go

You might also consider a BEGIN TRANSACTION block...

HTH

DavidM

Tomorrow is the same day as Today was the day before.

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-01-31 : 21:22:56

Hi,

I tried the storedproc...What happend is it inserted the datas of recruiterinfo to employerinfo but not companyinfo to employer_companyinfo...

If what I think is right this is what is happening when i execute the sp...

Create Proc changefromrectoemp
@RecruiterID int
as
Set NoCount On
declare @NewRecruiterID int
insert employerinfo (salutation,recruiter_name,lastname,recruiter_email,recruiter_pwd,
recruiter_designation,enabled,hold,splitpurchased,dateofregistration,
fresh,junior,middle,senior,rcxmailers)select salutation,recruiter_name,lastname,recruiter_email,recruiter_pwd,
recruiter_designation,enabled,hold,splitpurchased,dateofregistration,
fresh,junior,middle,senior,rcxmailers from recruiterinfo where
recruiter_id=@RecruiterID
--This Assumes that there will be only 1 record returned
select @NewRecruiterID = employerinfo.recruiter_id from
employerinfo,recruiterinfo where
recruiterinfo.recruiter_email=employerinfo.recruiter_email and
recruiterinfo.recruiter_id=@RecruiterID
--Use the NewrecuiterID for the next query..

Till the above sp works fine....
In this block

insert employer_companyinfo
(recruiter_id,company_name,address,city,state,pincode,phone,phone2,
fax,url,companyprofile)select
recruiter_id,company_name,address,city,state,pincode,phone,phone2,fax,
url,companyprofile from companyinfo where recruiter_id=@NewRecruiterID
go

what happens is it searches for the recruiter_id with value
NewRecruiterID in the companyinfo table...since I dont have any
records matching that value its not inserting the datas...This is
what happening in the SP I think...But What I want is I want to enter
the recruiter_id value in the employer_companyinfo table with the
NewRecruiterID and I want to insert the rest of the datas from the
recruiterinfo table which matches the criteria
recruiterinfo.recruiter_id=RecruiterID...

To achieve this what modification should I make to the above code...I
tried the below,But I got error.

insert employer_companyinfo
(recruiter_id,company_name,address,city,state,pincode,phone,phone2,
fax,url,companyprofile) values (1370,(select
company_name,address,city,state,pincode,phone,phone2,fax,url,
companyprofile from companyinfo where recruiter_id=@NewRecruiterID))

Karunakaran

Edited by - karuna on 01/31/2002 21:27:20

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-02-01 : 23:17:44

Hi,

I got the solution for my problem

.

I modified this block where i have give in italics and i got the result as i desired...

insert employer_companyinfo
(recruiter_id,company_name,address,city,state,pincode,phone,phone2,
fax,url,companyprofile)select
@NewRecruiterID,company_name,address,city,state,pincode,phone,phone2,fax,
url,companyprofile from companyinfo where recruiter_id=@RecruiterID

Thanks for your help BYRMOL....

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources