CTE vs multiple inserts

Question

As we all know, CTEs were introduced in SQL Server 2005 and the crowd went wild.

I have a case where I'm inserting a whole lot of static data into a table. What I want to know is which of the following is faster and what other factors I should be aware of.

INSERT INTO MyTable (MyField) VALUES ('Hello')
INSERT INTO MyTable (MyField) VALUES ('World')

or

WITH MyCTE(Field1) AS (SELECT 'Hello' UNION SELECT 'World')
INSERT INTO MyTable (MyField) SELECT Field1 FROM MyCTE

I have an uncomfortable feeling the answer will be dependent on things like what triggers exist on MyTable...

(Also, I know and don't care that BULK INSERTing a CSV and any number of other methods are objectively faster and better ways of inserting static data. I specifically want to know the concerns I should be aware of with a CTE vs multiple inserts.)

Answer 1

Not sure which version of SQL Server you're using (2005 or 2008) - but no matter which version you use, I don't see any big benefit in using a CTE in this case for multiple inserts, quite honestly. CTE's are indeed great for a great many situation - but this is not one of them.

So basically, I would suggest you just use several INSERT statements.

In SQL Server 2008, you could simplify those by just specifying multiple values tuples:

INSERT INTO MyTable (MyField) 
VALUES ('Hello'), ('World'), ('and outer space')

As always, your table structure, presence (or absence) of indices and triggers does indeed have a significant impact on your INSERT speed. If you need to load a lot of data, it's sometimes easier to turn these constraints and triggers OFF for the duration of the INSERT and then back on - but again: there's really no way to give you a clear indication whether that's the case in your specific situation or not - just too many variables we don't know about that play a siginificant role. Measure it, compare it, make a decision for yourself!