473,396 Members | 2,034 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

Copy field data from table to another

Let's say, I have Table1 and Table2, both with the exactly same
structure and data in it, except that Table1.Field1 is empty in data
but Table2.Field1 have data in it.

How could I copy all the data from Table2.Field1 to Table1.Field1?
Is there any simpler way except looping through all the records to do
it?

Jul 23 '05 #1
5 8709
Sorry, just some add on to my previous question.
If I have this:

Table1
RecId ==> record ID
Name ==> name (empty)
Desc ==> description
... (another 23 fields to go)

Table2
RecId ==> recordID
Name ==> name (with data)
Desc ==> description
... (another 29 fields to go)

The Table1.Name is empty in data, then Table2.Name have data.
How could I write a SQL statement to copy all the data from Table2.Name
to Table1.Name since the table structure is different. I need to copy
the data in by mathing the RecordId.

I mean, copy Table2.Name to Table1.Name where their recordID is the
same.
Just as an extra info, there're 500,000 records in the tables.
Thanks a lot in advance.


Peter CCH

Jul 23 '05 #2
Hi

Assuming recordid is a key that you match the records on. If your fields
have no value I assume they are NULL.

UPDATE T
SET Name = S.NAME,
Desc = S.Desc,
....

FROM Table 1 T
JOIN Table2 S ON s.recordid = t.recordid
WHERE Name IS NULL
OR DESC IS NULL

If you dont want to update table1 if it has a value try something like.
UPDATE T
SET Name = CASE WHEN NAME IS NULL THEN S.NAME ELSE NAME END,
Desc = CASE WHEN NAME IS NULL THEN S.DESC ELSE DESC END,,
....

FROM Table 1 T
JOIN Table2 S ON s.recordid = t.recordid
WHERE Name IS NULL
OR DESC IS NULL

John

"Peter CCH" <pe************@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Sorry, just some add on to my previous question.
If I have this:

Table1
RecId ==> record ID
Name ==> name (empty)
Desc ==> description
... (another 23 fields to go)

Table2
RecId ==> recordID
Name ==> name (with data)
Desc ==> description
... (another 29 fields to go)

The Table1.Name is empty in data, then Table2.Name have data.
How could I write a SQL statement to copy all the data from Table2.Name
to Table1.Name since the table structure is different. I need to copy
the data in by mathing the RecordId.

I mean, copy Table2.Name to Table1.Name where their recordID is the
same.
Just as an extra info, there're 500,000 records in the tables.
Thanks a lot in advance.


Peter CCH

Jul 23 '05 #3
Since it join both of the tables first then only update the value, if
the table have 500,000 records, will the SQL statement above takes very
long time? (Assume it update that 500,000 records)

Jul 23 '05 #4
Hi

5000000 rows would not take that long, but as the where condition limits the
rows you may see less being updated. You may also want to check that you are
not updating with the same values.

UPDATE T
SET Name = S.NAME,
[Desc] = S.[Desc],
....

FROM Table 1 T
JOIN Table2 S ON s.recordid = t.recordid
WHERE ( T.Name IS NULL AND T.Name <> S.Name )
OR ( T.[DESC] IS NULL AND T.[DESC] <> S.[DESC])
John
"Peter CCH" <pe************@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
Since it join both of the tables first then only update the value, if
the table have 500,000 records, will the SQL statement above takes very
long time? (Assume it update that 500,000 records)

Jul 23 '05 #5
Peter CCH (pe************@gmail.com) writes:
Since it join both of the tables first then only update the value, if
the table have 500,000 records, will the SQL statement above takes very
long time? (Assume it update that 500,000 records)

Updating 500000 rows is usually not snap. Exactly how long time depends
on your hardware, but also the definition of the column. If the column
is fixed length, all updates can be in place. But if the column is a
varchar or varbinary column, many rows will grow out of their current
pages, so that must be a lot of page splits and data moved around. Again,
exactly how massive this effect will be depends on the data. If the
average length is three characters, you have have luck and most pages
have space to spare. If the average length is 100 bytes, this is less
likely.

Another thing that matters is whether data and log files have the space.
Updating half a million rows will take a toll on the log file. Exactly
how big that toll is, depends on the width of the table. If each row
is 10 bytes in averages you need a lot less log if the average row length
is 360 bytes, If the log does not have the space, it will have to
autogrow and autogrow does not come for free. If you are running with
simple recovery, you can hold down the log explosion by doing the
update in batches of 50000 or so.

But in short, the only way to get answer to a question like this one is
to benchmark.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: middletree | last post by:
I have a table which has a datetime field for when a row in this one table (Ticket) was created. This is for trouble tickets for a help desk. After a ticket has been created, any additions to that...
4
by: sparks | last post by:
1 need to copy the data in one table into another...but its from another database. tables are demographics and testdata database1 database2 demographics personid personid same...
0
by: gary b | last post by:
My forehead is sore from beating it against the wall. Can anyone help? I've started drinking again because of this problem! Setup: ContestantTbl = table ('one' side) HorseTbl = table ...
3
by: david | last post by:
Hi, I've been reading tons of posts on how to copy records, but to no avail....i'm still stuck. There are three tables: Main, Sub-Form1 & Sub-Form2 I have a form which displays some data....
5
by: Clodoaldo Pinto Neto | last post by:
Hello, How to make sure COPY TO writes the table lines to the file in the same order they were inserted? I'm producing html pages in pl/pgsql and using COPY TO to write then to file....
9
by: David Rysdam | last post by:
I have a large amount of data that I copy in and out of Sybase very often. Now I also want to copy this data in and out of postgres. I have an existing script that creates the entire database(s)...
2
by: Swinky | last post by:
I hope someone can help...I feel like I'm walking in the dark without a flashlight (I'm NOT a programmer but have been called to task to do some work in Access that is above my head). I have...
4
by: colmkav | last post by:
Hi, I need to copy a linked table but without keeping the link. ie all the data and settings accept for the link to another db. How can I do this? Colm
3
by: Richnep | last post by:
Hi all, I have tabbed subforms where I need to copy one field value from one subform over to another subform. Although I can run an update query to accomplish this I would like to do it through...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.