Database

31-May-13 14:53

Hi,

I have a Master table A(AId, Value) and Detail table B (BId, Value, AId). AId and BId are Identity increment columns. The value field in B comes from some other table C, value field in A table is always the same value: 1.

When I am inserting Detail table B, I am doing it with a select statement on the other table C. The problem is filling the foreign key AId value in Table B. First thing to fill it is, if there is some AId already there in the B table then we have to assign that AId to it.

If there is no AId in the B table for the correspoding value, we have to insert a record in the A table and that AId should be assigned to the AId field of that row. For this given scenarion I am struggling to write a stored procedure.

I tried to write another function to be called inside the stored proc, but funtions in SQL Server dont support Insert inside the UDF.

Now trying to use Stored Procs return values in the Insert statement, I dont know so far no success. I am searching too. If anybody can please help me that would be really great.

Thanks in Advance.

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

Mycroft Holmes1-Jun-13 2:35

1-Jun-13 2:35

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

indian1431-Jun-13 17:38

1-Jun-13 17:38

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

Mycroft Holmes1-Jun-13 17:59

1-Jun-13 17:59

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

indian1432-Jun-13 12:18

2-Jun-13 12:18

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

Mycroft Holmes2-Jun-13 12:35

2-Jun-13 12:35

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

indian1432-Jun-13 13:54

2-Jun-13 13:54

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

Mycroft Holmes2-Jun-13 14:29

2-Jun-13 14:29

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

indian1432-Jun-13 19:15

2-Jun-13 19:15

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

Mycroft Holmes2-Jun-13 20:02

2-Jun-13 20:02

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

indian1433-Jun-13 7:12

3-Jun-13 7:12

. 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

indian14311-Jun-13 7:59

11-Jun-13 7:59

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

Ralph D. Wilson II10-Jul-13 9:42

Ralph D. Wilson II

10-Jul-13 9:42

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. Wink | ;-)

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. Wink | ;-)

query error

Bhairava Prasad30-May-13 2:03

Bhairava Prasad

30-May-13 2:03

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

Re: query error

Anurag Sinha V30-May-13 2:25

Anurag Sinha V

30-May-13 2:25

select COUNT(Category_name) from TableName where Category_name like '%SearchPrefix%'

Re: query error

Mycroft Holmes30-May-13 12:03

Multiple Join to the same field

30-May-13 12:03

SQL

Select count(*) Records. Category
From (PutYourOriginalQueryHere)
group by Category

Never underestimate the power of human stupidity
RAH

Re: query error

Azarudhin4-Jun-13 3:41

Azarudhin

4-Jun-13 3:41

hi,
select COUNT(Category_name) from TableName where Category_name like '%food%'
group by Category

S Azarudhin

Jassim Rahma29-May-13 4:31

Jassim Rahma

29-May-13 4:31

Hi,

i have the following code:

SQL

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

Re: Multiple Join to the same field

David Mujica29-May-13 4:56

David Mujica

29-May-13 4:56

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. Cool | :cool:

Re: Multiple Join to the same field

Mycroft Holmes29-May-13 12:49