Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Multiple Table Insert

By Narayana Raghavendra,

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

Total article views: 14024 | Views in the last 30 days: 13
 
Related Articles
FORUM

Pipe delimited VARCHAR column

Need to expand a pipe delimited string stored in a VARCHAR(4096)

SCRIPT

Delimited String Parser

Parses delimited string into a table of up to 9 varchar fields.

FORUM

Insert query

Bulk Insert query

FORUM

Extracting Rows from Delimited Strings

Extracting Rows from Delimited Strings

FORUM

Tricky ...VARCHAR

VARCHAR logics

Tags
miscellaneous    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones