T-SQL (SS2K8) :: Replace Column With Another Column When It Does Not Exist
Jun 24, 2014
I have a script that loops through a series of tables to send data to a table from each of the tables. My issue is that not all tables have the columns I need in them. What I would like is to replace the column with another column when it does not exist. Something like below
Select Misisng_Column(A.Name, replace with B.Name) as Name
FROM SomeTable A
Cross Join (Select Name FROM AnotherTable) B
AnotherTable has one record in it. To avoid a Cartesian issue. Like I said just an example
In my real script the table aliased as A is from a list of tables in a sys.tables query that loops through to the end.
View 2 Replies
ADVERTISEMENT
Jun 21, 2012
I am trying to replace all special characters in a column with one special character.
Example:
Table: dbo.Employee
Column: Name
Name
-------
edwardneuman!"<]
mikemoreno)'>$:
JeffJensen"?>"
I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.
I am expecting the following results:
Name
-------
edwardneuman<<<<
mikemoreno<<<<<
JeffJensen<<<<
View 9 Replies
View Related
Sep 11, 2007
Posted - 09/10/2007 : 15:53:26
Hey all - got a problem that seems like it would be simple (and probably is : )
I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.
Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("CM") = Month(Now)
DTSDestination("CY") = Year(Now)
DTSDestination("Comments") = DTSSource("Col031")
DTSDestination("Manufacturer") = DTSSource("Col030")
DTSDestination("Model") = DTSSource("Col029")
DTSDestination("Last Check-in Date") = DTSSource("Col028")
Main = DTSTransformStat_OK
End Function
***********************************************************
Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.
thanks in advance!
jm
View 1 Replies
View Related
May 7, 2007
I am using a SQL command in ASP.NET to send a query to a an OLAP cube that returns a dynamic set of data that I load into a datatable and then bind to a GridView. I have made my own ITemplate implementaton for displaying and formatting the data, and the following line is causing me problems:
RawValue = DataBinder.Eval(row.DataItem, "[Month].[Month].[MEMBER_CAPTION]")
The error returned is:
Month is neither a DataColumn nor a DataRelation for table
My guess as to what is happening is that it sees the brackets in the field name and stops reading the field name at [Month], when the actual field name is much longer. I know that it knows about the column name because it displays it correctly in the header. Since I am loading this from an OLAP cube the names of my columns vary based on the criteria so I cannot alias the column because I don't know exactly which columns will be displayed. Does anyone know how I might get the DataBinder.Eval function to work with fields that contain square brackets [ and ] ?
If I use the GridView's "auto-generate fields" option it will show the data (and this is the column name) but I lose all control over formatting and the other custom code I'm writing in my ITemplate interface.
Thanks
View 4 Replies
View Related
Sep 30, 2014
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
View 2 Replies
View Related
Sep 12, 2007
Hello Everyone,
I'm a newbie to SSIS. While experimenting with it I've encountered an issue which I'm hoping someone of you could help me out with. I have need to make a specific transformation which as output would have to produce rows with a new calculated column that replaces single column from input. New column has different data type than input column it is replacing. I've used Derived Column Transformation (DER) to do the first part of the work - appending new column and calculating its value (based solely on value from single original column that has to be replaced). Question is how should I do second part, task of removing no longer needed column from the pipeline? I've tried in DER instead of Derived Column being added as new column, selecting Replace 'column' but as it seems it is meant to replace only column data and not column data type (what I've expected). I've also tried using Copy Column Transformation (CPYC) but as it turns out CPYC transformation just (logically) duplicates data in the pipeline with optional different allias.
View 1 Replies
View Related
Aug 20, 2014
I have a table with Million plus records. Due to Running Totals article, I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.
For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46
I am trying to do some type of self join, but not sure how to include each actindx number differently.
Table creation and data insert is below.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TrialBalance](
[Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
[Code] ....
View 7 Replies
View Related
Aug 14, 2012
I have two tables with the following structure:
Table 1
---------
id | Name
---------
1 | John
2 | Debbie
3 | Kim
4 | Mary
Table 2
---------
id | Name
---------
654 | John
415 | Debbie
68 | Kim
289 | Mary
Table 2 id's values are all messed up and I need to replace them so that they look exactly like in Table one, my guess is that query will have to replace value of "Id" based on the "Name" column, I hope I made myself clear.
View 6 Replies
View Related
Nov 12, 2007
Hi,
I am trying to replace a string column with 'Y' or 'N' in my SSRS report. I tried the following expressions, but no one works for me.
=IIf(Fields!ExpectedWords.Value Is Null, 'Y', 'N')
=IIf(Fields!ExpectedWords.Value = '', 'Y', 'N')
=IIf(IsDBNull(Fields!ExpectedWords.Value), 'Y', 'N')
Would you please tell me what's wrong? Your suggestions would be appreciated.
Thanks alot.
View 3 Replies
View Related
Jul 28, 2006
I've searched through this forum, and am extremely disappointed that something as simple as this can't be done without resorting to a script component (which I refuse to do):
I have a Lookup transformation that returns a GUID value -- or not, so I've configured its error output to ignore failure. That should leave a NULL in the output column, and I should be able to chain a Derived Column transformation that replaces the Lookup output column (e.g. foo) with an expression like:
isnull(foo) ? newid() : foo
But there's no newid() function! Who was the genius that decided SSIS should implement different expressions than Transact SQL? Is there a workaround, like (DT_GUID)GETDATE()?
Or do I have to craft a SQL statement in the Lookup that guarantees a non-NULL result?
View 11 Replies
View Related
Mar 12, 2005
Table : Department
Col :
Department ID
Name
Description
CREATE PROCEDURE GetDepatmentID AS
SELECT Department ID, Name
FROM Department
RETURN
Trying to create strored procedure and get the error that "Department" column does not exist.
Please know verily new to this so have patience
View 1 Replies
View Related
Jan 22, 2007
Do you know of some SQL that I can test a table to see if a column exists or not inside a stored procedure?
What I have is a table that contains data for a 10 year history. I asked at the time and was told at the beginning of every new year the table would create 2 new columns so I dynamically set up some of my stored procedure to provide for that….well as of Jan 22 they have not updated them so I have 2 options:
1) hard code the first year in there and change it when they tell us
2) test for the field if it is there start the countdown to grab the rest of the historic info….if not check for the next year until it finds the field in the DB.
I’d rather do 2.
Thanks for any help.
View 2 Replies
View Related
Mar 26, 2015
I have an update where I need to replace the first two numbers of a string with SF and remove the last number - e.g.:
029929229 >> SF992922
I have an update statement which works in Oracle, but I cannot get it to work in SQL Server. The statement is below:
UPDATE oraowner.ems_sam
SET sam_id = replace(sam_id,sam_id,'SF'||substr(sam_id, 3, length(sam_id) - 3));
View 3 Replies
View Related
Mar 27, 2008
How do I improve a 3 column, composite clustered index on a large table when the developer insists there is no other way to achieve uniqueness? They say a uniqueindentifier column will not work.
View 5 Replies
View Related
Feb 29, 2008
Hi, I was wondering if it is posible as in derived columns to replace an input column with the output one in Data Conversion?
What I can see that when a data conversion is done, only we can do it in a new column and not in the same one we are using as input.
Do you know if this it is posible?}
Thanks for your help!
Beli
View 3 Replies
View Related
Jul 18, 2007
Hi,
I am getting message while modifying one table in SSMS like "Table '?' could not be loaded. Column '?' does not exist" . Howevery particular column exist in table and even i am able to modify it with T-SQL.
Any Idea?
View 2 Replies
View Related
Jul 31, 2007
Hi
How do i remove Carriage return in a varchar column?
Thanks
View 4 Replies
View Related
Apr 14, 2004
What is the correct syntax to replace a field data nvarchar(50)
Current data = 0020-10-02
Change = 2003-10-02
Thank you in advance.
View 8 Replies
View Related
May 14, 2007
How do you replace quotes in an expression
I mean for example if I needed to replace xx in a string with empty string then the following works: REPLACE(SelectedString, "xx","")
But the example I have needs to actually replace quote marks in a string with an empty string and REPLACE(SelectedString, " " ","") doesn't work. I tried guessing a few option like "E or &QTE or something...
Any ideas ?
Thanks
Richard
View 1 Replies
View Related
Jun 2, 2006
Hi,
We have a dataflow task that imports data from excel to a sql2005 database table. One of the columns is never filled in in the excel source. For updates we can use the lookup transformation to fill in that column, but for new values we need to calculate a new value for it (it's a PK) with MAX(column) +1 and replace the null value in the dataflow with this new value.
Just to be clear:
column id (int)
column name (string)
Based on the 'name' column, we can look up existing ids, and update these in the table. Ids that don't exist yet need to be filled in with the maximum value of the column + 1 (we can't use identity columns) and inserted in the table
Which transformation do I use to replace the value of the id column with this new id?
thanks,
Stephane
View 3 Replies
View Related
Sep 27, 2007
I have a SQL Server 2005 database that has a table with a TEXT column. This TEXT column has XML data in it. The length of the XML data in each record in the table is about 700,000 characters. What is the quickest most efficient method to replace a nodes text with another value? I.E., <LogoLarge>aasdfasdfaasadfasdfsdfasdfadsf</LogoLarge> with <LogoLarge>a</LogoLarge>. This table has about 2 million records. Thanks in advance for your help.
View 2 Replies
View Related
Jan 6, 2004
Source server MSSQL7.0
Destination server MSSQL2K
I'm setting up replication for reporting purposes to another SQL-server.
When I start the distribution agent I get the following error:
Column name '<here's the name of a column I didn't publish and don't need to replicate>' does not exist in the target table.
Error number: 1911
I assume this is because of some dependencies between the columns.
Is there any way I can replicate without those columns?
Regards
View 2 Replies
View Related
Jul 3, 2014
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
View 3 Replies
View Related
Mar 22, 2007
Hello All.
Hopefully someone out there will have an idea as this isdriving me nuts.
Ihave some sample problem. I want to use function replace() on Derived Column.
For example.
when strDate = 2007/03/22
I used ==> replace(strDate, "/", "") ==> 20060322
But If strTest = "123.10" ====>> 123.10
How can i do to replace ( " )double qoute ?
by function replace()
what is a statement for replace (") in Derived Column ?
please tell me for this event.
any suggesstion appreciated
Thank you very much.
Chonnathan
View 11 Replies
View Related
May 18, 2004
Hi,
I've a text column (text datatype) that contains carriage return and line feed.
Syntax-wise, how can I replace these by a space?
Thanks.
View 1 Replies
View Related
Apr 26, 2006
I'm importing a csv-file delimited with semicolons. Firstly I LTRIM the columns "in place" and the data imports fine. All the numbers in right columns in the target table. Then I add another Derived Colum Transformation to replace decimal character comma (,) to a dot (.) in order to convert the string/varchar value to numeric. But here I run into trouble. Running the task ends in success but the result in the target table (same as above) is not. All the commas are now dots as expected but what is worse is that SSIS have added values in cells that should not be there. I get values in cells that shoud be empty!
Shortly: Only LTRIM([Column1]) as expression and "Derived Column" as Replace 'Column1' works OK.
But adding REPLACE-expression (i.e REPLACE(LTRIM([Column1]) , "," , ".") to this breaks things up
I'm aware that I could do this with SQL but this is not the point...
Any ideas?
BR Jompe
View 6 Replies
View Related
Nov 7, 2006
Ok.. so I have a fixed position data feed. I read the file in as just whole rows initially, process a specific position and evaluate a conditional split to determine direction of the file for proper processing (file contains multiple recors with different layouts). This all works fine. I then use the derived column feature to process all the columns.
Most of the columns are as simple as SUBSTRING(RecordData,1,10) for example to get the first column. This all works for string data. I then have a column that is a date field. The problem occurs that the code SUBSTRING(RecordData,20,10) returns either a date or empty set of data if no date was in the original file. When this gets sent to the OLEDB connection (SQL Server 2005) into the date field it fails. If the record has a date it works, but if it is empty it fails the insert.
I tried to replace empty strings with NULLs with this code. REPLACE(TRIM(SUBSTRING(RecordData,20,10)),"",NULL(DT_WSTR,10)). This does not work. So my question is how do I bring a date field from a fixed flat file into a SQL datetime field using a derived column? More specifically is how do I set it to NULL if its empty data? When I use the above code it inserts all the rows from the file, but it sets all rows to NULL not just the empty ones.
Thanks.
View 6 Replies
View Related
Mar 27, 2008
Hi!
I recently was confronted with a problem where a piece of text that was included in many NTEXT column values in a table needed to be replaced with another piece of text. You can't issue normal REPLACE statements against NTEXT columns, so this seemed to be a bit of a challenge €” issuing a REPLACE() against a TEXT or NTEXT column in SQL Server yields error
I tried following
UPDATE CaseTypeDefs SET definition = replace(LTRIM(RTRIM(definition)), '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]>', '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]>')
But this is producing following error
Server: Msg 8116, Level 16, State 1, Line 1Argument data type ntext is invalid for argument 1 of replace function.
For Example: I want to replace string <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]> with <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]> in NTEXT column values in a table.
Need help, how to do it?.
Thanks In Advance
Devloper
Anil Kumar Dwivedi
View 4 Replies
View Related
Aug 8, 2007
hi All,
Following code generates error 107 (SQL Server 2000, SP3a):
SELECT
*
FROM
dbo.AssessmentItemHierarchy Parent
INNER JOIN dbo.RaterCategory
INNER JOIN dbo.RaterType
ON RaterCategory.ID = RaterType.RaterCategoryID
INNER JOIN dbo.AssessmentRater
ON AssessmentRater.RaterTypeID = RaterType.ID
INNER JOIN dbo.AssessmentItem
ON AssessmentItem.ID = Parent.ChildItemID
INNER JOIN dbo.Assessment
ON AssessmentItem.AssessmentID = Assessment.ID
INNER JOIN dbo.AssessmentResponse
ON AssessmentResponse.AssessmentItemID = AssessmentItem.ID
ON AssessmentResponse.AssessmentRaterID = AssessmentRater.ID
INNER JOIN dbo.ImageInstance
ON ImageInstance.ID = RaterType.ImageInstanceID
INNER JOIN dbo.AssessmentItem ParentGUID
ON Parent.ParentItemID = ParentGUID.ID
Error on Run:
Msg 107, Level 16, State 2, Line 2
The column prefix 'Parent' does not match with a table name or alias name used in the query.
The only work-around I have found is a rewrite of order:
SELECT
*
FROM
AssessmentItemHierarchy Parent
INNER JOIN RaterCategory
INNER JOIN RaterType
INNER JOIN AssessmentRater
INNER JOIN AssessmentItem
INNER JOIN Assessment ON AssessmentItem.AssessmentID = Assessment.ID
INNER JOIN AssessmentResponse ON AssessmentItem.ID = AssessmentResponse.AssessmentItemID
ON AssessmentRater.ID = AssessmentResponse.AssessmentRaterID
ON RaterType.ID = AssessmentRater.RaterTypeID
ON RaterCategory.ID = RaterType.RaterCategoryID
INNER JOIN ImageInstance
ON RaterType.ImageInstanceID = ImageInstance.ID
ON Parent.ChildItemID = AssessmentItem.ID
INNER JOIN AssessmentItem ParentGUID ON Parent.ParentItemID = ParentGUID.ID
Not very pretty... anyone know why I'm getting this error message with the first version of my query?
View 1 Replies
View Related
Jul 25, 2006
W2k3 server, SQL 2005.
@@version = Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Standard Edition on Windows NT 5.2
(Build 3790: Service Pack 1)
I have my first SSIS package almost working, but I'm having an odd problem and can't find any information to help resolve it.
I'm importing from a flat file (csv) to an existing table (append). I've got a Derived Column transformation in the middle to do some data cleanup. It's all working except for one little problem...
One of the transformations is 'REPLACE([Column 3],"^","; ")', output to a new column. (The input file has a field that uses carets as delimiters between an unknown number of items; I'm changing that to semicolons for easier reading.) Not all rows have data in this column, some will have one item, some will have multiple items.
The REPLACE works except that it fills in repeated data for all the blank rows.
Example:
Incoming data is:
1 Smith,Jane^Jones,Jane
2 Brown,John
3
4 Adams,James^Adams,Jim
5
6 White,Debra
Data inserted into the table is:
1 Smith,Jane; Jones,Jane
2 Brown,John
3 Brown,John
4 Adams,James; Adams,Jim
5 Adams,James; Adams,Jim
6 White,Debra
I've tried to use a Conditional to skip the empty rows, but I can't get that working at all (get syntax errors no matter what I put in).
Any suggestions on how to fix this would be most appreciated!
Thank you.
View 5 Replies
View Related
Jul 28, 2015
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
View 4 Replies
View Related
Feb 6, 2008
Hi guys,
I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.
similar to this SQL case statement
CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S2' THEN 'Other sale items'
ELSE 'Not for sale'
END,
the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.
thanks for any assistance you can provide.
regards,
Chris
View 1 Replies
View Related
Mar 4, 2014
Sample Data
MemberID Codes
00000123 012|222|123|333
00000233 012|222|332
00000244 012|211
00000332 012
I am trying to get it so as following:
MemberID Code1, Code2, Code3, Code4
I tried using the XML method but in working with an example, I actually got stuck on one of the declare fields and cant seem to work around it.
Failed Attempt:
DECLARE @x xml;
DECLARE @line VARCHAR(MAX);
SET @x = Cast(
'<field>'
+ replace(@line, '|', '</field><field>')
+ '</field>' AS XML);
[code]...
View 6 Replies
View Related
