Database

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

indian143

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

indian143

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

Select top 2 not working on Maximum Record using SQL

29-May-13 12:49

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

SE Ubaid29-May-13 2:38

SE Ubaid

29-May-13 2:38

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.

Re: Select top 2 not working on Maximum Record using SQL

Eddy Vluggen29-May-13 3:17

Re: Select top 2 not working on Maximum Record using SQL

29-May-13 3:17

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[^]

Arman S.29-May-13 9:44

Arman S.

29-May-13 9:44

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

decryption

Member 870181328-May-13 6:42

Member 8701813

28-May-13 6:42

hi experts,
can anyone guide me ,how to decrypt the encrypted user defined function in sql server 2008

R. Giskard Reventlov28-May-13 7:43

R. Giskard Reventlov

28-May-13 7:43

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

Eddy Vluggen28-May-13 9:45

28-May-13 9:45

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[^]

Member 870181330-May-13 16:37

Member 8701813

30-May-13 16:37

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

Eddy Vluggen31-May-13 6:23

31-May-13 6:23

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[^]

poongunrans28-May-13 20:21

poongunrans

28-May-13 20:21

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

SQL

Shiv irfi26-May-13 21:11

Shiv irfi

26-May-13 21:11

Hello everyone,
I created a table which has following columns Sl_no,name and employee_id.
Is that a possible when i select a table i should get a designation of an employee in place of his name??
Kindly please help me out.

Mycroft Holmes26-May-13 21:52

26-May-13 21:52

What we are supposed to be telepathic!

What database server
What is your select query
There is no field called designation in your table!

Never underestimate the power of human stupidity
RAH

Killzone DeathMan28-May-13 5:35

Killzone DeathMan

28-May-13 5:35

We are some kind of "gods" and we can read minds Laugh | :laugh:

Eddy Vluggen28-May-13 9:59

28-May-13 9:59

Shiv irfi wrote:
I created a table which has following columns Sl_no,name and employee_id

SQL

SELECT Sl_no, name, employee_id
  FROM [a table]
 WHERE 1=1

Shiv irfi wrote:
Is that a possible when i select a table i should get a designation of an employee in place of his name??

Yes.

SQL

SELECT Sl_no, designation, employee_id
  FROM [a table]
 WHERE 1=1

Bastard Programmer from Hell Suspicious | :suss:

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