Get @@Identity while doing a multiple insert into

[ShawnJClapper]

I'm getting sql timeout errors with this code, so I'm wondering if someone could help me make it smaller. I was wondering if there was a way to grab the @@Identity while inserting records so I didn't have to do the third statement which searches the key column for a unique id. Here is what I'm looking at:

currentSelect="select distinct sales_price, sales_date from tblFISData WHERE not exists (select salePrice, saleDate  from tblsales where tblFISData.sales_price=tblsales.salePrice and tblFISData.sales_date=tblsales.saleDate)"

insertCommand.CommandText = "Insert into tblsales (salePrice,saleDate)  "&currentSelect

insertCommand.Execute()



currentSelect="select distinct lender, rate_type,loan_type,mortgage_years,transaction_type,mortgage_amount from tblFISData WHERE not exists (select *  from tblLoans where tblFISData.lender=tblLoans.lender and tblFISData.rate_type=tblLoans.rateType and tblFISData.loan_type=tblLoans.loanType and tblFISData.mortgage_years=tblLoans.mortLength and   tblFISData.transaction_type=tblLoans.transType and tblFISData.mortgage_amount=tblLoans.mortAmount )"

insertCommand.CommandText = "Insert into tblLoans (lender,rateType,loanType,mortLength,transType,mortAmount)  "&currentSelect

insertCommand.Execute()



currentSelect="select tblLoans.loanID, tblsales.saleID from tblLoans,tblsales,tblFISData WHERE tblFISData.lender=tblLoans.lender and tblFISData.rate_type=tblLoans.rateType and tblFISData.loan_type=tblLoans.loanType and tblFISData.mortgage_years=tblLoans.mortLength and tblFISData.transaction_type=tblLoans.transType and tblFISData.mortgage_amount=tblLoans.mortAmount and tblFISData.sales_price=tblsales.salePrice and tblFISData.sales_date=tblsales.saleDate and not exists (select * from tblHomes where  tblLoans.loanID=tblHomes.loanID and tblsales.saleID=tblHomes.saleID)"

insertCommand.CommandText = "Insert into tblHomes (loanID,saleID)  "&currentSelect

insertCommand.Execute()

The tblFISData is a temporary table that the data is loaded into and then sorted to the appropriate tables. The first two inserts work fine, however I'm running into problems on the third insert and maybe because it's such a large query. What happens is if I start with a fresh tblHomes date (nothing in it), the script runs fine, however if I try to run this a second time after all of the data is loaded into tblHomes I recieve an sql timeout error. Any suggestions?

 

[SQLSister]

OK, in the first place you never and I do mean never, ever want the value for @@identity. If anyone ever places a trigger on your table that inserts to a different table with an identity field, this is the identity that will be returned not the one you just inserted. Using @@identity is a prescription for data integrity problems.

Use scope_identity() instead. Use a stored procedure instead of whatever kind of code that is above. To ensure the proper value returned the call for the value must come imediately after the step that did the insert and must be part of the same batch of statememts or you could be getting a wrong value.

In a stored procedure the best thing to do is set up a variable to store the value in, do the insert, then populate the value, then use it later on. An example follows"

Declare @idvalue as int
insert table1 (field2, field3)
Values ('test', 'test')
set @idvalue = scope_identity()

insert table 2 (field1, field2)
Values (@idvalue, 12)

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[ShawnJClapper]

Thanks. I understand what you mean about not using the @@identity. If I can grab the right identity through your method, I think my problem still lies in the fact that I am doing a multiple rows type insert statment like:

INSERT INTO tblName SELECT something FROM otherTable WHERE something=somethingelse

By this I mean that my insert statement is not going to just insert one row it's going to insert a whole lot of them in one statement as I'm dealing with huge amounts of data and need to avoid looping at all costs. The way I'm getting the unique keys now is through the last statement you can see that I'm just doing a big select that checks that all data is what I need then inserting that key into the table (also a large type insert not just one line). However I'm running into an error:

[Microsoft][ODBC SQL Server Driver]Timeout expired

If I try to run this script AFTER there is data in the tblHomes table.

Also I'll have to say that I'm not that versed in stored procedures yet so it would be best if I could do this in asp and then somewhere down the line move this into stored procedures once I get the hang of them more.

 

[ShawnJClapper]

I converted my insert statements into a stored procedure and still getting the error:

[Microsoft][ODBC SQL Server Driver]Timeout expired

-Shawn

 

[Catadmin]

Run SQL PRofiler while you're doing this. It should let you know at about what point this SP is timing out.

Also, double check your code against the Execution Plan in Query Analyzer. Look for any Index Scans or Table Scans or Missing Statistics.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ShawnJClapper]

Ok, I'll have to find out what those two tools are first. I wonder if 300,000 records in the tblFISData is just too much to try and sort through in one statement? For instance, running just the last insert by itself still causes a timeout error.

 

[Catadmin]

Depends on what else is going on with SQL, the Windows OS or your network. Any one of them could be the problem. WOn't know for sure, though, until you try one of the tools.

If you copy your query (without the Create Procedure statement and make sure to declare your variables) into QA, go to Tools -> Display Estimated Execution Plan.

Profiler is under the Programs -> Microsoft SQL Server group.

You should be able to look up both of these in Books Online.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Catadmin]

go to Tools -> Display Estimated Execution Plan

ACK! My bad. Meant go to Query -> Display Estimated Execution Plan.

Sorry for the confusion.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[SQLSister]

Do you have a way to identify a record uniquely without the key field? You can do a select based on the natural key to get the identity. So Insert the records and the natural key is Company Name, Location. Then you select the ID field based on a join to the orginial record source using the natural key. (Did that make sense, too tired to write code to explain better.)

If you have a lot of records it is better to do the inserting and getting of the identity's in batches. There isan FAQ in how to do this. This avoids the timeout problem.

I will point out you used the where not exists in your query which is not an efficient method. Might be better to use a left join and then a where clause to identify the records not in the right side of the join.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief