Solved

How to append data in SQL

Posted on 2014-09-12
9
132 Views
Last Modified: 2014-09-17
I wish to create a query to append some text to a an existing table field with a where statement

The where statement would be
where WOC.WONUM = WO.WONUM and WO.CLOSEDATE = '1900-01-01 00:00:00.000' and
WO.REQUESTDATE < '2014-01-01 00:00:00.000'

Do I use APPEND as a command?

Gordon
0
Comment
Question by:GiaHughes
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40318767
I think you want to update a column of a table, right?
Then you SQL statement should be something like:
UPDATE YourTableNameHere
SET FieldToBeUpdated = FieldToBeUpdated + TextToBeAppend
WHERE WOC.WONUM = WO.WONUM and WO.CLOSEDATE = '1900-01-01 00:00:00.000' and 
 WO.REQUESTDATE < '2014-01-01 00:00:00.000'

Open in new window

0
 
LVL 2

Expert Comment

by:Akilandeshwari N
ID: 40318819
UPDATE mytable
SET table_field = table_field + ISNULL(mytext,'')
FROM
mytable WOC,
mytable2 WO
WHERE WOC.WONUM = WO.WONUM and WO.CLOSEDATE = '1900-01-01 00:00:00.000' and 
 WO.REQUESTDATE < '2014-01-01 00:00:00.000'

Open in new window

0
 

Author Comment

by:GiaHughes
ID: 40318940
Hi

I have created the query
UPDATE WOC
Set WOC.TEXTS = WOC.TEXTS + 'Authorised by BJ'
From WOC,WO
where WOC.WONUM = WO.WONUM and WO.CLOSEDATE = '1900-01-01 00:00:00.000' and
WO.REQUESTDATE < '2014-01-01 00:00:00.000'

But got the following error
Msg 402, Level 16, State 1, Line 2
The data types text and varchar are incompatible in the add operator.

The WOC.TEXT field is (text, Null)

Gordon
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40318945
and that should be changed, unless you have some old sql server (or the db in a old compatibility version)
change the field to varchar(max) which has the same capacity as TEXT, but the normal operations than varchar.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40318948
Guy Hengel is right. Why you need a TEXT data type? How many characters are you expecting to store in that field?
0
 

Author Comment

by:GiaHughes
ID: 40318953
The WOC.TEXt is in a database which I cannot change

Gordon
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40319056
updating TEXT columns is not so easy, and cannot be done in a UPDATE or JOIN.
see here for the syntax, please refer to the tech reference:
http://msdn.microsoft.com/en-us/library/ms189466.aspx
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40323385
If the SQL is at least 2005 version, you should be able to do this:


UPDATE WOC
 Set WOC.TEXTS = CAST(WOC.TEXTS AS varchar(max)) + 'Authorised by BJ'
 From WOC
 Inner Join WO On WOC.WONUM = WO.WONUM
 Where WO.CLOSEDATE = '1900-01-01 00:00:00.000' and
       WO.REQUESTDATE < '2014-01-01 00:00:00.000'
0
 

Author Closing Comment

by:GiaHughes
ID: 40327519
Works Great
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now