October 17, 2012 at 3:10 pm
Any help is greatly appreciated, I have 2 different SQL servers, and I need to syncronize some information about users in 1 system with users in another.
In system #1, I was given this query:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select distinct r.reviewer, ac.accountid, ac.accounttypeid, at.accounttypename
, rr.claimid, c.claimnumber, rr.reviewed, c.trustid, t.name as Trust
from review r
join account ac on ac.username = r.reviewer
join accounttype at on at.accounttypeid = ac.accounttypeid
left join (select reviewer, max(reviewid) as maxid
from review
group by reviewer) maxr on maxr.reviewer = r.reviewer
left join review rr on rr.reviewid = maxid
left join claim c on c.claimid = rr.claimid
left join trust t on t.trustid = c.trustid
WHERE ac.Enabled = 1
order by r.reviewer
and this gives me a list of users and their usernames and the last trust account they used, etc.
Then I have a dotnetnuke SQL 2008 database, and there is a flat user table with UserID, etc and a UserProfile table with UserID, PropertyDefinitionID, PropertyValue, etc. The UserID is what links the UserProfile table to the User table.
I know the PropertyDefinitionID and PropertyValue of the fields I want to insert from the first system:
So I need to get out the last trust field from the query above and get it into my local dotnetnuke database, in the PropertyValue field, where the PropertyDefinitionID = 44, for each username.
ANY help is greatly appreciated, thanks!
October 17, 2012 at 7:12 pm
ericb1 (10/17/2012)
Any help is greatly appreciated, I have 2 different SQL servers, and I need to syncronize some information about users in 1 system with users in another.In system #1, I was given this query:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select distinct r.reviewer, ac.accountid, ac.accounttypeid, at.accounttypename
, rr.claimid, c.claimnumber, rr.reviewed, c.trustid, t.name as Trust
from review r
join account ac on ac.username = r.reviewer
join accounttype at on at.accounttypeid = ac.accounttypeid
left join (select reviewer, max(reviewid) as maxid
from review
group by reviewer) maxr on maxr.reviewer = r.reviewer
left join review rr on rr.reviewid = maxid
left join claim c on c.claimid = rr.claimid
left join trust t on t.trustid = c.trustid
WHERE ac.Enabled = 1
order by r.reviewer
and this gives me a list of users and their usernames and the last trust account they used, etc.
Then I have a dotnetnuke SQL 2008 database, and there is a flat user table with UserID, etc and a UserProfile table with UserID, PropertyDefinitionID, PropertyValue, etc. The UserID is what links the UserProfile table to the User table.
I know the PropertyDefinitionID and PropertyValue of the fields I want to insert from the first system:
So I need to get out the last trust field from the query above and get it into my local dotnetnuke database, in the PropertyValue field, where the PropertyDefinitionID = 44, for each username.
ANY help is greatly appreciated, thanks!
Proper DDL, sample data (in consumable form) and expected results would get you a tested answer to this question with great alacrity.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 19, 2012 at 10:17 am
Sorry, thanks for the help. The first query is from a custom SQL database, the results of the query give me this:
reviewer accountid accounttypeid accounttypename claimid claimnumber reviewed trustID Trust
ericb1 1234 8 operator 1234 12344556 2012-10-17 8 AB
etc
All I need to concentrate on from this query for now is the reviewer field and the TrustID field. For where each reviewer name is, I need to insert the TrustID into a new table on my local SQL 2008, It is called the UsersProfile table:
ProfileID
UserID
PropertyDefinitionID
PropertyValue
Visibility
So for me, I am UserID 3, and in the UsersProfile table, I have a PropertyDefinitionID of 44 which is ericb1 and a PropertyDefinitionID of 45 which is TrustID.
So for each record in the query (using the example above), I need to use the "reviewer" field, and insert the TrustID of 8 into the PropertyValue field of the UsersProfile table where the PropertyDefinitionID = 45 and where the PropertyValue of PropertyDefinitionID 45 = "ericb1".
Hope that makes sense, thanks again for the help!
October 19, 2012 at 10:38 am
ericb1 (10/19/2012)
Sorry, thanks for the help. The first query is from a custom SQL database, the results of the query give me this:
reviewer accountid accounttypeid accounttypename claimid claimnumber reviewed trustID Trust
ericb1 1234 8 operator 1234 12344556 2012-10-17 8 AB
etc
All I need to concentrate on from this query for now is the reviewer field and the TrustID field. For where each reviewer name is, I need to insert the TrustID into a new table on my local SQL 2008, It is called the UsersProfile table:
ProfileID
UserID
PropertyDefinitionID
PropertyValue
Visibility
So for me, I am UserID 3, and in the UsersProfile table, I have a PropertyDefinitionID of 44 which is ericb1 and a PropertyDefinitionID of 45 which is TrustID.
So for each record in the query (using the example above), I need to use the "reviewer" field, and insert the TrustID of 8 into the PropertyValue field of the UsersProfile table where the PropertyDefinitionID = 45 and where the PropertyValue of PropertyDefinitionID 45 = "ericb1".
Hope that makes sense, thanks again for the help!
There is nothing like proper ddl and sample data...and this is nothing like proper ddl and sample data. Take a look at the first link in my signature for best practices when posting questions.
The details are clear to you but we can't see what you see and we are not familiar with your project.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply