I want to add two text columns

Hi all,
I have a two text columns in my table with more than 100,000 rows.
I want to create a third text column with the data from text column 1 + text
column 2.
Is there an easy way to concatinate two text fields?
Thanks
RajuNo, there is no easy way. Why would you want to do this? Is your SQL
statement too long when you list both columns? This seems like a weird
requirement to me. Usually you use separate TEXT columns because you need
them separate. Otherwise, most applications could have just used a single
text column in the first place.
If you have less than 8000 characters in each column, you could use a view,
and say:
SELECT
..,
ConcatenatedColumn = CONVERT(VARCHAR(8000), textCol1)
+ CONVERT(VARCHAR(8000), textCol2)
FROM
..
Then, you don't have to change the underlying table, or any of the
procedures / apps that insert/update the data. (Or, better yet, if you
don't need TEXT, change the columns to VARCHAR.)
Otherwise, the best way would probably be to add the 3rd column, use an
external app to loop through, concatenate, and update the new column, and
then delete the first two columns. (An external app will be much more
friendly with joining the two values than SQL Server will be internally.)
A
"Raju" <npraju1@.hotmail.com> wrote in message
news:%23mM%237ZCAGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a two text columns in my table with more than 100,000 rows.
> I want to create a third text column with the data from text column 1 +
> text
> column 2.
> Is there an easy way to concatinate two text fields?
> Thanks
> Raju
>|||Aaron,
Thank you for your response.
Actually we have legacy application, which has summary in one text column
and description in one text column.
Our new application has only one column, now my job is to get those two
columns into one new column in our new application.
Hope this helps.
BTW
Unfortunately I can't use your solution as both the columns have huge data,
more than 8000 bytes.
Thanks
Raju
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWjN6eCAGHA.1256@.TK2MSFTNGP15.phx.gbl...
> No, there is no easy way. Why would you want to do this? Is your SQL
> statement too long when you list both columns? This seems like a weird
> requirement to me. Usually you use separate TEXT columns because you need
> them separate. Otherwise, most applications could have just used a single
> text column in the first place.
> If you have less than 8000 characters in each column, you could use a
view,
> and say:
> SELECT
> ...,
> ConcatenatedColumn = CONVERT(VARCHAR(8000), textCol1)
> + CONVERT(VARCHAR(8000), textCol2)
> FROM
> ...
> Then, you don't have to change the underlying table, or any of the
> procedures / apps that insert/update the data. (Or, better yet, if you
> don't need TEXT, change the columns to VARCHAR.)
> Otherwise, the best way would probably be to add the 3rd column, use an
> external app to loop through, concatenate, and update the new column, and
> then delete the first two columns. (An external app will be much more
> friendly with joining the two values than SQL Server will be internally.)
> A
>
> "Raju" <npraju1@.hotmail.com> wrote in message
> news:%23mM%237ZCAGHA.2040@.TK2MSFTNGP14.phx.gbl...
>

i think simple but tough for me Help

Hello Genuis

friend i had three columns in my crsytal report that shows values as
a = 1
b = 0
c shows nothing not 0 or some value as in database value of c = null
now i want
a+b+c using a formula
but it is not showing the output as null is their
please help out how can i get the result

as i want a+b+c = 1 should be output

if bychance the value of a= 1
b =1
c=0
then output s coming for sure
i.e 2

Hope u got my problem

How to add all the columns

With Best Regards
Rubyhi,
use the formula and palce ur fields in place of a,b and c. u will find the solution.
(IF ISNULL(a) THEN 0 ELSE a) + (IF ISNULL (b) THEN 0 ELSE b) +( IF ISNULL (c) THEN 0 ELSE c)
remeber in pryers.
online available at habibhussain82@.gmail.com|||Test if C is null before trying to add it. e.g.
if isnull({table.C}) then a+b
else a+b+c

I need to know the data changes in some table on a row level

Hello Guys
I have a table, contains some fileds
When update statment runs, I need to know which columns has been changed and to know the old and the new data for each row

simply i need to do the following in a trigger:

For each row ROW in tbl_Table
for each col COL in ROW
Save COL.oldValue and COL.newValue

I don't know how to do it by cursors and I don't want to use Cursors
If any one can help or provide a good advice, please help

Thanks and have a great day

Hi

It is very simply.

From Books Online:

"DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server 2005 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions."

More , look for "inserted tables" in Books Online

Gigi,

www.sqlserver.ro

|||The client has SQL Server 2000 installed and don't want to upgrade to SQL Server 2005|||

On the triggers use the following statements

Insert Into SomeLogTable Select Deleted.*,'Old Value' RowStatus From Deleted

Insert Into SomeLogTable Select Inserted.*,'New Value' RowStatus From Inserted

|||In addition to Mani, you should use the full qualified names rather than *. Be aware that triggers are executed for each DML statement not per row, the trigger is even fired if no row is affected:

UPDATE SomeTable SET SomeCol = 1 WHERE 1 =2 --Will fire the trigger

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The concepts (the deleted table and the inserted table) was in SQL 2000 too.

Gigi Ciubuc

www.sqlserver.ro

I need to know the data changes in some table on a row level

Hello Guys
I have a table, contains some fileds
When update statment runs, I need to know which columns has been changed and to know the old and the new data for each row

simply i need to do the following in a trigger:

For each row ROW in tbl_Table
for each col COL in ROW
Save COL.oldValue and COL.newValue

I don't know how to do it by cursors and I don't want to use Cursors
If any one can help or provide a good advice, please help

Thanks and have a great day

Hi

It is very simply.

From Books Online:

"DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server 2005 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions."

More , look for "inserted tables" in Books Online

Gigi,

www.sqlserver.ro

|||The client has SQL Server 2000 installed and don't want to upgrade to SQL Server 2005|||

On the triggers use the following statements

Insert Into SomeLogTable Select Deleted.*,'Old Value' RowStatus From Deleted

Insert Into SomeLogTable Select Inserted.*,'New Value' RowStatus From Inserted

|||In addition to Mani, you should use the full qualified names rather than *. Be aware that triggers are executed for each DML statement not per row, the trigger is even fired if no row is affected:

UPDATE SomeTable SET SomeCol = 1 WHERE 1 =2 --Will fire the trigger

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The concepts (the deleted table and the inserted table) was in SQL 2000 too.

Gigi Ciubuc

www.sqlserver.ro

I need to join some additional columns from SQL Server into the worksheet ...

Accounting has provided me with an Excel worksheet with several columns of
data, one of which is AccountID. There is a table in SQL Sever which also
has AccountID. I need to join (using AccountID) some additional columns from
SQL Server into the worksheet as new columns, save, and then send the
spreadsheet back to Accounting. What is the simplest method to accomplish
this? I would prefer a non programming approach with the fewest steps so
Accounting can do this themselves next time.DTS would be the easiest, if you can get the data into a staging table. Once
the resultset is generated you can get it back to the spreadsheet easily.
Alternatively, another "non programming approach" would be to use the rowset
provider OPENDATASOURCE. Check out the example in SQL Server Books Online
which shows how to use this with OLE DB provider for Jet.
Anith

I need help with SELECT statement

hello,
At the beginning I would like to describe my very simple table. It is
composed of three columns: 'name', 'forename', 'status'. This database
stores results of games. The values of column 'status' can be only: 1 for
win, 2 for lose,0 for draw. My aim is to get a table which consists also 3
columns, but these should be: 'name', 'forename', 'balance'. Of course
balance should display values: number of wins - number of loses for each
player. So the new table should be the agreggate of the same players which
their balance of games. Is there anyone who could create that statement? I
think that it is not very difficult for someone who knows the SQL. If it is
needed the DBMS which I use is MSSQL Server(MSDE). I can not speak and write
english very well so sorry for mistakes. Thanks and greetings for YOU.The design is wrong. You need to all the attributes (results) into a
single row for each entity (person)

CREATE TABLE Games
(first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
win_cnt INTEGER NOT NULL,
lose_cnt INTEGER NOT NULL,
draw_cnt INTEGER NOT NULL,
PRIMARY KEY (first_name, last_name));

Load the new table with this statement:

INSERT INTO Games
SELECT first_name, last_name,
SUM(CASE WHEN status = 1
THEN 1 ELSE 0 END) AS win,
SUM(CASE WHEN status = 2
THEN 1 ELSE 0 END) AS lose,
SUM(CASE WHEN status = 0
THEN 1 ELSE 0 END) AS draw
FROM Foobar
GROUP BY first_name, last_name;

Now your query is easy.

i need Column names !

Hi, i need a query to have the columns names !!
If you have another solution, let me know !!
superj !!!Originally posted by superj
Hi, i need a query to have the columns names !!
If you have another solution, let me know !!

superj !!! I kind of like Bob, Carol, Ted, and Alice myself. What column names do you like?

-PatP|||I like them blue. Databases I like yellow.|||Originally posted by rdjabarov
I like them blue. Databases I like yellow. So, if you put those columns into a database, does the whole thing turn green?

-PatP|||Hey, that would be cool!|||SELECT COLUMN_NAME
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'authors'
> ORDER BY ORDINAL_POSITION|||select colid, name from syscolumns where object_name(id) = 'authors' order by 1|||Originally posted by rdjabarov
I like them blue. Databases I like yellow.

Don't you read Dilbert?

Muave is the fastest db color|||whatever you do, dont get red
cops look for that color.

i myself , am currently configuring a Puce database with various pastel colored tables and some chince doilys.

yes i work for martha stewart living|||Why not :
EXEC sp_columns @.table_name = 'customers'|||okay i'll bite

exec SP_Help tablename

will give you everything about the table including all column names.|||But that gives whole lot of information where Originator requires only column information for a table.

I feel using INFORMATION SCHEMA VIEWS or SP_COLUMNS will suffice.
Originally posted by Ruprect
okay i'll bite

exec SP_Help tablename

will give you everything about the table including all column names.|||i was just jumpin' in
info_schema are just fine

or

how about

sp_columns

Returns column information for the specified tables or views that can be queried in the current environment.