By design a table shouldn’t contain duplicate rows. However sometimes under peculiar cases a table may end up having duplicate...
Using a temporary table is a convenient way to store intermediate results, and then use them at a later phase in our application logic. When using temporary tables SQL Server will inherit the collation for our newly created temporary table from the SQL Server instance default. In case our SQL Server database has a different collation setting than the instance’s default , you might get the error similar to
Cannot resolve collation conflict for equal to operation
For finding Collation of SQL Server instance default, use the query below
SELECT SERVERPROPERTY ('Collation');
For finding Collation of specific database use the query below
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;
Assume that you had to create temporary table like below
CREATE TABLE #TMP_TABLE (BUSINESS_UNIT varchar(10), JOURNAL_ID varchar(10), JOURNAL_DATE varchar(10)); INSERT INTO #TMP_TABLE VALUES ('NW011','JV0010012239','12/31/2011'); INSERT INTO #TMP_TABLE VALUES ('NW011','JV0010012240','12/31/2011')
Now if you want to join the values of the temporary table and another table in the database like below
SELECT COUNT(A.JOURNAL_ID) FROM #TMP_TABLE A WHERE EXISTS (SELECT 'X' FROM PS_JRNL_HEADER B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.JOURNAL_ID = B.JOURNAL_ID AND A.JOURNAL_DATE = B.JOURNAL_DATE)
If the collation of instance and the database are different you will encounter collation issues.
One way of getting rid of the problem is use SELECT INTO statement to create the table instead standard create table statement. If you create a temp table using this method, the fields take the collation from the source table, which eliminates the error
SELECT BUSINESS_UNIT,JOURNAL_ID, JOURNAL_DATE INTO #TMP_TABLE FROM PS_JRNL_HEADER WHERE 1 =0
In the above query where 1=0 is used to copy the structure of a table but not the data.
If you want create temporary table with more columns than source table, or a different datatype then the source table, you can try using CAST and CONVERT function like below
SELECT BUSINESS_UNIT,JOURNAL_ID, JOURNAL_DATE,CAST(NULL AS CHAR(5)) FIELD1,CAST(0 AS INT) FIELD2 INTO #TMP_TABLE FROM PS_JRNL_HEADER WHERE 1 =0
If you want to create a temporary table using CREATE TABLE statement than you can use COLLATE explicitly like the query below
CREATE TABLE #TMP_TABLE (BUSINESS_UNIT varchar(10) COLLATE DATABASE_DEFAULT, JOURNAL_ID varchar(10) COLLATE DATABASE_DEFAULT, JOURNAL_DATE varchar(10) COLLATE DATABASE_DEFAULT)