IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_generate_inserts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_generate_inserts]
GO

CREATE PROCEDURE dbo.sp_generate_inserts
(
	@database_name VARCHAR(100),
	@table_name VARCHAR(100)
	
)
AS
DECLARE @script varchar(MAX), @insert_into_fields VARCHAR(max), @parm_list VARCHAR(max), @values_list VARCHAR(max)
SELECT @script = ''

SELECT @insert_into_fields = (SELECT COLUMN_NAME + ',' 
  FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_NAME = @table_name
   AND ORDINAL_POSITION <> 1
 ORDER BY ORDINAL_POSITION ASC 
    FOR XML PATH('') )


SELECT @parm_list = (
	SELECT '@' + COLUMN_NAME + ' ' + DATA_TYPE + 
		   CASE DATA_TYPE 
			  WHEN 'nvarchar' THEN '(' +  RTRIM(LTRIM(STR(CHARACTER_MAXIMUM_LENGTH))) + ')' 
			  WHEN 'int' THEN ''
			  WHEN 'datetime' THEN ''
		   END + ',' 
	  --SELECT *  
	  FROM INFORMATION_SCHEMA.COLUMNS 
	 WHERE TABLE_NAME = @table_name
	   AND ORDINAL_POSITION <> 1
	 ORDER BY ORDINAL_POSITION ASC 
	   FOR XML PATH('') 
   )

SELECT @values_list = (
	SELECT '@' + COLUMN_NAME  + ',' 
	  FROM INFORMATION_SCHEMA.COLUMNS 
	 WHERE TABLE_NAME = @table_name
	   AND ORDINAL_POSITION <> 1
	 ORDER BY ORDINAL_POSITION ASC 
	   FOR XML PATH('') 
   )

SET @script = '/**********************************************************************
 Procedure name: ' + @table_name + '_ip
 File name:      ' + @table_name + '_ip.sql
 
 Purpose:	Insert user
 
 History:
 '
 + CONVERT(VARCHAR(10), GETDATE(),101) + ' ' + SYSTEM_USER + '    Initial Creation
 
 **********************************************************************/
USE ' + @database_name + '
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @table_name + ']'') AND type in (N''P'', N''PC''))
DROP PROCEDURE [dbo].[rightsholder_' + @table_name + '_ip]
GO

CREATE PROCEDURE dbo.rightsholder_' + @table_name + '_ip
(' + LEFT(@parm_list, LEN(@parm_list)-1)  + 
 
 ')
AS
	INSERT INTO dbo.vw_' + @table_name + '(' + LEFT(@insert_into_fields, LEN(@insert_into_fields)-1) + ') 
	VALUES  (' + LEFT(@values_list, LEN(@values_list)-1)  + ')
    GO'


PRINT @script
GO

sp_generate_inserts 'Adventure','contacts'