You may use COLLATE DATABASE_DEFAULT for columns coming from other databases.

Unless some tables in the database have not DB default collation.

Same for temp tables:

CREATE TABLE ##TS_ACCT (

[TS_ID] [nvarchar](100) COLLATE DATABASE_DEFAULT NULL

)