asked on

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

SOLUTION

Philip Portnoy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stefan Motz

ASKER

Thanks for the quick response. I'm working on it. I'll be back with the results.

ASKER CERTIFIED SOLUTION

Big Monty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stefan Motz

ASKER

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

Incorrect syntax near the keyword 'from'

Big Monty

can you post lines 1-6?

Stefan Motz

ASKER

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

Big Monty

if you run the sql on line 2 directly in SSMS like I suggested originally, do you get any records returned?

Stefan Motz

ASKER

I ran it in SSMS but no rows were returned

Big Monty

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

Stefan Motz

ASKER

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.

Stefan Motz

ASKER

<%
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

SOLUTION

Big Monty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stefan Motz

ASKER

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.

Big Monty

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