Collation question

INSERT INTO #TMP_Table#
Select * from tabCS, tabCI where
tabCS.col1 COLLATE SQL_Latin1_General_CP1_CS_AS = tabCI.col1 COLLATE SQL_Latin1_General_CP1_CS_AS
Currently this query is run in a case insensitive server:
tabCS is table from a case sensitive server and
tabCI is a table from a Case insensitive server which is the same
server as the above query is run.
Can you please let me know #TMP_Table# is case sensitive or not?
Thanks in advance.Since you are INSERTing into a #temp table, you had to first create it. It
is the creation step that will control what collation the table uses.
CREATE TABLE #TMP_Table# -- Use the COLLATE clause of column definitions
SELECT * INTO #TMP_Table# FROM tabCS -- Uses the collations in tabCS
You should also read the Books Online topic "Collations in Distributed
Queries", for how collations are treated across linked servers.
RLF
<sweetpotatop@.yahoo.com> wrote in message
news:1174668894.028880.118650@.n59g2000hsh.googlegroups.com...
> INSERT INTO #TMP_Table#
> Select * from tabCS, tabCI where
> tabCS.col1 COLLATE SQL_Latin1_General_CP1_CS_AS => tabCI.col1 COLLATE SQL_Latin1_General_CP1_CS_AS
> Currently this query is run in a case insensitive server:
> tabCS is table from a case sensitive server and
> tabCI is a table from a Case insensitive server which is the same
> server as the above query is run.
> Can you please let me know #TMP_Table# is case sensitive or not?
> Thanks in advance.
>|||On Mar 23, 2:30 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Since you are INSERTing into a #temp table, you had to first create it. It
> is the creation step that will control what collation the table uses.
> CREATE TABLE #TMP_Table# -- Use theCOLLATEclause of column definitions
> SELECT * INTO #TMP_Table# FROM tabCS -- Uses the collations in tabCS
> You should also read the Books Online topic "Collations in Distributed
> Queries", for how collations are treated across linked servers.
> RLF
> <sweetpota...@.yahoo.com> wrote in message
> news:1174668894.028880.118650@.n59g2000hsh.googlegroups.com...
>
> > INSERT INTO #TMP_Table#
> > Select * from tabCS, tabCI where
> > tabCS.col1COLLATESQL_Latin1_General_CP1_CS_AS => > tabCI.col1COLLATESQL_Latin1_General_CP1_CS_AS
> > Currently this query is run in a case insensitive server:
> > tabCS is table from a case sensitive server and
> > tabCI is a table from a Case insensitive server which is the same
> > server as the above query is run.
> > Can you please let me know #TMP_Table# is case sensitive or not?
> > Thanks in advance.- Hide quoted text -
> - Show quoted text -
Usually there is no need to "CREATE" a table. In that case, what will
be the default? Will it take whatever from the local server?|||> Usually there is no need to "CREATE" a table. In that case, what will
> be the default? Will it take whatever from the local server?
You cannot insert into a table that doesn't exist. The collation for the column is determined when
you created the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<sweetpotatop@.yahoo.com> wrote in message
news:1174677392.350919.149260@.n59g2000hsh.googlegroups.com...
> On Mar 23, 2:30 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
>> Since you are INSERTing into a #temp table, you had to first create it. It
>> is the creation step that will control what collation the table uses.
>> CREATE TABLE #TMP_Table# -- Use theCOLLATEclause of column definitions
>> SELECT * INTO #TMP_Table# FROM tabCS -- Uses the collations in tabCS
>> You should also read the Books Online topic "Collations in Distributed
>> Queries", for how collations are treated across linked servers.
>> RLF
>> <sweetpota...@.yahoo.com> wrote in message
>> news:1174668894.028880.118650@.n59g2000hsh.googlegroups.com...
>>
>> > INSERT INTO #TMP_Table#
>> > Select * from tabCS, tabCI where
>> > tabCS.col1COLLATESQL_Latin1_General_CP1_CS_AS =>> > tabCI.col1COLLATESQL_Latin1_General_CP1_CS_AS
>> > Currently this query is run in a case insensitive server:
>> > tabCS is table from a case sensitive server and
>> > tabCI is a table from a Case insensitive server which is the same
>> > server as the above query is run.
>> > Can you please let me know #TMP_Table# is case sensitive or not?
>> > Thanks in advance.- Hide quoted text -
>> - Show quoted text -
> Usually there is no need to "CREATE" a table. In that case, what will
> be the default? Will it take whatever from the local server?
>|||<sweetpotatop@.yahoo.com> wrote in message
news:1174677392.350919.149260@.n59g2000hsh.googlegroups.com...
> Usually there is no need to "CREATE" a table. In that case, what will
> be the default? Will it take whatever from the local server?
>
If you're doing an INSERT INTO there is.
You may be thinking SELECT INTO.
In which case I BELIEV (but would have to test) that the collation will be
of the database you create it in. (If not, then it would be the one that
tempdb has.)
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 23, 3:36 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <sweetpota...@.yahoo.com> wrote in message
> news:1174677392.350919.149260@.n59g2000hsh.googlegroups.com...
>
> > Usually there is no need to "CREATE" a table. In that case, what will
> > be the default? Will it take whatever from the local server?
> If you're doing an INSERT INTO there is.
> You may be thinking SELECT INTO.
> In which case I BELIEV (but would have to test) that the collation will be
> of the database you create it in. (If not, then it would be the one that
> tempdb has.)
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
Oh yes, I mean SELECT INTO, so what happens to the temporary
collation? I think it is not taking the local server's collation...|||> Oh yes, I mean SELECT INTO, so what happens to the temporary
> collation?
For SELECT INTO, the collation is determined by the source column's data.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<sweetpotatop@.yahoo.com> wrote in message
news:1174679399.030741.227080@.n59g2000hsh.googlegroups.com...
> On Mar 23, 3:36 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>> <sweetpota...@.yahoo.com> wrote in message
>> news:1174677392.350919.149260@.n59g2000hsh.googlegroups.com...
>>
>> > Usually there is no need to "CREATE" a table. In that case, what will
>> > be the default? Will it take whatever from the local server?
>> If you're doing an INSERT INTO there is.
>> You may be thinking SELECT INTO.
>> In which case I BELIEV (but would have to test) that the collation will be
>> of the database you create it in. (If not, then it would be the one that
>> tempdb has.)
>> --
>> Greg Moore
>> SQL Server DBA Consulting
>> Email: sql (at) greenms.com http://www.greenms.com
> Oh yes, I mean SELECT INTO, so what happens to the temporary
> collation? I think it is not taking the local server's collation...
>|||On Mar 23, 3:53 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Oh yes, I mean SELECT INTO, so what happens to the temporary
> > collation?
> For SELECT INTO, the collation is determined by the source column's data.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <sweetpota...@.yahoo.com> wrote in message
> news:1174679399.030741.227080@.n59g2000hsh.googlegroups.com...
>
> > On Mar 23, 3:36 pm, "Greg D. Moore \(Strider\)"
> > <mooregr_deletet...@.greenms.com> wrote:
> >> <sweetpota...@.yahoo.com> wrote in message
> >>news:1174677392.350919.149260@.n59g2000hsh.googlegroups.com...
> >> > Usually there is no need to "CREATE" a table. In that case, what will
> >> > be the default? Will it take whatever from the local server?
> >> If you're doing an INSERT INTO there is.
> >> You may be thinking SELECT INTO.
> >> In which case I BELIEV (but would have to test) that the collation will be
> >> of the database you create it in. (If not, then it would be the one that
> >> tempdb has.)
> >> --
> >> Greg Moore
> >> SQL Server DBA Consulting
> >> Email: sql (at) greenms.com http://www.greenms.com
> > Oh yes, I mean SELECT INTO, so what happens to the temporary
> > collation? I think it is not taking the local server's collation...- Hide quoted text -
> - Show quoted text -
Then is there a quick way to specify all temporary tables will be
created in case insentive? And ignore what case sensitivity of the
source table or server?
Thanks in advance.|||<sweetpotatop@.yahoo.com> wrote in message
news:1174915057.926229.29490@.y80g2000hsf.googlegroups.com...
> Then is there a quick way to specify all temporary tables will be
> created in case insentive? And ignore what case sensitivity of the
> source table or server?
Yes, use the COLLATION parameter when creating the table.
> Thanks in advance.
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||As Greg said, SELECT INTO #temp# will create the table based on the
underlying properties of the source.
If the source table does not have collation defined then it would use the
source server collation. Since your source is from 2 servers then it's quite
likely it'll use the first servers collation for the temp table.
As you have to have the destination use case insensitive collation then you
would need to create the temp table first, specifying the collation, before
filling it with data. If you don't know what the temp table structure will be
(as you may possibly have unknown queries populating it), then that's a lot
more work but still doable.
Just insert the TOP 1 record into the temp table, then alter it to change
the collation, then do the full insert of data.