Insert data from another table to a table

[jowan]

How do I insert to a table with data retrieve from another table? I hv the code below but couldn't work.What I have done wrong?Thanks in advance.

While not rs.EOF
No=0
Sql="SELECT * FROM X "
No=No + 1
Set rs=con.Exeecute(Sql)

For i=No to rs.EOF
If rs.EOF

Sql1="INSERT INTO Y (yID, yName) values('"&rs("IDX") &"', '"&rs( "nameX" )&"', '"&("customer")&"')"
Set rs=con.Exeecute(Sql1)
End If
Else
Response.redirect("default.html")
Next
Rs.MoveNext
Wend

[duckhunter]

A few problems. You can't reuse the "rs" object while reading from it. Just DB.Execute the inserts.

While Not rs.EOF
Sql1="INSERT INTO Y (yID, yName) values('"&rs("IDX") &"', '"&rs( "nameX" )&"', '"&("customer")&"')"
DBConnectionObject.Execute(Sql1)
Rs.MoveNext
Wend

'Then finally redirect
Response.redirect("default.html")

Another way to accomplish it in one statement:

insert into Y (FieldName1, FeildName2) (select Field1, 'SomeValue or field' from X where field = value)