Copying Rows between 2 Different Table Structures

[HoboTraveler]

Hi All,

I have two tables, table1 and table2. The number of columns between the two tables are different from each other. Both tables have totally different fields.

I need to copy certain fields from 20,000 rows from table1 into table2. Could someone please point me to the correct SQL syntax to accomplish this..?

Currently, I use a while loop through PHP to do the copying.. But, I think this is not the best optimized solution?

TIA

[edited by: HoboTraveler at 7:23 pm (utc) on Sep. 25, 2006]

[HoboTraveler]

*bump* anyone?

[tomda]

1/ Do a backup of the two tables first

2/ Do one SQL query to get fields value from Table1 that need to be copied to table2. Store the output in an array.

3/ Print the array to see what you get is correct.

4/ Loop through the array using PHP and do an UPDATE query for each element of the array.

Can't help you more, unless you give more info about your table structure and what you want to update.

[HoboTraveler]

@tomda,

Thanks for the reply. The steps that you outlined is exactly what I am doing now.

My question is, is this copying process the best optimized method? How can I be absolutely sure that the fields have been copied over correctly and that some were not left out?

TIA

[aspdaddy]

Well an aternative is to write a single set level/ bulk insert statement, but then error handling is more complex. Whats the problem exactly with your current solution?

[HoboTraveler]

There isint exactly a problem. My concern is when copying over 50,000 rows, and if a new row is inserted into the source table, will the copying process stop?

Will all the 50,000 rows get copied over successfully? What happens to the new data that is inserted into the source table during the copying process.

TIA

[aspdaddy]

Without seeing the code its impossible to say what it does in that situation - it does whatever your code told it to do :)

A good start would be to identify the potential errors that could happen and code it to write to the event log or a text log log when it runs then read the log to see what happennd. After a few test runs use the log to fix the code so you have some confidence being making it live.

[syber]

When you say you want to copy fields from one table to another, do you mean that you want to insert new rows? If so, then all you need to do is:

INSERT TABLE2 (COL1, COL2, COL3, COL4, ...)
SELECT COLa, COLb, COLc, COLd, COLe, ...
FROM TABLE1

Otherwise, it could be a straight forward UPDATE command

[HoboTraveler]

Hi All,

Here is the script that I currently use to copy the list of emails from one table into the other. The table structure between the two tables are different.

The number of rows in the table are about 25,000. My concern is, is this the best way to do the copying and how do I ensure integrity that all the 25,000 records have been copied over correctly..?

TIA

-- begin code--

<?php

// Select the emails from the users table
$SqlSelectQuery =
("
SELECT
email
FROM
{$table_users}
WHERE
email_enabled = '1'
");

// Perform Query
$SqlSelectResult = mysql_query($SqlSelectQuery);

// Check result. This shows the Error
if (!$SqlSelectResult)
{
$SqlSelectMessage = 'Invalid query: ' . mysql_error() . "\n";
die($SqlSelectMessage);
}

// If no emails found, exit
$SqlSelectResultMatch = mysql_numrows($SqlSelectResult);

if ($SqlSelectResultMatch == 0)
{
echo 'Exit! Emails were not found .. Script Halted!<br>';
exit();
}

// If emails were found, proceed to copy emails
while ($SqlSelectRow = mysql_fetch_assoc ($SqlSelectResult))
{
// Store the email var
$email = $SqlSelectRow['email'];

// Copy the emails into the logs table
$SqlInsertQuery =
("
INSERT INTO
{$table_logs}
(
`dateInserted`,
`email`
)
VALUES
(
NOW(),
'$email'
)
");

$SqlInsertQueryResult = mysql_query($SqlInsertQuery);

// Check result. This shows the Error
if (!$SqlInsertQueryResult)
{
$SqlInsertQueryResultMessage = 'Invalid query: ' . mysql_error() . "\n";
die($SqlInsertQueryResultMessage);
}

$nextID = mysql_insert_id();

if ($nextID)
{
// proceed
}

else
{
echo 'Error! INSERT Error .. Script Halted!<br>';
exit();
}

}
?>

-- end code--

[edited by: HoboTraveler at 5:39 am (utc) on Oct. 3, 2006]

[aspdaddy]

INSERT TABLE2 (COL1, COL2, COL3, COL4, ...)
SELECT COLa, COLb, COLc, COLd, COLe, ...
FROM TABLE1

I would that inside a database transaction, if it fails just make it roll back.

[HoboTraveler]

I've constructed the SQL query below to copy the records. However, I need to insert the NOW() datetime stamp in the datetime field.

Any ideas on how to insert the NOW()?

TIA

INSERT INTO
table_logs
(
dateSent,
email
)
(
SELECT
email
FROM
table_users
WHERE
email_enabled = '1'
)

[aspdaddy]

in t-sql its getdate().

[HoboTraveler]

Yes, but please note, I do not want to get the datetime.. I need to insert the date using NOW() at the time of insert.

Thanks

[aspdaddy]

well in t-sql you cant theer is no datatype for dates, its datetime or smalldatetime.

[HoboTraveler]

Hello,

I am trying to understand T-SQL.. what I am working on is PHP and MySQL v4.1

[HoboTraveler]

I was able to find the solution! The NOW() is to be used in the SELECT statement.. and the date gets inserted correctly..

INSERT INTO
table_logs
(
dateSent,
email
)
(
SELECT
NOW(),
email
FROM
table_users
WHERE
email_enabled = '1'
)

Thanks