How to duplicate SQL Rows

How to duplicate SQL Rows

In the world of database management, efficiency and precision are key. With its versatile commands, SQL offers a wide range of functionalities for handling data.

One such functionality is the combination of the INSERT INTO and SELECT statements, a powerful tool for duplicating or reorganizing data within a database. This blog post will explore how this combination can be used effectively, as illustrated by a specific SQL code example.

The SQL Code in Focus

Let’s take a look at the SQL code:

          ( Event_ID
          , col2
     SELECT "155"
          , col2
      FROM Table WHERE Event_ID = "120"

Understanding the Insert-Select Method

This SQL statement is a great example of the Insert-Select method. It’s essentially doing two things:

  1. Selecting Data: It selects data from a table where the Event_ID is “120”.
  2. Inserting Data: It then inserts this data into the same (or potentially different) table but with a new Event_ID of “155”.

Why Use Insert-Select?

  1. Data Duplication: This method is perfect for duplicating records with minor changes. In the given example, it’s creating a new record with most of the same data but a different Event_ID.
  2. Data Reorganization: It can also be used for reorganizing data within a table or across tables.
  3. Efficiency: This method is efficient in terms of execution time, especially for large datasets, as it minimizes the amount of data transfer between the application and the database.

Applications in Real-World Scenarios

  • Testing Environments: Quickly create test data by copying existing data and modifying certain fields.
  • Data Migration: Migrating specific data to a new table or database is especially useful during database restructuring.
  • Backup Purposes: Creating backup records before performing updates or deletions.

Best Practices

  • Transaction Management: Use transactions to ensure data integrity, especially in production.
  • Data Validation: Validate the data being inserted, particularly if it’s being modified as part of the insertion process.
  • Performance Considerations: Be mindful of the performance impacts, especially when dealing with large datasets or complex queries.


The Insert-Select method in SQL is a simple yet powerful tool for database manipulation. It provides a straightforward way to copy and modify data within a database, enhancing efficiency and effectiveness in data management. Understanding and using this method can greatly simplify many common database tasks.


Hi, my name is Stephen Finchett. I have been a software engineer for over 30 years and worked on complex, business critical, multi-user systems for all of my career. For the last 15 years, I have been concentrating on web based solutions using the Microsoft Stack including ASP.Net, C#, TypeScript, SQL Server and running everything at scale within Kubernetes.