• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

Transfer records from one table to another

I am trying to select certain records from one table and then place ALL the values retrieved into another table.

For example, if I retrieve all the records (Text1,Text4,Text5,Text7) for Table A.  How can I save the records temporarily, so that I can close the current table and then open another table so that I can place all the data in Table B?

Hope that makes sense.  
If conn.State = adStateClosed Then
        conn.Open "Provider=sqloledb;Data Source=" & ConnectionIP & ",1433;Network Library=DBMSSOCN;Initial Catalog= CAPRegistration; User ID=sa;Password=xxxx"
        End If
        
        esql = "select Text1 , Text4, Text5, Text7 From tblRegistration Where Committee = " & ravi & DataGridOrganization & ravi

Open in new window

0
al4629740
Asked:
al4629740
  • 4
  • 4
2 Solutions
 
game-masterCommented:


good evening!
try sumthing like this...

'assuming u already have connection...

dim rs as new adodb.recordset


set rs = conn.execute("<your select statement here>")

do until rs.eof

       conn.execute("Insert into YOURTABLE (field1, field2, field3, field4) values ('" & rs!Text1 & "' ,'" & rs!Text2 & "'  ,'" & rs!Text3 & "'     ,'" & rs!Text4 & "'  ")

rs.movenext
loop


i hope i can give u idea...

game-master
0
 
al4629740Author Commented:
Thank you.

YOURTABLE is my destination table?

also, what does "rs!" represent?
0
 
3_SCommented:
-Are both tables in same database  
-do you need to add extra field in table b

Can you use an select insert into?
INSERT INTO TABLEB ("Text1 , Text4, Text5, Text7) select Text1 , Text4, Text5, Text7 From tblRegistration

This way you can copy the records in one command and you don't need a loop
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
al4629740Author Commented:
They are in separate dbs.

Also, yes I need to add a new record when pasting the fields
0
 
game-masterCommented:


good morning!

if they're in different dbase, then u need 2 connection string...



game-master
0
 
al4629740Author Commented:
will it automatically add a new record?

INSERT INTO TABLEB ("Text1 , Text4, Text5, Text7) select Text1 , Text4, Text5, Text7 From tblRegistration
0
 
game-masterCommented:

YES IT WILL...

THE RS! MEANS YOUR POINTING ON THE REcords on your 'rs' recordset...



game-master
0
 
al4629740Author Commented:
INSERT INTO TABLEB ("Text1 , Text4, Text5, Text7) select Text1 , Text4, Text5, Text7 From tblRegistration

Is there suppose to be quotation marks before Text1 ?
0
 
game-masterCommented:


REMOVE THE QUOTATION MARK BEFORE THE TEXT1....


0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now