Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

multi table insert using stored procedure Expand / Collapse
Author
Message
Posted Friday, December 23, 2011 12:56 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:12 AM
Points: 631, Visits: 1,470
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?
Post #1226117
Posted Friday, December 23, 2011 1:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 2,013, Visits: 1,589
Please modify his SP with following (for debugging only)...

PRINT @SSQL -- Debugging
--EXEC (@SSQL) -- Execution

Please post back the result.


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1226118
Posted Friday, December 23, 2011 7:50 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:12 AM
Points: 631, Visits: 1,470
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'.

Post #1226250
Posted Friday, December 23, 2011 7:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 2,013, Visits: 1,589
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

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1226259
Posted Saturday, December 24, 2011 11:38 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:12 AM
Points: 631, Visits: 1,470
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?
Post #1226483
Posted Sunday, December 25, 2011 4:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 2,013, Visits: 1,589
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.


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1226547
Posted Monday, December 26, 2011 6:12 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:12 AM
Points: 631, Visits: 1,470
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

Post #1226725
Posted Monday, December 26, 2011 11:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 2,013, Visits: 1,589
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.


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1226758
Posted Tuesday, December 27, 2011 12:03 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:12 AM
Points: 631, Visits: 1,470
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?
Post #1226770
Posted Tuesday, December 27, 2011 12:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 2,013, Visits: 1,589
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.


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1226776
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse