Database collation different from tempdb collation
Does this database need to be on this Instance? If not, then as @Max Vernon suggested in a comment on the question, you could install this on an Instance that is created with a default Collation of SQL_Latin1_General_CP1_CI_AS. Perhaps you could even get away with using SQL Server Express Edition, if the database is &lt; 10 GB and doesn't require any Enterprise Edition features.

But if that is not an option, then what you are already doing,


  adding COLLATE SQL_Latin1_General_CP1_CI_AS to the CREATE TABLE #TEMPTABLE statement


is pretty much the only reliable thing you can do.

Since you do not want to adversely affect the operation of the app that was delivered by your client, you cannot change the Collation of the new database. And in fact, changing the DB's default Collation wouldn't fix your current problem as it wouldn't update the Collation of the column in the tables in that DB. It would only affect the Collation of string literals and variables, and you would only notice a difference when those two things are the only things being compared (since the Collation of a column would override the implied Collation of string literals and variables).

Since you don't want to adversely affect your existing DBs on your SQL Server, it wouldn't make sense going through the trouble of changing the current Instance's Collation to SQL_Latin1_General_CP1_CI_AS.

Considering that the new DB and related code were developed (and hopefully tested!) on an Instance that had SQL_Latin1_General_CP1_CI_AS as the default Collation, you really need to make it so that the queries in the new DB behave as they did in the environment in which they were created. You can only do this by forcing the Collation of string columns in temporary tables by adding the COLLATE clause (as you doing). Well, technically speaking you can change the temporary tables into table variables since their default Collation will be taken from the current database, but that is a more intrusive change as the Query Optimizer doesn't handle table variables in the same way as temporary tables (and there are other notable differences).

In the COLLATE clause, you could use either DATABASE_DEFAULT or continue using SQL_Latin1_General_CP1_CI_AS. Both are equivalent here since it is not likely that the Collation of the new DB will ever change (and if it does, that means you will have a new DB from the client that does not include your changes anyway ;-).



FYI: There are not many differences between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS. They both use the same Locale -- 1033 -- and same Code Page -- 1252. The main differences are:


The SQL_ Collations have been deprecated for a long time and should be avoided if possible
They treat apostrophes and dashes within words differently
The non-SQL_ Collations allow for some expansions / normalization offered by Unicode that will equate more characters than the SQL_ Collations.