Make a T-SQL stored procedure that can generically insert to a set of columns and a table specified from the outside.

IF OBJECT_ID(N'dbo.Attempt_Generic_Insert') IS NULL
BEGIN
   PRINT 'Create procedure : dbo.Attempt_Generic_Insert'
   EXECUTE('CREATE PROCEDURE dbo.Attempt_Generic_Insert AS RETURN 0')
END
GO
ALTER PROCEDURE [dbo].[Attempt_Generic_Insert]
(
   @TableName varchar(max),
   @ColumnSet Column_Setting READONLY
)
AS
BEGIN
DECLARE @LeftHalf varchar(max) = 'INSERT INTO [' + @TableName + '] ('
DECLARE @RightHalf varchar(max) = ']) VALUES ('
DECLARE @FullInsert varchar(max)
DECLARE @LeadWithComma bit = 0
DECLARE @ColumnSetName varchar(255)
DECLARE @ColumnSetSetting varchar(255)
DECLARE @ColumnSetEnclosedInQuotes bit
DECLARE curs CURSOR FOR SELECT * From @ColumnSet
OPEN curs
   FETCH NEXT FROM curs into @ColumnSetName, @ColumnSetSetting,
         @ColumnSetEnclosedInQuotes
   WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @LeftHalf = @LeftHalf + CASE @LeadWithComma WHEN 1 THEN '], [' ELSE
            '[' END + @ColumnSetName
      SET @RightHalf = @RightHalf + CASE @LeadWithComma WHEN 1 THEN ', ' ELSE
            '' END
      SET @RightHalf = @RightHalf + CASE @ColumnSetEnclosedInQuotes WHEN 1
            THEN '''' ELSE '' END
      SET @RightHalf = @RightHalf + @ColumnSetSetting
      SET @RightHalf = @RightHalf + CASE @ColumnSetEnclosedInQuotes WHEN 1
            THEN '''' ELSE '' END
      SET @LeadWithComma = 1
      FETCH NEXT FROM curs into @ColumnSetName, @ColumnSetSetting,
            @ColumnSetEnclosedInQuotes
   END
CLOSE curs
DEALLOCATE curs
SET @FullInsert = @LeftHalf + @RightHalf + ')'
EXECUTE(@FullInsert)
END

 
 

Alright, a few things to note... First, this will clearly blow up if a required column is left out, so the guts of this thing should really be wrapped in some try/catch logic. An error message should be returned if an error exists. Secondly, the line reading...

EXECUTE(@FullInsert)

 
 

...often times can be represented as...

exec sp_executesql @FullInsert

 
 

...I just couldn't get it to work in this case. Also we need a type named Column_Setting and that can look like this:

CREATE TYPE [dbo].[Column_Setting] AS TABLE(
Name varchar(255) NOT NULL,
Setting varchar(255) NOT NULL,
EncloseInQuotes bit NOT NULL,
PRIMARY KEY CLUSTERED
(
   Name ASC
   )WITH (IGNORE_DUP_KEY = OFF)
)

 
 

Assuming this table...

CREATE TABLE dbo.Cat
(
   CatId int IDENTITY NOT NULL,
   Name varchar(255) NOT NULL,
   LivesRemaining int NOT NULL,
   Birthdate datetime NULL,
   IsFluffy bit NOT NULL
)

 
 

...we could insert to it like so:

DECLARE @TableName varchar(max) = 'Cat'
DECLARE @ColumnSet Column_Setting
INSERT INTO @ColumnSet VALUES ('Name','Shadow',1)
INSERT INTO @ColumnSet VALUES ('LivesRemaining',9,0)
INSERT INTO @ColumnSet VALUES ('Birthdate','8/24/1974',1)
INSERT INTO @ColumnSet VALUES ('IsFluffy',1,0)
EXEC Attempt_Generic_Insert @TableName, @ColumnSet