copy data from one table to another but only data fields are called exactly the same

kanika · 2016-09-27

Good morning to everyone s,

SQL Server 2003 R2

I need to copy data from one table to another but only data fields are called exactly the same.

tabla_A fields: field1, field2, field3, field4, campo5, campo6

talba_B fields: field2, field3, field4, campo5, field8, campo9

You need to copy data from field2, field3, field4 and campo5 of tabla_A in tabla_B, a new record

although obviously do not know the names of all fields since they are hundreds, I just need to know what are called exactly the same and copy data

I searched the code but I have not found

thanks in advance

mohana_cse06 · 2016-09-27

You can use TRANSFERFIELDS to copy data with same Field ID but not name. you need to have same datatype also while using above function.

kanika · 2016-09-27

the data type is the same

Please, How would the code?

This was my code:

INSERT INTO tabla_A
SELECT campo2 campo3,campo4,campo5
FROM tabla_B
WHERE tabla_B.[Document Type]=5
and tabla_B.No_ like '%0004'
and tabla_B.[Version No_]=2

RockWithNAV · 2016-09-27

You can use Transferfields if Field ID is too same between the Tables.

kanika · 2016-09-27

yes but I do not know as used in sql

RockWithNAV · 2016-09-27

You want to achieve this from SQl Side and SQL Query?

Kishorm · 2016-09-27

As @mohana_cse06 and @RockWithNAV have already said - to use TRANSFERFIELDS both the Field IDs must be the same and the Data Types must be the same. Please also note that...

If there are any other fields with the same Field ID and same Data Type then these will also be copied (even if they have different field names)
If there are any fields with the same Field ID but different Data Type then an error will be generated

In summary you would want to...

table_b.SETFILTER(...) - set filters as required
IF table_b.FINDSET THEN REPEAT
  table_a.TRANSFERFIELDS(table_b);
  table_a.MODIFY;
UNTIL table_b.NEXT = 0;

vaprog · 2016-09-27

Use RecordRef / FieldRef to access / assign fields. Use the Field system table to find fields with the same name (and compatible data type).
For each record of one table enumerate the fields, search their name buddies in the receiving table, assign if found (FieldRef2.VALUE := FieldRef1.VALUE). When done processing all the fields insert the record.

Howdy, Stranger!

Quick Links

Categories

copy data from one table to another but only data fields are called exactly the same

Answers