insert records 2

[pcwaleed]

hello
i have tow tables... name and id
table name have 400 records
table id have 400 id
i would like inset 400 ids in table name
like this
name id
ghghg 15565
gfghfgh 6786
kjggjg 8768
etc...

thank you

 

[Olaf Doschke]

Your problem description seems to confuse tables with fields. It doesn't help me see, what you really have at hand and want to do.

You can do a single record insert:

INSERT INTO targettable (Field1, Field2) VALUES (value1, value2)

You can insert multiple records with APPEND:

USE targettable
APPEND FROM sourcetable

The sorucetable has to have (some) fields named the same as the targetttable, also with same/similar type, so the data gets over. It works with no common fields, too, but will just add reccount("sourcetable") new records to targettable.dbf with all default values.

In VFP9 you can insert multiple records with INSERT-SQL using a SELECT-SQL query:

INSERT INTO targettable SELECT * FROM sourcetable

In this simplistic way, it compares with APPEND, instead of * you can of course also query specific fields with or without aliasing them or even have a more complex SELECT-SQL query, as long as its result can be appended.

Bye, Olaf.

 

[Mike Lewis]

Whatever solution you adopt, you will need some way to link the IDs to the names. In your example, you have assigned the ID 8768 to the name kjggjg. How do you know that ID belongs to that name? What is the rule?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[pcwaleed]

i cant add him. i add him manualy

 

[mjcmkrsr]

Hi,

1) Please don't call a table "NAME" - perhaps tblNames. "NAME" is a reserved word in VFP (see below)

[link ]

https://jeffpar.github.io/kbarchive/kb/130/Q130440/

[/url]

2) If the RECNO() in the two tables may serve as the link you might code something like below. Be sure though that you have a field called ID in your tblNames

USE tblNames
UPDATE tblNames SET ID = ID.ID WHERE tblNames.RECNO() = ID.RECNO()

hth

MK

 

[Mike Lewis]

Even if you do it manually, you will still need some way of knowing which IDs belong to which names.

You will really need to give us some more information before we can help you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Let's assume:

1. Your table names are csrNames and csrIDs.

2. csrNames contains fields cName and nID. nID is currently empty.

3. csrIDs contains a field nID. This contains the IDs that you want to assign to csrNames.

4. The records in csrIDs follow the same sequence as in csrNames. In other words, the first record in csrIDs contains the ID that corresponds to the first name in csrNames. And so on.

5. There are no indexes in force, no deleted records, and no filter.

If all the above is true, you could do this:

GO TOP IN csrIDs
SELECT csrNames
SCAN
  GO RECNO("csrIDs")
  REPLACE csrNames.nID WITH csrIDs.nID
  SKIP IN csrIDs
ENDSCAN

After running this code, the IDs from csrIDs will be copied into the corresponding records in csrNames.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

MK,

You wrote:

UPDATE tblNames SET ID = ID.ID WHERE tblNames.RECNO() = ID.RECNO()

I don't think that would work, for two reasons.

tblNames.RECNO() is not the correct syntax. You would need RECNO("tblNames"). Similarly, RECNO("ID")

In any case, I think that would set all the IDs in tblNames to the ID in just the current record in the ID table. In other words, all the IDs in tblNames would end up the same.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi Mike,

You're right - twice. Sorry for not testing. The modified code below however works

CREATE TABLE tblNames (cId C(5), cName C(10))

INSERT INTO tblNames (cName) VALUES ("Michi")
INSERT INTO tblNames (cName) VALUES ("Mecky")
INSERT INTO tblNames (cName) VALUES ("Myaer")
INSERT INTO tblNames (cName) VALUES ("Johnny")
INSERT INTO tblNames (cName) VALUES ("Zicky")

BROWSE

CREATE TABLE tblIDs (cId C(5))

INSERT INTO tblIDs (cID) VALUES ("A2345")
INSERT INTO tblIDs (cID) VALUES ("B2345")
INSERT INTO tblIDs (cID) VALUES ("C2345")
INSERT INTO tblIDs (cID) VALUES ("D2345")
INSERT INTO tblIDs (cID) VALUES ("E2345")


BROWSE

SELECT tblIDs

SCAN 
	UPDATE tblNames SET cID = tblIDs.cID WHERE RECNO("tblNames") = RECNO("tblIDs")

ENDSCAN 

SELECT tblNames 
BROWSE

CLOSE ALL
CLEAR ALL

Thanks for the hints

MK

 

[Olaf Doschke]

Let me try to go through this step by step:

I have two tables... name and id

mjcmkrsr already mentioned it's a bad idea to use reserved words for names of tables (or fields, or variables), I'll not dive into this.

table name have 400 records

OK, that doesn't tell how the table is structured, but ok.

table id have 400 id

OK, and that same record count indicates you want to join data 1:1

i would like inset 400 ids in table name
like this
name id

Confirms my intuition, and now tells me, you have the id field in both name and id table. Or do you have two tables with each just one field, name.name and id.id and want to generate a third table t3 with both fields?

Bye, Olaf.

 

[Koen Piller]

Olaf,

i would like inset 400 ids in table name
like this
name id
ghghg 15565
gfghfgh 6786
kjggjg 8768

I believe your assumption about a 3rd table is incorrect. What he/she wants, according to me, is to extend table Name with a field ID and than insert/update name.ID with the content of ID.id, which is possible by just adding a field ID to table name and than append from table ID.
This will however result in a situation that recno 1 from name has in field ID the value of recno 1 of table ID, and nobody knows if this is correct, meaning it will create garbish.
Without a relation ID it will not be possible to make a sensible update.
This also applies for the solution by mr./mrs. mjCmkrsr where does it say that both tables are in 100% relationship by reccord number?
No where, so this will also result in 'garbish'

So mr./mrs. pcWaleed you shall have to do either a 'garbish' update or doit manualy. 400 reccords, should not take you to much time.

The main purpose of this advise to both mr./mrs pcWaleed and mr./mrs. mjCmkrsr: google for 'relational database' and learn about that technique be4 you do these kinds of updates.

Regards,
Koen

 

[Olaf Doschke]

>What he/she wants, according to me, is to extend table Name with a field ID and than insert/update name.ID with the content of ID.id

Might be, we both can only make assumptions, pcwaleed can simply clarify the outset.

Bye, Olaf.

 

[Mike Lewis]

The idea of the records being related by record number was simply an assumption that several of us made in order to move the thread along. It might or might not be true. In fact, we don't really know anything about the tables or their fields or their relationships.

This is clearly one of those threads where someone asks a vague question, and we do our best to help him out, but he fails to give us any feedback on our suggestions or even acknowledge that he has seen them.

Personally, I'm disinclined to waste any more time on it.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Dear Mr./Ms. Piller,

Maybe I should do what you suggest - however you might have READ more thoroughly what I wrote.

2) If the RECNO() in the two tables may serve as the link you might code something like below.

No further comment

MK

 

[Koen Piller]

Hi MK,
Yes I did notice, however the recno() should never ever serve as a relation identification. Let alone that it is too easy to shuffle the records.
To use recno() as puk is a basic violation of relational database design.
Regards,
Koen

 

[Mike Lewis]

Yes, I take your point, Keon. It goes back to what I said at the start of the thread: you can only solve this problem if you can establish a relationship between the two tables - or some other rule that tells you which ID belongs to which name.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi Koen,

however the recno() should never ever serve as a relation identification

Well, well, well - in this case the RECNO() could serve as the temporary identification key for a solution.

Furthermore I did not know my suggestion would be considered a "schism" - please apologize my "sins".

Regards

MK

 

[Olaf Doschke]

Just notice SET RELATION also allows relating records by RECNO() instead of a value sought in some index tag, when the target table of the RELATION has no index set. And in that context, you can also do a 1:1 relation by RECNO and even only need a REPLACE ALL.

Anyway, you never get into such situations (aka "trouble"), when you have initial ids in any table.

The reason I reject to give any solution code here is, that it'll only work based on several preconditions not generally true, repeatable or sensible. Two tables only related by having the same number of rows are an unfortunate outset, that could or could not work out to join.

If we knew how the list of those 400 names and the second list of 400 ids were generated, I'd say we could also join the names and ids at that stage already and not as an aftermath, and that would make things much more concise and more generally valid.

Bye, Olaf.

 

[pcwaleed]

thanks to all
folly
I create table contain names and some information
my Employer need to insert field id for every name...
the ids is ready in another table
I must insert table id to table name randomly

 

[JRB-Bldr]

I must insert table id to table name randomly

pcwaleed - While RANDOM assignment is a very poor way to assign ID's - if all you want to do is to assign ID's to the Names in the other table you can do with a small VFP program which looks something like what follows:

* --- The following assumes that there are as many or more ID's than Names ---
SELECT IDTable
SCAN
   m.id = IDTable.ID

   SELECT NameTable
   LOCATE FOR EMPTY(NameTable.ID)
   REPLACE NameTable.ID WITH m.ID

   SELECT IDTable
ENDSCAN

That's not a RANDOM assignment, but it will put the ID values into the NameTable field.
With some minor code changes you could make the ID assignment more RANDOM if you needed.

Good Luck,
JRB-Bldr