Are Blank String and String with only Space characters equal to each other? They should not be!!

I have a table called Table_1

Table_1
--
a varchar(10),
b varchar(20)

and Column a is the Primary Key.

INSERT INTO Table_1
(a ,b)
VALUES
('' , 'BLANK STRING')

INSERT INTO Table_1
(a ,b)
VALUES
(' ' ,'4 SPACES')

When i execute this Insertions, it inserts the First Record but on the Second one it gives Violation of Uniqueness

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 9
Violation of PRIMARY KEY constraint 'PK_Table_1'. Cannot insert duplicate key in object 'dbo.Table_1'.
The statement has been terminated.

My Questions is that; HOW can a Blank String or a String with 4(or any number) space charcters in it can be EQUAL? In my opinion they should not be regarded as the same! Is there a solution to this issue? That's not the case in Oracle Database.

Thanks for your help!

Regards,
Cem

According to ANSI standards (or is it SQL Server's implementation of ANSI...), anyway, for sorting purposes, leading blanks are ignored. (And Primary Keys are 'sorted'...)

Try these two statements, the first will provide the 'right' answer, and the second will provide the 'real' answer.

SELECT len( (replicate( ' ', 4 )) )
SELECT datalength( (replicate( ' ', 4 )) )

|||

You should check the setting of SET ANSI_PADDING. If you want behavior such as you find in ORACLE, you will need SET ANSI_PADDING ON. That way trailing blanks will not be trimmed from your VARCHAR insert statements.

http://msdn2.microsoft.com/en-us/library/ms187403.aspx

Dan

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVR
Why not just back up the database itself?
One thing you could do is put the tables w/ binary data on their own
filegroup -- then just back up that filegroup more regularly -- if that's
what you need?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:eMjAtpdKFHA.3552@.TK2MSFTNGP12.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVRVyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>|||Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
Vyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>|||Narayanan,
I saw your Link, it looks good but i think it may or may not work if the
orders for 6 months is more than 100,000.
What happens if the data is more than 100,000 as we have in a real-time
system.
my solution was (lets take 6 months period - 100,000 records)
If you have to leave 10,000 on live production database and remove the rest
to history database.
This is how I did and its working great.
1. Check the record count of the database based on date.
2. Use Chunking method - means copy 1000-5000 every time depending on the
load from the live database to temp archive database on the live server then
call another str proc to copy data from temp archive to remote archive
database server.
(reason being if network goes down btw live server/remote server [reason
unknown] you can still have data in temp not lost and can come back redo the
data.)
3. once copied to remote archive server, now come back delete that data from
the live database by "delete from live where seqno in temp archive database"
and later delete from temp archive. (seqno is a primary key field)
I go beyond this, once all the data has been copied, i check for the first
and last copied to archive server exists meaning it has successfully copied.
I wrote a program to do this, the program just calls 3 stored procedures and
str proc does all the above function.
Too much isn't it.
Arun
"Narayana Vyas Kondreddi" wrote:
> Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
> Vyas has a great article on his web site about archiving.
> http://vyaskn.tripod.com/
>
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> > What's the best way to archive tables with binary data? Flat
> files/bcp/DTS
> > may not work if need to import binary data(not a fixed length column) with
> > quotes etc. What I am thinking is to export to another database and back
> it
> > up/archive on a regular basis. That way it will be easy to restore when
> > needed. Any other options?
> >
> > Thanks
> > BVR
> >
> >
>
>

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVR
Vyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>
|||Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
Vyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>
|||Narayanan,
I saw your Link, it looks good but i think it may or may not work if the
orders for 6 months is more than 100,000.
What happens if the data is more than 100,000 as we have in a real-time
system.
my solution was (lets take 6 months period - 100,000 records)
If you have to leave 10,000 on live production database and remove the rest
to history database.
This is how I did and its working great.
1. Check the record count of the database based on date.
2. Use Chunking method - means copy 1000-5000 every time depending on the
load from the live database to temp archive database on the live server then
call another str proc to copy data from temp archive to remote archive
database server.
(reason being if network goes down btw live server/remote server [reason
unknown] you can still have data in temp not lost and can come back redo the
data.)
3. once copied to remote archive server, now come back delete that data from
the live database by "delete from live where seqno in temp archive database"
and later delete from temp archive. (seqno is a primary key field)
I go beyond this, once all the data has been copied, i check for the first
and last copied to archive server exists meaning it has successfully copied.
I wrote a program to do this, the program just calls 3 stored procedures and
str proc does all the above function.
Too much isn't it.
Arun
"Narayana Vyas Kondreddi" wrote:

> Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
> Vyas has a great article on his web site about archiving.
> http://vyaskn.tripod.com/
>
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> files/bcp/DTS
> it
>
>

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVRWhy not just back up the database itself?
One thing you could do is put the tables w/ binary data on their own
filegroup -- then just back up that filegroup more regularly -- if that's
what you need?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:eMjAtpdKFHA.3552@.TK2MSFTNGP12.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>sql

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVRVyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>|||Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
Vyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>|||Narayanan,
I saw your Link, it looks good but i think it may or may not work if the
orders for 6 months is more than 100,000.
What happens if the data is more than 100,000 as we have in a real-time
system.
my solution was (lets take 6 months period - 100,000 records)
If you have to leave 10,000 on live production database and remove the rest
to history database.
This is how I did and its working great.
1. Check the record count of the database based on date.
2. Use Chunking method - means copy 1000-5000 every time depending on the
load from the live database to temp archive database on the live server then
call another str proc to copy data from temp archive to remote archive
database server.
(reason being if network goes down btw live server/remote server [reason
unknown] you can still have data in temp not lost and can come back redo the
data.)
3. once copied to remote archive server, now come back delete that data from
the live database by "delete from live where seqno in temp archive database"
and later delete from temp archive. (seqno is a primary key field)
I go beyond this, once all the data has been copied, i check for the first
and last copied to archive server exists meaning it has successfully copied.
I wrote a program to do this, the program just calls 3 stored procedures and
str proc does all the above function.
Too much isn't it.
Arun
"Narayana Vyas Kondreddi" wrote:

> Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
> Vyas has a great article on his web site about archiving.
> http://vyaskn.tripod.com/
>
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> files/bcp/DTS
> it
>
>

April CTP superkey oddity

I've set up a table with a superkey composed of a uniqueidentifier column and a varchar column. The uniqueidentifier column is not set to be a row GUID. When I try to add a new row with a duplicate uniqueidentifier value but different varchar value, I am told it violates the IX uniqueness constraint. I can't figure out what's wrong with this since it looks valid to me. Thanks in advance for suggestions.

JoshJust an FYI- I dropped the table and recreated it as I did before and everything went as expected!

Josh

Appropriate Index

I have a table where data is deleted, inserted and updated. What kind of
index is appropriate for the column
where data is deleted ?
where data is inserted ?
where data is updated ?
Thanks for any input."DXC" <DXC@.discussions.microsoft.com> wrote in message
news:945F0AE5-AB17-4D18-8A35-312F4DB6E16F@.microsoft.com...
> I have a table where data is deleted, inserted and updated. What kind of
> index is appropriate for the column
> where data is deleted ?
> where data is inserted ?
> where data is updated ?
> Thanks for any input.
This depends on many things. If you post some DDL, we can help you out.
Read up on Indexes in the BOL for some tips on which columns are good
candidates for indexes and which ones are not.
Rick Sawtell
MCT, MCSD, MCDBA

application name in profiler, could it be modifed

Hi,
We have 500+ dts running 24 X 7 ,started from dts run
In profiler every enty in application name column show as Dts
designer.
1.
Is possible to modify dts property or anything and add customer
informatiom ?
example
application name = dtsrun - Insert_Into_table_X
2.
is possible to modify application name for any connection ?
Thanks
AlexSure. For a SQL connection just bring up the properties and click on the
Advanced button and you'll see an Application Name property. You could for
example add an ActiveX script task to the start of your package that loops
through the SQL connections and sets the Application Name property of the
connection to the package name.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
<t2581@.hotmail.com> wrote in message
news:1137770497.717723.119880@.g14g2000cwa.googlegroups.com...
> Hi,
>
> We have 500+ dts running 24 X 7 ,started from dts run
> In profiler every enty in application name column show as Dts
> designer.
>
> 1.
> Is possible to modify dts property or anything and add customer
> informatiom ?
> example
> application name = dtsrun - Insert_Into_table_X
> 2.
> is possible to modify application name for any connection ?
>
> Thanks
>
> Alex
>

application name in profiler, could it be modifed

Hi,
We have 500+ dts running 24 X 7 ,started from dts run
In profiler every enty in application name column show as Dts
designer.
1.
Is possible to modify dts property or anything and add customer
informatiom ?
example
application name = dtsrun - Insert_Into_table_X
2.
is possible to modify application name for any connection ?
Thanks
Alex
Sure. For a SQL connection just bring up the properties and click on the
Advanced button and you'll see an Application Name property. You could for
example add an ActiveX script task to the start of your package that loops
through the SQL connections and sets the Application Name property of the
connection to the package name.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
<t2581@.hotmail.com> wrote in message
news:1137770497.717723.119880@.g14g2000cwa.googlegr oups.com...
> Hi,
>
> We have 500+ dts running 24 X 7 ,started from dts run
> In profiler every enty in application name column show as Dts
> designer.
>
> 1.
> Is possible to modify dts property or anything and add customer
> informatiom ?
> example
> application name = dtsrun - Insert_Into_table_X
> 2.
> is possible to modify application name for any connection ?
>
> Thanks
>
> Alex
>

application name in profiler, could it be modifed

Hi,
We have 500+ dts running 24 X 7 ,started from dts run
In profiler every enty in application name column show as Dts
designer.
1.
Is possible to modify dts property or anything and add customer
informatiom ?
example
application name = dtsrun - Insert_Into_table_X
2.
is possible to modify application name for any connection ?
Thanks
AlexSure. For a SQL connection just bring up the properties and click on the
Advanced button and you'll see an Application Name property. You could for
example add an ActiveX script task to the start of your package that loops
through the SQL connections and sets the Application Name property of the
connection to the package name.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
<t2581@.hotmail.com> wrote in message
news:1137770497.717723.119880@.g14g2000cwa.googlegroups.com...
> Hi,
>
> We have 500+ dts running 24 X 7 ,started from dts run
> In profiler every enty in application name column show as Dts
> designer.
>
> 1.
> Is possible to modify dts property or anything and add customer
> informatiom ?
> example
> application name = dtsrun - Insert_Into_table_X
> 2.
> is possible to modify application name for any connection ?
>
> Thanks
>
> Alex
>

Appending two tables

I have 2 SQL tables that Ineed to combine (append). The column information in
each table is exactly the same, except they are for different years (one is
history and one is current year). I need to be able to create a view that has
both the history data and current year data so I can list transactions
chronologically from the two tables.Create View MyView
AS
Select Field1, Field2, Field3
From Table1
UNION
Select Field1, Field2, Field3
From Table2
"Ranjit Charles" <RanjitCharles@.discussions.microsoft.com> a écrit dans le
message de news: 055547F7-9031-4E92-9868-8A11493C7865@.microsoft.com...
>I have 2 SQL tables that Ineed to combine (append). The column information
>in
> each table is exactly the same, except they are for different years (one
> is
> history and one is current year). I need to be able to create a view that
> has
> both the history data and current year data so I can list transactions
> chronologically from the two tables.

Appending two tables

I have 2 SQL tables that Ineed to combine (append). The column information in
each table is exactly the same, except they are for different years (one is
history and one is current year). I need to be able to create a view that has
both the history data and current year data so I can list transactions
chronologically from the two tables.
Create View MyView
AS
Select Field1, Field2, Field3
From Table1
UNION
Select Field1, Field2, Field3
From Table2
"Ranjit Charles" <RanjitCharles@.discussions.microsoft.com> a crit dans le
message de news: 055547F7-9031-4E92-9868-8A11493C7865@.microsoft.com...
>I have 2 SQL tables that Ineed to combine (append). The column information
>in
> each table is exactly the same, except they are for different years (one
> is
> history and one is current year). I need to be able to create a view that
> has
> both the history data and current year data so I can list transactions
> chronologically from the two tables.

Appending two tables

I have 2 SQL tables that Ineed to combine (append). The column information i
n
each table is exactly the same, except they are for different years (one is
history and one is current year). I need to be able to create a view that ha
s
both the history data and current year data so I can list transactions
chronologically from the two tables.Create View MyView
AS
Select Field1, Field2, Field3
From Table1
UNION
Select Field1, Field2, Field3
From Table2
"Ranjit Charles" <RanjitCharles@.discussions.microsoft.com> a crit dans le
message de news: 055547F7-9031-4E92-9868-8A11493C7865@.microsoft.com...
>I have 2 SQL tables that Ineed to combine (append). The column information
>in
> each table is exactly the same, except they are for different years (one
> is
> history and one is current year). I need to be able to create a view that
> has
> both the history data and current year data so I can list transactions
> chronologically from the two tables.

Appending info into a column

How would you go about adding info to a column.

ex. There are 3 tables where all of the information is the same except the last column. The last column contains different types.

The tables are formatted something like

Name|Phone|Coverage|AA
Name|Phone|Coverage|BB
Name|Phone|Coverage|CC

But I want to have one table that contains the info as

Name|Phone|Coverage|AA|BB|CC|

| = Column changeJoin (possibly outer join) the 3 tables on the common columns.|||Name|Phone|Coverage|AA|BB|CC|

But are you not going to have a record for each type here also?|||I am not even sure how to word this. I tried the join and made sure to connect all of the tables to the most important information. Then I only output the AA, BB, CC columns. So in essence I have 3 free standing columns that are based on the same ID.

Here comes the problem. How do I match these columns with their related row.

I would like to take these 3 columns and append them to the end of the row with the corresponding information.

ex. I have AA|BB|CC and have Name|Number|Date

and want to have Name|Number|Date|AA|BB|CC

(the original rows looked something like my original post description)

but not all rows that had a BB correspond to a row with AA and would I want them to end up looking like this

ex. Name|Number|Date| |BB|CC

I know this is confusing, but I think it is coherent.

Please help!

Originally posted by andrewst
Join (possibly outer join) the 3 tables on the common columns.

Appending from one column to another for every row in a table

Hi there,
I need to carry out a database cleaning/maintenance operation whereby I
take the text in one column called ShortDescription and add it to the
text in a column called AdditionalDetails.
I know how to copy from one to the other but that would have the effect
of overwriting the values in the AdditionalDetails column. I need
whatever is in the first column to be simply appended to whatever is in
the second. If the first has null in it, nothing should happen, and I
guess if the second has a null in it, the null should be replaced with
whatever's in the second column.
Can anyone advise me on how to do this? I'm sure it must be a relatively
simple operation but I've never seen it done.
Thanks to anyone who can help
SimonUpdate table1 set additionaldetails = isnull(additionaldetails,'') +
shortdescription
where shortdescription is not null
Jay
Simon Harvey wrote:
> Hi there,
> I need to carry out a database cleaning/maintenance operation whereby I
> take the text in one column called ShortDescription and add it to the
> text in a column called AdditionalDetails
> I know how to copy from one to the other but that would have the effect
> of overwriting the values in the AdditionalDetails column. I need
> whatever is in the first column to be simply appended to whatever is in
> the second. If the first has null in it, nothing should happen, and I
> guess if the second has a null in it, the null should be replaced with
> whatever's in the second column.
> Can anyone advise me on how to do this? I'm sure it must be a relatively
> simple operation but I've never seen it done.
> Thanks to anyone who can help
> Simon|||You need concatenation, which is implemented using the + character.
UPDATE
SET AdditionalDetails = AdditionalDetails + ShortDescription
WHERE ShortDescription IS NOT NULL
The problem with the query above is that if AdditionalDetails is NULL
the concatenation ends up as NULL. So the more robust version would
be:
UPDATE
SET AdditionalDetails = COALESCE(AdditionalDetails, '') +
ShortDescription
WHERE ShortDescription IS NOT NULL
Roy Harvey
Beacon Falls, CT
On Wed, 13 Dec 2006 16:20:40 +0000, Simon Harvey
<nothanks@.hotmail.com> wrote:
>Hi there,
>I need to carry out a database cleaning/maintenance operation whereby I
>take the text in one column called ShortDescription and add it to the
>text in a column called AdditionalDetails.
>I know how to copy from one to the other but that would have the effect
>of overwriting the values in the AdditionalDetails column. I need
>whatever is in the first column to be simply appended to whatever is in
>the second. If the first has null in it, nothing should happen, and I
>guess if the second has a null in it, the null should be replaced with
>whatever's in the second column.
>Can anyone advise me on how to do this? I'm sure it must be a relatively
>simple operation but I've never seen it done.
>Thanks to anyone who can help
>Simon|||You are both SQL Stalions!
I knew it would be simple!
Thank you - you've been a great help
Kindest Regards
Simon

Appending from one column to another for every row in a table

Hi there,
I need to carry out a database cleaning/maintenance operation whereby I
take the text in one column called ShortDescription and add it to the
text in a column called AdditionalDetails.
I know how to copy from one to the other but that would have the effect
of overwriting the values in the AdditionalDetails column. I need
whatever is in the first column to be simply appended to whatever is in
the second. If the first has null in it, nothing should happen, and I
guess if the second has a null in it, the null should be replaced with
whatever's in the second column.
Can anyone advise me on how to do this? I'm sure it must be a relatively
simple operation but I've never seen it done.
Thanks to anyone who can help
Simon
Update table1 set additionaldetails = isnull(additionaldetails,'') +
shortdescription
where shortdescription is not null
Jay
Simon Harvey wrote:
> Hi there,
> I need to carry out a database cleaning/maintenance operation whereby I
> take the text in one column called ShortDescription and add it to the
> text in a column called AdditionalDetails
> I know how to copy from one to the other but that would have the effect
> of overwriting the values in the AdditionalDetails column. I need
> whatever is in the first column to be simply appended to whatever is in
> the second. If the first has null in it, nothing should happen, and I
> guess if the second has a null in it, the null should be replaced with
> whatever's in the second column.
> Can anyone advise me on how to do this? I'm sure it must be a relatively
> simple operation but I've never seen it done.
> Thanks to anyone who can help
> Simon
|||You need concatenation, which is implemented using the + character.
UPDATE
SET AdditionalDetails = AdditionalDetails + ShortDescription
WHERE ShortDescription IS NOT NULL
The problem with the query above is that if AdditionalDetails is NULL
the concatenation ends up as NULL. So the more robust version would
be:
UPDATE
SET AdditionalDetails = COALESCE(AdditionalDetails, '') +
ShortDescription
WHERE ShortDescription IS NOT NULL
Roy Harvey
Beacon Falls, CT
On Wed, 13 Dec 2006 16:20:40 +0000, Simon Harvey
<nothanks@.hotmail.com> wrote:

>Hi there,
>I need to carry out a database cleaning/maintenance operation whereby I
>take the text in one column called ShortDescription and add it to the
>text in a column called AdditionalDetails.
>I know how to copy from one to the other but that would have the effect
>of overwriting the values in the AdditionalDetails column. I need
>whatever is in the first column to be simply appended to whatever is in
>the second. If the first has null in it, nothing should happen, and I
>guess if the second has a null in it, the null should be replaced with
>whatever's in the second column.
>Can anyone advise me on how to do this? I'm sure it must be a relatively
>simple operation but I've never seen it done.
>Thanks to anyone who can help
>Simon
|||You are both SQL Stalions!
I knew it would be simple!
Thank you - you've been a great help
Kindest Regards
Simon

Appending from one column to another for every row in a table

Hi there,
I need to carry out a database cleaning/maintenance operation whereby I
take the text in one column called ShortDescription and add it to the
text in a column called AdditionalDetails.
I know how to copy from one to the other but that would have the effect
of overwriting the values in the AdditionalDetails column. I need
whatever is in the first column to be simply appended to whatever is in
the second. If the first has null in it, nothing should happen, and I
guess if the second has a null in it, the null should be replaced with
whatever's in the second column.
Can anyone advise me on how to do this? I'm sure it must be a relatively
simple operation but I've never seen it done.
Thanks to anyone who can help
SimonUpdate table1 set additionaldetails = isnull(additionaldetails,'') +
shortdescription
where shortdescription is not null
Jay
Simon Harvey wrote:
> Hi there,
> I need to carry out a database cleaning/maintenance operation whereby I
> take the text in one column called ShortDescription and add it to the
> text in a column called AdditionalDetails
> I know how to copy from one to the other but that would have the effect
> of overwriting the values in the AdditionalDetails column. I need
> whatever is in the first column to be simply appended to whatever is in
> the second. If the first has null in it, nothing should happen, and I
> guess if the second has a null in it, the null should be replaced with
> whatever's in the second column.
> Can anyone advise me on how to do this? I'm sure it must be a relatively
> simple operation but I've never seen it done.
> Thanks to anyone who can help
> Simon|||You need concatenation, which is implemented using the + character.
UPDATE
SET AdditionalDetails = AdditionalDetails + ShortDescription
WHERE ShortDescription IS NOT NULL
The problem with the query above is that if AdditionalDetails is NULL
the concatenation ends up as NULL. So the more robust version would
be:
UPDATE
SET AdditionalDetails = COALESCE(AdditionalDetails, '') +
ShortDescription
WHERE ShortDescription IS NOT NULL
Roy Harvey
Beacon Falls, CT
On Wed, 13 Dec 2006 16:20:40 +0000, Simon Harvey
<nothanks@.hotmail.com> wrote:

>Hi there,
>I need to carry out a database cleaning/maintenance operation whereby I
>take the text in one column called ShortDescription and add it to the
>text in a column called AdditionalDetails.
>I know how to copy from one to the other but that would have the effect
>of overwriting the values in the AdditionalDetails column. I need
>whatever is in the first column to be simply appended to whatever is in
>the second. If the first has null in it, nothing should happen, and I
>guess if the second has a null in it, the null should be replaced with
>whatever's in the second column.
>Can anyone advise me on how to do this? I'm sure it must be a relatively
>simple operation but I've never seen it done.
>Thanks to anyone who can help
>Simon|||You are both SQL Stalions!
I knew it would be simple!
Thank you - you've been a great help
Kindest Regards
Simon

Appending data to an existing record

I am a novice when it comes to using SQL. I would like to append text to existing data in a specified column, but I can not figure out how to do it. I was trying to use an update query, but as far as I can tell this only replaces the data within the column. Any help would be appreciatedHi

Use update statement .
eg.

table1 (tablename)
id values
1 text1
2 text2
3 text3

Let the table1 contains two fields id and value. U need to append some text
to the existing values. is it ?
ok...use the following postgresql query .
update table1 set values = values || 'newtext' where id = 1

|| --> string concat operator

This will result in

table1
id values
1 text1newtext
2 text2
3 text3

If you want to update all the rows remove the 'where' condition
Try this and reply

-somaskarthic

Quote:

Originally Posted by srkartes

I am a novice when it comes to using SQL. I would like to append text to existing data in a specified column, but I can not figure out how to do it. I was trying to use an update query, but as far as I can tell this only replaces the data within the column. Any help would be appreciated

|||Hi

Here i mentioned is postgresql query , if u use some other database
please find the appropriate sting concat operator . In postgres || is the concat operator . If you use mysql or sql server , please find the correct concatenation operator.

-somaskarthic

Quote:

Originally Posted by somaskarthic

Hi

Use update statement .
eg.

table1 (tablename)
id values
1 text1
2 text2
3 text3

Let the table1 contains two fields id and value. U need to append some text
to the existing values. is it ?
ok...use the following postgresql query .
update table1 set values = values || 'newtext' where id = 1

|| --> string concat operator

This will result in

table1
id values
1 text1newtext
2 text2
3 text3

If you want to update all the rows remove the 'where' condition
Try this and reply

-somaskarthic

|||

Quote:

Originally Posted by somaskarthic

Hi

Here i mentioned is postgresql query , if u use some other database
please find the appropriate sting concat operator . In postgres || is the concat operator . If you use mysql or sql server , please find the correct concatenation operator.

-somaskarthic

This is the way:

update table1 set values = values + 'newtext' where id = 1|||Thanks guys! Worked perfectly.

appending data to a text column

Hello everyone, I'm a newbie so have some mercy please. Please feel free to
make some best practice suggestions. Here we go, I am importing data into a
table within my database. I'll use some DDL for clarity.
Create table ImportTable
(ImportId int Not Null,
ImportLog text (10000)
)
Create table ExistingTable
(Existingid int Not Null,
Existinglog text (10000)
)
When the data is imported I would like to check if the ImportId column in
ImportTable has a match in ExistingId column within ExistingTable, is so I
would like to APPEND the data from ImportLog to ExistingLog
This is a shortened version of the actual table but its give you guys a
general feel for what I am trying to accomplish. Thanks in advance for any
assistance.
kw_uh97I think this does the job, you need the where condition if it's possible
to have a null entry in the importlog column, for some reason trying to
append null to existingtext converts it to null too:
update existingtable
set existingtext = existingtext + importlog
from existingtable inner join importtable on existingid = importid
where importlog is not null
*** Sent via Developersdex http://www.examnotes.net ***|||Anthony
Sorry for the delay and thanks for the query it works just fine I will
implement this ASAP.
Thanks, Again
kw_uh97
"Anthony Brown" wrote:

> I think this does the job, you need the where condition if it's possible
> to have a null entry in the importlog column, for some reason trying to
> append null to existingtext converts it to null too:
>
> update existingtable
> set existingtext = existingtext + importlog
> from existingtable inner join importtable on existingid = importid
> where importlog is not null
>
> *** Sent via Developersdex http://www.examnotes.net ***
>