Merge Same Structure Text Files with Powershell

$dir = "C:\test\myfolder"
$outFile = Join-Path $dir "FileCombined.txt" 
$fileList = Get-ChildItem -Path $dir\* -Include *.txt -Exclude FileCombined.txt -File
 Get-Content $fileList[0] | select -First 2 | Out-File -FilePath $outfile -Encoding ascii
 
 foreach ($file in $filelist)
{
    Get-Content $file | Select-Object -Skip 2 | Out-File -FilePath $outfile -Encoding ascii -Append
}




--Copy and rename file with appended timestamp

gci -Path 'c:\temp\test\src\' | % {copy $_.FullName "c:\temp\test\dest\$($_.BaseName+$(Get-Date -f yyy-MM-dd-hh-mm)+$_.Extension)"}  
 

http://powershell.com/cs/forums/p/20268/43748.aspx#43748

Split QueryString Parameter Value Pair and Assign to Variables with T-SQL XML

--

declare @test table (id int, Col  varchar(2000))
Insert into @test values
(1, 'teststring1=testdata1;teststring2=testdata2;teststring3=testdata3;')

DECLARE @teststring1 as VARCHAR(10)=null
DECLARE @teststring2 as VARCHAR(10)=null
DECLARE @teststring3 as VARCHAR(10)=null
DECLARE @teststring4 as VARCHAR(500)=null
DECLARE @teststring5 as VARCHAR(500)=null

;With mycte as (
SELECT id, Col,  Cast (N'<H><r ' + Replace(Replace(Replace(Col, ';','" '),'=','="')  + '" /></H>' ,'" "','"' ) AS XML) AS [vals]
FROM   @test
)
,mycte1 as (
SELECT id, ROW_NUMBER() OVER (ORDER BY S.a.value('count(.)', 'tinyint')) rn,
S.a.value('@teststring1', 'varchar(50)') as teststring1,
S.a.value('@teststring2', 'varchar(50)') as teststring2,
S.a.value('@teststring3', 'varchar(50)') as teststring3,
S.a.value('@teststring4', 'varchar(50)') as teststring4,
S.a.value('@teststring5', 'varchar(50)') as teststring5  

FROM mycte d CROSS APPLY d.[vals].nodes('/H/r') S(a)  )
 --select * from mycte1
SELECT
@teststring1=teststring1,
@teststring2=teststring2,
@teststring3=teststring3,
@teststring4=teststring4,
@teststring5=teststring5

from mycte1 WHERE id =1

Print '@teststring1 value ---' + @teststring1;
Print '@teststring2 value ---' + @teststring2;
Print '@teststring3 value ---' + @teststring3;
Print '@teststring4 value ---' + @teststring4;
Print '@teststring5 value ---' + @teststring5;

 

Compute 10! using Recursive WITH clause

-- 
; with refc as (

select 1 as n, 1 as running_factorial
union all
select r.n+1, running_factorial*b.num from refc r 
inner join  (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) b(num)  on r.n+1=b.num )

 
select n, running_factorial from refc


--******RunningProduct

SELECT n, ROUND(EXP(SUM(LOG(n)) OVER  (ORDER BY n)), 0)  as runningProduct
FROM (Values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) d(n)

--*******This one is clower
 

Custom Date and Time formats — a sample and link

select datename(WEEKDAY, current_timestamp) WeekdayName
--SQL Server 2012 or 2014
,FORMAT(current_timestamp,'dddd') AS WeekdayName1
,FORMAT(current_timestamp,'ddd') AS ShortWeekdayName2

, FORMAT(getdate(), 'dd-MMM-yy hh.mm.ss.fffffff tt')

,format(getdate(),'HHmmssfff')

http://msdn.microsoft.com/en-us/library/ee634398.aspx

http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx

Nested Ranking function to Get an Answer

create table #Data
(
  Date date,
  Balance money
)

insert into #Data values('2014-01-01', 1000)
insert into #Data values('2014-01-02', 1000)
insert into #Data values('2014-01-03', 2000)
insert into #Data values('2014-01-04', 2000)
insert into #Data values('2014-01-05', 1000)
insert into #Data values('2014-01-06', 2000)
insert into #Data values('2014-01-07', 500)


select Date, Balance,RN, dense_rank() over (partition by RNS order by date) Seq, RNS 
from ( select *,  row_number() over (partition by Balance, rn2 order by date) RNS 
from ( select *,  row_number() over ( order by date) RN
 , row_number() over ( order by date) - row_number() over (partition by  balance order by date) rn2  
 from #Data) t1   ) t2
Order by [Date]

 

drop table #Data

/*
Date	Balance	RN	Seq	RNS
2014-01-01	1000.00	1	1	1
2014-01-02	1000.00	2	1	2
2014-01-03	2000.00	3	2	1
2014-01-04	2000.00	4	2	2
2014-01-05	1000.00	5	3	1
2014-01-06	2000.00	6	4	1
2014-01-07	500.00	7	5	1
*/


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b39cc65a-a83b-4302-8288-a0ceaa0f637b/detecting-change-in-values-of-a-column?forum=transactsql

Mark a Date as the Start or End of Week, Month, Quarter and Year

create table MyDates(MyDate  date, MyFlag  Varchar(10) )
insert MyDates(MyDate) Values
('2014-01-01'),   -- ys
('2014-01-31'),   -- me
('2014-04-01'),   -- ms, qs
('2014-03-31'),   -- me, qe
('2014-01-06'),   -- ws
('2014-01-12'),   -- we
('2014-12-31'),  -- ye
('2014-01-04');  -- nothing special, so NULL


SELECT MyDate, Reverse(Stuff(Reverse(
 Case WHen Datepart(weekday, MyDate)=2 Then'ws,' 
 WHEN Datepart(weekday, MyDate)=1 Then'we,' Else'' End
  + 
 CASE When Datediff(day,DATEADD(mm, DATEDIFF(mm, 0, MyDate) , 0), MyDate )=0 Then'ms,' 
 When Datediff(day,DATEADD(mm, DATEDIFF(mm, 0, MyDate)+1 , 0), cast(MyDate as datetime)+1 )=0 Then'me,'
 Else'' End
 + 
 CASE When Datediff(day,DATEADD(Q, DATEDIFF(Q, 0, MyDate) , 0), MyDate )=0 Then'qs,' 
 When Datediff(day,DATEADD(Q, DATEDIFF(Q, 0, MyDate)+1 , 0), cast(MyDate as datetime)+1 )=0 Then'qe,' Else'' End 
   +
 Case When Datediff(day, DATEADD(yy, DATEDIFF(yy,0,MyDate),0), MyDate )=0 Then'ys,'
  When Datediff(day, DATEADD(yy, DATEDIFF(yy,0,MyDate)+1,0),  cast(MyDate as datetime)+1 )=0 Then'ye,' Else'' End  
 ),1,1,'')) as MyFlag 
  
FROM  MyDates
 
Drop Table MyDates;


 



 

Create a Table Daily From Another Table with Date Appended to Table Name

DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @TableName AS NVARCHAR(500)='dbo.test1'+ convert(varchar(10),getdate(),112)
DECLARE @SQLQuery2 AS NVARCHAR(500)
SET @SQLQuery2 ='IF OBJECT_ID ('''+@TableName+ ''',N''U'') IS NOT NULL drop table ' +@TableName

EXEC (@SQLQuery2 )


SET @SQLQuery = 'SELECT * into ' + @TableName + ' FROM  dbo.test1' 

EXEC(@SQLQuery)

EXEC ('select * from '+ @TableName )


 

Generate Update Script for a Table

  select 'UPDATE yourtable SET '+ STUFF  (( select ',' + quotename(name) + ' = isnull(' + quotename(name) + ', 0)'
               FROM   sys.columns
WHERE  object_id = object_id('yourtable')
  AND  type_name(system_type_id) LIKE '%int'
               FOR XML PATH('')), 1, 1, '') AS yourquery




-- Or use a cursor solution
DECLARE @TABLE_NAME  NVARCHAR(50) = 'AMaster',
        @TABLE_SCHEMA NVARCHAR(50) = 'dbo',
        @sql          NVARCHAR(4000),
        @col          NVARCHAR(50)
 
DECLARE c_cursor CURSOR FOR
  SELECT column_Name
  FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE  TABLE_NAME = @TABLE_NAME
         AND TABLE_SCHEMA = @TABLE_SCHEMA
         AND IS_Nullable = 'YES'

OPEN c_cursor;
FETCH NEXT FROM c_cursor INTO @col;
WHILE ( @@FETCH_STATUS = 0 )
  BEGIN
      SET @sql = N' UPDATE  ' + Quotename(@TABLE_SCHEMA) + '.'  + Quotename( @TABLE_NAME) +' SET ' + @col +' = 0 WHERE ' + @col + ' Is Null'        
                
      print @sql
    --  EXEC (@sql);
      FETCH NEXT FROM c_cursor INTO @col; 
  END
CLOSE c_cursor;
DEALLOCATE c_cursor;


 

Two samples with dm_exec_query_plan

http://www.sqlservercentral.com/Forums/Topic1306043-21-1.aspx
https://social.msdn.microsoft.com/Forums/en-US/833653ec-cb22-4cc9-93ed-42de01e3d298/statementtext-from-showplanxml-only-outputs-4000-characters?forum=transactsql

The device_type 9 in backupmediafamily —Backup to URL (Azure storage)

The device_type 9 in backupmediafamily — Azure storage

I have found out the device_type 9 is for onlince backup to URL (Azure storage) but it is not documented.
You can check physical_device_name starts with http: https://myAzurestorage.blob.core.windows.net/myservername/myDB_3bc8ab8d4f8540acbfd7da39fc575b2e_20141203131113-06.bak

http://msdn.microsoft.com/en-us/library/ms190284(v=sql.110).aspx