Solved

How to append data in SQL

Posted on 2014-09-12
9
151 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 49

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 143

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 49

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 143

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Return Rows as per Quantity of Columns Value In SQL 6 29
SQL 2014 missing dll from Bin? 3 35
MS SQL Server Management Studio R2 4 33
SQL query 7 23
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 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