Create Insert script in sql

--sp_CreateInsertScript 'ntrp.tblreceiptpurposemaster','PurposeStatusId=556',1
CREATE  PROC  procCreateInsertScript (
    @tablename NVARCHAR(256) -- table name
    ,@con NVARCHAR(400) -- condition to filter data
    ,@ignoreIdentityCol bit=0 --indicate if ignore columne with identity
    )
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @sqlstr NVARCHAR(MAX);
    DECLARE @valueStr1 NVARCHAR(MAX);
    DECLARE @colsStr NVARCHAR(MAX);
    SELECT @sqlstr='SELECT ''INSERT '+@tablename;
    SELECT @valueStr1='';
    SELECT @colsStr='(';
    SELECT @valueStr1='VALUES (''+';

    IF RTRIM(LTRIM(@con))=''
        SET @con='1=1';

    SELECT @valueStr1=@valueStr1+col+'+'',''+'
            ,@colsStr=@colsStr+name +','
    FROM (
            SELECT
                        CASE
                        /* xtype=173 'binary'*/WHEN a.xtype =173 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR('+CONVERT(NVARCHAR(4),a.length*2+2)+'),'+a.name +')'+' END'
                        /*xtype=104 'bit'*/WHEN a.xtype =104 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(1),'+a.name +')'+' END'
                        /*xtype=175 'char'*/WHEN a.xtype =175 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'''N''''''+'+'REPLACE('+a.name+','''''''','''''''''''')' + '+'''''''''+' END'
                        /*xtype=61 'datetime'*/WHEN a.xtype =61 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'''N''''''+'+'CONVERT(NVARCHAR(23),'+a.name +',121)'+ '+'''''''''+' END'
                        /*xtype=106 'decimal'*/WHEN a.xtype =106 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR('+CONVERT(NVARCHAR(4),a.xprec+2)+'),'+a.name +')'+' END'
                        /*xtype=62 'float' */WHEN a.xtype =62 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(23),'+a.name +',2)'+' END'
                        /*xtype=56 'int'*/WHEN a.xtype =56 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(11),'+a.name +')'+' END'
                        /*xtype=60 'money'*/WHEN a.xtype =60 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(22),'+a.name +')'+' END'
                        /*xtype=239 'nchar'*/WHEN a.xtype =239 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'''N''''''+'+'REPLACE('+a.name+','''''''','''''''''''')' + '+'''''''''+' END'
                        /*xtype=108 'numeric'*/WHEN a.xtype =108 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR('+CONVERT(NVARCHAR(4),a.xprec+2)+'),'+a.name +')'+' END'
                        /*xtype=231 'nvarchar'*/WHEN a.xtype =231 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'''N''''''+'+'REPLACE('+a.name+','''''''','''''''''''')' + '+'''''''''+' END'
                        /*xtype=59 'real'*/WHEN a.xtype =59 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(23),'+a.name +',2)'+' END'
                        /*xtype=58 'smalldatetime'*/WHEN a.xtype =58 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'''N''''''+'+'CONVERT(NVARCHAR(23),'+a.name +',121)'+ '+'''''''''+' END'
                        /*xtype=52 'smallint'*/WHEN a.xtype =52 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(12),'+a.name +')'+' END'
                        /* xtype=122 'smallmoney'*/WHEN a.xtype =122 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(22),'+a.name +')'+' END'
                        /*xtype=127 'bigint'*/WHEN a.xtype =127 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(6),'+a.name +')'+' END'
                        /*xtype=48 'tinyint'*/WHEN a.xtype =48 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(6),'+a.name +')'+' END'
                        /*xtype=165 'varbinary'*/WHEN a.xtype =165 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR('+CONVERT(NVARCHAR(4),a.length*2+2)+'),'+a.name +')'+' END'
                        /*xtype=167 'varchar'*/WHEN a.xtype =167 THEN 'CASE WHEN '+a.name+' is null THEN ''NULL'' ELSE '+'''N''''''+'+'REPLACE('+a.name+','''''''','''''''''''')' + '+'''''''''+' END'
                        ELSE '''NULL'''
                        END    AS col
                ,a.colid
                ,a.name
            FROM syscolumns a
            WHERE a.id = object_id(@tablename)
            and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36
            and (columnproperty(a.id, a.name, 'IsIdentity') = 0 OR @ignoreIdentityCol=0)
            ) AS t
    ORDER BY colid;

    SELECT @sqlstr=@sqlstr+left(@colsStr,len(@colsStr)-1)+') '+left(@valueStr1,len(@valueStr1)-3)+')'' AS sql FROM '+@tablename +  ' WHERE 1=1 AND  ' + isnull(@con,'1=1');
    PRINT @sqlstr;
    EXEC( @sqlstr);
    SET NOCOUNT OFF
END

How to hash your binary data that exceed the limit (8000bytes) of input parameter of HASHBYTES in Sql

Introduction

Some times, we store binary data in database and need to hash them in order to get an identifier of the binary data. We know the Sql server has a built-in function to do that, it's HASHBYTES. But it's allowed input values are limited to 8000 bytes. Then how to hash the binary data that exceed 8000 bytes? We know Sql Server can be extended with CLR. Here there are mainly two hash algorithms: MD5 and SHA1. Now let us get started.
Background

You should know the  knowledges on C#, Sql, SHA1, MD5
Using the code

1. In Visual Studio 2015, create a Sql Server Database Project.

2. Add a file with template Sql CLR C# User Dfined Function

3. Add the SHA1 and MD5 method as below in your .cs file

The  C# method code lines of hash binary data using SHA1:
Hide   Shrink   Copy Code

/// <summary>
///  Encrypt data with type [varbinary](max) in sql server using SHA1 then return the encrypted data
/// </summary>
/// <param name="content">Input data you will enterd to encrypt it</param>
/// <returns>Return the encrypted text as hexadecimal string</returns>
[SqlFunction(DataAccess = DataAccessKind.None)]
public static String ComputeSHA1(SqlBytes content)
{
    String hashSHA1 = String.Empty;
    //Create new instance of SHA1 and convert the input data to array of bytes
    SHA1 calculator = SHA1.Create();
    Byte[] buffer = calculator.ComputeHash(content.Stream);
    calculator.Clear();

    //loop for each byte, convert it to hexadecimal string and add it to StringBuilder
    StringBuilder stringBuilder = new StringBuilder();
    for (int i = 0; i < buffer.Length; i++)
    {
        stringBuilder.Append(buffer[i].ToString("x2"));
    }
    hashSHA1 = stringBuilder.ToString();

    // return hexadecimal string
    return hashSHA1;
}

The  C# method code lines of hash binary data using MD5:


/// <summary>
///  Encrypt data with type [varbinary](max) in sql server using MD5 then return the encrypted data
/// </summary>
/// <param name="content">Input data you will enterd to encrypt it</param>
/// <returns>Return the encrypted text as hexadecimal string</returns>
[SqlFunction(DataAccess = DataAccessKind.None)]
public static String ComputeMD5(SqlBytes content)
{
    String hashMD5 = String.Empty;

    //Create new instance of md5 and convert the input data to array of bytes
    MD5 calculator = MD5.Create();
    Byte[] buffer = calculator.ComputeHash(content.Stream);
    calculator.Clear();

    //loop for each byte, convert it to hexadecimal string and add it to StringBuilder
    StringBuilder stringBuilder = new StringBuilder();
    for (int i = 0; i < buffer.Length; i++)
    {
        stringBuilder.Append(buffer[i].ToString("x2"));
    }
    hashMD5 = stringBuilder.ToString();

    //return hexadecimal string
    return hashMD5;
}

4. build your project and you will get a dll file in bin directory.

5. Publish your assembly file into your Sql Server database. There are two ways to do this.

    Use the publish tool provided by visual studio. You can generate script file and use the script file to pulish or directly publish it into your database.

    Manually register assembly into your database using Transact-SQL

First, you should ensure to enable CLR in your database. If not, execute the following sql


EXEC sp_configure 'clr enabled',1
go
RECONFIGURE
go

Use this sql to register your assembly as below:


CREATE ASSEMBLY [hashassembly]
    AUTHORIZATION [dbo]
    FROM 'c:\hashassembly.dll' WITH PERMISSION_SET = SAFE;

Create sql functions in your database using the following sql


CREATE FUNCTION [dbo].[ComputeMD5]
(@content VARBINARY (MAX))
RETURNS NVARCHAR (40)
AS
 EXTERNAL NAME [hashassembly].[UserDefinedFunctions].[ComputeMD5]

GO

CREATE FUNCTION [dbo].[ComputeSHA1]
(@content VARBINARY (MAX))
RETURNS NVARCHAR (40)
AS
 EXTERNAL NAME [hashassembly].[UserDefinedFunctions].[ComputeSHA1]

So far, we have finished deploying your assembly into you database, you can call the sql function to use it generate your hash value. For example:


UPDATE [dbo].[Picture]
   SET [HashKey] = dbo.ComputeSHA1([PictureBinary])