Script for creating stored procedue for insert record for whole database in 1 minutes.

Posted: January 11, 2013 in Uncategorized

AUTOMATICALLY CREATE PROCEDURE FOR INSERT RECORDS | GENERATE SCRIPT AROMATICALLY
CREATE FUNCTION [dbo].[FN_CreateStoredProcedureforInsertRecord]
(
    @TableName NVARCHAR(100),–table name
    @ProcedureName NVARCHAR(100)–procudure name you want to give it.
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

    DECLARE @PRMNAME NVARCHAR(1000)
    DECLARE @PrmA1 NVARCHAR(1000)  –It will contain parameters ex @PramA1 = ‘@Id,@EmployeeName,….’
    SET @PrmA1 = ”
    DECLARE @PROC_Name NVARCHAR(100)
    SET @PROC_Name = @ProcedureName  –Procedure name
    DECLARE @RESULT NVARCHAR(MAX)
    SET  @RESULT = ” — Final procedure script
    DECLARE @CurrentCLMNAME NVARCHAR(100) — It will be used in loop for storing the current column name
    DECLARE @CLMNAMES NVARCHAR(MAX) — It will contain the column name ex @CLMNAMES = ‘Id, EmployeeName’
    SET @CLMNAMES = ”
    DECLARE @VALUES NVARCHAR(MAX)  –It will contain values ex @VALUES = ‘@Id,@EmployeeName,….’
    SET @VALUES = ”
    DECLARE @i INT
    SET @i = 0
    DECLARE @j INT
    SET @j = 0
    DECLARE @IdentityColumn NVARCHAR(50)
    SET @IdentityColumn = ”
    
    –LOOP FOR GET TABLE FILD NAMES AND CREATE FORMAT AS  @FildName1,@FildName2…
    DECLARE CUR_LOOP CURSOR
        FOR SELECT ‘@’+ CASE WHEN (data_type = ‘nvarchar’ OR data_type = ‘varchar’) then  (column_name + ‘ ‘+ data_type +
        ‘(‘+
        (CASE WHEN (CONVERT(NVARCHAR(5),CHARacter_maximum_length) = -1 )
        THEN ‘MAX’
        ELSE CONVERT(NVARCHAR(5),CHARacter_maximum_length)
        END
        )+’)’) else (column_name + ‘ ‘+ data_type) END AS [ClmType]
        FROM information_schema.columns
        WHERE table_name = @TableName
    OPEN CUR_LOOP
    FETCH NEXT FROM CUR_LOOP INTO @PRMNAME
    WHILE @@FETCH_STATUS=0
            BEGIN
            IF(@j<>0)
                BEGIN
                    SET @PrmA1 = @PrmA1 + @PRMNAME+’,’+CHAR(13)+’  ‘
                END
                SET @j = @j + 1
                FETCH NEXT FROM CUR_LOOP INTO @PRMNAME
            END
    CLOSE CUR_LOOP
    DEALLOCATE CUR_LOOP
    –END  

–LOOP FOR GET TABLE FILD NAMES AND CREATE FORMAT AS  SET FildName1 = @FildName1,FildName2 = @FildName2…
    DECLARE CUR_LOOP CURSOR
    FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@TableName
    OPEN CUR_LOOP
    FETCH NEXT FROM CUR_LOOP INTO @CurrentCLMNAME
    WHILE @@FETCH_STATUS=0
            BEGIN
                IF(@i<>0)
                BEGIN
                    SET @VALUES = @VALUES +’@’+@CurrentCLMNAME+’,’+CHAR(13)
                    SET @CLMNAMES = @CLMNAMES + @CurrentCLMNAME+’,’+CHAR(13)
                    SET @RESULT =  @RESULT + @CurrentCLMNAME + ‘ = @’+ @CurrentCLMNAME + ‘,’+CHAR(13)+’  ‘
                END
                ELSE
                BEGIN
                SET @IdentityColumn = @CurrentCLMNAME
                END
                SET @i = @i + 1
                FETCH NEXT FROM CUR_LOOP INTO @CurrentCLMNAME
            END
    CLOSE CUR_LOOP
    DEALLOCATE CUR_LOOP
    –END
    
    –CREATE THE FORMAT OF PROCEDURE WITH THE HELP OF @PrmA1 AND Result… variables…
    — +CHAR(13)+ is for new line
    SET @RESULT = ‘CREATE PROC ‘+@PROC_Name
    +CHAR(13)+'(‘
    +CHAR(13)+’   ‘+ SUBSTRING(@PrmA1,0,(LEN(@PrmA1)-1))
    +CHAR(13)+’)’
    +CHAR(13)+’AS’
    +CHAR(13)+’BEGIN’
    +CHAR(13)+’ INSERT INTO ‘+@TableName
    +CHAR(13)+'(‘
    +CHAR(13)+ SUBSTRING(@CLMNAMES,0,(LEN(@CLMNAMES)-1))
    +CHAR(13)+’)’
    +CHAR(13)+’VALUES(‘
    +CHAR(13)+ SUBSTRING(@VALUES,0,(LEN(@VALUES)-1))
    +CHAR(13)+’)’
    +CHAR(13)+’END’
    RETURN  @RESULT    
END

After Sucessfully  executed above script.

Now run above function:

select [dbo].[FN_CreateStoredProcedureforInsertRecord](‘Topic’,’inserttopic’)

See screenshot:

Image

Now copy output and execute.

 

 

Advertisements
Comments
  1. Thomas says:

    Good article mayank@
    It’s save my time…

  2. Messi says:

    Nice script mayank. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s