Solved

How to append data in SQL

Posted on 2014-09-12
9
136 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 47

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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
 
LVL 47

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:
Scott Pletcher 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

770 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