SQL: How to insert new rows that are copies of existing rows

In SQL you can create/insert new rows that are essentially copies of the old rows, without needing to specify every column value.

Let’s say you have a table called “EVENTS” with the following columns:

  • event_name
  • location_name
  • promoter_name
  • event_date
  • promote_address
  • location_address

Now, let’s say you want to create a new row with all the same information except the event_name and event_date.

insert into events (event_name, location_name, promoter_name, event_date, promote_address, location_address) 
select 'new event' as event_name, location_name, promoter_name, 'todays date' as event_date, promote_address, location_address where promoter_name='Test1';

In the above sql, the select statement becomes the input for the insert statement. All the rows returned by the select statement will be inserted as new rows exactly as returned. Which means that instead of the ‘event_name’ as stored in the database, it will insert ‘new event’ as the event_name for each and every row. This is useful if you are trying to create new rows with the same “event_name” value.

 

Advertisements