Sha1395
asked on
Sql Join Insert Query
Hi,
I have two table Phone and ADUser.
ADUser have all the data,am trying to write store procedure to retireve data from ADUser Table (only phone number) and insert in to Phone table based on employee number.
Am not sure exactly how write write store proc for that.Any help would be great and thanks in Advance
I have two table Phone and ADUser.
ADUser have all the data,am trying to write store procedure to retireve data from ADUser Table (only phone number) and insert in to Phone table based on employee number.
Am not sure exactly how write write store proc for that.Any help would be great and thanks in Advance
ASKER
thanks for your comment
i already wrote a store procedure for Insert and update but i don't know how to use "Join" in Insert and update.
i already wrote a store procedure for Insert and update but i don't know how to use "Join" in Insert and update.
On which condition basis you need to enter phone data into Phone table.
Or it mean you need all users phone number into Phone table.
Or it mean you need all users phone number into Phone table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
insert into phone (employeenumber, phonenumber)
select employeenmber, phonenumber
from ADUser
ASKER
Sorry for the late reply.
here is my inline command query for insert
here is my inline command query for insert
cmd.CommandText = " INSERT INTO Phone (employeeno, PhoneNumber,CreatedBy,UpdatedBy)Select " + "employeeno" + "," + "phoneNo" + "," + "'" + "dbo" + "'" + "," + "'" + "dbo" + "'" + "from ADUser where employeeno=" + item.EmployeeNumber;
cmd.ExecuteNonQuery();
ASKER
Am trying to write a store procedure
already i have a store proc for insert an update but i want change my query based on this below
already i have a store proc for insert an update but i want change my query based on this below
[
" INSERT INTO Phone (employeeno, PhoneNumber,CreatedBy,UpdatedBy)Select " + "employeeno" + "," + "phoneNo" + "," + "'" + "dbo" + "'" + "," + "'" + "dbo" + "'" + "from ADUser where employeeno=" + item.EmployeeNumber;
/code]
store proc
[code
IF EXISTS(SELECT 'True' FROM DEV.[dbo].[Phone] WHERE EmployeeNo = @EmployeeNo)
BEGIN
DECLARE @CONTEXT_INFO varchar(100)
select @CONTEXT_INFO = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)
update DEV.[dbo].[Phone] set EmployeeNo = @EmployeeNo,
PhoneNumber=@PhoneNumber,
UpdatedBy=@CONTEXT_INFO,
DateUpdated=getdate()
where EmployeeNo = @EmployeeNo
END
ELSE
BEGIN
select @CONTEXT_INFO = COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), CURRENT_USER)
INSERT INTO DEV.[dbo].[Phone]
([EmployeeNo]
,[CreatedBy]
,[UpdatedBy]
,[PhoneNumber])
VALUES
(@EmployeeNo,
@CONTEXT_INFO,
@CONTEXT_INFO,
@PhoneNumber)
END
]
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/09cdc1e0-7d91-41a6-b605-3b0c758c4399