Month: July 2017

How to turn a list of (e.g.) countries into a concatenated string (e.g. France, New Zealand, California)

There’s a comprehensive post about different methods at https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/#Toc205129485 : the method I’ve used is “Scalar UDF with t-SQL update extension”

ALTER function [dbo].[GetCountriesOfOrigin](@productId int) returns 
varchar(max)
as
begin
 
 declare @result varchar(max) 
 set @result = '' 
 select @result = @result + c.CountryOfOrigin + ', ' from tblProductCountry pc 
 inner join tblCountryOfOrigin c on pc.CountryOfOriginID=c.CountryOfOriginID 
 where pc.ProductID=@productId 
 order by c.CountryOfOrigin
 set @result = ltrim(rtrim(@result))
 if right(@result,1)=',' set @result=left(@result, len(@result)-1) --damn LEN! ignores trailing spaces
 if len(@result)=0 set @result=null
 return @result
end

This sql script will add system versioning to all your tables in your existing SQL 2016 database.
For example table [Stuff] will have a [StuffHistory] table where all old values are logged.
As we have existing data, we have to do each table in four SQL statements and I don’t think we can make the columns HIDDEN.

/*
 Set all tables in this database as SYSTEM VERSIONED TEMPORAL TABLES
 Version 1.1
 Nick Ajderian
 
Enable data audit and tracking


References:
 https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table
 
Convert existing table to temporal table


*/

/*
 create table stuff6(stuffid int identity primary key, body nvarchar(200))
 update stuff6 set body=body+'x'
 insert into stuff6(body) values('hello ')

select * from stuff6
 select * from stuff6history

*/
 declare @sql1 nvarchar(1024),@sql3 nvarchar(1024),@sql4 nvarchar(1024), @sql2 nvarchar(1024)

declare @sqlx nvarchar(1024)
 set @sql1='ALTER TABLE TABLE_NAME ADD
 SysStartTime datetime2(0) not null DEFAULT SYSUTCDATETIME()
 , SysEndTime datetime2(0) not null DEFAULT CONVERT(datetime2 (0), ''9999-12-31 23:59:59.99999999'') '
 set @sql2='UPDATE TABLE_NAME SET SysStartTime = ''19000101 00:00:00.0000000'', SysEndTime = ''99991231 23:59:59.9999999'' '
 set @sql3='ALTER TABLE TABLE_NAME ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);'
 set @sql4='ALTER TABLE TABLE_NAME SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TABLE_NAMEHistory))'

--GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_SysStart
 --GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_SysEnd

declare @tn nvarchar(200)
 declare c cursor for
 select name from sys.tables
 where not
 (
 name like '%History'
 )
 and not exists(
 select * from sys.tables tt where tt.name=sys.tables.name+'history'
 )
 and name not in ('temp_NHSPatients','Diagnosis','EinsteinData','tbl_DiagnosisImport','tbl_OverwriteCCGs_KH','tbl_OverwriteCCGs_KH2')
 order by name
 open c
 fetch next from c into @tn
 while @@FETCH_STATUS=0
 begin
 print 'Adding temporal fields to ' + @tn
 set @sqlx = replace(@sql1,'TABLE_NAME', @tn)
 exec (@sqlx)
 set @sqlx = replace(@sql2,'TABLE_NAME', @tn)
 exec (@sqlx)
 set @sqlx = replace(@sql3,'TABLE_NAME', @tn)
 exec (@sqlx)
 set @sqlx = replace(@sql4,'TABLE_NAME', @tn)
 exec (@sqlx)

fetch next from c into @tn

end

close c

deallocate c