You Want To INSERT Data into More Than One Table. You want to include conditions to specific/all tables that participates as “Destination” in Multi Table Insert part. This Stored Procedure can insert rows into any number tables based on the source table with or without conditions. 

SP Script

CREATE PROCEDURE SP_MULTI_INSERTS

(@SUB_QUERY AS VARCHAR(2000),

@INSERT_PART AS VARCHAR(2000),

@DELIMITER AS VARCHAR(100),

@ERRORMESSAGE AS VARCHAR(2000)

)

AS

--VARIABLES DECLARATION

DECLARE @SAND AS VARCHAR(10)

DECLARE @SSTR AS VARCHAR(2000)

DECLARE @SSTR2 AS VARCHAR(2000)

DECLARE @SSTR3 AS VARCHAR(2000)

DECLARE @SSQL AS VARCHAR(2000)

DECLARE @SUB_QUERY2 AS VARCHAR(2000)

--VARIABLES TO CONSTRUCT INSERT SQL

DECLARE @LASTPOS AS INT

DECLARE @LASTPOS2 AS INT

DECLARE @LASTPOS3 AS INT

--DATA TRIMMING, AND DEFAULT VALUE SETTINGS

SET @INSERT_PART = ltrim(rtrim(@INSERT_PART))

SET @SUB_QUERY = ltrim(rtrim(@SUB_QUERY))

IF LEN(@INSERT_PART) = 0 OR LEN(@SUB_QUERY) = 0

BEGIN

SET @ERRORMESSAGE = 'INCOMPLETE INFORMATION'

RETURN -1

END

SET @LASTPOS = 0

SET @SAND = ' '

--CHECK WHETHER SUBQUERY I.E. SOURCE DATA QUERY HAS WHERE CONDITION

IF CHARINDEX(' WHERE ', @SUB_QUERY) > 0

BEGIN

IF CHARINDEX(' WHERE ', @SUB_QUERY) > CHARINDEX(' FROM ', @SUB_QUERY) 

SET @SAND = ' AND '

END

ELSE

SET @SAND = ' WHERE '

BEGIN TRANSACTION MULTIINSERTS

--LOOP STARTS

WHILE LEN(@SUB_QUERY) > 0

BEGIN

SET @LASTPOS2 = @LASTPOS

SET @LASTPOS = CHARINDEX(@DELIMITER, @INSERT_PART, @LASTPOS2)

IF @LASTPOS = 0

SET @SSTR = SUBSTRING(@INSERT_PART, @LASTPOS2, 2001)

ELSE

SET @SSTR = SUBSTRING(@INSERT_PART, @LASTPOS2, @LASTPOS-@LASTPOS2)

--CHECK WHETHER 'WHERE' CONDITION REQUIRED FOR INSERT SQL

IF LEFT(@SSTR, 5) = 'WHEN '

BEGIN

SET @SUB_QUERY2 = @SUB_QUERY + @SAND + SUBSTRING(@SSTR, 5, 2001)

SET @LASTPOS2 = @LASTPOS

SET @LASTPOS3 = CHARINDEX(@DELIMITER, @INSERT_PART, @LASTPOS+LEN(@DELIMITER))

IF @LASTPOS3 = 0

SET @SSTR = SUBSTRING(@INSERT_PART, @LASTPOS2+LEN(@DELIMITER), 2001)

ELSE

SET @SSTR = SUBSTRING(@INSERT_PART, @LASTPOS2+LEN(@DELIMITER), @LASTPOS3 - (@LASTPOS2+LEN(@DELIMITER)))

SET @LASTPOS = @LASTPOS3

END

ELSE

BEGIN

SET @SUB_QUERY2 = @SUB_QUERY 

END

--CONSTRUCT ACTUAL INSERT SQL STRING

SET @SSTR2 = LEFT(@SSTR, CHARINDEX('VALUES', @SSTR)-1)

SET @SSTR3 = SUBSTRING(@SSTR, LEN(LEFT(@SSTR, CHARINDEX('VALUES', @SSTR)))+6, 2000)

SET @SSTR3 = REPLACE(@SSTR3, '(', '') 

SET @SSTR3 = REPLACE(@SSTR3, ')', '') 

SET @SSQL = 'INSERT ' + @SSTR2 + ' SELECT ' + @SSTR3 + ' FROM (' + @SUB_QUERY2 + ') ZXTABX1 '

--EXECUTE THE CONSTRUCTED INSERT SQL STRING

EXEC (@SSQL)

--CHECK FOR ERRORS, RETURN -1 IF ANY ERRORS

IF @@ERROR > 0

BEGIN

ROLLBACK TRANSACTION MULTIINSERTS

SET @ERRORMESSAGE = 'Error while inserting the data'

RETURN -1

END

--CHECK WHETHER ALL THE TABLES IN 'MULTIPLE TABLE' LIST OVER 

IF @LASTPOS = 0

BREAK 

SET @LASTPOS = @LASTPOS + LEN(@DELIMITER)

END

--LOOP ENDS

--FINISHED SUCCESSFULLY, COMMIT THE TRANSACTION 

COMMIT TRANSACTION MULTIINSERTS

RETURN 0

GO

Parameters

Returns

Returns 0 on successful execution.

Returns –1 on unsuccessful execution with error message in @ErrorMessage input/output parameter

Algorithm

a) Accepts parameters for Source dataset, destination table with/without conditions, and the delimiter string that delimits the table, column names and where conditions.

b) Check the parameters passed, if the information is improper or incomplete, return error.

c) Check whether the subquery i.e. source data set has the where condition in the Query, this is to identify whether to add "And" or "Where" as condition if the user has given any conditions in Source sub query itself.

d) Loop till the insertion of Rows into destination tables is completed.

• Get the sub string of Multiple Table insertion string by using the Delimiter. The character position of the Delimiter is recorded in a varialbe, later it is used to find the next delimiter to extract either "When" or "Into" sub string.
• If the extracted sub string starts with 'When ' that means user is giving a filter condition while inserting rows into that particular table. Include that filter condition to the source dataset query.
• The next delimited part contains the column name and value list that needs to be inserted into a table. Manipulate the Destination table parameter to construct an "Insert" SQL statement.
• Execute the constructed Insert statement, and check for errors.
• Exit the loop if the insertion to multiple tables finished the last insertion.

Base logic in SP

Inserting Rows Using INSERT...SELECT.

The “Insert..Select” sql statement is constructing using @Insert_part parameter with little manipulation.

Example

This example uses “Employee” table in Northwind database. The structure(without constraints) of Employee table is copied to Employee2 and Employee3 to try out an example.

This example copies the LastName, FirstName data from Employees table 

To Employees1 – If the EmployeeID in Employees table is less than 5

To Employees2 – if the EmployeeID in Employees table is greater than 4

DECLARE @DELIMITER AS VARCHAR(200)
DECLARE @INSERT_PART AS VARCHAR(2000)
SET @DELIMITER = 'ZZZYYYXXX'
SET @INSERT_PART = 'WHEN EMPLOYEEID < 5' + @DELIMITER + 'INTO EMPLOYEES1 (LASTNAME, FIRSTNAME
VALUES (LASTNAME, FIRSTNAME)' + @DELIMITER + 'WHEN EMPLOYEEID >4' + @DELIMITER + 'INTO EMPLOYEES2 (LASTNAME, FIRSTNAME) VALUES (LASTNAME, FIRSTNAME)'
EXEC SP_MULTI_INSERTS 'SELECT EMPLOYEEID, LASTNAME, FIRSTNAME FROM EMPLOYEES', @INSERT_PART, @DELIMITER, ''

Result

(EmployeeID in Employee1 and Employee2 table is generated because it is an Identity column, increments by 1)

In this example, rows will be inserted into the SalaryHistory table only when

the value of the Salary is greater than 30000 (the annual salary of the employee

is more than 30,000). Rows will not be inserted into the ManagerHistory table

unless the manager ID is 200.

DECLARE @DELIMITER AS VARCHAR(2000)

DECLARE @INSERT_PART AS VARCHAR(2000)

SET @DELIMITER = 'ZZZYYYXXX'

SET @INSERT_PART = 'WHEN Salary > 30000' + @DELIMITER + 'INTO SalaryHistory

VALUES (empid, datehired, salary) ' + @DELIMITER + 'WHEN MgrID = 200' +

@DELIMITER + 'INTO ManagerHistory VALUES (empid, mgrid, SYSDATE)'

EXEC SP_MULTI_INSERTS 'SELECT EmployeeID EMPID, HireDate DATEHIRED,(Sal*12)

SALARY, ManagerID MGRID FROM Employees WHERE DeptID = 100', @INSERT_PART,

@DELIMITER, ''

Usage

• To achieve insertion to multiple tables in a single shot. As the functionality is written in a Stored Procedure, the task is performed little faster.
• It is has similar functionality of Oracle 9i “Multi Table Insert” feature, you can use this as an alternate if you are migrating from Oracle 9i to MS SQL Server 2000. This SP is more tuned to accept Column names of Tables in Insert Parameter, and you can give condition to specific/all tables that participates in Multi table insert Destination part.

Note

Maintain the sequence of “When”(Optional) and “Into” part in @Insert_Part parameter with proper delimiter after every “When” and “Into” key words.

- RAGHAVENDRA NARAYANA Raghavendra.Narayana@thomson.com