|
Do this in 2 steps, insert the master record, get the ID and then insert the details records.
I use 2 separate stored procs called from the DAL. The first one inserts the master record and returns the record inserted based on using scope_identity() to get the ID (you can return the ID only if you don't need the entire record.
The second proc inserts the detail records using the ID from the record/integer returned by the first proc.
You can write it into 1 proc but I find that irritating if you need to store more than 1 detail record.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Is it better to use cursors or is it better to use multiple stored procs to do the same. Because the same thing I can achieve by using Cursor in stored proc. But its really really slow. Like may be its processing 23000 records per 40 minutes.
I need to process 1 million records.
And as you said If I call the database for each insert, isnt it going to be more back and forth traffic from the application database server vice versa.
I tried my best to avoid cursors, only one way is to use function but I have to use my connection with some special permissions to execute Insert command within UDF. It seems its not easy in the organization.
I am trying to put the insert logic in the stored proc and calling that stored proc within fuction but it is asking for the below permission. I am not sure if I can really do that in this way but when I am trying it failed to execute with the below error. Then I went for the Cursor approach but its running for too long. Please if you can please advice me something, I have to finish it as soon as possible.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
If you are inserting 1m records through a UI then you have bigger problems, I'd seriously look at your business logic.
Where are you getting your 1m records from?
Why are you inserting then one at a time?
Nothing is going insert 1m single records with any acceptable speed. You need to look at how to get the bulk data into your database and then process the data in bulk!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I am getting the records from two different databases and I have to put them into another database. I have to run a nightly job to perform this operation.
I am not saying I am inserting one record at a time, I am doing it using cursor right now. But its taking too long time. Is there any other approach to do it.
The insert process has to have the logic that I explained in the thread.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
indian143 wrote: I am not saying I am inserting one record at a time, I am doing it using cursor
right now
Yes you are, a cursor implies you insert the master then insert the detail and loop to the next master!
How are you transporting from the 2 databases to your DB?
Idea
Set up a view that joins the master/detail in the source systems and load all the records into a staging table in the destination DB, then process the inserts in bulk using 2 queries (if the DBs are linked then use the view direct).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Is it ok if I do it using xp_CmdShell or OpenRowSet functions, because I need to have insert logic in my function. If there is any other approach without using these three CmdShell, OpenRowSet, Cursor, please give me that advice.
staging is not an option for me right now.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
indian143 wrote: staging is not an option for me right now.
IMHO you are screwed, your thinking is too small, no method that processes a single record at a time is going to perform when attempting to process 1m records! It seems like you are being artificially constrained by policy/political issues not technical ones.
If you can't use a staging process on the target DB then create a staging DB on the same server that does the processing and shoves the data into the target DB.
I have no experience with either openrowset or xp_cmdshell in a data loading context, never used them for that - ever!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am sorry, I didnt mean to discard your advice. But I am unable to decide. Time is very less for me. Can I use SSIS package for this. If it is can you please let me know how to do by just some psuedo code.
I know SSIS a little bit but I am not an expert.
Thanks in advance.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
A little pseudo code is not going to help you design an SSIS package to do this job. Besides my knowledge of SSIS is possibly less than your!
You need to resolve you transport issues (ie how am I going to get 1m records from 2 DBs into 1) before deciding on the tool to use! 1 record at a time is not going to cut it!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ok  . Is it possible by using TempTable or Table Variable. If it can, please give me some Idea. I have to implement some logic in the Import process.
The logic is if there is an existing value field in the Destination table I have to use that value only in the EntityId foreign key place, if there is no value field exists then I have to insert a record in the other Primary key table and use that same primary key value into this record insert.
If that can happen by using temp table then that would really help me. Its kind of really complex situation. Please if you can help me by giving some idea that would help me a lot.
Thanks in advance.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
Hi,
I didnt want to create another thread for this again so I am reusing the existing thread. Sorry if it causes inconvenience to you.
I have implemented as you said, there are around 3 million records. I created a staging table for loading the data temporary. Now I have to insert or update all this data in to the actual table and delete data from staging.
Now the problem that I am facing is, for certain conditions only we have to insert data but for certain conditions I have to update the data depending upon some logic.
Everything I tried has failed or took enormously huge amount of time except SSIS that too only if I use just loading with mapped columns. But that is not going to help me a lot because I have to decide insert or update depending upon some logic.
My questions here are:
1. Can I write logic in SSIS to update if the record already exists and certains columns are same, if so how can I do that.
2. If I incorporate logic into SSIS, can the performance be same as simple import process. Because the simple import process in SSIS uses the bulk insert mechanism with batch inserts of records.
I tried in the following way, but it is taking a huge amount of time to insert 3 million records. Like 7 or 8 hrs.
UPDATE [IdentityResolutionService].[dbo].[Entity]
SET [SystemOfRecordId] = t2.[SystemOfRecordId]
from [IdentityResolutionService].[dbo].[Entity] as t1
inner join
(select DISTINCT [EntityId], SystemOfRecordId from IdentityResolutionService.dbo.Identifier_Staging where EntityId
in (select EntityId from [IdentityResolutionService].[dbo].[Entity])) as t2
on t1.EntityId=t2.EntityId
WHERE t2.EntityId = t1.EntityId and t1.SystemOfRecordId=@ACESSystemOfRecordId
INSERT INTO [IdentityResolutionService].[dbo].[Entity]
([EntityId]
,[SystemOfRecordId])
select DISTINCT [EntityId], [SystemOfRecordId] from IdentityResolutionService.dbo.Identifier_Staging where EntityId
not in (select EntityId from [IdentityResolutionService].[dbo].[Entity])
and [SystemOfRecordId] not in (select [SystemOfRecordId] from [IdentityResolutionService].[dbo].[Entity])
Thanks in advance.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
Abdul,
My questions here are:
1. Can I write logic in SSIS to update if the record already exists and certains columns are same, if so how can I do that.
2. If I incorporate logic into SSIS, can the performance be same as simple import process. Because the simple import process in SSIS uses the bulk insert mechanism with batch inserts of records.
The answer to your question isn't as simple as you may think. In the first place, your use of the phrase "can I" adds to the problem because it seems that you are very much an SSIS novice. so let me approach this in anothe way.
1. An SSIS package can be created that would handle your problem. The trick is to use the MERGE statement that is available in T-SQL. You can also use a pair of SQL statements such as those that you provided in your most recent post. Howver, your SQL code may be impacting our performance somewhat because you are using the IN (SELECT....) instead of using the INNER JOIN approach to determining which rows to update.
2. A straight forward bulk load is always going to be the fastest choice; however, since you need to UPDATE some rows and INSERT others, that option isn't available to you,so there's no point in worrying about that. 
That being said, though, if this is your first SSIS package, I would suggest that you create a temp table into which you the INSERT both updated versions of the existing rows and then the new rows (with the IDENTITY INSERT ON, so that you can keep the IDs of the existing rows), and then truncate the existing table and reload it from the temp table.
|
|
|
|
|
select Category_name,Subcategory_name,Title from Foodbazaar_tbl where Foodbazaar_tbl.Category_name Like'food%' UNION ALL select Category_name,Subcategory_name,Title from Weightmanagement_tbl where Weightmanagement_tbl.Category_name Like'food%'
buy using this query am getting the output of combination data from all tables.
now i require the count of Category_name
what is the query for getting the count of category_name to above output
plz replay........
bhairava prasad
|
|
|
|
|
select COUNT(Category_name) from TableName where Category_name like '%SearchPrefix%'
|
|
|
|
|
Select count(*) Records. Category
From (PutYourOriginalQueryHere)
group by Category
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi,
select COUNT(Category_name) from TableName where Category_name like '%food%'
group by Category
S Azarudhin
|
|
|
|
|
Hi,
i have the following code:
SELECT visit_investigations.*,
price_list_items.*,
employee.employee_short_name as created_by
FROM visit_investigations
INNER JOIN price_list_items ON price_list_items.item_id = visit_investigations.item_id
INNER JOIN employee ON visit_investigations.created_user = employee.employee_number
WHERE visit_investigation_id = param_visit_investigation_id;
where created by is the employee created the record andit will never be blank.
I want to add the field visit_investigations.result_by for the user who submited the result. this will be a number representing the employee.employee_number and it might be blank for records without result yet.
How can I do this?
Technology News @ www.JassimRahma.com
|
|
|
|
|
I beleve you want to join to the employee table more than once.
It would be something like:
INNER JOIN employee e1 ON visit_investigations.created_user = e1.employee_number
INNER JOIN employee e2 ON <someother foreign="" key="" to="" the="" employee="" table=""> = e2.employee_number
Hope that helps. 
|
|
|
|
|
As David has suggested you need a 2nd join to the employee table but make it a LEFT join if there are missing records.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Issue occrue on select top 2 like this
when run the following query then show the following result
SELECT u.ElectionDtlID,u.PartyShortNameEng,u.ConstituencyName,u.VotesObtained
FROM tblElectionsDetail u where u.ConstituencyName='NA 108' and u.electionyear='2013'
and u.VotesObtained > 0
RESULT:
ID NameEng Constituency VotesObtained
10864 PPPP NA 108 29883
10865 PMLN NA 108 73789
10866 PTI NA 108 25406
10867 JI NA 108 18270
10868 Ind NA 108 85009
But now i want to get only top result of Maximum VotesObtained Candidate
like this :
Query:
select top 2 VotesObtained from tblElectionsDetail where ConstituencyName='NA 108';
and electionyear='2013'; order by VotesObtained desc
Then this issue the following Result
OutPut Table:
VotesObtained
85009
73789
this is working good and also result show correct but when i want to show max/top 2 recrod but order by asc then it's working correct like this
Query:
select top 2 VotesObtained from tblElectionsDetail where ConstituencyName='na 108'
and electionyear='2013' order by VotesObtained asc
the result of the query like this
OutPut Table:
VotesObtained
0
0
so i wan to get the result like this.
VotesObtained
73789
85009
any body can help me ?
-- modified 29-May-13 8:47am.
|
|
|
|
|
SELECT everything from your resultset in reverse order.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
select T.ElectionDtlID, T.PartyShortNameEng, T.ConstituencyName, T.VotesObtained
FROM (select * from tblElectionsDetail where ConstituencyName='NA 108' and electionyear='2013' order by VotesObtained desc limit 2) T order by T.VotesObtained asc;
--
Arman
|
|
|
|
|
hi experts,
can anyone guide me ,how to decrypt the encrypted user defined function in sql server 2008
|
|
|
|
|
Your question makes no sense: are you saying that the functions, itself, is encrypted and you need to decrypt it or the output from the function? In either case, speak to the original author of the function: there is no earthly way that anyone here could possibly guess at the encryption used.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Those who seek perfection will only find imperfection
nils illegitimus carborundum
me, me, me
me, in pictures
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
