December 23, 2011 at 12:56 am
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?
December 23, 2011 at 1:27 am
Please modify his SP with following (for debugging only)...
PRINT @SSQL -- Debugging
--EXEC (@SSQL) -- Execution
Please post back the result.
December 23, 2011 at 7:50 am
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'.
December 23, 2011 at 7:59 am
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
December 24, 2011 at 11:38 am
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?
December 25, 2011 at 4:01 am
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.
December 26, 2011 at 6:12 pm
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
December 26, 2011 at 11:40 pm
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:
December 27, 2011 at 12:03 am
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?
December 27, 2011 at 12:16 am
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.
December 27, 2011 at 12:23 am
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
December 27, 2011 at 12:33 am
I was wrong, Employees1 doesn't have an identity. Let me change it so it does and re-run statement.
December 27, 2011 at 12:40 am
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.
December 27, 2011 at 12:53 am
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. 😀
December 27, 2011 at 1:24 am
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