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

kanika
Member Posts: 247
in SQL General
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
0
Answers
-
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.
0 -
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
0 -
You can use Transferfields if Field ID is too same between the Tables.Thanks
Blog - rockwithnav.wordpress.com/
Twitter - https://twitter.com/RockwithNav
Facebook - https://facebook.com/rockwithnav/0 -
yes but I do not know as used in sql0 -
You want to achieve this from SQl Side and SQL Query?Thanks
Blog - rockwithnav.wordpress.com/
Twitter - https://twitter.com/RockwithNav
Facebook - https://facebook.com/rockwithnav/0 -
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;
0 -
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.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions