multi table insert using stored procedure

  • I have made a copy of Narayana's sproc (http://www.sqlservercentral.com/articles/Miscellaneous/multipletableinsert/1194/ ) on my Northwind db, in order to learn how to create a sproc to insert data in multiple tables at the same time.

    Following his directions, I've made two copies of Employees table calling them Employees1 and Employee2.

    However, when I exec Narajan's sp_multi_inserts sproc on Northwind database I get the following error:

    Msg 156, Level 15, State 1, Line 2

    Incorrect syntax near the keyword 'SELECT'.

    His execute statement looks as follows:

    USE Northwind

    GO

    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, ''

    I do not see where near the 'Select' keyword there is a syntax error. Do you?

  • Please modify his SP with following (for debugging only)...

    PRINT @SSQL -- Debugging

    --EXEC (@SSQL) -- Execution

    Please post back the result.

  • Thanks Dev, like this? I added Print statements for the variables. I do not understand the purpose of @DELIMITER or have never seen WHEN used in SQL statements. Nevertheless....see below debug and result.

    EXECUTED:

    USE Northwind

    GO

    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)'

    PRINT @DELIMITER --debug

    PRINT @INSERT_PART --debug

    EXEC SP_MULTI_INSERTS 'SELECT EMPLOYEEID, LASTNAME, FIRSTNAME FROM EMPLOYEES', @INSERT_PART, @DELIMITER, ''

    RESULT:

    ZZZYYYXXX

    WHEN EMPLOYEEID < 5ZZZYYYXXXINTO EMPLOYEES1 (LASTNAME, FIRSTNAME

    VALUES (LASTNAME, FIRSTNAME)ZZZYYYXXXWHEN EMPLOYEEID >4ZZZYYYXXXINTO EMPLOYEES2 (LASTNAME, FIRSTNAME) VALUES (LASTNAME, FIRSTNAME)

    Msg 156, Level 15, State 1, Line 2

    Incorrect syntax near the keyword 'SELECT'.

  • You missed my point. Anyways, execute following in your system (another window). Then execute your query in first window.

    ALTER PROCEDURE SP_MULTI_INSERTS

    (@SUB_QUERY AS VARCHAR(2000),

    @INSERT_PART AS VARCHAR(2000),

    @DELIMITER AS VARCHAR(100),

    @ERRORMESSAGE AS VARCHAR(2000)

    )

    AS

    --By Narayana Raghavendra

    --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

    PRINT @SSQL -- DEBUGGING

    --EXEC (@SSQL) -- EXECUTION

    --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

  • Dev, I did this already.

    I executed the stored procedure and then the query.

    Will you kindly explain you initial point, so that I can understand where you were going with it?

  • hxkresl (12/24/2011)


    Dev, I did this already.

    I executed the stored procedure and then the query.

    Will you kindly explain you initial point, so that I can understand where you were going with it?

    You are calling a Stored Procedure that is accepting Query components as parameters, generating a query at runtime & executing.

    For debugging purpose I modified that SP. It will just print the SQL query, won’t execute it.

    --EXECUTE THE CONSTRUCTED INSERT SQL STRING

    PRINT @SSQL -- DEBUGGING

    --EXEC (@SSQL) -- EXECUTION

    When you will run your code it will print the BAD query that is causing error. And many of us (in SSC forum) will be able to help you.

  • ok, i understand now. Thank you for help in placement of the debug statement.

    Now, I'm trying to deconstruct the sproc myself and getting tripped up on how the actual insert sql string is being formed. I'm especially confused about the use of delimiters, let alone the syntax of the resulting insert statement.

    Here's the output of the query when I modify the sproc to print the @SSQL instead of execute it.

    INSERT INTO EMPLOYEES1 (LASTNAME, FIRSTNAME) SELECT LASTNAME, FIRSTNAME FROM (SELECT EMPLOYEEID, LASTNAME, FIRSTNAME FROM EMPLOYEES WHERE EMPLOYEEID < 5) ZXTABX1

    INSERT INTO EMPLOYEES2 (LASTNAME, FIRSTNAME) SELECT LASTNAME, FIRSTNAME FROM (SELECT EMPLOYEEID, LASTNAME, FIRSTNAME FROM EMPLOYEES WHERE EMPLOYEEID >4) ZXTABX1

  • I don't see any issue in following query.

    INSERT INTO EMPLOYEES1 (LASTNAME, FIRSTNAME)

    SELECT LASTNAME, FIRSTNAME

    FROM

    (SELECT EMPLOYEEID, LASTNAME, FIRSTNAME

    FROM EMPLOYEES

    WHERE EMPLOYEEID < 5

    ) ZXTABX1

    Do you get any errors on it? What input parameters you are passing? Output of the query is dependent of Inputs and I am not able to correlate it. :unsure:

  • Dev,

    When you re-org the query like that it is much more readable. Can we momentarily just focus on this insert query? The following query should insert into employees1 table values for the only two 'not null' columns (besides the identity column) in the table:

    INSERT INTO EMPLOYEES1 (LASTNAME, FIRSTNAME)

    SELECT LASTNAME, FIRSTNAME

    FROM

    (SELECT EMPLOYEEID, LASTNAME, FIRSTNAME

    FROM EMPLOYEES

    WHERE EMPLOYEEID < 5

    ) ZXTABX1

    It generates following error:

    Msg 515, Level 16, State 2, Line 1

    Cannot insert the value NULL into column 'EmployeeID', table 'Northwind.dbo.Employees1'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    1. True enough the employeeID column is not null, but shouldn't it auto-populate?

    2. What is the 'ZXTABX1' delimiter? I tried replacing it with semi-column and I get 'Incorrect syntax near ';'.'

    3. why doesn't the insert work?

  • 1. True enough the employeeID column is not null, but shouldn't it auto-populate?

    If it's identity column, then yes it should be auto populated. Please crosscheck on Identity column for Employee1 table. If possible post DDL here.

    2. What is the 'ZXTABX1' delimiter? I tried replacing it with semi-column and I get 'Incorrect syntax near ';'.'

    It's just an alias for sub query (derived table).

    3. why doesn't the insert work?

    Let me see the DDL.

  • Thanks so much. Here's the DDL.

    USE [Northwind]

    GO

    /****** Object: Table [dbo].[Employees1] Script Date: 12/26/2011 23:23:03 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[Employees1](

    [EmployeeID] [int] NOT NULL,

    [LastName] [nvarchar](20) NOT NULL,

    [FirstName] [nvarchar](10) NOT NULL,

    [Title] [nvarchar](30) NULL,

    [TitleOfCourtesy] [nvarchar](25) NULL,

    [BirthDate] [datetime] NULL,

    [HireDate] [datetime] NULL,

    [Address] [nvarchar](60) NULL,

    [City] [nvarchar](15) NULL,

    [Region] [nvarchar](15) NULL,

    [PostalCode] [nvarchar](10) NULL,

    [Country] [nvarchar](15) NULL,

    [HomePhone] [nvarchar](24) NULL,

    [Extension] [nvarchar](4) NULL,

    [Photo] [image] NULL,

    [Notes] [ntext] NULL,

    [ReportsTo] [int] NULL,

    [PhotoPath] [nvarchar](255) NULL

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

  • I was wrong, Employees1 doesn't have an identity. Let me change it so it does and re-run statement.

  • and now the sproc works too. That is too simply stuuppppiiiid (on my part, ofcourse :pinch:).

    Dev, please, can you point me to a resource that will explain more about usage of the delimiter ZXTABX1? I have never seen this before.

  • hxkresl (12/27/2011)


    and now the sproc works too. That is too simply stuuppppiiiid.

    It was not stupid just an oversight. It happens sometimes. Please don't discourage yourself. 🙂

    Dev, please, can you point me to a resource that will explain more about usage of the delimiter ZXTABX1? I have never seen this before.

    It's not a standard term. If you see the Stored Procedure you will find some non-standard variable declarations (@SSTR2, @SSTR3 etc) etc. Narayana was trying to explain the functionality of 'Multiple Table Insert’ & might never thought that someone would try to use the code as it is.

    IMO the delimiter is just a derived table (or sub query alias). The only way to understand it is to analyze his SP or ask him personally. 😀

  • Thank you Dev.

    Last question to tie things up for me. What brought me to his sproc was the question: 'how to insert data into multiple tables, using T-SQL'. Would it be true that this is possible to accomplish only one of two ways:

    1. using multiple sql statements executed as one query.

    OR

    2. using T-SQL programming logic executed as a sproc.

    Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply