Posted: March 26, 2019 | Author: Jingyang Li | Filed under: Uncategorized |
--sample table
create table test (FileName NVARCHAR(50), CreateDate DateTime, [File] Varbinary(MAX) )
--sample 1
--dynamic insert data (datetime)
Create PROCEDURE [dbo].[SetFilestreamData_datetime]
@TableName NVARCHAR(100)
, @FileName NVARCHAR(50)
, @CreatedDate DateTime
as
Begin
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO [dbo].' + quotename(@TableName) +
+ '([FileName], [CreateDate]) VALUES (@FileName, @CreatedDate)'
EXEC sp_executesql @sql, N'@FileName NVARCHAR(50), @CreatedDate DateTime'
,@FileName=@FileName, @CreatedDate =@CreatedDate
END
--execute sp
declare @CreatedDate DateTime = getdate()
EXEC [dbo].[SetFilestreamData_datetime] 'Test', 'Test1',@CreatedDate
--Sample 2
--dynamic insert data (binary)
Create PROCEDURE [dbo].[SetFilestreamData_varbinary]
@TableName NVARCHAR(100)
, @FileName NVARCHAR(50)
, @FileData Varbinary(MAX)
as
Begin
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO [dbo].' + quotename(@TableName) +
+ '([FileName], [File]) VALUES (@FileName, @FileData)'
EXEC sp_executesql @sql, N'@FileName NVARCHAR(50), @FileData Varbinary(MAX)'
,@FileName=@FileName, @FileData =@FileData
END
--execute sp
declare @FileData Varbinary(MAX)=Cast('aaa' as Varbinary(MAX))
EXEC [SetFilestreamData_varbinary] 'Test', 'testfilename',@FileData
Posted: March 13, 2019 | Author: Jingyang Li | Filed under: Uncategorized |
--*********begin of sample table and data
create table LZO_a (id int, MODIFIEDAT date)
insert into LZO_a values (1, '2019-03-01'),(2, '2019-03-02'),(3, '2019-03-03'),(4, '2019-03-04'),(5, '2019-03-05')
,(6, '2019-03-02'),(7, '2019-03-03'),(8, '2019-03-04'),(9, '2019-03-05'),(10, '2019-03-12'),(11, '2019-03-13'),(12, '2019-03-14')
create table LZO_b (id int, MODIFIEDAT date)
insert into LZO_b values (1, '2019-03-01'),(2, '2019-03-01'),(3, '2019-03-03'),(4, '2019-03-04'),(5, '2019-03-05')
,(6, '2019-03-03'),(7, '2019-03-03'),(8, '2019-03-04'),(9, '2019-03-05'),(10, '2019-03-05'),(11, '2019-03-05'),(12, '2019-03-14')
--************end of sample table and data
DECLARE @RunDate date = '2019-03-02' --change date parameter
DECLARE @sql1 nvarchar(4000)=N''
DECLARE @ColsPivot as NVarchar(4000)=null
Select @sql1= 'Select tname, cname , count(*) RowCnt into tmp_BUILDMODIFIED FROM ('+ Stuff((
SELECT ' UNION ALL '+ char(13) + char(10) + 'Select ''' + quotename(t.[TABLE_SCHEMA]) + '.' + quotename(t.[TABLE_NAME] )
+ ''' as tname, ' + quotename(c.COLUMN_NAME) +' as cname, MODIFIEDAT FROM '
+ quotename(t.[TABLE_SCHEMA]) + '.' + quotename(t.[TABLE_NAME]) + ' WHERE 1=1 and MODIFIEDAT >=''' + CONVERT(NVARCHAR(10),@RunDate)+''''
+ char(13) + char(10)
FROM [INFORMATION_SCHEMA].[TABLES] t join [INFORMATION_SCHEMA].[COLUMNS] c
on t.[TABLE_SCHEMA] =c.[TABLE_SCHEMA] and t.[TABLE_NAME]=c.[TABLE_NAME]
WHERE TABLE_TYPE='BASE TABLE' and [DATA_TYPE] Like '%date%' and t.[TABLE_NAME] like'LZO_%'
and t.[TABLE_NAME] NOT LIKE '%DJW%' AND t.[TABLE_NAME] NOT LIKE 'LZO_TRUST%'
and c.COLUMN_NAME='MODIFIEDAT'
FOR XML PATH(''), type ).value('.', 'varchar(max)'),1,10,'')
+ ' ) t group by tname,cname'
--create the temp table
EXEC sp_executesql @sql1
Select @ColsPivot =N' Select tname, '+STUFF( (SELECT distinct ',' + ' sum(CASE WHEN cname=' + quotename(cname,'''') + ' THEN RowCnt else 0 end ) as ' + quotename(cname,'[') + char(10)+char(13)
FROM tmp_BUILDMODIFIED
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
+ ' from tmp_BUILDMODIFIED group by tname '
--print @ColsPivot
EXEC sp_executesql @ColsPivot
--clean up
IF OBJECT_ID('dbo.tmp_BUILDMODIFIED','U') IS NOT NULL
DROP TABLE tmp_BUILDMODIFIED
--drop sample tables
drop table LZO_b,LZO_a
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/aa0c4f82-cfd6-4922-9f64-4985fedf8a4c/how-do-i-pivot-date-rows-into-columns?forum=transactsql