Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Insert records from one table into another

Posted on 2014-07-25
4
311 Views
Last Modified: 2014-07-25
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

0
Comment
Question by:romsom
  • 2
  • 2
4 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40220008
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
 

Author Comment

by:romsom
ID: 40220018
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
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 40220045
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
 

Author Closing Comment

by:romsom
ID: 40220083
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL BCP Extra Lines Between Records 2 23
Downside of adding characters set in ASP pages 6 29
MS SQL Update query with connected table data 3 33
Negative isnull? 3 14
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question