Combining Columns and Grouping By....

Hi,
I have the following SQL

SELECT Table1.Col1, Table3.Col1 AS Expr1,
COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,
FROM Table3
INNER JOIN Table2 ON Table3.Col1=Table2.Col1
RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2
GROUP BY Table1.Col1, Table3.Col1

The output rows have a value in either Table1.Col1 or Table3.Col1 but not
both.
I'd like to combine Table1.Col1 and Table3.Col1 and group by the combined
column in the result but don't know how.
Thanks gratefullyHi

It would help if you posted the DDL (Create Table Statements) , example data
(as insert statements) and expected output. From your description it is not
100% clear how the tables relate or what results you expect.

If the values of Col1 are unique between each table your solution might be:

SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table1
GROUP BY Col1
UNION
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table3
GROUP BY Col1

If not

SELECT IsNULL(T1.Col1,T3.Col1), COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col2
ELSE T3.Col2 END ) AS Col2No, COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col3
ELSE T3.Col3 END ) AS Col3No
FROM Table1 T1
LEFT JOIN Table3 T3 ON T1.Col2 = T3.Col2
GROUP BY IsNULL(T1.Col1,T3.Col1)

or more probably

SELECT Col1, SUM(Col2No) as Col2No, SUM(Col3No) as Col3No
FROM (
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table1
GROUP BY Col1
UNION
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table3
GROUP BY Col1 ) A
GROUP BY Col1

John

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:ovWhb.854$_54.168325@.newsfep2-win.server.ntli.net...
> Hi,
> I have the following SQL
> SELECT Table1.Col1, Table3.Col1 AS Expr1,
> COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,
> FROM Table3
> INNER JOIN Table2 ON Table3.Col1=Table2.Col1
> RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2
> GROUP BY Table1.Col1, Table3.Col1
> The output rows have a value in either Table1.Col1 or Table3.Col1 but not
> both.
> I'd like to combine Table1.Col1 and Table3.Col1 and group by the combined
> column in the result but don't know how.
> Thanks gratefully|||Thanks John,
I didn't explain too well so I'll detail tables, releationships and what I'm
trying to do. I have managed to reduce & simplify the issue to two tables:-

Targets table which has columns:
target id - key identity autoincrement integer
locationid - integer

Actions table which has columns:
actionid - key identity autoincrement integer
targetid - integer
locationid integer

relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targetid =
Actions.targetid (I want results from all rows in Actions).

I want to count all rows from Actions and group by locationid combined from
both tables.

Targets content:
targetid locationid
1 1
2 1

Actions Content:
actionid targetid locationid
1 NULL 1
2 NULL 2
3 NULL 3
4 1 NULL
5 1 NULL
6 2 NULL

If I use:
SELECT Actions.locationid, Targets.locationid, COUNT(actionid) AS actions
FROM Targets RIGHT JOIN Actions ON Targets.targetid = Actions.target id
GROUP BY Actions.locationid, Targets.locationid

I get:
Actions Actions.locationid Targets.locationid
1 1 NULL
1 2 NULL
1 3 NULL
3 NULL 1

I want to combine both locationid columns in result giving:
Actions locationid
4 1
1 2
1 3

There are more columns than illustrated but if you the above can be cracked,
I'll be away!
Cheers,
Jack

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f886ac8$0$11451$afc38c87@.news.easynet.co.uk. ..
> Hi
> It would help if you posted the DDL (Create Table Statements) , example
data
> (as insert statements) and expected output. From your description it is
not
> 100% clear how the tables relate or what results you expect.
> If the values of Col1 are unique between each table your solution might
be:
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table1
> GROUP BY Col1
> UNION
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table3
> GROUP BY Col1
> If not
> SELECT IsNULL(T1.Col1,T3.Col1), COUNT(CASE WHEN T1.Col1 IS NULL THEN
T1.Col2
> ELSE T3.Col2 END ) AS Col2No, COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col3
> ELSE T3.Col3 END ) AS Col3No
> FROM Table1 T1
> LEFT JOIN Table3 T3 ON T1.Col2 = T3.Col2
> GROUP BY IsNULL(T1.Col1,T3.Col1)
> or more probably
> SELECT Col1, SUM(Col2No) as Col2No, SUM(Col3No) as Col3No
> FROM (
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table1
> GROUP BY Col1
> UNION
> SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
> FROM Table3
> GROUP BY Col1 ) A
> GROUP BY Col1
> John|||John,

Thanks for putting me on the right track. With ref to the example in my
reply post I used:

SELECT ISNULL(Actions.locationid, Targets.locationid) AS Location,
COUNT(Actions.actionid) AS Actions_No
FROM Actions LEFT OUTER JOIN
Targets ON Actions.targetid = Targets.targetid
GROUP BY ISNULL(Actions.locationid, Targets.locationid)

All the other columns I want to count are in the Actions table so I just
need to add them to the SELECT statement.
Thanks again,
Jack

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:Fu0ib.1525$_54.280845@.newsfep2-win.server.ntli.net...
> Thanks John,
> I didn't explain too well so I'll detail tables, releationships and what
I'm
> trying to do. I have managed to reduce & simplify the issue to two
tables:-
> Targets table which has columns:
> target id - key identity autoincrement integer
> locationid - integer
> Actions table which has columns:
> actionid - key identity autoincrement integer
> targetid - integer
> locationid integer
> relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targetid =
> Actions.targetid (I want results from all rows in Actions).
> I want to count all rows from Actions and group by locationid combined
from
> both tables.
> Targets content:
> targetid locationid
> 1 1
> 2 1
> Actions Content:
> actionid targetid locationid
> 1 NULL 1
> 2 NULL 2
> 3 NULL 3
> 4 1 NULL
> 5 1 NULL
> 6 2 NULL
> If I use:
> SELECT Actions.locationid, Targets.locationid, COUNT(actionid) AS actions
> FROM Targets RIGHT JOIN Actions ON Targets.targetid = Actions.target id
> GROUP BY Actions.locationid, Targets.locationid
> I get:
> Actions Actions.locationid Targets.locationid
> 1 1 NULL
> 1 2 NULL
> 1 3 NULL
> 3 NULL 1
> I want to combine both locationid columns in result giving:
> Actions locationid
> 4 1
> 1 2
> 1 3
> There are more columns than illustrated but if you the above can be
cracked,
> I'll be away!
> Cheers,
> Jack|||Hi

It sounds like it worked then!

Here is usable DDL and example data in case you need it again.

create table Targets (
targetid integer NOT NULL identity (1,1) CONSTRAINT PK_Targets PRIMARY KEY,
locationid integer,
)

create table Actions (
actionid integer NOT NULL identity (1,1) CONSTRAINT PK_Actions PRIMARY KEY,
targetid integer NULL,
locationid integer,
CONSTRAINT FK_Actions FOREIGN KEY (TargetId) REFERENCES Targets(TargetId)
)

INSERT INTO Targets (locationid) VALUES (1)
INSERT INTO Targets (locationid) VALUES (1)

INSERT INTO Actions (targetid, locationid) VALUES (NULL,1)
INSERT INTO Actions (targetid, locationid) VALUES (NULL,2)
INSERT INTO Actions (targetid, locationid) VALUES (NULL,3)
INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
INSERT INTO Actions (targetid, locationid) VALUES (2,NULL)

SELECT * FROM Targets

/*
targetid locationid
---- ----
1 1
2 1

(2 row(s) affected)
*/
SELECT * FROM Actions

/*
actionid targetid locationid
---- ---- ----
1 NULL 1
2 NULL 2
3 NULL 3
4 1 NULL
5 1 NULL
6 2 NULL

(6 row(s) affected)
*/

-- Your attempt
SELECT A.locationid, T.locationid, COUNT(A.actionid) AS actions
FROM Targets T RIGHT JOIN Actions A ON T.targetid = A.targetid
GROUP BY A.locationid, T.locationid

/*
locationid locationid actions
---- ---- ----
1 NULL 1
2 NULL 1
3 NULL 1
NULL 1 3

(4 row(s) affected)
*/

-- Your second attempt
SELECT ISNULL(A.locationid, T.locationid) AS Location,
COUNT(A.actionid) AS Actions_No
FROM Actions A LEFT OUTER JOIN Targets T ON A.targetid = T.targetid
GROUP BY ISNULL(A.locationid, T.locationid)

/* Gives
Location Actions_No
---- ----
1 4
2 1
3 1

(3 row(s) affected)
*/

John

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:498ib.4706$_54.349437@.newsfep2-win.server.ntli.net...
> John,
> Thanks for putting me on the right track. With ref to the example in my
> reply post I used:
> SELECT ISNULL(Actions.locationid, Targets.locationid) AS Location,
> COUNT(Actions.actionid) AS Actions_No
> FROM Actions LEFT OUTER JOIN
> Targets ON Actions.targetid = Targets.targetid
> GROUP BY ISNULL(Actions.locationid, Targets.locationid)
> All the other columns I want to count are in the Actions table so I just
> need to add them to the SELECT statement.
> Thanks again,
> Jack
> "JackT" <turnbull.jack@.ntlworld.com> wrote in message
> news:Fu0ib.1525$_54.280845@.newsfep2-win.server.ntli.net...
> > Thanks John,
> > I didn't explain too well so I'll detail tables, releationships and what
> I'm
> > trying to do. I have managed to reduce & simplify the issue to two
> tables:-
> > Targets table which has columns:
> > target id - key identity autoincrement integer
> > locationid - integer
> > Actions table which has columns:
> > actionid - key identity autoincrement integer
> > targetid - integer
> > locationid integer
> > relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targetid =
> > Actions.targetid (I want results from all rows in Actions).
> > I want to count all rows from Actions and group by locationid combined
> from
> > both tables.
> > Targets content:
> > targetid locationid
> > 1 1
> > 2 1
> > Actions Content:
> > actionid targetid locationid
> > 1 NULL 1
> > 2 NULL 2
> > 3 NULL 3
> > 4 1 NULL
> > 5 1 NULL
> > 6 2 NULL
> > If I use:
> > SELECT Actions.locationid, Targets.locationid, COUNT(actionid) AS
actions
> > FROM Targets RIGHT JOIN Actions ON Targets.targetid = Actions.target id
> > GROUP BY Actions.locationid, Targets.locationid
> > I get:
> > Actions Actions.locationid Targets.locationid
> > 1 1 NULL
> > 1 2 NULL
> > 1 3 NULL
> > 3 NULL 1
> > I want to combine both locationid columns in result giving:
> > Actions locationid
> > 4 1
> > 1 2
> > 1 3
> > There are more columns than illustrated but if you the above can be
> cracked,
> > I'll be away!
> > Cheers,
> > Jack
>|||Thanks John,
Appreciate your informative close-out post and will certainly file for
reference.
Cheers,
Jack

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f891931$0$11446$afc38c87@.news.easynet.co.uk. ..
> Hi
> It sounds like it worked then!
> Here is usable DDL and example data in case you need it again.
> create table Targets (
> targetid integer NOT NULL identity (1,1) CONSTRAINT PK_Targets PRIMARY
KEY,
> locationid integer,
> )
> create table Actions (
> actionid integer NOT NULL identity (1,1) CONSTRAINT PK_Actions PRIMARY
KEY,
> targetid integer NULL,
> locationid integer,
> CONSTRAINT FK_Actions FOREIGN KEY (TargetId) REFERENCES Targets(TargetId)
> )
> INSERT INTO Targets (locationid) VALUES (1)
> INSERT INTO Targets (locationid) VALUES (1)
> INSERT INTO Actions (targetid, locationid) VALUES (NULL,1)
> INSERT INTO Actions (targetid, locationid) VALUES (NULL,2)
> INSERT INTO Actions (targetid, locationid) VALUES (NULL,3)
> INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
> INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
> INSERT INTO Actions (targetid, locationid) VALUES (2,NULL)
> SELECT * FROM Targets
> /*
> targetid locationid
> ---- ----
> 1 1
> 2 1
> (2 row(s) affected)
> */
> SELECT * FROM Actions
> /*
> actionid targetid locationid
> ---- ---- ----
> 1 NULL 1
> 2 NULL 2
> 3 NULL 3
> 4 1 NULL
> 5 1 NULL
> 6 2 NULL
> (6 row(s) affected)
> */
> -- Your attempt
> SELECT A.locationid, T.locationid, COUNT(A.actionid) AS actions
> FROM Targets T RIGHT JOIN Actions A ON T.targetid = A.targetid
> GROUP BY A.locationid, T.locationid
> /*
> locationid locationid actions
> ---- ---- ----
> 1 NULL 1
> 2 NULL 1
> 3 NULL 1
> NULL 1 3
> (4 row(s) affected)
> */
> -- Your second attempt
> SELECT ISNULL(A.locationid, T.locationid) AS Location,
> COUNT(A.actionid) AS Actions_No
> FROM Actions A LEFT OUTER JOIN Targets T ON A.targetid = T.targetid
> GROUP BY ISNULL(A.locationid, T.locationid)
> /* Gives
> Location Actions_No
> ---- ----
> 1 4
> 2 1
> 3 1
> (3 row(s) affected)
> */
>
> John

ColumnName Parameter for Table Valued Function

Hi All,

Is it possible to do the following:
1. I have a udf_xxx which returns Col1,Col2 and Col3
2. I need to join this udf_xxx to get Col1 and Col2 where Col3 matches with table tbl1.

SELECT udf.Col1, udf.Col2, t.Col3
FROM dbo.tbl1 t
JOIN dbo.udf_xxx (t.Col3) udf ON
t.Col3 = udf.Col3

Thanks in advance.In SQL Server 2005, you could use CROSS (OUTER) APPLY:
SELECT udf.Col1, udf.Col2, t.Col3
FROM dbo.tbl1 t
CROSS APPLY dbo.udf_xxx (t.Col3) udf|||I just happened to find out that too. Thanks Konstantin Kosinsky!

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.googlegr oups.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.googlegr oups.com...
>
>
>
> - 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.googlegr oups.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.googlegr oups.com...
>
>
> 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:
> 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.googlegr oups.com...
>
>
>
>
>
> - 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.googlegro ups.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.

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.

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. I
t
> 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...
>
>
>
>
>
> - 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 col
umn 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:
> 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...
>
>
> 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:
> 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:
> For SELECT INTO, the collation is determined by the source column's data.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> <sweetpota...@.yahoo.com> wrote in message
> news:1174679399.030741.227080@.n59g2000hsh.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - 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 b
e
(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.