Combining Two Tables Via T-SQL

Hello,

I have two tables that have different column names so I can not combine them using UNION statement. Is there a way to combine two tables and have all the columns from both tables.

Thank you for your help!

UNION does not require that the column names be the same, only that the datatypes are similar enough to combine. See this Example:

Code Snippet

USE Northwind
GO

SELECT CompanyName FROM Customers
UNION
SELECT FirstName + ' ' + LastName FROM Employees

CompanyName
-
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Andrew Fuller
Anne Dodsworth
Antonio Moreno Taquería
Around the Horn
Berglunds snabbk?p
Bill Smith
...

|||

Donnie:

The column names do not have to be the same for you to union together two tables. If you are trying to union together to tables column-for-column, it is sufficient to have:

The number of columns the same The datatypes of corresponding columns be the same|||

Can you expand on what you are trying to accomplish?. As long as the data type of the columns be the same, including collation, then there is no problem using union or "union all".

declare @.t1 table(c1 int, c2 int)

declare @.t2 table(c3 int, c4 int)

insertinto @.t1 values(1, 2)

insertinto @.t2 values(3, 4)

select c1, c2 from @.t1

union all

select c3, c4 from @.t2

AMB

|||

Are you certain that it is a UNION that you need to perform, and not a JOIN?

A JOIN will allow you to return all columns from both tables as individual columns within the same resultset (i.e. merge the data vertically), like so:

Code Snippet

Table 1 - Sample Data

Column1a Column2a Column3a

--

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

Table 2 - Sample Data

Column1b Column2b Column3b

--

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

Output

Column1a Column2a Column3a Column1b Column2b Column3b

--

1 T1C2R1 T1C3R1 1 T2C2R1 T2C3R1

2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2

3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3

SELECT t1.Column1a,
t1.Column2a,
t1.Column3a,
t2.Column1b,
t2.Column2b,
t2.Column3b
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Column1a = t2.Column1b

A UNION will allow you to horizontally merge the data from both tables, like so:

Code Snippet

Table 1 - Sample Data

Column1a Column2a Column3a

--

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

Table 2 - Sample Data

Column1b Column2b Column3b

--

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

Output

Column1 Column2 Column3

-

1 T1C2R1 T1C3R1

2 T1C2R2 T1C3R2

3 T1C2R3 T1C3R3

1 T2C2R1 T2C3R1

2 T2C2R2 T2C3R2

3 T2C2R3 T2C3R3

SELECT t1.Column1a AS Column1,
t1.Column2a AS Column2,
t1.Column3a AS Column3

FROM Table1 t1

UNION ALL

SELECT t2.Column1b,
t2.Column2b,
t2.Column3b

FROM Table2 t2

Chris|||

Some kind of join is probably a good idea since I want to join matching rows as well as non matching rows from both tables. Maybe, a full join would be good but I don't want duplicates. Please see my example of the output. What do you think?

Thanks for your help!

Table 1 - Sample Data Column1a Column2a Column3a 1 T1C2R1 T1C3R1 2 T1C2R2 T1C3R2 3 T1C2R3 T1C3R3 5 T1C2R5 T1C3R5 Table 2 - Sample Data Column1b Column2b Column3b 4 T2C2R4 T2C3R4 2 T2C2R2 T2C3R2 3 T2C2R3 T2C3R3 6 T2C2R6 T2C3R6 Ouptput: Column1a Column2a Column3a Column1b Column2b Column3b 1 T1C2R1 T1C3R1 NULL NULL NULL 2 T1C2R2 T1C3R2 2 T2C2R2 T2C3R2 3 T1C2R3 T1C3R3 3 T2C2R3 T2C3R3 NULL NULL NULL 4 T2C2R4 T2C3R4 5 T1C2R5 T1C3R5 NULL NULL NULL NULL NULL NULL 6 T2C2R6 T2C3R6

|||

Yes, a full join should work for you.

SELECT a.Column1a, a.Column2a, a.Column3a,

b.Column1b, b.Column2b, b.Column3b

FROM Table1 a FULL JOIN Table2 b ON (a.Column1a = b.Column1b)

There should not be any duplicates in the result set.

Combining two rows in a view

I have created a view for reporting. Im basically just joining a few
tables. It is for a University so the results shows students names and
the credits they are currently taking and the school code (There is 3
Colleges under one ownership)
The problem is some students attend two colleges and appear twice,
one for each enrollment. For example
FName LName Credits SchoolCode
John Smith 12 1468
John Smith 4 1469
I need to combine these results so it would look like this
John Smith 16 1468
This is not for all students just certain ones. I would like to do
this in the view if possible. Any help is appreciated.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Programming...50.h
tml
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=904750Looks like you want to return just one of the school codes? In that case,
just group the data by student, and aggregate the measures:
SELECT StudentID, FName, LName, SUM(Credits) AS TotalCredits,
MIN(ScheelCode) AS MinSchoolCode
FROM ViewName
GROUP BY StudentID, FName, LName;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"TheCount" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_904750_a05cfa9ea57158f694c614723c
ee26e9@.dbforumz.com...
>I have created a view for reporting. I'm basically just joining a few
> tables. It is for a University so the results shows students names and
> the credits they are currently taking and the school code (There is 3
> Colleges under one ownership)
> The problem is some students attend two colleges and appear twice,
> one for each enrollment. For example
> FName LName Credits SchoolCode
> John Smith 12 1468
> John Smith 4 1469
> I need to combine these results so it would look like this
> John Smith 16 1468
> This is not for all students just certain ones. I would like to do
> this in the view if possible. Any help is appreciated.
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Programming...pict262850.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=904750|||Take a look at this example:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML

Combine Names

Hello.
A quick layout of what I am trying to do.
I have 3 fields in a table. There names are..
fname, lname and fullname.
What I would like to know is, how do I combine the first 2 fields to show
the results as the full name in the fullname field?
Is this possible. If so, can someone please explain.
Thank you so much.You can use a computed column, basically:
ALTER TABLE your_table DROP COLUMN fullname
ALTER TABLE your_table ADD fullname
AS fname + ' ' + lname
Jacco Schalkwijk
SQL Server MVP
"noixa1234" <noixa1234@.discussions.microsoft.com> wrote in message
news:300E0247-E903-44CD-A6AB-68A64D7489F5@.microsoft.com...
> Hello.
> A quick layout of what I am trying to do.
> I have 3 fields in a table. There names are..
> fname, lname and fullname.
> What I would like to know is, how do I combine the first 2 fields to show
> the results as the full name in the fullname field?
> Is this possible. If so, can someone please explain.
> Thank you so much.|||If the Fname and/or the Lname might be null, then you need to use the follow
ing
IsNull(FName+ ' ', '') + IsNull(LName, '')
If only the first name might be null then you can use
IsNull(FName+ ' ', '') + LName
Charly
"noixa1234" wrote:

> Hello.
> A quick layout of what I am trying to do.
> I have 3 fields in a table. There names are..
> fname, lname and fullname.
> What I would like to know is, how do I combine the first 2 fields to show
> the results as the full name in the fullname field?
> Is this possible. If so, can someone please explain.
> Thank you so much.|||Select FName, LName , FName + ' ' + LName as Fullname from <source table>
I assume FName and Lname are either char or varchar fields; you can simply
concatenate to create the full name.
regards,
Sarav...
"noixa1234" <noixa1234@.discussions.microsoft.com> wrote in message
news:300E0247-E903-44CD-A6AB-68A64D7489F5@.microsoft.com...
> Hello.
> A quick layout of what I am trying to do.
> I have 3 fields in a table. There names are..
> fname, lname and fullname.
> What I would like to know is, how do I combine the first 2 fields to show
> the results as the full name in the fullname field?
> Is this possible. If so, can someone please explain.
> Thank you so much.|||Here is an example:
use northwind
go
select employeeid, lastname, firstname
into dbo.t1
from dbo.employees
go
-- you do not need a computed column.
-- you can do the concatenation in a select statement
alter table dbo.t1
add fullname as lastname + ', ' + firstname
go
select * from dbo.t1
go
drop table dbo.t1
go
AMB
"noixa1234" wrote:

> Hello.
> A quick layout of what I am trying to do.
> I have 3 fields in a table. There names are..
> fname, lname and fullname.
> What I would like to know is, how do I combine the first 2 fields to show
> the results as the full name in the fullname field?
> Is this possible. If so, can someone please explain.
> Thank you so much.

Columns names

I need a querry to get all columns names.
thanksAll possible column names, or just the ones in your database?

Check out INFORMATION_SCHEMA.COLUMNS (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ia-iz_87w3.asp) to see if that will help.

-PatP|||Select query against the Syscolumns table should work for you.|||Join Sysobjects to Syscolumns (ID) to get the table name corresponding each column.|||Just for what it is worth, the information_schema.columns view does exactly what the user wants, it is documented, works on many database engine platforms, and removes the need to reference system tables.

-PatP

ColumnName size

Do large column names hamper performance?Names - no widths - yes. Increases the physical I/O required to process
queries.
HTH
Jerry
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:003C3771-3BA5-4A37-B7A9-0951B2BCAD09@.microsoft.com...
> Do large column names hamper performance?|||Possibly. At the very least longer names might increase network traffic if
they are being used in SQL batches. But if you are experiencing a
performance problem then I would look for other potential causes first.
An unusually long column name perhaps indicates either a poor naming
convention or, worse, that you are representing some element of data in a
column name. Either of those are good reasons to change the name - with or
without any performance impact. For the record, most of the column names in
my current project are less than 30 characters long.
David Portas
SQL Server MVP
--

columnname in sql

how to select all column names from sql whose columnvalue is '1' for a specific user

Could you please post the table structure? Ideally the Create Table SQL.

Thanks,

Matt

|||

Thankyou for the response Matt.

Actually the entire column in the table is dynamically created.it will grow everytime wen user add .but it wont happen frequently.so i cant post that table struct.let me describe my needs with this sample

Create table sample(id varchar(10), item1 bit,item2 bit,item3 bit,item4 bit)...... and goes on

now i need to select all column names ,actually i can do this.

but i jus need to select columns whose value is one.

eg.

id item1 item2 item3 item4

zzz 1 0 0 1

yyy 0 1 1 1

my sql qurey or SP should return the column name for specific id whose value is one.

lets say id=zzz it should returnItem1 and item4(i meant column name)

if id = yyy it should return item2,item3,item4(i meant column name)

Thanks in advance

|||

HiTweety@.net,

As far as i know you need to use cursor in your case. But where are planing to store those column names??

See the following codes i've written for you:

declare @.item1int,@.item2int,@.item3int,@.item4intdeclare cursor_test cursorforselect * from test_tblopen cursor_testfetch next from cursor_testwhile @.@.fetch_status=0beginfetch next from cursor_testinto @.item1,@.item2,@.item3,@.item4if(@.item1=1)print'column 1 name'if(@.item2=1)print'column 2 name'if(@.item3=1)print'column 3 name'if(@.item4=1)print'column 4 name'endclose cursor_testdeallocate cursor_test

BTW, if possible, i would suggest you write the code in your application(using c# or other .net languages instead of T-SQL)

Hope my suggestion helps

|||

Hi Thanks for the Response,

i managed to solve this issue by retreiving the column names in table first and then checking the value for one using vb program

column names, table name, user name

Hi;
I am new to SQL2000 & T-SQL
Is there a T-SQL SELECT statement I can use to query these info :
1) existing column names of a table (select * will give col name & all data
rows, I only wish to see columns names)
2) data types of columns (object viewer can do this, but is there a SELECT s
tatemtent to get these info?)
3) existing tables in a database (system & users' tables)
4) existing database users in a databaseFor the first three, you can use sp_help. For the last one, you can use
sp_helpuser.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||1) you could do - select * from table where 0=1
or to get all columns - select column_name from
INFORMATION_SCHEMA.columns
2) You can get all of this from INFORMATION_SCHEMA.columns
3) select table_name from INFORMATION_SCHEMA.tables will get you all user
tables
or select name from sysobjects where type = 's' or type = 'u' to get all
tables
4) select * from sysusers where issqluser = 1
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||Column names and other pertinent info is stored in system table syscolumns.
You can get to your first two questions joining sysobjects with syscolumns.
Question 3 is sysobjects only. Question 4 is sysusers. But after replyin
g I would like to know what
exactly you're trying to learn.
Since you can't answer this question I'm curious what you're trying to do.
What you're trying to learn is in the system tables.

Column Names with a cursor

I have set up a query where I am using a cursor to pass result from one
query to use as select parameters in another query. The problem I am
having is that when I pass the results into the second query I get the
column headers?

How do you suppress the column headers from showing in the query? I
see how you select options - print headers.

Thanks

JasonIf you dont want to have headers, then you can use ' '

Select col1 as ' ', col2 as ' ' ...... from table

Madhivanan|||Why use a cursor at all? You should be able to make the first query into a
correlated subquery.

--
David Portas
SQL Server MVP
--

column names of the table

Hi guys,

Is there any function that can the column names of the table? I know about the sp_help but I want I'm going to call this from my .net application?

Thanks

How about using the INFORMATION_SCHEMA.COLUMNS view? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:2ff402f1-91fb-4795-aee4-bc05d3efcffa@.discussions.microsoft.com...
> Hi guys, >
> Is there any function that can the column names of the table? I know
> about the sp_help but I want I'm going to call this from my .net
> application? > > >
> Thanks >
>|||try this
select column_name from information_schema.columns where table_name ='agents'|||Thank guys!!!

Column Names of Fulltext search result

I was trying to do full text search. I have no problem to do the search as
following:
select MemberID, Surname, Firstname from members where FREETEXT(*, N'moore')
It returned all rows for any column containing values that match the
meaning, but not the exact wording, of the text 'moore'.
My problem is that my client want to know the name(s) of the column(s) which
the keyword 'moore' was found. How can I get the required info?
And I also tried FREETEXTTABLE, which only returns a relevance ranking value
(RANK) and full-text key (KEY) for each row. Same things using CONTAINS or
CONTAINSTABLE
Thanks in advance.
To get an exact search, ie moore, but not moores wrap your freetext search
in double quotes or use contains, ie
select MemberID, Surname, Firstname from members where FREETEXT(*,
N'"moore"')
You can't easily get the column where the hit occurs. You would have to do
something like this
create table members(MemberID int identity not null, surname varchar(20),
firstname varchar(20), constraint memberspk primary key (memberid))
GO
insert into members(surname, firstname) values('moore','moore')
insert into members(surname, firstname) values('moore','dave')
insert into members(surname, firstname) values('dave','moore')
insert into members(surname, firstname) values('dave','dave')
GO
sp_fulltext_database 'enable'
GO
create fulltext index on members(surname, firstname) key index memberspk
GO
select *, case
when charindex('moore',surname)>0 and charindex('moore',firstname)=0 then
'surname'
when charindex('moore',surname)=0 and charindex('moore',firstname)>0 then
'Firstname'
when charindex('moore',surname)>0 and charindex('moore',firstname)>0 then
'both'
else 'not sure'
end From members where contains(*,'moore')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"X. Zhang" <XZhang@.discussions.microsoft.com> wrote in message
news:5C88126C-FC9F-4281-BAA7-7FC3AAE2E0BD@.microsoft.com...
>I was trying to do full text search. I have no problem to do the search as
> following:
> select MemberID, Surname, Firstname from members where FREETEXT(*,
> N'moore')
> It returned all rows for any column containing values that match the
> meaning, but not the exact wording, of the text 'moore'.
> My problem is that my client want to know the name(s) of the column(s)
> which
> the keyword 'moore' was found. How can I get the required info?
> And I also tried FREETEXTTABLE, which only returns a relevance ranking
> value
> (RANK) and full-text key (KEY) for each row. Same things using CONTAINS or
> CONTAINSTABLE
> Thanks in advance.
|||Hilary,
Thank you for your reply. I thought about this way too, but I have problem
with it. I had more than 50 columns to be searched, which means I have to
'when' more than 50 times, actually lots more than 50 times if I need the
column combinations, such as your 'both' case.
I was wondering if there is a straight forward way to do so. If no, I guess
I have to do some programming...
Thanks,
"Hilary Cotter" wrote:

> To get an exact search, ie moore, but not moores wrap your freetext search
> in double quotes or use contains, ie
> select MemberID, Surname, Firstname from members where FREETEXT(*,
> N'"moore"')
> You can't easily get the column where the hit occurs. You would have to do
> something like this
> create table members(MemberID int identity not null, surname varchar(20),
> firstname varchar(20), constraint memberspk primary key (memberid))
> GO
> insert into members(surname, firstname) values('moore','moore')
> insert into members(surname, firstname) values('moore','dave')
> insert into members(surname, firstname) values('dave','moore')
> insert into members(surname, firstname) values('dave','dave')
> GO
> sp_fulltext_database 'enable'
> GO
> create fulltext index on members(surname, firstname) key index memberspk
> GO
> select *, case
> when charindex('moore',surname)>0 and charindex('moore',firstname)=0 then
> 'surname'
> when charindex('moore',surname)=0 and charindex('moore',firstname)>0 then
> 'Firstname'
> when charindex('moore',surname)>0 and charindex('moore',firstname)>0 then
> 'both'
> else 'not sure'
> end From members where contains(*,'moore')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "X. Zhang" <XZhang@.discussions.microsoft.com> wrote in message
> news:5C88126C-FC9F-4281-BAA7-7FC3AAE2E0BD@.microsoft.com...
>
>
|||You could also limit your FT query to a certain column.
For example
select MemberID, Surname, Firstname from members where
contains(surname,'moore')
and do the same for every fulltext indexed column in your table and then
combine the results.
Thanks
"X. Zhang" wrote:
[vbcol=seagreen]
> Hilary,
> Thank you for your reply. I thought about this way too, but I have problem
> with it. I had more than 50 columns to be searched, which means I have to
> 'when' more than 50 times, actually lots more than 50 times if I need the
> column combinations, such as your 'both' case.
> I was wondering if there is a straight forward way to do so. If no, I guess
> I have to do some programming...
> Thanks,
> "Hilary Cotter" wrote:

Column Names cAsE SeNSitive? - DFT

Does the column names are case sensitive for the DFT? Recently in one of table, the column names were changed to Upper Case from Lower case and the package started failing in validation. Is there any solution / work around for this?

Thanks

Is the database itself case-sensitive?|||

No the database is not case sensitive. I can use mixed cases for column names in Management Studio/Query Analyzer, I still get results. Only in SSIS it fails, especially when you modify the column case after the package is created.

This is what I have:-
A simple DFT task.
OLE DB Source is Sybase, OLE DB Destination is SQL SERVER 2005. No other transformations in between.
Sql Server has columns in lowercase when the package was developed, but later somebody recreated table with columns in upper case and package started to fail in validation.

Thanks

|||

Can any one tell me if this is the behaviour?

Thanks

|||

Karunakaran wrote:

Can any one tell me if this is the behaviour?

Thanks

Yes, this is confirmed in SP2. I agree, this probably should not be designed to be case sensitive. Can you post a bug at http://connect.microsoft.com/sqlserver/feedback? Then post back here with a link to the submission?

Thanks,
Phil|||

I have submitted the bug, below is the link.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=296668

Thanks

Column Names cAsE SeNSitive? - DFT

Does the column names are case sensitive for the DFT? Recently in one of table, the column names were changed to Upper Case from Lower case and the package started failing in validation. Is there any solution / work around for this?

Thanks

Is the database itself case-sensitive?|||

No the database is not case sensitive. I can use mixed cases for column names in Management Studio/Query Analyzer, I still get results. Only in SSIS it fails, especially when you modify the column case after the package is created.

This is what I have:-
A simple DFT task.
OLE DB Source is Sybase, OLE DB Destination is SQL SERVER 2005. No other transformations in between.
Sql Server has columns in lowercase when the package was developed, but later somebody recreated table with columns in upper case and package started to fail in validation.

Thanks

|||

Can any one tell me if this is the behaviour?

Thanks

|||

Karunakaran wrote:

Can any one tell me if this is the behaviour?

Thanks

Yes, this is confirmed in SP2. I agree, this probably should not be designed to be case sensitive. Can you post a bug at http://connect.microsoft.com/sqlserver/feedback? Then post back here with a link to the submission?

Thanks,
Phil|||

I have submitted the bug, below is the link.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=296668

Thanks

Column Names cAsE SeNSitive? - DFT

Does the column names are case sensitive for the DFT? Recently in one of table, the column names were changed to Upper Case from Lower case and the package started failing in validation. Is there any solution / work around for this?

Thanks

Is the database itself case-sensitive?|||

No the database is not case sensitive. I can use mixed cases for column names in Management Studio/Query Analyzer, I still get results. Only in SSIS it fails, especially when you modify the column case after the package is created.

This is what I have:-
A simple DFT task.
OLE DB Source is Sybase, OLE DB Destination is SQL SERVER 2005. No other transformations in between.
Sql Server has columns in lowercase when the package was developed, but later somebody recreated table with columns in upper case and package started to fail in validation.

Thanks

|||

Can any one tell me if this is the behaviour?

Thanks

|||

Karunakaran wrote:

Can any one tell me if this is the behaviour?

Thanks

Yes, this is confirmed in SP2. I agree, this probably should not be designed to be case sensitive. Can you post a bug at http://connect.microsoft.com/sqlserver/feedback? Then post back here with a link to the submission?

Thanks,
Phil|||

I have submitted the bug, below is the link.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=296668

Thanks

Column Names cAsE SeNSitive? - DFT

Does the column names are case sensitive for the DFT? Recently in one of table, the column names were changed to Upper Case from Lower case and the package started failing in validation. Is there any solution / work around for this?

Thanks

Is the database itself case-sensitive?|||

No the database is not case sensitive. I can use mixed cases for column names in Management Studio/Query Analyzer, I still get results. Only in SSIS it fails, especially when you modify the column case after the package is created.

This is what I have:-
A simple DFT task.
OLE DB Source is Sybase, OLE DB Destination is SQL SERVER 2005. No other transformations in between.
Sql Server has columns in lowercase when the package was developed, but later somebody recreated table with columns in upper case and package started to fail in validation.

Thanks

|||

Can any one tell me if this is the behaviour?

Thanks

|||

Karunakaran wrote:

Can any one tell me if this is the behaviour?

Thanks

Yes, this is confirmed in SP2. I agree, this probably should not be designed to be case sensitive. Can you post a bug at http://connect.microsoft.com/sqlserver/feedback? Then post back here with a link to the submission?

Thanks,
Phil|||

I have submitted the bug, below is the link.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=296668

Thanks

Column Names - Modifing

Can the column names be change in SQL Express.

I am amist of pulling my hair out here, I have got a scenerio of needing to be able to move data around, my first thought was to have two columns, one for the data and the other for the column name. Once getting into the manipulation of the data, it occurred. MUCH EASIER to modify the column name in the data table rather the data.

Is it possible from VB to change the name of the column in the database table?

Thanks Again

Davids Learning

Use sp_rename

create table test
(
trestId int
)
go

exec sp_rename 'test.trestId','testId','Column'

go

select *
from test

testId
--

|||

Ok

You can call me a dummy here,

Can you explain this a little bit more. I havent done a whole lot with TSQL,

and also, is this in VB?

Very Confused

Davids Learning

|||

No, the stuff in bold is the code you would use from management studio (2005) or query analyzer (2000):

create table test
(
trestId int
)
go

exec sp_rename 'test.trestId','testId','Column'

go

select *
from test

The other stuff was setup to show you how it worked. If you don't know how to execute queries, you might ask for a prod in the right direction in the VB forums:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=32&SiteID=1

These forums are for how to write TSQL, which is its own language...

Column name with period

I heard a rumour that column names with a period "." in
the name had a negative affect on performance.
I haven't yet managed to find proof of this but was told
that someone found an article suggesting that it does.
If anyone knows of this could they please post a link or
explain?I couldn't find any collation with a period in the name:
select * from ::fn_helpcollations() where name like '%.%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Willem" <anonymous@.discussions.microsoft.com> wrote in message
news:1575001c41631$2c2d2060$a501280a@.phx
.gbl...
> I heard a rumour that column names with a period "." in
> the name had a negative affect on performance.
> I haven't yet managed to find proof of this but was told
> that someone found an article suggesting that it does.
> If anyone knows of this could they please post a link or
> explain?
>|||Tibor,
Go and visit your optician! ;-) The OP said column, not collation!
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Willem,
I don't know whether it would affect performance or not but it is not "best
practise" to have non alphanumeric characters in column names. You are expos
ing yourself to bugs by doing this. I know that you can use square brackets
but in some situations you
may find yourself hitting a bug or two. I would rename the column in the nex
t release of your application, if possible.
There may be a performance hit, but I doubt you would notice it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||> Go and visit your optician! ;-) The OP said column, not collation!
LOL... Thanks Mark :-)
Willem,
I have not heard anything to the effect that SQL Server should treat columns
differently in any way based on
the name of the column. Of course, using a period makes the name a non-stand
ard identifier and you will have
to handle that in every query you issue against that column. I never divert
from standard identifiers myself.
I have written code against databases which required me to use delimited ide
ntifiers, no fun...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:71883438-B332-4E1D-92B7-E0F8B6AA8962@.microsoft.com...
> Tibor,
> Go and visit your optician! ;-) The OP said column, not collation!
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk|||Thanks for the feedback.

>--Original Message--
>Willem,
>I don't know whether it would affect performance or not
but it is not "best practise" to have non alphanumeric
characters in column names. You are exposing yourself to
bugs by doing this. I know that you can use square
brackets but in some situations you may find yourself
hitting a bug or two. I would rename the column in the
next release of your application, if possible.
>There may be a performance hit, but I doubt you would
notice it.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>.
>|||Great, thanks.

>--Original Message--
not collation!
>LOL... Thanks Mark :-)
>
>Willem,
>I have not heard anything to the effect that SQL Server
should treat columns differently in any way based on
>the name of the column. Of course, using a period makes
the name a non-standard identifier and you will have
>to handle that in every query you issue against that
column. I never divert from standard identifiers myself.
>I have written code against databases which required me
to use delimited identifiers, no fun...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message
>news:71883438-B332-4E1D-92B7-
E0F8B6AA8962@.microsoft.com...
not collation!
>
>.
>

Column Descriptions

Is there a way to get a list column names and their descriptions?

You can run sp_help (tablename) to get a full description of the table's columns along with other information.

Is this what you need?

|||

No.

In the design view of table one can apply a column description to a column is there a way to see that description?

|||

Using Books Online, refer to Topics:

Extended properties sp_addextendedproperty sp_updateextendedproperty sp_dropextendedproperty fn_listextendedproperty

Column aliases in MDX

Is it possible to use column aliases in MDX query?
I execute MDX using MSOLAP and I'd like to have a specific names for the columns returned.
Linkedserver and OPENROWSET is a one solution, but is there a way to specify column aliases in MDX query?

You can use calculated members to change names of dimension members (including measures).

So you could write your query as something like the following:

WITH MEMBER Measures.[NiceName1] as Measures.M1, Measures.[NiceName2] as M2

SELECT {Measures.[NiceName1], Measures.[NiceName2]} on 0

FROM [MyCube]

|||Using CM for that purposes is a very dangerous way.|||Thank you.
When running some examples, I'm still getting errors:
1. "Parser: The syntax for '.' is incorrect." for:
WITH MEMBER Measures.[Reseller Sales Amount] as Measures.M1, Measures.[Calendar Year].[CY 2004] as M2
SELECT
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON 0
FROM [Adventure Works]

2. "The Reseller Sales Amount calculated member cannot be created because a member with the same name already exists." for:
WITH MEMBER Measures.[Reseller Sales Amount] as M2
SELECT
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON 0
FROM [Adventure Works]

You're saying: "Using CM for that purposes is a very dangerous way."
What would be the best? Linkedserver and OPENROWSET only?

|||

Try reversing the syntax. (In other words NiceName1 was the new calculated member serving as an alias to M1 in my original example.)

You will lose the ability to do drillthrough on the calc members. Vladamir can comment on other concerns he may have about this approache.

|||

Vladimir -

Why is this considered a bad practice? What other ways are there to accomplish this? You have me very curious...

Thanks,

John

Column aliases in MDX

Is it possible to use column aliases in MDX query?
I execute MDX using MSOLAP and I'd like to have a specific names for the columns returned.
Linkedserver and OPENROWSET is a one solution, but is there a way to specify column aliases in MDX query?

You can use calculated members to change names of dimension members (including measures).

So you could write your query as something like the following:

WITH MEMBER Measures.[NiceName1] as Measures.M1, Measures.[NiceName2] as M2

SELECT {Measures.[NiceName1], Measures.[NiceName2]} on 0

FROM [MyCube]

|||Using CM for that purposes is a very dangerous way.|||Thank you.
When running some examples, I'm still getting errors:
1. "Parser: The syntax for '.' is incorrect." for:
WITH MEMBER Measures.[Reseller Sales Amount] as Measures.M1, Measures.[Calendar Year].[CY 2004] as M2
SELECT
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON 0
FROM [Adventure Works]

2. "The Reseller Sales Amount calculated member cannot be created because a member with the same name already exists." for:
WITH MEMBER Measures.[Reseller Sales Amount] as M2
SELECT
([Measures].[Reseller Sales Amount],[Date].[Calendar Year].[CY 2004]) ON 0
FROM [Adventure Works]

You're saying: "Using CM for that purposes is a very dangerous way."
What would be the best? Linkedserver and OPENROWSET only?

|||

Try reversing the syntax. (In other words NiceName1 was the new calculated member serving as an alias to M1 in my original example.)

You will lose the ability to do drillthrough on the calc members. Vladamir can comment on other concerns he may have about this approache.

|||

Vladimir -

Why is this considered a bad practice? What other ways are there to accomplish this? You have me very curious...

Thanks,

John

Colum Name - Alias

We are thinking of using general purpose column names in our application schema. We want to give the option to the end user to customize the filed names to fit their business . We want to build the functionality on the generic names so that it will work for multiple customes.

Example: We may want to have 10 Strings, 10 numbers and 5 booleans pre defined and reports running off of the table. The customer can name first number as pressure, second one for length and map their data to the table. Other customer can use the first number for temperature and the second one for width.

Is there a way to do it in SQL server w/o having a lookup table for column name aliasing?

Thanks

Option 1: Any Reporting application generally has provision to display a customized column names for the table reports.

Option 2: When you query the table - you can provide column alias for the columns queried for example:

SELECT Column1 AS Pressure, Column2 AS Length FROM TableName

Option 3: You can create multiple views over the base table and the created views can have appropriate column names.

Thanks,

Sankaranarayanan MG