Insert records from one table into another

Hi Experts,
I'm trying to insert records into Contact_Table from Employees, but only if the record is not already in Contact_Table. For some reason my query always times out after inserting about 3000 records. I was wondering if using a stored procedure would be more efficient. If yes, I would need some help writing the stored procedure and calling it from my classic ASP page. This is my code:
<%
sql = "select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num from Employees WHERE Emp_ID not in ( select Emp_Id from Contact_Table)"
set rs= Conn.execute(sql)

do while not rs.eof

sEmp_Id=rs("Emp_Id")
sLast_Name=replace(rs("Last_Name"),"'","''")
sFirst_Name=replace(rs("First_Name"),"'","''")

if not isnull(rs("Email_Id")) then
sEmail_Id=replace(rs("Email_Id"),"'","''")
else
sEmail_Id=""
end if

sWork_Loc_Cd=rs("Work_Loc_Cd")
sWork_Phone_Num=rs("Work_Phone_Num")


strSQL = "INSERT INTO Contact_Table (Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num) VALUES ('"& sEmp_Id &"', '"& sLast_Name &"', '"& sFirst_Name &"', '"& sEmail_Id &"', '"& sWork_Loc_Cd &"', '"& sWork_Phone_Num &"')"

conn.Execute(strSQL)
rs.movenext
loop

'Redirect to the UPDATED page
Response.Redirect "Employees.asp"
%>

Open in new window

romsomIT DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
You can perform this same action in a single insert statement:
INSERT INTO <destination table>
(<field list>)
SELECT <field list>
FROM <source table>
WHERE <conditions>
0
romsomIT DeveloperAuthor Commented:
Shall I just do this?

INSERT INTO Contact_Table
(Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num)
SELECT Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num
FROM Employees
WHERE Emp_ID not in ( select Emp_Id from Contact_Table)

Open in new window

0
Shaun KlineLead Software EngineerCommented:
In your code, you are changing null values for email to an empty string. If you need to do this, you'll need to change the Email_ID in the SELECT statement to ISNULL(Email_ID, '').

You can also use the EXISTS clause instead of NOT IN in the WHERE clause:
WHERE NOT EXISTS (SELECT 1 FROM Contact_Table WHERE Emp_ID = Employees.Emp_ID)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
romsomIT DeveloperAuthor Commented:
I tried the first solution but it still timed out. After that I tried EXIST and it worked fine.
Thank you very much for your help.

This is the code I used:

<%
sql = "INSERT INTO Contact_Table (Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num) SELECT Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num FROM Employees e WHERE not exists(select Emp_Id from Contact_Table where emp_id <> e.emp_id)"
set rs= Conn.execute(sql)

'Redirect to the UPDATED page
Response.Redirect "Employees.asp"
%>
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.