Insert Statements Creator / Generator Method 2

Insert Statements Creator / Generator Method 2


Create Procedure dbo.usp_GBulkInsert1
(
@pNumOfRows Int,
@pTableName VarChar(500),
@pValueList VarChar(Max),
@pColumnList VarChar(Max) = Null,
@pIdentityInsert Bit = 0,
@pDebug Bit = 0,
@pErrorFlag Char(1) Output,
@pErrorDesp VarChar(100) Output
)
As
Begin
Set NoCount On
/*
--To Execute

Declare @lNumOfRows Int,
@lTableName VarChar(500),
@lValueList VarChar(Max),
@lColumnList VarChar(Max),
@lIdentityInsert Bit,
@lDebug Bit,
@lErrorFlag Char(1),
@lErrorDesp VarChar(100)
Select @lTableName ='Customer',
@lValueList ='ZZ,Z', --'Z, YY' --'YY, YY, 7'
@lColumnList ='', --'CustomerID, ContactName' --'CustomerID, ContactName, UniverstalID'
@lIdentityInsert =0,
@lDebug = 0
Exec usp_GInsertStatement @lTableName, @lValueList, @lColumnList, @lIdentityInsert, @lDebug, @lErrorFlag Output, @lErrorDesp Output
Select @lDebug DebugFlag, @lErrorFlag ErrorFlag, @lErrorDesp ErrorDescription

*/

Declare @lValueList VarChar(Max),
@lPointer Int,
@lValue VarChar(Max)

--To Save Transaction with TableName
If @@TranCount > 0
Exec ('Save Transaction BulkInsert' + @pTableName)
Select @lPointer = 0
While @lPointer <> @pNumOfRows
Begin
If @lPointer = 0
Begin
--To Insert Data as provided
Exec usp_GInsertStatement @pTableName, @pValueList, @pColumnList, @pIdentityInsert, @pDebug, @pErrorFlag Output, @pErrorDesp Output
If @pErrorFlag = 'Y'
GoTo ErrorHandler
End
Else
Begin
--To Insert Bulk Data
Select @lValueList = lTrim(rTrim(@pValueList)) + ' ,', @pValueList = ''
While PatIndex('%,%', @lValueList) > 0
Begin
Select @lValue = lTrim(rTrim(SubString(@lValueList, 1, (PatIndex('%,%', @lValueList) -1))))
Select @lValueList = SubString(@lValueList, (PatIndex('%,%', @lValueList) +1), Len(@lValueList))
If IsNumeric(@lValue) = 1
Select @lValue = Convert(Numeric(16,2), @lValue) + 1
Else If IsDate(@lValue) = 1
Select @lValue = DateAdd(d,1, @lValue)
Else
Select @lValue = @lValue + Char(@lPointer + 64)

Select @pValueList = @pValueList + @lValue + ' ,'
End

Exec usp_GInsertStatement @pTableName, @pValueList, @pColumnList, @pIdentityInsert, @pDebug, @pErrorFlag Output, @pErrorDesp Output
If @pErrorFlag = 'Y'
GoTo ErrorHandler
End
Select @lPointer = @lPointer + 1
End

Select @pErrorFlag = 'N', @pErrorDesp = ''
Set NoCount Off
Return
ErrorHandler:
--To Rollback Transaction
If @@TranCount > 0
Exec ('Rollback Transaction BulkInsert' + @pTableName)
Set NoCount Off
End

Insert Statements Creator / Generator Method 1

Insert Statements Creator
Method 1


CREATE Function dbo.udf_GetNoOfOccurence
(
@pSourceString VarChar(Max),
@pSearchString VarChar(Max)
)Returns Int
As
Begin
Declare @lCount Int
Select @pSourceString = @pSourceString + @pSearchString, @lCount = 0
While PatIndex('%'+ @pSearchString + '%', @pSourceString) > 0
Begin
Select @lCount = @lCount + 1
Select @pSourceString = SubString(@pSourceString, PatIndex('%'+ @pSearchString + '%', @pSourceString) + 1, Len(@pSourceString))
End
Return @lCount -1
End

Select dbo.udf_GetNoOfOccurence('R,A,B,C', ',')
*/


CREATE Procedure dbo.usp_GInsertStatement
(
@pTableName VarChar(500),
@pValueList VarChar(Max),
@pColumnList VarChar(Max) = Null,
@pIdentityInsert Bit = 0,
@pDebug Bit = 0,
@pErrorFlag Char(1) Output,
@pErrorDesp VarChar(100) Output
)
As
Begin
Set NoCount On
/*
--To Execute

Declare @lTableName VarChar(500),
@lValueList VarChar(Max),
@lColumnList VarChar(Max),
@lIdentityInsert Bit,
@lDebug Bit,
@lErrorFlag Char(1),
@lErrorDesp VarChar(100)
Select @lTableName ='State',
@lValueList ='ZZ,Z,Z', --'Z, YY' --'YY, YY, 7'
@lColumnList ='StateID, StateCode, StateName', --'CustomerID, ContactName, UniverstalID'
@lIdentityInsert =0,
@lDebug = 0
Exec usp_GInsertStatement @lTableName, @lValueList, @lColumnList, @lIdentityInsert, @lDebug, @lErrorFlag Output, @lErrorDesp Output
Select @lDebug DebugFlag, @lErrorFlag ErrorFlag, @lErrorDesp ErrorDescription

*/
Declare @lIdentityColumn VarChar(100),
@lNoOfColumns Int,
@lNoOfValues Int,
@lColumnID Int,
@lColumnDataType VarChar(500),
@lColumnList VarChar(Max),
@lValueList VarChar(Max),
@lColumnName VarChar(500),
@lValue VarChar(500),
@lIdInsertStatement VarChar(500)

--To Check TableName and ValueList is not blank.
If (lTrim(rTrim(@pTableName)) = '' Or lTrim(rTrim(@pValueList)) = '' )
Begin
Select @pErrorFlag = 'Y', @pErrorDesp = 'TableName/ ValueList can''t be blank.'
GoTo ErrorHandler
End

Select @pTableName = QuoteName(@pTableName)
--To Check Identity_Insert is On/Off
Select @lIdentityColumn = ''
Select @lIdentityColumn = Name From sys.identity_columns Where object_id = Object_ID(@pTableName)
If @lIdentityColumn <> ''
Begin
If @pIdentityInsert = 1 --Or PatIndex('%' + @lIdentityColumn + '%', @lColumnList) > 0
Begin
Select @lIdInsertStatement = 'SET IDENTITY_INSERT ' + @pTableName + ' On'
End
Else
Begin
Select @lIdInsertStatement = 'SET IDENTITY_INSERT ' + @pTableName + ' Off'
End
End
If @pDebug = 1
Select @lIdentityColumn 'IdentityColumn', @lIdInsertStatement '@lIdInsertStatement'

--To Replace single-code into double single-code from valuelist or columnlist, if exist
Select @pValueList = Replace(lTrim(rTrim(@pValueList)), '''', ''''''),
@pColumnList = Replace(lTrim(rTrim(IsNull(@pColumnList, ''))), '''', '''''')

--To Get the ColumnList, if user has not provide
If @pColumnList = ''
Begin
If (@lIdentityColumn <> '' OR @pIdentityInsert = 0)
Begin
Select @pColumnList = @pColumnList + QuoteName(Name) + ', '
From SysColumns
Where ID = Object_ID(@pTableName)
And [Name] <> @lIdentityColumn
Order By ColID
End
Else
Begin
Select @pColumnList = @pColumnList + QuoteName(Name) + ', '
From SysColumns
Where ID = Object_ID(@pTableName)
Order By ColID
End
Select @pColumnList = SubString(lTrim(rTrim(@pColumnList)), 1, Len(lTrim(rTrim(@pColumnList))) -1)
End
/*
Else
Begin
If (@lIdentityColumn <> '' And @pIdentityInsert = 0 And PatIndex('%' + @lIdentityColumn + '%', @pColumnList) >0)
Begin
Select 'Rahul', @pColumnList, @lIdentityColumn
Select @pColumnList = ' ' + @pColumnList
Select @pColumnList = SubString(@pColumnList, 1, (PatIndex('%' + @lIdentityColumn + '%', @pColumnList) -4)) +  SubString(@pColumnList, (PatIndex('%' + @lIdentityColumn + '%', @pColumnList) + Len(@lIdentityColumn) + 1), Len(@pColumnList))
End
End
*/

--Patch For Add/Remove Identity Column from ColumnList Or ValueList according to @pIdentityInsert
--===============================================================================================
Declare @lMaxValue Int,
@lDelPos Int,
@lTemp Int,
@lTempValueList VarChar(Max),
@lTempColumnList VarChar(Max)
Declare @lTblMaxValue Table
(
MaxValue Int
)

If (@lIdentityColumn <> '' And @pIdentityInsert = 0 And PatIndex('%' + @lIdentityColumn + '%', @pColumnList)>0)
Begin
--If Identity column exist in columnlist and identityinsert is set to 0, Remove identity column from columnlist and valuelist

--Remove QuoteName From ColumnList
--========================================
Select @lTempColumnList = '', @pColumnList = @pColumnList + ', '
While PatIndex('%,%', @pColumnList)> 0
Begin
If PatIndex('%]%', SubString(lTrim(rTrim(@pColumnList)), 1, (PatIndex('%,%', lTrim(rTrim(@pColumnList))) -1) )) > 0
Select @lTempColumnList = @lTempColumnList + SubString(SubString(lTrim(rTrim(@pColumnList)), 1, (PatIndex('%,%', lTrim(rTrim(@pColumnList))) -1) ), 2, (Len(SubString(lTrim(rTrim(@pColumnList)), 1, (PatIndex('%,%', lTrim(rTrim(@pColumnList))) -1) )) -2
)) + ', '
Select @pColumnList = SubString(@pColumnList, PatIndex('%,%', @pColumnList) + 1, Len(@pColumnList))
End
Select @pColumnList = SubString(@lTempColumnList, 1, Len(@lTempColumnList) -1)
--========================================

--Remove Value of ID Column From ValueList
--========================================
Select @lTemp = 1, @lTempValueList = ''
Select @lDelPos = dbo.udf_GetNoOfOccurence(SubString(@pColumnList, 1, (PatIndex('%' + @lIdentityColumn + '%', @pColumnList) - 1)), ',')
While @lTemp <= @lDelPos
Begin
Select @lTempValueList = @lTempValueList + SubString(@pValueList, 1, PatIndex('%,%', @pValueList))
Select @pValueList = SubString(@pValueList, PatIndex('%,%', @pValueList) + 1, Len(@pValueList))
Select @lTemp = @lTemp + 1
End
If PatIndex('%,%', @pValueList) > 0
Begin
Select @lTempValueList = @lTempValueList + SubString(@pValueList, PatIndex('%,%', @pValueList) + 1, Len(@pValueList))
End
Else
Begin
Select @lTempValueList = Reverse(lTrim(rTrim(@lTempValueList)))
Select @lTempValueList = Reverse(SubString(@lTempValueList, PatIndex('%,%', @lTempValueList) + 1, Len(@lTempValueList)))
End
Select @pValueList = @lTempValueList
--========================================


--Remove IdentityColumn from ColumnList
--=====================================
Select @pColumnList = SubString(@pColumnList, 1, (PatIndex('%' + @lIdentityColumn + '%', @pColumnList) - 1)) +
SubString(@pColumnList, (PatIndex('%' + @lIdentityColumn + '%', @pColumnList) + Len(@lIdentityColumn) + 1), (Len(@pColumnList) -1))
If SubString(lTrim(rTrim(Reverse(@pColumnList))), 1,1) = ','
Begin
Select @pColumnList = Reverse(SubString(lTrim(rTrim(Reverse(@pColumnList))), 2,Len(lTrim(rTrim(Reverse(@pColumnList))))))
End
Else
Begin
Select @pColumnList = lTrim(rTrim(@pColumnList))
End
--=====================================

--Add QuoteName in ColumnList
--=====================================
Select @lTempColumnList = '', @pColumnList = @pColumnList + ', '
While PatIndex('%,%', @pColumnList)> 0
Begin
Select @lTempColumnList = @lTempColumnList + QuoteName(lTrim(rTrim(SubString( @pColumnList, 1, (PatIndex('%,%', @pColumnList) -1))))) + ', '
Select @pColumnList = SubString(@pColumnList, PatIndex('%,%', @pColumnList) + 1, Len(@pColumnList))
End
Select @pColumnList = SubString(@lTempColumnList, 1, Len(@lTempColumnList) -2)
--=====================================

End
Else If (@lIdentityColumn <> '' And @pIdentityInsert = 1 And PatIndex('%' + @lIdentityColumn + '%', @pColumnList)=0)
Begin
--If Identity column not exist in columnlist and identityinsert is set to 1, Add identity column from columnlist and valuelist
Select @pColumnList = @pColumnList + ', ' + QuoteName(@lIdentityColumn)
Insert Into @lTblMaxValue(MaxValue)
Exec ('Select Case When Max(QuoteName(' + @lIdentityColumn + ')) Is Null Then 0 Else  Max(QuoteName(' + @lIdentityColumn + ')) + 1 End From ' + @pTableName)
Select @lMaxValue = MaxValue
From @lTblMaxValue
Select @pValueList = @pValueList + ', ' + Cast(@lMaxValue As VarChar)
Delete From @lTblMaxValue
End
--===============================================================================================

If @pDebug = 1
Select @pColumnList ColumnList, @pValueList ValueList, dbo.udf_GetNoOfOccurence(@pColumnList, ','), dbo.udf_GetNoOfOccurence(@pValueList, ',')
--To Check Column name or number of supplied values does not match table definition
Select @lNoOfColumns = 0, @lNoOfValues = 0
If dbo.udf_GetNoOfOccurence(@pColumnList, ',') <> dbo.udf_GetNoOfOccurence(@pValueList, ',')
Begin
Select @pErrorFlag = 'Y', @pErrorDesp = 'Column name or number of supplied values does not match table definition.'
GoTo ErrorHandler
End

--To Update the ValuList accourding to Insert statement
Select @lColumnDataType = '', @lColumnList = @pColumnList + ',', @lValueList = @pValueList + ',', @lColumnName = '', @pValueList = ''

While PatIndex('%,%',@lColumnList) > 0
Begin
Select @lColumnName = SubString(@lColumnList, 1, (PatIndex('%,%', @lColumnList) -1))
Select @lColumnList = SubString(@lColumnList, (PatIndex('%,%', @lColumnList) +1), Len(@lColumnList))

IF PatIndex('%]%', @lColumnName) > 0
Begin
Select @lColumnName = SubString(lTrim(rTrim(@lColumnName)), 2, Len(lTrim(rTrim(@lColumnName))) -2)
If @pDebug = 1
Select @lColumnName 'ColumnName'
End

Select @lValue = SubString(@lValueList, 1, (PatIndex('%,%', @lValueList) -1))
Select @lValueList = SubString(@lValueList, (PatIndex('%,%', @lValueList) +1), Len(@lValueList))

Select @lColumnDataType = lTrim(rTrim(ST.[Name]))
From SysColumns SC
Inner Join SysTypes ST On ST.XUsertype = SC.Xtype
Where Id = Object_ID(lTrim(rTrim(@pTableName)))
And SC.[Name] = lTrim(rTrim(@lColumnName))

If @pDebug = 1
Select @lColumnDataType DataType, lTrim(rTrim(@lColumnName)) ColumnName, lTrim(rTrim(@pTableName)) TableName

Select @pValueList = @pValueList +
CASE
WHEN @lColumnDataType IN ('char','varchar','nchar','nvarchar')
THEN
--'REPLACE(RTRIM(' + @lValue + '),'''''''','''''''''''')'
'REPLACE(RTRIM(''' + @lValue + '''),'''''''','''''''''''')'
WHEN @lColumnDataType IN ('datetime','smalldatetime')
THEN
'RTRIM(CONVERT(char,''' + @lValue + ''',112))'
WHEN @lColumnDataType IN ('uniqueidentifier')
THEN
'REPLACE(CONVERT(char(255),RTRIM(' + @lValue + ')),'''''''','''''''''''')'
WHEN @lColumnDataType IN ('text','ntext')
THEN
'REPLACE(CONVERT(char(8000),''' + @lValue + '''),'''''''','''''''''''')'
WHEN @lColumnDataType IN ('binary','varbinary')
THEN
'RTRIM(CONVERT(char,''' + 'CONVERT(int,''' + @lValue + ''')''))'
WHEN @lColumnDataType IN ('float','real','money','smallmoney')
THEN
'LTRIM(RTRIM(' + 'CONVERT(char, ''' +  @lValue  + ''',2)' + '))'
ELSE
'LTRIM(RTRIM(' + 'CONVERT(char, ''' +  @lValue  + ''')' + '))'
END +
', '
End
Select @pValueList = SubString(@pValueList, 1, Len(@pValueList) -2)

If @pDebug = 1
Select @lIdInsertStatement, 'Insert Into ' + @pTableName + ' (' + @pColumnList + ') Values( ' + @pValueList + ') '

--To Execute Insert Statement
If @lIdentityColumn <> ''
Begin
Exec(
@lIdInsertStatement + ' ' +
'Insert Into ' + @pTableName + ' (' + @pColumnList + ') Values( ' + @pValueList + ') '
)
If @@Error > 0
Begin
Select @pErrorFlag = 'Y', @pErrorDesp = 'Error on Executing Insert Statement.'
GoTo ErrorHandler
End
End
Else
Begin
Exec ('Insert Into ' + @pTableName + ' (' + @pColumnList + ') Values( ' + @pValueList + ') ')
If @@Error > 0
Begin
Select @pErrorFlag = 'Y', @pErrorDesp = 'Error on Executing Insert Statement.'
GoTo ErrorHandler
End
End

Select @pErrorFlag = 'N', @pErrorDesp = ''
Set NoCount Off
Return
ErrorHandler:

Set NoCount Off
End

Insert Statements Generator

Generate or Create Insert statements from a table


Purpose:    To generate INSERT statements from existing data.
        These INSERTS can be executed to regenerate the data at some other location.
        This procedure is also useful to create a database setup, where in you can
        script your data along with your table definitions.



SET NOCOUNT ON
GO

PRINT 'Using Master database'
USE master
GO

PRINT 'Checking for the existence of this procedure'
IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL --means, the procedure already exists
    BEGIN
        PRINT 'Procedure already exists. So, dropping it'
        DROP PROC sp_generate_inserts
    END
GO

CREATE PROC sp_generate_inserts
(
    @table_name varchar(776),       -- The table/view for which the INSERT statements will be generated using the existing data
    @target_table varchar(776) = NULL,  -- Use this parameter to specify a different table name into which the data will be inserted
    @include_column_list bit = 1,       -- Use this parameter to include/ommit column list in the generated INSERT statement
    @from varchar(800) = NULL,      -- Use this parameter to filter the rows based on a filter condition (using WHERE)
    @include_timestamp bit = 0,         -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement
    @debug_mode bit = 0,            -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
    @owner varchar(64) = NULL,      -- Use this parameter if you are not the owner of the table
    @ommit_images bit = 0,          -- Use this parameter to generate INSERT statements by omitting the 'image' columns
    @ommit_identity bit = 0,        -- Use this parameter to ommit the identity columns
    @top int = NULL,            -- Use this parameter to generate INSERT statements only for the TOP n rows
    @cols_to_include varchar(8000) = NULL,  -- List of columns to be included in the INSERT statement
    @cols_to_exclude varchar(8000) = NULL,  -- List of columns to be excluded from the INSERT statement
    @disable_constraints bit = 0,       -- When 1, disables foreign key constraints and enables them after the INSERT statements
    @ommit_computed_cols bit = 0        -- When 1, computed columns will not be included in the INSERT statement

)
AS
BEGIN

/*******************************

Example 1:  To generate INSERT statements for table 'titles':

        EXEC sp_generate_inserts 'titles'

Example 2:  To ommit the column list in the INSERT statement: (Column list is included by default)
        IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below,
        to avoid erroneous results

        EXEC sp_generate_inserts 'titles', @include_column_list = 0

Example 3:  To generate INSERT statements for 'titlesCopy' table from 'titles' table:

        EXEC sp_generate_inserts 'titles', 'titlesCopy'

Example 4:  To generate INSERT statements for 'titles' table for only those titles
        which contain the word 'Computer' in them:
        NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter

        EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"

Example 5:  To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:
        (By default TIMESTAMP column's data is not scripted)

        EXEC sp_generate_inserts 'titles', @include_timestamp = 1

Example 6:  To print the debug information:

        EXEC sp_generate_inserts 'titles', @debug_mode = 1

Example 7:  If you are not the owner of the table, use @owner parameter to specify the owner name
        To use this option, you must have SELECT permissions on that table

        EXEC sp_generate_inserts Nickstable, @owner = 'Nick'

Example 8:  To generate INSERT statements for the rest of the columns excluding images
        When using this otion, DO NOT set @include_column_list parameter to 0.

        EXEC sp_generate_inserts imgtable, @ommit_images = 1

Example 9:  To generate INSERT statements excluding (ommiting) IDENTITY columns:
        (By default IDENTITY columns are included in the INSERT statement)

        EXEC sp_generate_inserts mytable, @ommit_identity = 1

Example 10:     To generate INSERT statements for the TOP 10 rows in the table:

        EXEC sp_generate_inserts mytable, @top = 10

Example 11:     To generate INSERT statements with only those columns you want:

        EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"

Example 12:     To generate INSERT statements by omitting certain columns:

        EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"

Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements:

        EXEC sp_generate_inserts titles, @disable_constraints = 1

Example 14:     To exclude computed columns from the INSERT statement:
        EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
****************************/

SET NOCOUNT ON

--Making sure user only uses either @cols_to_include or @cols_to_exclude
IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))
    BEGIN
        RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
        RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
    END

--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))
    BEGIN
        RAISERROR('Invalid use of @cols_to_include property',16,1)
        PRINT 'Specify column names surrounded by single quotes and separated by commas'
        PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'
        RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property
    END

IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))
    BEGIN
        RAISERROR('Invalid use of @cols_to_exclude property',16,1)
        PRINT 'Specify column names surrounded by single quotes and separated by commas'
        PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'
        RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property
    END

--Checking to see if the database name is specified along wih the table name
--Your database context should be local to the table for which you want to generate INSERT statements
--specifying the database name is not allowed
IF (PARSENAME(@table_name,3)) IS NOT NULL
    BEGIN
        RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
        RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed
    END

--Checking for the existence of 'user table' or 'view'
--This procedure is not written to work on system tables
--To script the data in system tables, just create a view on the system tables and script the view instead

IF @owner IS NULL
    BEGIN
        IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL))
            BEGIN
                RAISERROR('User table or view not found.',16,1)
                PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.'
                PRINT 'Make sure you have SELECT permission on that table or view.'
                RETURN -1 --Failure. Reason: There is no user table or view with this name
            END
    END
ELSE
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @owner)
            BEGIN
                RAISERROR('User table or view not found.',16,1)
                PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'
                PRINT 'Make sure you have SELECT permission on that table or view.'
                RETURN -1 --Failure. Reason: There is no user table or view with this name
            END
    END

--Variable declarations
DECLARE     @Column_ID int,
        @Column_List varchar(8000),
        @Column_Name varchar(128),
        @Start_Insert varchar(786),
        @Data_Type varchar(128),
        @Actual_Values varchar(8000),   --This is the string that will be finally executed to generate INSERT statements
        @IDN varchar(128)       --Will contain the IDENTITY column's name in the table

--Variable Initialization
SET @IDN = ''
SET @Column_ID = 0
SET @Column_Name = ''
SET @Column_List = ''
SET @Actual_Values = ''

IF @owner IS NULL
    BEGIN
        SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
    END
ELSE
    BEGIN
        SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
    END

--To get the first column's ID

SELECT  @Column_ID = MIN(ORDINAL_POSITION)
FROM    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE   TABLE_NAME = @table_name AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)

--Loop through all the columns of the table, to get the column names and their data types
WHILE @Column_ID IS NOT NULL
    BEGIN
        SELECT  @Column_Name = QUOTENAME(COLUMN_NAME),
        @Data_Type = DATA_TYPE
        FROM    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
        WHERE   ORDINAL_POSITION = @Column_ID AND
        TABLE_NAME = @table_name AND
        (@owner IS NULL OR TABLE_SCHEMA = @owner)

        IF @cols_to_include IS NOT NULL --Selecting only user specified columns
        BEGIN
            IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0
            BEGIN
                GOTO SKIP_LOOP
            END
        END

        IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
        BEGIN
            IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0
            BEGIN
                GOTO SKIP_LOOP
            END
        END

        --Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
        IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1
        BEGIN
            IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
                SET @IDN = @Column_Name
            ELSE
                GOTO SKIP_LOOP
        END

        --Making sure whether to output computed columns or not
        IF @ommit_computed_cols = 1
        BEGIN
            IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1
            BEGIN
                GOTO SKIP_LOOP
            END
        END

        --Tables with columns of IMAGE data type are not supported for obvious reasons
        IF(@Data_Type in ('image'))
            BEGIN
                IF (@ommit_images = 0)
                    BEGIN
                        RAISERROR('Tables with image columns are not supported.',16,1)
                        PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'
                        PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'
                        RETURN -1 --Failure. Reason: There is a column with image data type
                    END
                ELSE
                    BEGIN
                    GOTO SKIP_LOOP
                    END
            END

        --Determining the data type of the column and depending on the data type, the VALUES part of
        --the INSERT statement is generated. Care is taken to handle columns with NULL values. Also
        --making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
        SET @Actual_Values = @Actual_Values  +
        CASE
            WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')
                THEN
                    'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
            WHEN @Data_Type IN ('datetime','smalldatetime') AND @Column_Name <> '[created_dt]'
                THEN
                    'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',121))+'''''''',''NULL'')'
            WHEN @Data_Type IN ('datetime','smalldatetime') AND @Column_Name = '[created_dt]'
                THEN
                    '''GETDATE()'''
            WHEN @Data_Type IN ('uniqueidentifier')
                THEN
                    'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
            WHEN @Data_Type IN ('text','ntext')
                THEN
                    'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
            WHEN @Data_Type IN ('binary','varbinary')
                THEN
                    'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
            WHEN @Data_Type IN ('timestamp','rowversion')
                THEN
                    CASE
                        WHEN @include_timestamp = 0
                            THEN
                                '''DEFAULT'''
                            ELSE
                                'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
                    END
            WHEN @Data_Type IN ('float','real','money','smallmoney')
                THEN
                    'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ',2)' + ')),''NULL'')'
            ELSE
                'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ')' + ')),''NULL'')'
        END   + '+' +  ''',''' + ' + '

        --Generating the column list for the INSERT statement
        SET @Column_List = @Column_List +  @Column_Name + ','    

        SKIP_LOOP: --The label used in GOTO

        SELECT  @Column_ID = MIN(ORDINAL_POSITION)
        FROM    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
        WHERE   TABLE_NAME = @table_name AND
        ORDINAL_POSITION > @Column_ID AND
        (@owner IS NULL OR TABLE_SCHEMA = @owner)

    --Loop ends here!
    END

--To get rid of the extra characters that got concatenated during the last run through the loop
SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)

IF LTRIM(@Column_List) = ''
    BEGIN
        RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
        RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
    END

--Forming the final string that will be executed, to output the INSERT statements
/*
IF (@include_column_list <> 0)
    BEGIN
        SET @Actual_Values =
            'SELECT ' +
            CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
            '''' + RTRIM(@Start_Insert) +
            ' ''+' + '''(' + RTRIM(@Column_List) +  '''+' + ''')''' +
            ' +''VALUES(''+ ' +  @Actual_Values  + '+'')''' + ' ' +
            COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
    END
ELSE IF (@include_column_list = 0)
    BEGIN
        SET @Actual_Values =
            'SELECT ' +
            CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
            '''' + RTRIM(@Start_Insert) +
            ' '' +''VALUES(''+ ' +  @Actual_Values + '+'')''' + ' ' +
            COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
    END
*/

IF (@include_column_list <> 0)
    BEGIN
        SET @Actual_Values =
            'SELECT ' +
            CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
            '''' + 'SELECT ''+' +
            --' ''+' + '''(' + RTRIM(@Column_List) +  '''+' + ''')''' +
            +  @Actual_Values  + '+'' UNION ALL ''' + ' ' +
            COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
    END
ELSE IF (@include_column_list = 0)
    BEGIN
        SET @Actual_Values =
            'SELECT ' +
            CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
            '''' + 'SELECT ''+' +
            + @Actual_Values + '+'' UNION ALL ''' + ' ' +
            COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
    END
--Determining whether to ouput any debug information
IF @debug_mode =1
    BEGIN
        PRINT '/*****START OF DEBUG INFORMATION*****'
        PRINT 'Beginning of the INSERT statement:'
        PRINT @Start_Insert
        PRINT ''
        PRINT 'The column list:'
        PRINT @Column_List
        PRINT ''
        PRINT 'The SELECT statement executed to generate the INSERTs'
        PRINT @Actual_Values
        PRINT ''
        PRINT '*****END OF DEBUG INFORMATION*****/'
        PRINT ''
    END

--Determining whether to print IDENTITY_INSERT or not

IF (@IDN <> '')
    BEGIN
        PRINT 'DELETE FROM ' + @table_name
        PRINT 'DBCC CHECKIDENT('+@table_name+', RESEED, 0)'
        PRINT ''
        PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'
        PRINT 'GO'
        PRINT ''
    END

IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
    BEGIN
        IF @owner IS NULL
            BEGIN
                SELECT  'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
            END
        ELSE
            BEGIN
                SELECT  'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
            END

        PRINT 'GO'
    END

--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!

SET @Column_Name = ''
SELECT @Column_Name = COALESCE(@Column_Name, '') + ISNULL(',' + COLUMN_NAME, '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name
SELECT @Column_Name = STUFF(@Column_Name, 1,1, '')

SELECT 'INSERT INTO ' + @table_name + '(' + @Column_Name + ')'

EXEC (@Actual_Values)

IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
    BEGIN
        IF @owner IS NULL
            BEGIN
                SELECT  'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL'  AS '--Code to enable the previously disabled constraints'
            END
        ELSE
            BEGIN
                SELECT  'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
            END

        PRINT 'GO'
    END

PRINT ''
IF (@IDN <> '')
    BEGIN
        PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF'
        PRINT 'GO'
    END

PRINT 'SET NOCOUNT OFF'

SET NOCOUNT OFF
RETURN 0 --Success. We are done!
END

GO

PRINT 'Created the procedure'
GO

--Marking this as a system procedure in master database
EXEC sys.sp_MS_marksystemobject sp_generate_inserts
GO

PRINT 'Granting EXECUTE permission on sp_generate_inserts to all users'
GRANT EXEC ON sp_generate_inserts TO public

SET NOCOUNT OFF
GO

PRINT 'Done'

Sql Server Table Export to CSV in Query Result

Sql Server Table Export to CSV in Query Result

Another method to export the sql server table in query result. Useful where user is not authenticate for file system commands.



Declare
@TableName nvarchar(500)
SELECT @TableName = '[SchemaName].[YourTableName]'
DECLARE @FirstColumn bit
DECLARE @ColumnOrder varchar(500)
DECLARE @ColumnName varchar(500)
DECLARE @ColumnType varchar(100)
DECLARE @ColumnsCount int
DECLARE @SelectCommand nVarChar(Max)
DECLARE @ColumnList nVarChar(Max)
DECLARE @TextLine varchar(Max)
DECLARE cColumns CURSOR SCROLL FOR
SELECT Distinct Ordinal_Position As colorder, Column_Name As colName, Data_Type
FROM INFORMATION_SCHEMA.Columns
WHERE '['+ Table_Schema + '].[' + Table_Name + ']' = @TableName
Order by Ordinal_Position


SET @SelectCommand = 'SELECT '
SET @FirstColumn = 1
SET @ColumnList = ''

--Get table's columns & construct SELECT command string
OPEN cColumns
FETCH NEXT FROM cColumns INTO @ColumnOrder, @ColumnName, @ColumnType
WHILE @@FETCH_STATUS = 0
BEGIN

IF @FirstColumn = 0 SET @SelectCommand = @SelectCommand + ' + '' '' + '
SET @FirstColumn = 0
-- binary fields cannot be exported to text file
IF @ColumnType = 'image' OR @ColumnType = 'varbinary' OR @ColumnType = 'binary'
SET @SelectCommand = @SelectCommand + '''NULL'''
ELSE
SET @SelectCommand = @SelectCommand + ' ISNULL(CAST([' + @ColumnName + '] AS varchar(Max)), ''NULL'')'

SET @ColumnList = @ColumnList + '[' + @ColumnName + '],'
FETCH NEXT FROM cColumns INTO @ColumnOrder, @ColumnName, @ColumnType
END
CLOSE cColumns
DEALLOCATE cColumns
SET @SelectCommand = @SelectCommand + ' FROM ' + @TableName --+ ' Order By SourceSystem, Company'
--Select @SelectCommand


-- create
EXEC('DECLARE cTable CURSOR SCROLL FOR ' + @SelectCommand)


SET @TextLine = Reverse(SubString(Reverse(@ColumnList), 2, Len(@ColumnList)))
SET @TextLine = REPLACE(@TextLine, ',', '","')
SET @TextLine = '"' + @TextLine + '"'
-- write line into text file

Print @TextLine



-- get from every row in table @TableName columns and insert into text file (csv)
OPEN cTable
FETCH NEXT FROM cTable INTO @TextLine
WHILE @@FETCH_STATUS = 0
BEGIN
-- remove new line chars
SET @TextLine = REPLACE(@TextLine, CHAR(13), '')
SET @TextLine = REPLACE(@TextLine, CHAR(10), '')
-- put inside string value two " instead of one "
SET @TextLine = REPLACE(@TextLine, '"', '""')
-- replace separator with ,
SET @TextLine = REPLACE(@TextLine, ' ', '","')
SET @TextLine = '"' + @TextLine + '"'
SET @TextLine = REPLACE(@TextLine, 'NULL', '')

Print @TextLine

FETCH NEXT FROM cTable INTO @TextLine
END
CLOSE cTable
DEALLOCATE cTable

Export to CSV

Sql Server Table export to CSV format



ALTER PROCEDURE [dbo].[File_Export]
@TableName nvarchar(500),
@FileName nvarchar(1000),
@Result int  = NULL OUTPUT
AS
/*
Method: Exports table rows to .csv file (Comma Separated Values file)
Input Parameters:
@TableName nvarchar(100)
@FileName nvarchar(1000)
Output Parameters:
@Result int  
- Return values (@Result)
 1 the operation completed successfuly
-1 error
*/

--Exec dbo.[File_Export] '[dbo].[IncidentsHistory]', 'D:\Test.csv'

DECLARE @ErrorID int
DECLARE @ErrorMessage nvarchar(100)
DECLARE @FS int, @OLEResult int, @FileID int

-- verify table @TableName existence
IF NOT exists (SELECT * FROM INFORMATION_SCHEMA.Tables WHERE '['+ Table_Schema + '].[' + Table_Name + ']' = @TableName)
BEGIN
SET @ErrorMessage = 'Invalid table name ' + @TableName
Print @ErrorMessage
RETURN -1
END

-- create FileSystemObject
EXEC @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
SET @ErrorMessage = 'Fail to create FileSystemObject'
Print @ErrorMessage
RETURN -1
END

-- open(create) file
EXEC @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 2, 1
IF @OLEResult <> 0
BEGIN
SET @ErrorMessage = 'Fail to open file ' + @FileName
Print @ErrorMessage
RETURN -1
END

DECLARE @FirstColumn bit
DECLARE @ColumnOrder varchar(500)
DECLARE @ColumnName varchar(500)
DECLARE @ColumnType varchar(100)
DECLARE @ColumnsCount int
DECLARE @SelectCommand nVarChar(Max)
DECLARE @ColumnList nVarChar(Max)
DECLARE @TextLine varchar(Max)
DECLARE cColumns CURSOR SCROLL FOR
SELECT Distinct Ordinal_Position As colorder, Column_Name As colName, Data_Type
FROM INFORMATION_SCHEMA.Columns
WHERE '['+ Table_Schema + '].[' + Table_Name + ']' = @TableName
Order by Ordinal_Position


SET @SelectCommand = 'SELECT '
SET @FirstColumn = 1
SET @ColumnList = ''

--Get table's columns & construct SELECT command string
OPEN cColumns
FETCH NEXT FROM cColumns INTO @ColumnOrder, @ColumnName, @ColumnType
WHILE @@FETCH_STATUS = 0
BEGIN

IF @FirstColumn = 0 SET @SelectCommand = @SelectCommand + ' + '' '' + '
SET @FirstColumn = 0
-- binary fields cannot be exported to text file
IF @ColumnType = 'image' OR @ColumnType = 'varbinary' OR @ColumnType = 'binary'
SET @SelectCommand = @SelectCommand + '''NULL'''
ELSE
SET @SelectCommand = @SelectCommand + ' ISNULL(CAST([' + @ColumnName + '] AS varchar(100)), ''NULL'')'

SET @ColumnList = @ColumnList + '[' + @ColumnName + '],'
FETCH NEXT FROM cColumns INTO @ColumnOrder, @ColumnName, @ColumnType
END
CLOSE cColumns
DEALLOCATE cColumns
SET @SelectCommand = @SelectCommand + ' FROM ' + @TableName
-- create
EXEC('DECLARE cTable CURSOR SCROLL FOR ' + @SelectCommand)
IF @@Error <> 0
BEGIN
SET @ErrorMessage = 'Fail to open table ' + @TableName
Print @ErrorMessage
RETURN -1
END

SET @TextLine = Reverse(SubString(Reverse(@ColumnList), 2, Len(@ColumnList)))
SET @TextLine = REPLACE(@TextLine, ',', '","')
SET @TextLine = '"' + @TextLine + '"'
-- write line into text file
EXEC @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @TextLine
IF @OLEResult <> 0
BEGIN
SET @ErrorMessage = 'Fail to write into file ' + @FileName
Print @ErrorMessage
RETURN -1 -- if one fail to write one line into file is useless to try again
END


-- get from every row in table @TableName columns and insert into text file (csv)
OPEN cTable
FETCH NEXT FROM cTable INTO @TextLine
WHILE @@FETCH_STATUS = 0
BEGIN
-- remove new line chars
SET @TextLine = REPLACE(@TextLine, CHAR(13), '')
SET @TextLine = REPLACE(@TextLine, CHAR(10), '')
-- put inside string value two " instead of one "
SET @TextLine = REPLACE(@TextLine, '"', '""')
-- replace separator with ,
SET @TextLine = REPLACE(@TextLine, ' ', '","')
SET @TextLine = '"' + @TextLine + '"'
SET @TextLine = REPLACE(@TextLine, 'NULL', '')

-- write line into text file
EXEC @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @TextLine
IF @OLEResult <> 0
BEGIN
SET @ErrorMessage = 'Fail to write into file ' + @FileName
Print @ErrorMessage
RETURN -1 -- if one fail to write one line into file is useless to try again
END

FETCH NEXT FROM cTable INTO @TextLine
END
CLOSE cTable
DEALLOCATE cTable

-- destroy objects
EXEC @OLEResult = sp_OAMethod @FileID, 'Close'
EXEC @OLEResult = sp_OADestroy @FileID
EXEC @OLEResult = sp_OADestroy @FS

RETURN 1

SQL Server Jobs Monitor

Monitor Sql Server Jobs

ALTER PROCEDURE [dbo].[spETLMonitor] As

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Test].[dbo].[Raw_tblETLMonitor]') AND type in (N'U'))
DROP TABLE [Test].[dbo].[Raw_tblETLMonitor]

declare @session_id INT
declare @session_id_as_char NVARCHAR(16)

IF @session_id IS NULL
  SELECT TOP(1) @session_id = session_id FROM msdb.dbo.syssessions ORDER by agent_start_date DESC
  ELSE IF NOT EXISTS( SELECT * FROM msdb.dbo.syssessions WHERE session_id = @session_id)
  BEGIN
    SELECT @session_id_as_char = CONVERT(NVARCHAR(16), @session_id)
    RAISERROR(14262, -1, -1, '@session_id', @session_id_as_char)
  END

Declare @enum_job Table (
JobID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)      



insert into @enum_job exec master.dbo.xp_sqlagent_enum_jobs 1,garbage
SELECT  a.job_id As JobId,
        case when charindex('_',b.name) > 0 then
            left(b.name,charindex('_',b.name)-1)
            else b.name
       end [AccountCode],
       right(b.name,len(b.Name)-charindex('_',b.name)) [ProcessArea],
       b.name [JobName],
       a.start_execution_date   [LastStartDate],    
       a.stop_execution_date    [LastStopDate],
       Case When a.stop_execution_date Is Null Then
       Cast(DateDiff(ss,a.start_execution_date ,Getdate())/60.0 As Decimal(28,2))
       Else
       Cast(DateDiff(ss,a.start_execution_date ,a.stop_execution_date)/60.0 As Decimal(28,2))
       End As ElapsedTime,
       c.message [Message],
       case d.State
   when 1 then 'Executing'
   when 2 then 'Waiting for thread'
   when 3 then 'Between retries'
   when 5 then 'Suspended'
   when 7 then 'Performing completion actions'
    Else
       case c.run_status
            when 0 then 'Failed'
            when 1 then 'Succeeded'
            when 3 then 'Canceled'
            when 5 then 'Unknown'
       end end as [RunStatus],
       a.next_scheduled_run_date [NextScheduledRunDate],
       c.step_id [StepID],
       c.retries_attempted [RetriesAttempted],'AMER' As Region,
       c.server  as HostName   Into test.dbo.Raw_tblETLMonitor
FROM   msdb.dbo.sysjobactivity a
       LEFT JOIN msdb.dbo.sysjobhistory c ON a.job_history_id = c.instance_id
       join msdb.dbo.sysjobs_view b on a.job_id = b.job_id
       Join @enum_job d On a.job_id = d.JobId
where  a.session_id = @session_id
order by b.name

CSV To Table

CSV To Table

CREATE Procedure [dbo].[CSVToTable]
@CSV NVARCHAR(MAX),
@firstRowHeadings int = 1,
@Delimiter char(1) = ',',
@LineTerminator char(1) = null,
@DoRowOrderColumn int = 0
as
declare @MyHierarchy hierarchy
insert into @MyHierarchy
  Select * from parseCSV(@CSV, @firstRowHeadings,@Delimiter,@LineTerminator)

Declare @Command NVarchar(2000)
if @DoRowOrderColumn  = 0
  Select @command= 'Select'+stuff((Select line from (
  Select distinct ',
    MAX( case when sequenceNo='+convert(Varchar(10),sequenceNo)+' then StringValue else '''' end) as ['+name+']'
  from @MyHierarchy  where object_ID is null union all
  select '
  FROM @TheHierarchy WHERE object_ID is null
  GROUP BY parent_ID ORDER BY parent_ID' )f(line)
  FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')
else
  Select @command= (Select line+'
  ' from (
  Select 'Select parent_ID as [Row]' union all
  Select  distinct ',  MAX( case when sequenceNo='+convert(Varchar(10),sequenceNo)+' then StringValue else '''' end) as ['+name+']'
  from @MyHierarchy where object_ID is null union all
  select 'from @TheHierarchy where object_ID is null
  group by parent_ID order by parent_ID' )f(line)
  FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

EXEC sp_executesql @Command,
      N'@TheHierarchy hierarchy READONLY',
      @TheHierarchy = @MyHierarchy