INSERT Failing

Question

Post reply

INSERT Failing

water490

SSC Eights!

Points: 918
More actions
May 2, 2023 at 5:34 am

#4182690
Hi everyone
I have a 1000 line SQL query that performs a complicated calculation. I have broken up the query into smaller pieces and each of these is a sub-calculation that is being stored in a physical table. I do this for debugging purposes. Before today, I was using a small set of data but today I ordered the full dataset so now I have many years of data. The query failed. The query is long so no point in sharing it. I will describe the problem and hopefully someone else has also experienced it and can give some helpful tips.
Format of the query:
```
INSERT INTO DBO.Table1
SELECTT6.UNDERLYING_SYMBOL, T6.QUOTE_DATE, T6.EXPIRATION, T6.ROOT_SYMBOL
FROM ...

INSERT INTO Table2
SELECTT6.UNDERLYING_SYMBOL, T6.QUOTE_DATE, T6.EXPIRATION, T6.ROOT_SYMBOL
FROM ...
```
If I run the stored proc then Table1 and Table2 are empty.
If I highlight the section for Table1 only (ie, just the sub-calculation) and run just this highlighted section then Table1 gets populated.
Why is the INSERT failing in the stored proc? It was working fine a few days ago. Not sure what happened today.
I even tried this and it doesn't work:
```
INSERT INTO DBO.Table1 (UNDERLYING_SYMBOL, QUOTE_DATE, EXPIRATION, ROOT_SYMBOL)
SELECTT6.UNDERLYING_SYMBOL, T6.QUOTE_DATE, T6.EXPIRATION, T6.ROOT_SYMBOL
FROM ...
```
What do you suggest I do to fix this?
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

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

Johan Bijnens SSC Guru Points: 135307 More actions · Answer 1

What's the actual error message you are getting ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Phil Parkin SSC Guru Points: 246873 More actions · Answer 2

Duplicate key / constraint violation? As requested above, please provide the text of the error message.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 3

Are there constraints on the tables you're inserting to? I'm with everyone else, not enough information to make a single suggestion, not even a guess.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jason A. Long SSC-Insane Points: 23785 More actions · Answer 4

Jason A. Long

SSC-Insane

Points: 23785

May 2, 2023 at 12:18 pm

#4182866

Try pulling a space between SELECT and T6...

water490 SSC Eights! Points: 918 More actions · Answer 5

thank you everyone for the reply. There is no error message on why it failed so that is why it is so puzzling. I am continuing to investigate to see what is going on..

Jeffrey Williams SSC Guru Points: 90286 More actions · Answer 6

Is the stored procedure in the same database - and is the manual execution in the same database?

You didn't share all of the relevant code. I don't see how you are clearing the tables before executing if you are - or if your insert is checking for existing data already or if you are using any variables. Or if those queries are joining multiple tables and how they are joined.

All of that could impact what data is selected - or if any data is selected. Especially if there are issues with how the variables are declared vs the data types of the columns being compared. For example, using a varchar/char data type and comparing to a date/time column.

Final thoughts: any time I hear that someone has a 1000+ stored procedure with multiple inserts/updates/deletes to perform some calculations my immediate instinct is that the code can be optimized. The first thing I look for is the initial insert into the 'working' table - and if that table has a slew of blank/null columns that are filled in at some later stage, it is almost certainly something that can be done in the initial query instead of performing a secondary update to 'calculate' the value.

It is often just as performant (if not better) to use CROSS/OUTER APPLY to generate the column data for those types of columns than it is to perform a later update.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Johan Bijnens SSC Guru Points: 135307 More actions · Answer 7

Start an appropriate XE session to monitor that sproc's execution

You need to debug this sproc, because it will hit some error somewhere and may not hand it over to the calling entity in the correct way