Copy data into another table

Delve deeper

Handling differing schemas

For trees that differ (tables with different schemas), enlist columns in your command:

INSERT INTO target_table (targetCol1, targetCol2) SELECT sourceCol1, sourceCol2 FROM source_table;


By indicating columns explicitly, data lands precisely where desired.

Steer clear of schema errors

Attention! An INSERT INTO goes into an existing table, unlike using SELECT INTO which creates a new one:

SELECT * INTO new_table FROM source_table; -- Bad code, it’s a trap!


So create your new table first. We want to avoid accidental table creation in production, don't we?

The performance boost with 'INSERT SELECT'

Heavy data lifting? INSERT SELECT is your best bet. It's faster and safer. For larger copy jobs, meet the big guns: bulk operations:

BULK INSERT target_table FROM 'source_data_file' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n'); -- Heavy weights, handle with care!


or

OPENROWSET(BULK 'source_data_file', FORMATFILE = 'format_file_path') AS source_table; -- ‘Bulk up’ your SQL performance!


Focusing on data accuracy

When the stakes are high, avoid wild card *. Be explicit with columns. This level of detailing prevents mismatches.

Practical use cases

Condition based selection

For conditional copying, that's when the WHERE clause comes to the rescue:

INSERT INTO target_table (col1, col2) SELECT col1, col2 FROM source_table WHERE col3 = 'condition'; -- 'Where' there’s will, there’s a way!


Subsets transfer

Subsets call for JOIN or subqueries to get the spotlight on required data:

INSERT INTO target_table (ID, DataPoint)
SELECT s.ID, s.DataPoint
FROM source_table AS s
INNER JOIN another_table AS a ON s.ID = a.ID
WHERE a.Condition = TRUE; -- SQL ninja level: Subquery expert!


Dealing with duplicates

Nobody likes a copycat, especially databases. Deduplicate using NOT EXISTS or EXCEPT:

INSERT INTO target_table (ID, DataPoint)
SELECT ID, DataPoint FROM source_table
WHERE NOT EXISTS (SELECT 1 FROM target_table WHERE target_table.ID = source_table.ID); -- This SQL walks into a bar... sees tables... walks out!


or

INSERT INTO target_table
SELECT ID, DataPoint FROM source_table
EXCEPT SELECT ID, DataPoint FROM target_table; -- Exterminate! (Dr. Who pun #sorrynotsorry)


Less common scenarios

Database cross-border data transfer

To copy data to another database, add the full path to the table:

INSERT INTO Database2.dbo.target_table (col1, col2)
SELECT col1, col2 FROM Database1.dbo.source_table;


Transform while you transfer

You can make function transformations during your data flight:

INSERT INTO target_table (adjustedCol)
SELECT CONVERT(VARCHAR, CAST(sourceCol AS DATE), 101) FROM source_table;


Saving your work mid-flight

Wrap your copy operation in a transaction session for safekeeping:

BEGIN TRANSACTION;
INSERT INTO target_table SELECT * FROM source_table;
-- If something goes wrong, I want my data back, Capitan!
COMMIT TRANSACTION;


Avoid these pitfalls

Data type mismatch

Manage your data types vigilantly. Mismatched data types result in conversion errors.

Permissions necessity

Before you embark on the data transfer, ensure you possess required permissions to read from source and write into target.

Performance killers

Copying a large dataset? Be aware of locks or long-running transactions. Keep an eye on performance or consider the friendly neighborhood indexes or batch operations.