Link to home
Start Free TrialLog in
Avatar of Stefan Motz
Stefan MotzFlag for United States of America

asked on

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

Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

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>
Avatar of Stefan Motz

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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"
%>