Stefan Motz
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:
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"
%>
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"
%>
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"
%>
INSERT INTO <destination table>
(<field list>)
SELECT <field list>
FROM <source table>
WHERE <conditions>