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
| Parameter Name | Description |
| @SUB_QUERY | Source data set. A query that returns the desired rows that you want to insert to multiple tables. |
| @INSERT_PART | Column names and values of condition and insert part of Insert SQL statement |
| @DELIMITER | Delimiter value that delimits multiple inserts and where conditions |
| @ErrorMessage | [INPUT/OUTPUT Parameter]Any error during the SP execution. |
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
