Replace Text/data In One Table With Corresponding Text/data From Another
Jan 10, 2006
Hi -
I've never used SQL for anything but simple copies and queries, and now I need to do something that's probably simple, as well, but I don't know how to start. I need to update values in a text field with new values from another table. I can do it individually with an update statement (Set [field1] = 'newvalue' where [field1] = 'oldvalue'). But I have 400 different values and a bunch of different tables that need to be updated, and can't imagine that's the only solution. I can make up a simple table that will have all the old values in one column, and the new values in the next. I need a statement that look at the old value, then fetch the new value from the table I made, and replace the old value with the new.
I've looked at various help files and tried to search for a solution elsewhere, but I'm not coming up with anything. I guess I just don't know where to look. Thanks in advance for any help you can give me.
Rebekkah
View 5 Replies
ADVERTISEMENT
Jun 14, 2006
I am working with a database named €œDocuments€? that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:
1 = Text
2 = Report
3 = Background
4 = Index
I would like to create a new column named €œDocType€? in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I). I was able to easily create the new column and cast the data type from integer to varchar:
--CREATE NEW COLUMN €œDocType€? WITH VARCHAR DATATYPE
ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL
Go
--UPDATE NEW COLUMN WITH CAST STRING
UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))
Go
But I have problems with the REPLACE method for replacing the numbers with letters. First I tried this based on the examples in MSDN Library:
--REPLACE NUMBERS WITH LETTERS
UPDATE Fulldocuments REPLACE (DocType,"1","T")
Which produced an error message: €œIncorrect syntax near 'REPLACE'.€?
Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:
UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")
Which produced the same error message: €œIncorrect syntax near 'REPLACE'.€?
I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.
View 3 Replies
View Related
Apr 29, 2015
I have a pivot table that connects to our data warehouse via a PowerPivot connection. Â The data contains a bunch of comment fields that are each between 250 and 500 characters. Â I've set the columns in this pivot table to have the 'Wrap Text' set to true so that the user experience is better, and they can view these comment fields more clearly.
However, whenever I refresh the data, the text wrapping un-sets itself. Â Interestingly, the 'Wrap Text' setting is still enabled, but I have to go and click it, then click it again to actually wrap the text. Â This is very burdensome on the user, and degrading the experience.
Any way to make this text wrapping stick so that I don't have to re-set it every time I refresh the data?
View 2 Replies
View Related
Jan 22, 2008
I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.
View 9 Replies
View Related
May 14, 2008
Hi There,
Could someone please tell me why I am getting the above error on this code:
select (replace
(replace
(replace
(replace (serviceType, 'null', ' ')
, '<values><value>', ' ')
, '</value><value>', ',')
, '</value></values>', ' '))
from credit
serviceType (text,null)
Thanks,
Rhonda
View 1 Replies
View Related
Apr 25, 2006
I get this error:
The data types text and text are incompatible in the equal to operator.
when trying to execute this query
SELECT id FROM users WHERE username=@userName
Any Ideas?
View 3 Replies
View Related
Aug 27, 2015
I have a parameter value as shown below and this is dynamic and can growÂ
Example : 101-NY, 102-CA, 165-GA
116-NY, 258-NJ, 254-PA, 245-DC, 298-AL
How do I get the values in the below format
NY,CA,GA Â --- each state to be followed with comma and the next state
NY,NJ,PA,DC,AL Â --- each state to be followed with comma and the next state
correct query that will fetch  only state names and not the numbers.
View 8 Replies
View Related
Aug 25, 2005
Hi,I have a problem to insert(update) a long text (more than 64K) intoSQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.MSDN says: "When the ntext, text, and image data values get larger,however, they must be handled on a block-by-block basis. BothTransact-SQL and the database APIs contain functions that allow applications towork with ntext, text, and image data block by block." Could somebodygive me an example how to do this, please.Thank you
View 2 Replies
View Related
Jun 1, 2001
We have a text field which is being written to from a java app through JDBC-ODBC. But the data seems to be trucated in the DB. How do we store all the data in this field (the text being stored can be quite large) without it being truncated?
View 1 Replies
View Related
Oct 26, 2007
Hello all,
started working on a website in ASP that use mssql, i am trying to store large ammount of text, for now its just 10kk letters, the data type is set to text in the mssql db, but when i read it back from the db, i only get 7.7k letters, i know its not an ASP problem cause i print the var holding the content before storing it in the db.
my question is, is there a way to change the text length limit in mssql, or use another data type?
Thanks,
Gilad Darshan
EDIT: forgot its saving the html source with the style and not only the words. fixed to the right amount of letters
View 2 Replies
View Related
Jan 7, 2008
Hi Experts,
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
Can anyone suggest how to solve this issue.
View 11 Replies
View Related
May 3, 2004
Hello all,
Please help....
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
Any help would be highly appreciated.
Thanks in advance.
View 3 Replies
View Related
Apr 22, 2002
I need to export data from a table to a text file, where the data in the table is deleted after written to the file. It is simple using DTS, but I want to do the export in "chunks" of data, committing the delete say after every 1000 rows.
My thought was a stored procedure would be easy enough to do this (done these in Oracle many times), but I don't know the quickest way to export a row of data from a stored procedure to a text file. Isn't using a command-line shell too slow? What are my options?
View 1 Replies
View Related
Apr 15, 2006
Hi. my websever mysql manager allows to Insert data from a text file into the table.
currently i have a table with the following fields.
email name country
so i wanto insers data from a text file into this table.
so my question is : how the text file format should be prepared.
i.e. the first record would be > xxxx@yahoo.com john us
View 2 Replies
View Related
Nov 24, 2006
Hello,
I have an application taht requires the use of a table. The device that this application works on, has a local memory that does not allow me to insert the 800,000 records that I need. Therefore I have two approaches:
1. To insert less records into my local memory database e.g 40,000 but not row by row, bulk insert is better. How do I do the bulk insert?
2. This is the most prefferable way: To find a way to insert all 800,000 records into a table on the storage card which is 1GB. What do you suggest? Will using threads be helpfull? Any ideas?
I use C# from VS 2005, SQL ME, compact framework 2.0 and windows 4.2.
Thanks in advance,
John.
View 6 Replies
View Related
May 23, 2008
Hi,
In my report, I have a table that displays all the data from query. I need to take the value of the 3rd row,2nd column of the table and display in a text box.
I tried to do like:
=ReportItems("TextBox2").Value., where TextBox2 is the 3rd row,2nd column of the table.
This returns just the first row value of the seleted data. But, I need the 3rd row value.
Please let me know how can I acheive this.
View 1 Replies
View Related
Jun 22, 2001
Hi,
I'm trying to export data from one of the table in my SQL 7.0 database into text file. Can someone tell me how can i do this using SQL Query instead of using BCP (command line) ?? Thank you in advance.
View 4 Replies
View Related
Feb 12, 2003
Hi,
I need to prevent modification/update to a field.
So I created a trigger. To take the data from DELETED table then replace the field data.
However, I have problem with one field which data type is text.
MS SQL always return me this error:
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
I use the following code to take the data from deleted table
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(NOTES )
FROM DELETED
I also tried simple Select statement
Select notes from deleted
What code should I use to take the deleted data
Please help.
Thanks in advance
View 9 Replies
View Related
Dec 24, 1998
I needs export data on table to text file so I can process this data
with another database engine ie. Informix.
Can anybody help me to solve this problem ?
View 2 Replies
View Related
Sep 10, 2001
Hi all,
I got a situation here.....
From a source table (in SERVER1) I get ids of candidates and from another source (in SERVER2) I get their CVs (text files stored in various Folders). My destination table (in SERVER3) has two fields, CandidateId & CandidateCV.
I have to transfer the data in above fashion for nearly 1 million records.
How can I write a DTS package which picks up the text file from SERVER2 based on the CandidateId which comes from SERVER1? Probably I need some kind of looping mechanism which changes the candidate id & his CV file.
Can anyone help???
Thanks...
View 2 Replies
View Related
Mar 28, 2008
hi my friends
i want read data from text file,and write to my table in sql,
please help me
M.O.H.I.N
View 6 Replies
View Related
Feb 11, 2008
Hi
Column with TEXT datatype is not stored in the same data row any way. I am wondering if there is any performance gain to put it in a seperate table. Thanks
View 4 Replies
View Related
Feb 12, 2008
I am learning SQLServer Integration Services.
I created a file People.txt containing firstName, LastName seperated by a pipe.
------------------content-----------
John | Doe
Mike | James
Adam | Smith
-----------------------------------------
and another one called gender.txt
------------------content-----------
M
---------------------------------------
I will would like to create integration services package that compines each record of the first file with the record of the second file and inserts the result into table.
--------------Result table content------------------
John
Doe
M
Mike
James
M
Adam
Smith
M
-----------------------------------------------------------
Thanks
View 5 Replies
View Related
Aug 24, 1999
I want to check a table to see what rows have been updated today, then write to a text file some data from the selected rows; then I want to automate this (DTS package? TSQL stored procedure job?) to run every night at midnight. Is the DTS Wizard the best way, that's what I did, but have not confirmed that it is writing a new text file every day, and does each new version write over the old one?
View 1 Replies
View Related
Mar 11, 1999
Hi,
I have to export the table data from my databse into text files as I nedd to put it in Informix database using a sheel script. Is there a way by which I can do this.
Is there any other way by which I can put the data from SQL Server to Informix.
Any takers,
Thanking you in advance.
Bye for now,
Himauhu
View 1 Replies
View Related
Oct 16, 2015
I am putting together an invoice for my company. I have a text box describing each section of the invoice, followed by a table to list out the charges. I am using multiple tables based on what type of charge the client is receiving.Â
I would like to hide each section if there are no items purchased of that type. I can do this with the table using the expression "=CountRows() < 1", but I do not know how to refer to that table (call it Tablix1 for the sake of discussion) for the text box. I've tried using a ReportItems function as my basis, without success.Â
View 2 Replies
View Related
Feb 27, 2008
Hii
I want to transfer data from table to a text file.I m trying to use bcp utility and xp_cmdshell.but the export is not successful.
My query is:
EXEC master..xp_cmdshell'bcp "Select * from test..emp" queryout "c:dept.txt" -c -T -x'
and its output is:
NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 16 Average : (187.50 rows per sec.)
NULL
but there is no row copied into c:dept.txt
where is the problem??
Thanx
-Supriya
View 23 Replies
View Related
Oct 1, 2001
Hey,
can one of you please show me how to import data from a text file into a temp table in a stored proc.
thanks
Zoey
View 1 Replies
View Related
Dec 10, 2014
I need to update a large table, about 55 million rows, without filling the transaction log, in the shortest time as possible. The goal is to alter the table and change the data type for Text column from VARCHAR(7900) to NVARCHAR(MAX).
Since I cannot do it with an ALTER TABLE statement (it would fill up the transaction log) I'm thinking to:
- rename column Text in Text_OLD
- add Text column of type NVARCHAR(MAX)
- copy values in batches from Text_OLD to Text
The table is defined like:
create table DATATEXT(
rID INTEGER NOT NULL,
sID INTEGER NOT NULL,
pID INTEGER NOT NULL,
cID INTEGER NOT NULL,
err TINYINT NOT NULL,
[Code] ....
I've thought about a stored procedure doing this but how to copy values in batch from Text_OLD to Text.
The code I would start with (doing just this part) is the following, but maybe there are more efficient ways to do it, or at least there's a better way to select @startSeq in the WHILE loop (avoiding to select a bunch of 100000 sequences and later selecting the max).
declare @startSeq timestamp
declare @lastSeq timestamp
select @lastSeq = MAX(sequence) from [DATATEXT] where [Text] is null
select @startSeq = MIN(Sequence) FROM [DATATEXT] where [Text]is null
BEGIN TRANSACTION T1
WHILE @startSeq < @lastSeq
[Code] ....
View 1 Replies
View Related
May 29, 2015
I am looking for a way to convert the following format into a sql table. The format it is Bib Tex.
Essentially a new row in the table would be for each entry, denoted by an @ logo and each column is denoted by an =, as you can see from the example data no one contains all the possible columns and some fields can be over two lines long.
To load this I was considering loading it into a table as each line being a row. Adding a row number, then a column counting the @ signs in order and essentially grouping each record, then for each group running through and looking for the column keywords 'author' , 'title' etc then splitting the data out into those constituent parts using substring and charindex.
@Book{hicks2001,
author = "von Hicks, III, Michael",
title = "Design of a Carbon Fiber Composite Grid Structure for the GLAST
Spacecraft Using a Novel Manufacturing Technique",
publisher = "Stanford Press",
year = 2001,
[Code] ....
View 9 Replies
View Related
Nov 8, 2006
Hello, I want to load data from text file to MS SQL DB table.
In MySQL, it is the "LOAD DATA INFILE..." query statement.
What is sutable query if I want to migration from Mysql to MS SQL Server 2005 Express?
View 2 Replies
View Related
Aug 3, 2015
I'm importing comma-delimited text files into a SQL table. The data imports in a seemingly random order. One time I import and the lines appear one way and the next time I import they import another way.
Is there a way to force the text files to import in the same order the data is found in the file?
View 10 Replies
View Related
Jul 20, 2005
Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
View 1 Replies
View Related
