SQL question - copy data to a new primary key 1

[cl8855]

There has to be an easy way to do this.

How can I copy a full entry in a table to a new entry just changing one field (which happens to be part of the primary key)
for example:
servers_table has app_name, server_name, ip_address, firewall fields (plus more)

I want to insert a copy of everything for server_name = TEST just with server_name = TEST2 (same entries for all the rest of the fields except server_name)

keeping in mind that the primary key is made up of app_name and server_name


I know I could do a general select of all columns, and a corresponding insert, but on subsequent tables there can be more than one entry that I want to copy so I am hoping to avoid a looping situation.

 

[carp]

INSERT INTO table2
SELECT app_name, replace(server_name,'TEST','TEST2'),ip_address, firewall fields (plus more)
FROM table1;

 

[cl8855]

thanks, I will give that a test run

 

[cl8855]

It seems to be working, but do I have to list the fields in the order they are in the database? I had one in a separate order and it seemed to put the data in the wrong columns. I had thought it would use the field names from the select to insert since they are the same.

Oh well looks good either way!

 

[cl8855]

OK how about I make this harder then.
First part works well.

2nd part is I have audit tables that track all changes, but they have 3 additional fields that original table doesn't (last_update, updated_by, action_type).

How do I do an insert into/select from and also add those three extra fields?

One of those fields is NOT NULL so I can't just insert some and then update after.

 

[carp]

I can answer both of your questions with the same basic change in approach.

1. No, you don't have to list the columns in the same order. However, if you change the order, you must map the columns in the target table to the columns in the source table:

INSERT INTO table2(server_name, app_name, ip_address, firewall_fields, (plus more))
SELECT app_name, replace(server_name,'TEST','TEST2'),ip_address, firewall fields (plus more) 
FROM table1;

This same approach allows you to be selective about which columns you are populating. Suppose you have two extra columns for auditing, and they hold the timestamp for when the row was added, as well as who added it. Then you could populate with something like the following:

INSERT INTO table2(server_name, app_name, ip_address, firewall_fields, creation_date, created_by)
SELECT app_name, replace(server_name,'TEST','TEST2'),ip_address, firewall fields,
SYSDATE, USER 
FROM table1;
[code]
Note that while sysdate and user are not part of your source table, you are able to include them in your selection list.

 

[cl8855]

Excellent. I had figured out the specification of each field, but of course I wanted to save as much typing/code as possible. But for the 2nd table update, that works perfectly. I had tried similar things to add the other fields, but after the from statement, not in the middle of the select.