Link to home
Start Free TrialLog in
Avatar of universalglove
universalgloveFlag for United States of America

asked on

Combining Different Statements in MS SQL

I haven't been able to clarify yet to my satisfaction:

Is it possible to combine INSERT/UPDATE and SELECT statements in the same SQL query using MS SQL?

I'm trying to combine a complex set of SQL statements (in VBScript / ASP, not that it should matter), so I can run them all at once using one connection to the SQL server.
I've tried inserting GO between each statement.
I've tried just ending lines with "; " & vbCrLf

Is this sort of thing only possible using MERGE or views or stored procedures?

SQLS = "DECLARE @vListingID int; " & vbCrLf

SQLS = SQLS & "INSERT INTO Table1 (MemberID, ListingURL, ListingTitle, ListingDate, ListingContent, ListingImgURL) " & _
"VALUES (" & intMemberID & ", " & _
"'" & strItemURL & "', " & _
"'" & strItemTitle & "', " & _
"'" & strItemDate & "', " & _
"'" & strItemContent & "', " & _
"'" & arrItemImgs(0) & "'); " & vbCrLf

SQLS = SQLS & "SET @vListingID = (SELECT ListingID FROM Table1 WHERE ListingID = IDENT_CURRENT('Table1')); " & vbCrLf

for each child in arrItemImgs
	SQLS = SQLS & "INSERT INTO Table2 (ListingID, ImgURL, CurrentFeature) " & _
	"VALUES (@vListingID, " & _
	"'" & child & "', " & _
	"0, " & _
	"0); " & vbCrLf
next

Open in new window


In the example, I was hoping to INSERT a record, then immediately select IDENT_CURRENT() for that table, so I could, in a different table, associate a separate set of info with the newly inserted record; and then INSERT those records.
Everything except the 1st line would be looped through numerous times.

That's where I was hoping to avoid opening and closing connections to the database, and instead send just one compiled set of statements.

I may have other errors in my code, because I'm still figuring a lot of this out, and haven't been able to test this out fully yet.
SOLUTION
Avatar of Saurabh Bhadauria
Saurabh Bhadauria
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Is it possible to combine INSERT/UPDATE and SELECT statements in the same SQL query using MS SQL?
Yes, you can.  Read up on the OUTPUT clause.  If it is not clear, let me know and I can clarify.

immediately select IDENT_CURRENT() for that table
Never use IDENT_CURRENT() that is a very bad idea.  Use SCOPE_IDENTITY() instead.
>> Never use IDENT_CURRENT() that is a very bad idea.  Use SCOPE_IDENTITY() instead. <<

I agree that SCOPE_IDENTITY() should be used in this context, but not that IDENT_CURRENT() should never be used.  That's an overstatement.  IDENT_CURRENT is the most accurate method if you just want to know the highest identity value for a given table, regardless of how/where it was inserted.
Avatar of universalglove

ASKER

SCOPE_IDENTITY() makes sense, so long as I successfully get the full set of SQL statements into one batch.

OUTPUT ... read Microsoft's documentation and trying to read through another page or two. Not really following how I'd use it, since it sounds like I'd have to do something with the output, and that sounds like it makes me have to start getting into temp tables and such.

I'm having a hard time following anything at the moment anyway (throat and head cold), but I wanted to give y'all a reply. Doesn't help that I can only manage to look at these for short, sporadic moments throughout the day, either.

It's looking like stored procedures or transactions will have to be the way I go for what I can understand successfully right now. Either will work so long as GO and ; are respected.

Transactions require nesting or at least sequential transaction statements, correct, if I'm performing the insert and select "together"? Is that how they manage to handle combined statements? Otherwise, I don't see an advantage, and none of the examples I've found so far do more than a singular type of statement together. Plus, they always use a GO statement between transactions, or stored procedures. Either way, transactions not looking to me like they'll work, if GO has not already worked successfully on its own.

I'll quit being Mr. Pessimist and try some test queries...
Transactions maintain data integrity. It makes sure that a select the. An insert and then the update all succeeded for the data to be committed into the database, if any query fails, there will not be erroneous entries in a table ( orphan records).
Just wanting to drop a note that I'm trying to get back to this after getting halfway through writing up a test query, and Thanksgiving break, and illness and all...
Interesting!

Well, I've finally managed to accomplish what I was trying to do; and was led to it while trying to work with transactions.

SQLS = "DECLARE @vListingID int; " & vbCrLf

SQLS = SQLS & _
"BEGIN TRY" & vbCrLf & _
	"BEGIN TRANSACTION" & vbCrLf & _
		"INSERT INTO Table1 (MemberID, ListingURL, ListingTitle, ListingDate, ListingContent, ListingImgURL) " & _
		"VALUES (" & intMemberID & ", " & _
		"'" & strItemURL & "', " & _
		"'" & strItemTitle & "', " & _
		"'" & strItemDate & "', " & _
		"'" & strItemContent & "', " & _
		"'" & arrItemImgs(0) & "'); " & vbCrLf

		SQLS = SQLS & "SET @vListingID = ( SELECT ListingID FROM Table1 WHERE ListingID = SCOPE_IDENTITY() ); " & vbCrLf

		for each child in arrItemImgs
			SQLS = SQLS & "INSERT INTO Table2 (ListingID, ImgURL, CurrentFeature) " & _
			"VALUES (@vListingID, " & _
			"'" & child & "', " & _
			"0); " & vbCrLf
		next

SQLS = SQLS & _
	"COMMIT TRANSACTION" & vbCrLf & _
"END TRY" & vbCrLf & _
"BEGIN CATCH" & vbCrLf & _
	"PRINT 'ERROR - Transaction for GGBlogID " & arrGGFeedData(0) & " at " & arrGGFeedData(1) & " generated the following error: ' + ERROR_MESSAGE(); " & vbCrLf & _
	"PRINT '@GGListingID set to ' + @GGListingID; " & vbCrLf & _
	"IF (@@TRANCOUNT > 0)" & vbCrLf & _
		"ROLLBACK TRANSACTION" & vbCrLf & _
"END CATCH" & vbCrLf
SQLS = SQLS & "SET @GGListingID = NULL; " & vbCrLf

Open in new window


But, I don't know if that really answers all of my actual question.
I'm realizing, looking back at the code snippet I originally included, that I didn't have a SELECT statement except when setting the variable.
So I haven't truly included a "SELECT" statement by itself with the other INSERTS; but I am using a SELECT on a record immediately after inserting it, in the same batch of statements.

And in my testing, I did include a SELECT directly, as well as an UPDATE following it, based on the SELECT, all within the same TRANSACTION.
SQL didn't like that.

The code above generated errors when transactions failed; successfully inserted numerous records; etc. In other words, successful!

Anyway, not sure whether transactions was entirely the answer, but the larger picture of which this was a piece is working now!
You do not issue a "commit transaction" before your check on whether there were errors.
Lines 23,24.
Line 24 should be in the else line 31
If error, roll back, elae commit transaction
You should check after every insert to make sure it worked.
I.e the first error needs to be bailed out. There is no point in performing 30 additional inserts when the first one failed.
I see no reason you cannot do that with two INSERT statements instead of 1 + n INSERT statements, but if performance is not such a big deal you should be fine.
Huh. I apparently never submitted my last comment.
Sorry for the long time out.

arnold:
This is the logic and info I based the transactions on -
http://blog.ashodnakashian.com/2011/11/understanding-ms-sql-transactions-and-error-handling/

acperkins:
I think I see what you're saying. Pertains to the loop that creates an INSERT for each image, right? But all the image data could be combined into one INSERT?
I didn't know that, but I see examples now that I look it up.
Thanks.
I'll give priority to the TRANSACTION as solution, since I have that functional now.
But, I'm sure the stored procedures would also help the problem - I just haven't gotten to trying that yet and didn't take that route initially in experimentation. Meaning, I don't think there's anything wrong with it.