Source language: Translate to:

Copy Data From 1 Database to Another Database

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Copy Data From 1 Database to Another Database

Postby Pat Baner » Tue Apr 05, 2011 2:32 pm

Hello all. Hope all is well. I am looking for some advice from the forum on the program I am working on.

What is the best way to copy a record (or multiple records) from a table in one database and place it into a new record(s) in a different database/table. Two issues to note: 1) the destination database has more fields but the initial fields are the same name, order, and type and 2) some of the fields are pictures fields that contain uploaded files (.pdf, jpg, ect...)

Thanks for any help you can provide me.
Pat Baner
 
Posts: 75
Joined: Tue Jan 04, 2011 9:21 am

Postby carlos torres » Wed Apr 06, 2011 6:15 am

Hi Travin69

I'm pretty sure that the best way is usin an SQL statement like this:
Code: Select all
SELECT TableName.field1, tableName.field2,  INTO tblResult
FROM TableName
WHERE (((TableName.Field1)="[criteria1]")
ORDER BY TableName.Field1;


Where tblResult is the target table that you want to create

Hopefuly it will help you

regards
carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Postby Wrangler » Wed Apr 06, 2011 8:32 am

dbprecordtovar
dbpaddrecord
dbpvartorecord

For multiple records, do a query in the source table first and loop through the records.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

About copy data

Postby carlos torres » Wed Apr 06, 2011 10:18 am

Dear Wrangler

Using a loop become to slow the faster method is using an SQL statement coping w/ those records that fill the requirements/ conditions.

regards
carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Postby Wrangler » Wed Apr 06, 2011 10:21 am

Hi Carlos,

Yes. I know. Especially when copying a blob field. But I wanted to give him the choice in case he is intimidated with sql commands.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Pat Baner » Wed Apr 06, 2011 10:30 am

Wrangler:

Thanks for that. I had considered that method but I don't think I can export a record that has a picture type field in it to a variable or can I?

Carlos:

tblResult would already exist and have data in it. I am using a primary key of the individual's user name to link all tables and content.

Let me be more specific on how my set up works (or I want it to work):

I have 2 programs: the client's program on their computer and my program on my computer.

I want the client to upload all their data to their program and then just give me their database.

I then want to take their database and merge it into my database.

Now, I considered during the merge, to name each of the client's tables with their user name, but it makes my program too crazy for me to try and program all the features I need.

So, I thought, if I just merge their data from their tables into my pre-existing tables, then that is the easiest. I have arranged both databases to have tables (different names though) that contain the same data, same type of field, same everything, same field names, in the same order. The only exception to this rule is my program's database has additional fields with information, but these are arranged after the fields that both tables have in common.

Obviously, I am not adept at SQL statements. I looked at using the copy feature but didn't get very far with that.
Pat Baner
 
Posts: 75
Joined: Tue Jan 04, 2011 9:21 am

Postby Wrangler » Wed Apr 06, 2011 10:33 am

Would exporting to csv in their program and then importing from csv in your program work?

You can set which fields are exported and imported using dbpsetimportoptions.
Last edited by Wrangler on Wed Apr 06, 2011 10:35 am, edited 1 time in total.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Pat Baner » Wed Apr 06, 2011 10:35 am

It would except I loose the contents of the picture field for some reason.
Pat Baner
 
Posts: 75
Joined: Tue Jan 04, 2011 9:21 am

Postby Wrangler » Wed Apr 06, 2011 10:36 am

True. csv will only handle a path to the images.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Wrangler » Wed Apr 06, 2011 10:40 am

I guess Carlos' way would be the best. Maybe time to learn a little sql. Google is really your friend here. Using Carlos' example, and looking at the sql web site, you may be able to get it to work, without really understanding all of sql.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Pat Baner » Wed Apr 06, 2011 10:41 am

Thanks.

Anyone that knows SQL wanna make some money? I will gladly pay someone to help me and post the results for all to benefit from.


Thanks again for the help.
Pat Baner
 
Posts: 75
Joined: Tue Jan 04, 2011 9:21 am

About copy data

Postby carlos torres » Wed Apr 06, 2011 8:01 pm

Hi Travin69

Those suggestions made by Wrangler are ok, but try a little bit with SQL.

The statement I posted has a one more '(' on the third line. Check for pairs.

regards,
carlost
User avatar
carlos torres
 
Posts: 289
Joined: Mon May 02, 2005 8:14 am
Location: Pamplona, Colombia

Postby Pat Baner » Mon May 09, 2011 12:18 pm

Ok, maybe you fine folks can give me another hand here. I have attempted to learn some sql but can't seem to get it to work. Here is my code:

dbpExecSQL "FCRDB" "SELECT * INTO CreditBureauData IN 'C:\Users\Main\Desktop\Free Credit Repair Manager\TestingDatabase.mdb' FROM CreditBureauData" ""

I have both databases open and the table open that i am trying to move. I want to take a table and copy the entire table from FCRDB to the TestingDatabase.mdb. Now, I tried the Neobook name for the TestingDatabase, which is "Test". I also tried just the name TestingDatabase.mdb. I get the error "Cannot perform on a closed dataset".
Pat Baner
 
Posts: 75
Joined: Tue Jan 04, 2011 9:21 am


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 4 guests