Re: Inserting Master and DEtail Tables - Database Discussion Boards

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

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%'

SQL

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:

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

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:

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 Suspicious | :suss:

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

Member 8701813 wrote:
can anyone guide me ,how to decrypt the encrypted user defined function in sql server 2008

http://sqljunkieshare.com/2012/03/07/decrypting-encrypted-stored-procedures-views-functions-in-sql-server-20052008-r2/[^]

Now, whose database are you trying to hack?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

hi,
thanks for replying...by d way i am not hacking anyone's db...i tried my function...i succeeded in encrypting but couldn't succeed in decrypting it....ie i asked in this forum...once again thanks for replying...

Member 8701813 wrote:
by d way i am not hacking anyone's db

I do not see any other reason to decrypt a procedure.

Member 8701813 wrote:
i succeeded in encrypting but couldn't succeed in decrypting it

There are some tools and some sprocs that can decrypt an sproc/function on Sql2005. None of those will work on Sql2010, and that change "might" have been introduced in Sql2k8R2. In that case, you're out of luck.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Encryption / Decryption

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_Cryptography]
(
@String varchar(50)
)
/*
Author: POONGUNRAN S
Create date: 29-May-2013
Description: Encrypt / Decrypt
Parameters : @String - String to be encrypted / decrypted
*/
RETURNS varchar(50)
AS
BEGIN
IF @String = ''
RETURN ''

SELECT @String = LTRIM(RTRIM(@String))

DECLARE @Key varchar(50), @Crypt varchar(50), @Len smallint, @n smallint

SELECT @n = 235
SELECT @Key = ''
WHILE @n <= 255
BEGIN
SELECT @Key = @Key + CHAR(@n)
SELECT @n = @n + 1
END

SELECT @Crypt = ''
SELECT @Len = LEN(@String)
SELECT @n = 1

WHILE @n <= @Len
BEGIN
SELECT @Crypt = @Crypt + CHAR(ASCII(SUBSTRING(@String,@n,1)) ^ ASCII(SUBSTRING(@Key,@n,1)))
SELECT @n = @n + 1
END

RETURN @Crypt
END

Input put:

Select dbo.fn_Cryptography('test')

o/p : Ÿ‰žš

select dbo.fn_Cryptography('Ÿ‰žš')

o/p : test