USE master
go
-- ** BEGIN ** 
-- If Shiloh, then create as system SP for the ANSI views to function correctly
IF CHARINDEX( '8.00' , @@version ) > 0
BEGIN
        EXEC sp_configure 'allow updates' , 1
        EXEC sp_MS_upd_sysobj_category 1
END
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_genstmt'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_genstmt
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_genstmt'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_genstmt >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_genstmt >>>'
END
go
CREATE PROCEDURE sp_genstmt
(
@table_name varchar(30),
@stmt_type char(1) = ''
)
--WITH ENCRYPTION
AS
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    11 October 1999                                         */
/*      Description:    Create this SP in the database where the tables are     */
/*                      present. This SP can be used to generate INSERT stmts.  */
/*                      with the variable list, INSERT statement itself. More   */
/*                      options to be added later. Just saves typing time and   */
/*                      mistakes.                                               */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
select stmt from
(
select '@' + column_name + ' ' + data_type +
        case when charindex('char', data_type) > 0
                then '(' + ltrim(str(character_octet_length)) + ')'
                else ''
        end +
        case when ordinal_position < (select max(ordinal_position)
                                        from information_schema.columns
                                        where table_name = @table_name)
                then ','
                else ''
        end as stmt,
        (select max(ordinal_position) from information_schema.columns
                where table_name = @table_name) + 3 + ordinal_position as pos
from information_schema.columns where table_name = @table_name
) as a

select stmt from
(
select 'Insert Into ' + @table_name + '(' as stmt,
        (select min(ordinal_position) from information_schema.columns
        where table_name = @table_name) + -1 as pos
union
select column_name +
        case when ordinal_position < (select max(ordinal_position)
                                        from information_schema.columns
                                        where table_name = @table_name)
                then ','
                else ''
        end as stmt,
        ordinal_position as pos
from information_schema.columns where table_name = @table_name
union
select ')' as stmt, (select max(ordinal_position) from information_schema.columns
                        where table_name = @table_name) + 1 as pos
union
select 'Values(' as stmt, (select max(ordinal_position) from information_schema.columns
                                where table_name = @table_name) + 2 as pos
union
select '@' + column_name +
        case when ordinal_position < (select max(ordinal_position)
                                        from information_schema.columns
                                        where table_name = @table_name)
                then ','
                else ''
        end as stmt,
        (select max(ordinal_position) from information_schema.columns
        where table_name = @table_name) + 3 + ordinal_position as pos
from information_schema.columns where table_name = @table_name
union
select ')' as stmt, 2*(select max(ordinal_position) from information_schema.columns
                        where table_name = @table_name) + 4 as pos
) as a
order by pos
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_genstmt'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.sp_genstmt To Public
    PRINT '<<< CREATED PROCEDURE dbo.sp_genstmt >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_genstmt >>>'
go
-- ** END ** 
IF CHARINDEX( '8.00' , @@version ) > 0 EXEC sp_MS_upd_sysobj_category 2
go

This page was last updated on May 01, 2006 04:28 PM.