Hello again,
As a beginner I would like to replace records in a Table with Records from a Flatfile.
ILet's say, I got a custnum from the Flatfile and I like to replace the whole record of custnum (Primary Key) in a Table.
Can someone give me a hint how to do?
I'm looking forward to an early answer.
Regards
Chaepp
I've got a lot of %20 characters in my tables, and need to remove them however I get the error:
Argument data type ntext is invalid for argument 1 of replace function.
My Code:
UPDATE cmsPropertyData
SET dataNtext = REPLACE(dataNtext,'%20','')
WHERE cmsPropertyData.contentNodeId = 1151
Hi! I've some data in a table, and I want to add these records to another table, but I tried yet with select ... Into ... and I lose all the data from this one. I need to ADD the data from table to table. Any suggestion?
Thx
Hi
acutally due to certain reason i have changed the few tables name in my database, but now i m facing problem in my software, i have alot of procedures and function as well in my database and these are using old table name,
Is there any script that i can use to replace the name of the tables.
i have checked few procedure/function in which i use table name with different style like [dbo].[tablename] and in some places i just use only [tablename].
Can anyone help me ?
Thanks and looking forward.
I have an automatic data collection system. The large majority of the time, all data is electronically gathered. On occasion, the need arises to substitute a manually entered value into the data when a valuable piece of information cannot be electronically secured. When a piece of data is open to substitution, it arrives with a value of -9999 (a value not naturally occuring) in Table A. I want to be able to use a trigger on table A to get its substitution value from a corresponding column (Same name) in Table B. Tables A & B will have an identical schema. A is raw data, B is the manually updated table. Any value in the raw table with the content of -9999 gets its replacement from Table B.
I'm open to any ideas!!
RC =:((
Greetings,
I'm a web developer who is new to T-SQL scripting.
I need to write a script that will update a table from a text file daily; the columns will be identical but their content will vary. I immediately thought that dropping the existing table and creating a new one from the file as a scheduled job was the answer.
My boss thinks that this approach is risky and suggests comparing the new and old data and altering the existing table instead. My gut says that this is an inefficient and unecessary solution.
Any advice?; if so, any sample scripts you can point to?
Thanks!
I need to strip some puntcuation from any field in a given table.I'd rather like to avoid using the replace () for each field in thetable.Anyone have a nifty way do this?Is there a special name that I can use in the replace that means theentire row?(other than syntax, something like REPLACE(@ROW,CHAR(39),'') )tiaRob
View 1 Replies View RelatedHello,I'm a beginner in SQL and I have been searching through the SQL Cookbook and Google but I can't seem to find an example of what I want to do. I want to create a report that will return names and emails using two of my tables. I want to use the email in my primary table in the select but if it is null or empty I want to replace it with an email from my secondary table. Below is what I would like to do but I got a syntax error with it in SQL Server 2000. SELECT MemberID As ID, MemberFirstName As FirstName, MemberLastName As LastName, (IF MemberEmail = '' THEN SELECT TOP 1 OtherEmail FROM OtherTable WHERE OtherID = MemberID) As EmailFROM PrimaryTableThanks for your time.Jason
View 8 Replies View RelatedWhat is the correct syntax to replace a field data nvarchar(50)
Current data = 0020-10-02
Change = 2003-10-02
Thank you in advance.
I have the below table :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/2015NULL 23:00.0 NULL
10xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
11xxxxx@gmail.com 15/06/2015NULL 00:00.0 NULL
12xxxxx@gmail.com NULL 15/06/2015 NULL 00:00.0
13xxxxx@gmail.com 14/06/2015NULL 00:00.0 NULL
14xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
Then i need to replace second row with value with first row with null and so on :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/201514/06/2015 23:00.0 00:00.0
10xxxxx@gmail.com NULL NULL NULL NULL
11xxxxx@gmail.com 15/06/201515/06/2015 00:00.0 00:00.0
12xxxxx@gmail.com NULL NULL NULL NULL
13xxxxx@gmail.com 14/06/201514/06/2015 00:00.0 00:00.0
14xxxxx@gmail.com NULL NULL NULL NULL
need help
replace characters on condition in table and cells
but only if
if i put number or 1 , 2 , 3 , 4 above the cell of the eployee for example( employee id=222 name =bbbb day1)
i replace characters with '0' and '#'
and it must work dynamically AND replace ONLY THIS characters
table before the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 1 3
111 aaaa 1 A C
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 2
555 eeee 1 B
table after the replace
id fname val day1 day11 day111 day2 day22 day222
------------------------------------------------------
111 aaaa 2 0 0
111 aaaa 1 # #
222 bbbb 2
222 bbbb 1
333 cccc 2
333 cccc 1
444 dddd 2
444 dddd 1
555 eeee 2 0
555 eeee 1 #
tnx FOR THE HELP
need help
condition 1
replace characters on condition in table and cells
but only if
how to do it - if i put * (asterisk) like in employee 111 in day1 - the the upper characters the (A S B)
i replace characters with '-'
and it must work dynamically
condition 2
replace characters on condition in table and cells
but only if
if i put number or 1 , 2 , 3 , 4 above any cell for example( employee id=222 name =bbbb day1)
i replace characters with '0' and '#'
and it must work dynamically
table before the replace
id
fname
val
day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555
111
aaaa
2
A
S
B
e
t
y
R
Y
M
j
o
p
111
aaaa
1
*
*
*
*
222
bbbb
2
1
1
222
bbbb
1
A
-
-
-
B
-
333
cccc
2
333
cccc
1
444
dddd
2
3
4
444
dddd
1
-
-
C
C
555
EEE
2
A
G
C
555
EEE
1
*
table after the replace
id
fname
val
day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555
111
aaaa
2
-
-
-
-
-
-
-
-
-
-
-
-
111
aaaa
1
null
null
null
null
222
bbbb
2
0
0
222
bbbb
1
#
-
-
-
#
-
333
cccc
2
333
cccc
1
444
dddd
2
0
0
444
dddd
1
-
-
#
#
555
EEE
2
-
-
-
555
EEE
1
null
tnx for the help
I would like to create a function that will replace a string using a reference table
I have a table : reference
ID String ReplaceWith
1 ≈ &
2 < <
3 > >
If I pass a string into a function, function needs to replace a string with replace with string column in reference table
For example, if I pass a string : car $ap; fjld
The function should return car & fjld
How can i create a function like this so that i can call it in Stored procedure....
when insert a multiple line string value into a field it display with two squares when open table.
insert into temptest (test,refdetails,cardno)values('test1','this is first line
and second line
and third line',
'23243')
this record shows in the database with squares for enter key. how to replace these squares with space?
pat
this is first line and [][]second line [][]and third line
Trying to replace a string in a table and it is not working the path can be like OM-WD08-1 reportData.raw
USE Config
DECLARE @OldPath varchar(30), @NewPath varchar(30)
-- Initialize the variable
SET @OldPath ='OM-WD08-1';
SET @NewPath ='AA-PC';
UPDATE AnatomyConfigs
SET Path = REPLACE(Path,@OldPath,@NewPath) WHERE Path IS NOT NULL
AND Path LIKE @OldPath
GO
Hi folks,I'm doing calculations based on data in a table, but the data has somezeros in the field I'm dividing by. I'm trying to write a script toreplace any field with 0 or null with 1, but it's not working. HEre'swhat I've got:Update A Set A.deptcode = A.deptcode,A.type = A.Type,A.Volume = (case A.VolumeWhen Null Then 1When 0 then 1Else A.VolumeEnd)From Data_Unsorted A Join Data_Unsorted B OnA.deptcode = B.deptcode and A.type = B.TypeMy table is data_unsorted and deptcode and type are my primary keysVolume is the item I want to put 1 if null or zero, and I'd thing theabove statement would work, but it doesn't. This table has 383 rows,and it says it updates 383 rows, but when I run the following query totest:select a.deptcode, a.type, a.volumefrom data_unsorted awhere a.AveMonthVolume = 0 or a.AveMonthVOlume is nullIt didn't work... still TONS of nulls and zero's. Is there a trick tothis???Thanks,Alex.
View 2 Replies View RelatedThere is a table [Formula_Calc] with formula calculations that need to be replaced with relevant values based on another table [Totals]
[Totals]
RowNo|Total
F1|240
F2|160
F3|180
F11|1000
F12|1500
F13|2000
For example we've got a row from [Formula_Calc] table 'F1+F3' as a string that needs to be transformed as 240+160=400
The below code works for the above example but if I pick 'F11+F3' instead , returns 2561 which comes from 2401+16.
Probably replaces F1 value instead of F11 and adds 1st digit (1) if I got it right ...
DECLARE @formula NVARCHAR(100);
DECLARE @Total NVARCHAR(100);
SET @formula = 'F11+F3';
SELECT @formula = REPLACE(@formula,RowNo,Total)
FROM [Totals]
SET @Total='select '+@formula
EXECUTE sp_executesql @Total;
PRINT @Total;
Hi i am getting a weekly transaction file which has two columns trans code and trans date to indicate whether the record is changed, added or modified . and the monthly master file contains blank in these two fields.
How do i update the row coming from the transaction file in the tables which contain the rows from the master file .
to better explain the example is
Master File
ID Name AGE Salary Transcode TransDate
2 dev 27 2777
Transaction File indicating change
ID Name AGE Salary Transcode TransDate
2 dev 27 24444 C 08072007
the ouput should be
2 dev 27 24444 C 08072007
replacing the existing row in the table(updating the whole row)
i have 50 columns in my table and based on the two fields i should replace the rows exisiting in table and if ID doesnot match exist just add them as a new row.
what transformation should i use .... to replace all the columns which have matching ID in table to the current record from trans file and if there doesnt exist matching id just add them as new row.
Thanks...
I currently have a flatfile with a seperate COBOL copybook. I need to be able to import all of it correctly into a db in mssql. Are there any *free programs that will turn the flatfile into some form for insertion into the db? What about csv format into the db?
View 14 Replies View RelatedWhat is the true difference between FlatFile and File in terms of ConnectionManagerType? If FlatFile is just a subset, why does SSIS architechture need it? What is the reasoning behind?
View 1 Replies View RelatedThe CreatePackage sample provided with SQL Server programmatically creates a package that has a source type of OLEDB to a flat file destination. I am building exactly the opposite, source=flatfile, destination=SQL Server. I expect that will be a more common scenario is using SSIS.
The problem I have is populating the source columns in the FlatFileSource connection manager programmatically. I know it can be done because it happens when you build a package in Visual Studio. What I'd like to know is how to do it programmatically in the object model. How can I interrogate the datasource through the connection manager to find out what columns it has? If I know, I can add the columns to the connection manager. My sample below does this, but it doesn't know the number of columns in the source so that value is hardcoded. I'm guessing there is a better way to do this than what I've got below.
How can I find the number of columns in my source so I can add the columns to the connection manager?
Thanks.
Private Sub AddColumnsToFlatFileConnectionManager()
Dim ff As wrap.IDTSConnectionManagerFlatFile90 = Nothing
For Each cm As ConnectionManager In _Package.Connections
If cm.Name.Equals(_ExternalConnectionID) Then
ff = TryCast(cm.InnerObject, wrap.IDTSConnectionManagerFlatFile90)
DtsConvert.ToConnectionManager90(cm)
End If
Next
If Not ff Is Nothing Then
Dim col As wrap.IDTSConnectionManagerFlatFileColumn90
Dim name As wrap.IDTSName90
Dim Min As Int32 = 0
Dim Max As Int32 = Min + 3 ' *** HARDCODED LIMIT ***
For cols As Integer = Min To Max
col = ff.Columns.Add()
If cols = Max Then
col.ColumnDelimiter = vbCrLf
Else
col.ColumnDelimiter = ","
End If
Dim width As Int32 = 50
Dim DataType As wrap.DataType = wrap.DataType.DT_STR
col.ColumnType = "Delimited"
col.DataType = DataType
col.MaximumWidth = width
col.DataPrecision = 0
col.DataScale = 0
col.ColumnWidth = width
name = TryCast(col, wrap.IDTSName90)
name.Name = "Column " & cols.ToString
Next
End If
End Sub
Hi,
Iam migration data from a table into a comma delimited flatfile,but i need to specify all the columns within [ " ] in the flatfile
for example
i have a column [Name] the values are John,Mani,Raghu.....
The flat file should be outputted as
"John"
"Mani"
"Raghu"
Is there anyway to do this.Pls help.
Thanks,
SVGP
Hi all,
my package has a lot of lookups of fact table fields against dimension tables.
i did a redirect row for each lookup error to a flat file.
i wanted to put all lookup mismatch on the same file, but i couldn't do it because there'll be error saying that
The process cannot access the file because it is being used by another process.
i don't think it's practical to have flat files for each lookups. how is this normally done? please help...
thanks!
This seems so obvious, but is causing me a lot of trouble:
The flat files input to my SSIS packages may contain an empty line between each row of data, or may be "continuous" without any extra empty lines. Here are small examples of the data I'm working with:
With the extra line:
"M","SFH","Single Family Mortgage Loans","Single Fam Mtg"
"M","MFH","Multifamily Mortgage Loans","Multi-Fam Mtg"
"R","MIX","Mixed/Various/Unknown","Mixed/Various"
Without the extra line:
"M","SFH","Single Family Mortgage Loans","Single Fam Mtg"
"M","MFH","Multifamily Mortgage Loans","Multi-Fam Mtg"
"R","MIX","Mixed/Various/Unknown","Mixed/Various"
Seems to me it should be possible to set up a single FlatFile connection to handle both these formats, but it's not easy.
I can easily manually edit the FlatFile connection and change the "RowDelimiter" to either "{CR}{LF}" or "{CR}{LF}{CR}{LF}" and the package runs against the corresponding flat file.
However, when I try to use an expression (@[User::StrRowDelimiter]) to set the RowDelimiter to the value of a package variable, the expression is completely ignored.
I can only change the RowDelimiter property on the FlatFile connection by manually opening the editor on the connection and changing it to match the incoming file.
Why isn't this connection seeing the expression I've set up for its RowDelimiter property?
Thanks for any help!
Hi,
I have a Conditional Split to FlatFile Destination.
How can I put the result, that goes in the FlatFile Destination, in a variable also (like in Recordset Destination).
Do I have to runs this thing twise (and put the first time in FlatFile Destination and the second time in Recordset Destination)?
Thank you.
I am importing a flatfile and cannot seem to deal with an issue that seems quite simple.
The files have a header row with column names and those rows start with '#'
However sometimes this header row will also be present in the middle of the file.
The Source tries to parse this row and fails
Is there any way to tell the flafile source to skip rows that start with a particular character like comment rows?
My DTS package, deployed and run from the file system, works just fine for me, but fails when someone else runs it. The only explicit error from the dtexec command is:
View 11 Replies View RelatedI am running an ISQL script every day and automatically emailing the output of a query as the body of an email, to a group of people. ISQL executes a SQL file which selects some data and the output of the ISQL is sent to a *.txt file.
The Issue is that the output of the query wraps in the flatfile. It seems that the *.txt file wraps at character 76.
Does anyone have an Idea how to prevent query output data from wrapping in the flatfile?
-thanks
-tom
Hello Folks,
Im trying to use Integrations services to import a file on a daily basis.
My flatfile has to different type of rows that comes from an transaction system, it looks like this:
132,1/1/2008,00,123654,text,1,123.00
132,1/1/2008,00,123652,text,1,23.00
132,1/1/2008,00,123655,text,1,3.00
123,1/1/1/2008,01,149.00
1125,1/1/2008,00,123654,text,1,123.00
1125,1/1/2008,00,123652,text,1,23.00
1125,1/1/2008,00,123655,text,1,3.00
1125,1/1/1/2008,01,149.00;Cash;EUR;01;12
After the date you can see that there is two digits number either 00 or 01. The rows also have a different lengthts.
When ever that columns contains 00 the line should be inserted to a special text file, if the columns contains 01 it should to another file.
How can I solve this in a good way?
One of the problems I have is that when I try to import the rows the flat file connections indicates(erros message) that I have partial row in the file which is true since the the rows with the columns content 01 have more fields then the other.
Thanks for you help.
holtis
Why does the raw file have an option for a variable path and the flat file destination does not? Not having this feature makes it impossible to work with variable environments. Please add this option to the Flatfile Destination.
View 5 Replies View RelatedHi:
Am trying to write SQL data to multiple flat files.
I use a For Each loop,
store field Values in variables
construct a fileName(variable as expression) for each row,
then create a text file for each row in resultset, (Filesystem task)
and then try to fill each file from a SQL Source to a Flatfile Destination.
Destination.connectionString=Filename.
Works ok for creation of Text Files, so I know my fileName variable is getting evaluated for each iteration.
But the flat file connection manager is stuck and evaluates to the static part of my expression.
What am I doing wrong?
TIA
Kar
We are storing incoming flatfiles into a text field in a table and then we are processing this table on a regular basis. What I would like to do is to get this flatfile from the textfield and populate a flatfile source with it, but so far I have only been able to do that with XML files as there are no option for doing that with the flatfile source. Using the disk as a temporary storage for the flatfile is prohibited.
Does anyone have any suggestions on how to solve this?
The first is the
With every keystroke in the flat file connection manager editor on the filename
the bids system goes out and trys to find the file
this is stupidly slow when using \sqldevelopc$zipcode.txt
The second is creating an import from a flat file to database file using data flow task
when you run the task I get the following error trying to open a c:demozipcode.txt file
[AdventureWorks [30]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'GlobalDTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".
I can see there is an issue with BIDS that when you developing it is using the local drive C drive and running it using the servers C drive
I have tried unc paths also and it doesnt work either
Everything in the connection looks fine, i can see the data, the columns etc
I can get this to work using bulk load task and the exact same connection, but not in the data flow task