Wednesday, August 7, 2013

[T-SQL] Adding special characters

[T-SQL] Adding special characters

Adding special characters

Posted: 07 Aug 2013 12:48 AM PDT

Hi,I have a scenario in which I have to find a column with varchar or nvarchar datatype in all tables of a database and update the value of those columns with special characters like '!@#$%^&*()'.Here is the DDL script : [code="sql"]CREATE TABLE table1( ID INT IDENTITY, Name1 VARCHAR(50))GOCREATE TABLE table2( ID INT IDENTITY, Name2 VARCHAR(50))GOCREATE TABLE table3( ID INT IDENTITY, Name3 VARCHAR(50))INSERT INTO table1SELECT 'a'UNION ALLSELECT 'b'GOINSERT INTO table2SELECT 'c'UNION ALLSELECT 'd'GOINSERT INTO TABLE3SELECT 'e'UNION ALLSELECT 'f'[/code]Expected Output:table1 '!@#$%^&*()a'table1 '!@#$%^&*()b'table2 '!@#$%^&*()c'table2 '!@#$%^&*()d'table3 '!@#$%^&*()e'table3 '!@#$%^&*()f'I just have to add these special characters in front of columnvalues whose datatype is varchar or nvarchar..

Splitting Comma Separated Values into Rows

Posted: 06 Aug 2013 04:25 PM PDT

[code="sql"]DECLARE @t Table( AreaID int, AreaName nvarchar(100), Responsible nvarchar(100))Insert Into @tSelect 1, 'Finance',NullUnion AllSelect 2, 'IT','Internal, External'Union AllSelect 3, 'Audit, Security', 'Internal'Union AllSelect 4, 'Health, Safety, Compliance', 'Internal, External' Select * From @t/* I want the results like the following without having to use any splitter udf AreaID AreaName Responsible ------ ------------- --------------- 1 Finance Null 2 IT Internal 2 IT External 3 Audit Internal 3 Security Internal 4 Health Internal 4 Health External 4 Safety Internal 4 Safety External 4 Compliance Internal 4 Compliance External*/[/code]

Need help with TSQL

Posted: 06 Aug 2013 11:37 PM PDT

Hi,I have table structure as shown in the below image. I have attached script for the table with sample data.There is parent child relation between Time_Id and Parent_Id columns. [img]http://www.sqlservercentral.com/Forums/Attachment14139.aspx[/img]I want output rows in the following order. Basically all the root nodes(with NULL parent_id) should be in the order of SortOrder column. But at the same time if root has childs, all the childs should immediately appear below the root in the sorted order.[img]http://www.sqlservercentral.com/Forums/Attachment14140.aspx[/img]Can you please help ?Thanks.

Splitting Comma Separated Values into Rows

Posted: 06 Aug 2013 04:28 PM PDT

I want to split the Comma Separated Values into rows[code="sql"]DECLARE @t Table( AreaID int, AreaName nvarchar(100), Responsible nvarchar(100))Insert Into @tSelect 1, 'Finance',NullUnion AllSelect 2, 'IT','Internal, External'Union AllSelect 3, 'Audit, Security', 'Internal'Union AllSelect 4, 'Health, Safety, Compliance', 'Internal, External' Select * From @t/* I want the results like the following without having to use any splitter udf AreaID AreaName Responsible ------ ------------- --------------- 1 Finance Null 2 IT Internal 2 IT External 3 Audit Internal 3 Security Internal 4 Health Internal 4 Health External 4 Safety Internal 4 Safety External 4 Compliance Internal 4 Compliance External*/[/code][b]Reason: Not allowed to build custom function on a proprietary database[/b]Any help please?

Trying to select the last 3 months of data

Posted: 06 Aug 2013 06:50 AM PDT

I am trying to create a date, that I can put in a where statement to filter for the last 3 months of data... but not use today's date but the latest date in the table.I was thinking along these lines.. but SQL errors out:;with base as(select post_dm from ztb_forecastable_metrics_hist group by Post_DM)set @lastdate = (select MAX(post_dm) as Max_DM from base)

DML with linked servers is so slow!!!

Posted: 06 Aug 2013 08:19 AM PDT

Grettings everybody, I looking for the one who can solve this issue!I need to save data from Server1 to Server2, this data is created from a stored procedure because It is the result from other tables and this data must to travel to a Publicator for future Replication.The problem is, when Server1 updates the table on Server2, is too slow. I do something like this: insert into [Server1].[Base].dbo.table select * from #tablebut if I do -> insert into table select * from #table. It is so fast! I testing this process on my laptop but I need use two servers.I read this is why SQL Server scans the table from linked server. Now, I need to know if exists a way to do this without this scan!Thanks!PD: Sorry if my english was bad! :(

Using MERGE to perform the INSERT?

Posted: 06 Aug 2013 02:06 AM PDT

Hi,Please see the below sample data:[code="sql"]create table TargetTable (Uarn INT, BACode INT, VO_Ref INT, From_Date datetime, To_Date datetime)insert into TargetTable (Uarn, BACode, VO_Ref, From_Date, To_Date)select 906900, 1935, 121067, '2010-04-01', NULL UNION ALLselect 946003, 5249, 121041, '2012-08-06', NULLcreate table SourceTable (Uarn INT, BACode INT, VO_Ref INT, From_Date datetime, To_Date datetime)insert into SourceTable (Uarn, BACode, VO_Ref, From_Date, To_Date)select 906900, 1935, 112067, '2013-05-12', NULL UNION ALLselect 946003, 5249, 199041, '2013-01-01', NULLcreate table ResultTable (Uarn INT, BACode INT, VO_Ref INT, From_Date datetime, To_Date datetime)insert into ResultTable (Uarn, BACode, VO_Ref, From_Date, To_Date)select 906900, 1935, 121067, '2010-04-01', '2013-05-11' UNION ALLselect 946003, 5249, 121041, '2012-08-06', '2012-12-31' UNION ALLselect 906900, 1935, 112067, '2013-05-12', NULL UNION ALLselect 946003, 5249, 199041, '2013-01-01', NULLselect * from TargetTableselect * from sourcetableselect * from ResultTable order by 1[/code]Is it possible to achieve this with the MERGE statement? So far my efforts have failed!Thanks in advance.

Is possible create numbered sequences

Posted: 06 Aug 2013 03:18 AM PDT

ROW_ID12233344445555566666677777778888888899999999910101010101010101010111212131313Up to 10000Is possible create numbered sequences like the ROW_ID above, where the row numbers repeat themselves up to 10000 into an empty column? In SQL serverThanks

Touchdowns & Missing Threads

Posted: 06 Aug 2013 08:44 AM PDT

It seems that a thread that requested help in determining the name of a quarterback, the max number of touchdowns thrown and year it happened was inadvertently deleted during a purge of lots of spam. I have part of the OP, DDL and data, and my reply. Steve Jones, SSC Editor, asked if I would repost so that the OP, and others, could see it, contribute, etc...[code]CREATE TABLE player_goals (Name varchar(50), Year int, Touchdowns int);INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Drew Brees', 2007, 29);INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Drew Brees', 2008, 25);INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Drew Brees', 2009, 20);INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Steve Young', 2007, 19);INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Steve Young', 2007, 38);INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Steve Young', 2009, 44);[/code]My reply[quote]Try this and see if it works:[code];with cte as( select Year, Name, touchdowns, ROW_NUMBER() over(PARTITION by name order by touchdowns desc) RowNum from player_goals)select Year, Name, touchdowns from cte where RowNum = 1[/code][/quote]

Pivot on two columns

Posted: 06 Aug 2013 07:49 AM PDT

I have a dataset similar to the following:Account Date Amt1111 20090228 200.001111 20090328 175.001111 20090428 250.001111 20090528 210.002222 20120115 100.002222 20120213 150.003333 20110605 300.003333 20110705 300.003333 20110805 300.00I am trying to pivot on the date and the amount columns. The final output would look like this:Account Date1 Amt1 Date2 Amt2 Date3 Amt3 Date4 Amt41111 20090228 200.00 20090328 175.00 20090428 250.00 20090528 210.002222 20120115 100.00 20120213 150.003333 20110605 300.00 20110705 300.00 20110805 300.00 I've attempted doing this using a CTE to add row numbers and then pivoting on the row numbers, which works well when just pivoting one column, but I have not been able to do this to pivot both columns out.I think that it may be able to be done using two subsets or the original dataset and some sort of row numbering, but my mind is twisted in a knot thinking about it. Any help would be greatly appreciated.-Jeremy

Query Help - Embed a \ string in a string

Posted: 06 Aug 2013 06:50 AM PDT

I'm trying to modify an existing stored proc. This seems like of like a weird way to do it, but based on input parameters they are basically constructing a script string in the stored proc and then running it.[code]if @Interval = 'Monthly' Begin set @groupclause = ' Group by MONTH(ContractDate), YEAR(ContractDate) ' set @sql = 'select (CAST(MONTH(ContractDate) as varchar(2)) + CAST(YEAR(ContractDate) as varchar(4))) as Date_Marker, ROUND(sum(' + @field + '),2) ' + @fieldname + ' ' + ' From Reporting.dbo.PrgSum ' set @orderclause = ' ORDER BY YEAR(ContractDate), MONTH(ContractDate)' End...declare @bigquery nvarchar(max)=@sql+@whereclause+@groupclause+@orderclause exec sp_executesql @bigquery[/code]I'm trying to figure out how to insert a "/" between the Month and Year values:[Code]set @sql = 'select (CAST(MONTH(ContractDate) as varchar(2)) + CAST(YEAR(ContractDate) as varchar(4))) as Date_Marker, ROUND(sum(' + @field + '),2) ' + @fieldname + ' ' + ' From Reporting.dbo.PrgSum 'r[/CODE]

Execute a function from a query

Posted: 06 Aug 2013 06:09 AM PDT

Hi,I have this function that is used to fill a SQL table - which is then used for a data export.[code="sql"]ALTER FUNCTION [dbo].[itfAPVEND]( -- Add the parameters for the function here)RETURNS @APVEND TABLE ([CO] [char](5) NOT NULL,[A] [char](12) NOT NULL, [B] [char](60) NULL, [C] [char](30) NULL, [D] [char](30) NULL, [E] [char](30) NULL, [F] [char](30) NULL, [G] [char](20) NULL, [H] [char](20) NULL, [I] [char](20) NULL, [J] [char](20) NULL, [K] [text] NULL, [L] [char](15) NULL)ASBEGIN insert into @APVENDselect 'WSIAP', * fromopenquery(WSIAP, 'select VendorKey, Vendorname, ContactName, Vendoraddress1, Vendoraddress2, Vendoraddress3, VendorZipCode, ContactPhone, AttnPhone, Faxnumber, emailaddress, termskey from APVEND') insert into @APVENDselect 'ASSFD', * fromopenquery(ASSFD, 'select VendorKey, Vendorname, ContactName, Vendoraddress1, Vendoraddress2, Vendoraddress3, VendorZipCode, ContactPhone, AttnPhone, Faxnumber, emailaddress, termskey from APVEND') RETURN; END[/code]Problem is - I now need to execute this function every night. What is the best way to accomplish this? I was thinking of using a SQL job and call it from a query. BUt I can't seem to get the function to execute from a query. How would I execute this function from a query?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog