How to copy a row from one SQL Server table to another

sql

data-transfer

sql-performance

sql-best-practices

byAlex Kataev·Aug 26, 2024

INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM source_table
WHERE id = row_id_to_copy;

Leverage INSERT INTO...SELECT to copy rows between tables. Match column names in both target and source, and include a WHERE clause to specify the row.

Understanding table structures

Familiarize yourself with table schemas before copying. Check column compatibility and confront any constraints that could lead to errors during data transfer.

/* This isn't just reading tea leaves, it's taking SQL seriously! */
SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'source_table'

Performance considerations

Copying large datasets between tables may affect performance. Optimize by using specific SELECT statements instead of SELECT *. This reduces the amount of data transferred, thus boosting performance.

/* More selective than a hipster at a vegan buffet */
SELECT col1, col2 FROM source_table WHERE condition;

Alternative approaches

While INSERT INTO...SELECT is the go-to, SELECT * INTO offers an alternative method, generating a new table for the copied rows.

/* Because sometimes you just need a fresh start! */
SELECT * INTO new_table FROM source_table WHERE condition;

Remember that this method requires the target table to not exist beforehand and is suitable for ad-hoc duplications.

Programmatic solutions

For recurring tasks, consider encapsulating SQL logic into a stored procedure, making it reusable and schedulable.

/* The SQL equivalent of "set it and forget it" */
CREATE PROCEDURE copy_rows AS ...

Tweaking transferred data

In instances where a straightforward copy isn't what you need, employ expressions, CASE statements, or constants to shape your data:

INSERT INTO TargetTable (col1, computed_col, fixed_value)
SELECT col1, 
       CASE WHEN condition THEN value ELSE other_value END,
       'FixedValue'
FROM SourceTable
WHERE condition_to_select;

This approach provides flexibility for custom data transformations.

Avoiding copy blunders

Copying data can occasionally lead to errors. Prepare for potential snags, such as type mismatches, which are circumvented by explicit casting, or constraint violations, preventable by thoroughly checking referential integrity.

/* Safer than a padded cell */
INSERT INTO TargetTable (col1, CAST(col2 AS datatype), ...)
SELECT col1, col2, ...
FROM SourceTable
WHERE condition;

Dealing with complex scenarios

When handling group of related tables, maintain data consistency by using transactions:

/* No SQL were harmed during this transaction */
BEGIN TRANSACTION;
INSERT INTO target_table_a (...) SELECT ... FROM source_table_a WHERE ...;
INSERT INTO target_table_b (...) SELECT ... FROM source_table_b WHERE ...;
COMMIT TRANSACTION;