I have a query which selects rows from a source database (DatabaseA), and inserts them into a target database (Database B). The collation type differs between the databases and they cannot be changed. I need to address the collation difference in my query by explicitly specifying the collation for varchar fields.
Currently my query looks like this:
INSERT INTO DatabaseB.dbo.Users( Id, UserNumber, FirstName, Surname, Address1, Address2, AddressTown, AddressCity ) SELECT Id, UserNumber, FirstName COLLATE SQL_Latin1_General_CI_AS, Surname COLLATE SQL_Latin1_General_CI_AS, Address1 COLLATE SQL_Latin1_General_CI_AS, Address2 COLLATE SQL_Latin1_General_CI_AS, AddressTown COLLATE SQL_Latin1_General_CI_AS, AddressCity COLLATE SQL_Latin1_General_CI_AS FROM DatabaseA.dbo.Users
My question is, can I avoid typing the collation type for every string-based field? Is there a way that I can specify the collation type for the whole query at once? If this is not possible, are there any other shortcuts?