Solved

Insert records from one table into another

Posted on 2014-07-25
4
308 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
Troubleshooting Methodology - steps 3 36
AWE-based memory on 32-bit servers 1 23
Storage Spaces 3 38
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/…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

912 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now