Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to append data in SQL

Posted on 2014-09-12
9
Medium Priority
?
181 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 53

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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 53

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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
Suggested Courses

577 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