Update one table from an other table in classic ASP

Hi Experts,
I'm adding records to the New_Employees table from the Employees table, but only if the Emp_Id is not already present in the New_Employees table. I am using a button to execute:

<form action="UpdateEmployees.asp">
    <input type="submit" value="Update Employee List">
</form>

The problem is that I have thousands of rows and it times out in the middle of the process, after transferring a couple of hundred records. The button has to be pressed several times in order to transfer all missing employees. I was wondering if there is a better method to perform this task. Maybe using stored procedure would be more efficient? If yes, may I ask for your help to write the stored procedure and call it from my classic asp page? I don't have too much experience with writing stored procedures.
This is my code:
<%
rs1="select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt from New_Employees order by ID"
set rs1= Conn.execute(rs1)

rs2="select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt from Employees order by ID"
set rs2= Conn.execute(rs2)

rs2.movefirst

do while not rs2.eof

New_Emp_Id =  rs2("Emp_Id")

rsSFO= "select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt from New_Employees where Emp_Id='" & New_Emp_Id & "'"
set rsSFO= Conn.execute(rsSFO)

sEmp_Id=rs2("Emp_Id")
sLast_Name=replace(rs2("Last_Name"),"'","''")
sFirst_Name=replace(rs2("First_Name"),"'","''")
if not isnull(rs2("Email_Id")) then
sEmail_Id=replace(rs2("Email_Id"),"'","''")
else
sEmail_Id=""
end if
sWork_Loc_Cd=rs2("Work_Loc_Cd")
sWork_Phone_Num=rs2("Work_Phone_Num")
sDivision_Dscr=replace(rs2("Division_Dscr"),"'","''")
sPosition_Title=replace(rs2("Position_Title"),"'","''")
sMail_Cd_Dscr=rs2("Mail_Cd")
sAdj_Svc_Dt=rs2("Adj_Svc_Dt")

if rsSFO.eof then

strSQL = "INSERT INTO New_Employees (Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt) VALUES ('"& sEmp_Id &"', '"& sLast_Name &"', '"& sFirst_Name &"', '"& sEmail_Id &"', '"& sWork_Loc_Cd &"', '"& sWork_Phone_Num &"', '"& sDivision_Dscr &"', '"& sPosition_Title &"', '"& sMail_Cd &"', '"& sAdj_Svc_Dt &"')"

conn.Execute(strSQL)
end if

rs2.movenext
loop

'Redirect to the UPDATED page
Response.Redirect "New_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.

Philip PortnoySr. MS SQL DBA and Technical Account ManagerCommented:
I know nothing about ASP, but from the SQL standpoint you're actually doing it in a completely strange way.
Stored procedure is a good idea.

I'm not going to write the whole thing for you, but here's main thing, make sure that datatypes are right and list all the fields:

CREATE PROC sp_add_new_employee
@Emp_ID int,
@Last_Name nvarchar(max),
@First_Name nvarchar(max),
...
@Adj_Svc_Dt nvarchar(max)
AS
IF @Emp_ID NOT IN (SELECT DISTINCT EMP_ID FROM NEW_EMPLOYEES)
BEGIN
INSERT INTO New_Employees (@Emp_ID, @Last_Name..., @Adj_Svc_Dt)
END

Open in new window


And use following code to call your stored proc from the ASP app:

strSQL = "INSERT INTO New_Employees VALUES ('"& sEmp_Id &"', '"& sLast_Name &"', '"& sFirst_Name &"', '"& sEmail_Id &"', '"& sWork_Loc_Cd &"', '"& sWork_Phone_Num &"', '"& sDivision_Dscr &"', '"& sPosition_Title &"', '"& sMail_Cd &"', '"& sAdj_Svc_Dt &"')"

Open in new window

0
romsomIT DeveloperAuthor Commented:
Thanks for the quick response. I'm working on it. I'll be back with the results.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
hello again :)

i always try to use stored procedures when doing DB intensive actions, however in this case, let's see how fast the code below runs. if it still has poor performance, we can easily convert it over.

I changed the logic a bit, have a look and ask questions on anything you don't understand

<%
sql = "select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt " _
	& "from New_Employees " _
	& "where Emp_ID not in ( select Emp_ID order by ID from Employees )"
set rs1= Conn.execute(sql)

'-- don't need these 3 lines, comment them out
'rs2="select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt from Employees order by ID"
'set rs2= Conn.execute(rs2)
'rs2.movefirst

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")
	sDivision_Dscr=replace(rs("Division_Dscr"),"'","''")
	sPosition_Title=replace(rs("Position_Title"),"'","''")
	sMail_Cd_Dscr=rs("Mail_Cd")
	sAdj_Svc_Dt=rs("Adj_Svc_Dt")

	strSQL = "INSERT INTO New_Employees (Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt) VALUES ('"& sEmp_Id &"', '"& sLast_Name &"', '"& sFirst_Name &"', '"& sEmail_Id &"', '"& sWork_Loc_Cd &"', '"& sWork_Phone_Num &"', '"& sDivision_Dscr &"', '"& sPosition_Title &"', '"& sMail_Cd &"', '"& sAdj_Svc_Dt &"')"

	conn.Execute(strSQL)

	rs.movenext
loop

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

Open in new window


for now, we'll build a sql string to make sure the process is working properly. If it is, I recommend switching it over to a parameterized query, which will help ensure proper data is passed over. for now, let's see if this works.

Before executing any asp code, can you run the sql statement on line 2 directly in SSMS and make sure you get the proper records?
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

romsomIT DeveloperAuthor Commented:
Thanks Big Monty, now I get this error on line 6:

Incorrect syntax near the keyword 'from'
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you post lines 1-6?
0
romsomIT DeveloperAuthor Commented:
I did this, but now it won't transfer any data
<%
sql = "select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt from New_Employees where Emp_ID not in ( select Emp_Id from Employees )"
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")
	sDivision_Dscr=replace(rs("Division_Dscr"),"'","''")
	sPosition_Title=replace(rs("Position_Title"),"'","''")
	sMail_Cd_Dscr=rs("Mail_Cd")
	sAdj_Svc_Dt=rs("Adj_Svc_Dt")

	strSQL = "INSERT INTO New_Employees (Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt) VALUES ('"& sEmp_Id &"', '"& sLast_Name &"', '"& sFirst_Name &"', '"& sEmail_Id &"', '"& sWork_Loc_Cd &"', '"& sWork_Phone_Num &"', '"& sDivision_Dscr &"', '"& sPosition_Title &"', '"& sMail_Cd &"', '"& sAdj_Svc_Dt &"')"

	conn.Execute(strSQL)

	rs.movenext
loop

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

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if you run the sql on line 2 directly in SSMS like I suggested originally, do you get any records returned?
0
romsomIT DeveloperAuthor Commented:
I ran it in SSMS but no rows were returned
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
that indicates there are no new Emp_ID's, meaning it can't find any any Emp_ID's in the new table that is not already in the original table.

Make sure you have some unique Emp_ID's in the new employee table
0
romsomIT DeveloperAuthor Commented:
I think the table names have to be reversed. I'd like to insert From Employees to New_Employees, but only if the Emp_Id is not in New_Employees already. So I made the following change and ran it. About 3830 records were inserted, the it timed out giving me an error message:
Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
0
romsomIT DeveloperAuthor Commented:
<%
sql = "select Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt from Employees where Emp_ID not in ( select Emp_Id from New_Employees )"
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")
	sDivision_Dscr=replace(rs("Division_Dscr"),"'","''")
	sPosition_Title=replace(rs("Position_Title"),"'","''")
	sMail_Cd_Dscr=rs("Mail_Cd")
	sAdj_Svc_Dt=rs("Adj_Svc_Dt")

	strSQL = "INSERT INTO New_Employees (Emp_Id, Last_Name, First_Name, Email_Id, Work_Loc_Cd, Work_Phone_Num, Division_Dscr, Position_Title, Mail_Cd, Adj_Svc_Dt) VALUES ('"& sEmp_Id &"', '"& sLast_Name &"', '"& sFirst_Name &"', '"& sEmail_Id &"', '"& sWork_Loc_Cd &"', '"& sWork_Phone_Num &"', '"& sDivision_Dscr &"', '"& sPosition_Title &"', '"& sMail_Cd &"', '"& sAdj_Svc_Dt &"')"

	conn.Execute(strSQL)

	rs.movenext
loop

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

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you're right, I had the table logic backwards...

you have a few options here, depending on what you're looking to do....

Are you concerned about it taking a long time (assuming we can get it to finish without an error)? if the answer is no, then you can increase the Server.ScriptTimeOut property by doing something like:

Line 1: Server.ScriptTimeOut = 600

this would set the script timeout to 600 seconds, which is 10 minutes. During this time, the page will just look like it's doing nothing, it'll just look like the page is loading and loading and loading and....

not a good solution if a lot of people are going to access the page, and there are ways to display to display some kind of message stating the page is loading, please wait... if only a handful of people are using the page, this would be the easiest.

if not, we can move it over to a stored procedure...I'm going to be wrapped up for awhile, but could possibly get to it later if no one else chimes in
0
romsomIT DeveloperAuthor Commented:
I guess the stored procedure would be the best solution. Your code is much better than the one I used before, thank you so much for your help. I'm trying to write the stored procedure, and I'll post another question while I'm doing it.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
glad I could help :)

if you switch over to a SP, you'll want to load all of the ID's into a temp table, along with the other columns you want to do the insert on, and then loop through each record to do the insert. Below is a very basic example on how to do that:

Select *
Into   #Temp
From   ATable

Declare @Id int

While (Select Count(*) From #Temp) > 0
Begin

    Select Top 1 @Id = Id From #Temp

    --Do some processing here

    Delete #Temp Where Id = @Id

End

Open in new window

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.