• SQL Server Creating a temp table with collation

    Posted on July 16, 2012 by in Database, SQL Server

    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

    How to find the collation for a database

    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;
    

    The Problem

    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.

    The solution(s)

    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)
    
    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    Vanamali Juvvadi is a Web enthusiast and loves all things design and technology. Founded qnownow with a group of friends to share anything/everything they know/find on the internet.

    View all articles by

    Email : [email protected]

    Leave a Reply