February 22, 2013 at 5:00 pm
GOAL: insert into tblcompanyassignments from tbluserassignments. Only attempt the insert for userid that exist in both tbluserassignments and tblexistingusers. For existing users, further filter down the insert attempt for only companies that exist. Final requirement, don't attempt to insert if there is already an existing user, company combination
tbluserassignments
username
companyname
record 1: userA, companyA
record 2: userA, companyB
record 3: userB, companyB
record 4: userB, companyC
record 5: userC, companyA
record 5: userC, companyB
tblexistingusers
userid
record 1: userA
record 2: userB
tblexistingcompanies
companyid
record 1: companyA
record 2: companyB
tblcompanyassignments
userid
companyid
record 1: userA, companyA
record 2: userB, companyB
February 22, 2013 at 6:13 pm
Sounds suspiciously like homework...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 23, 2013 at 3:30 am
I agree it looks like homework.
But the funny part is: the expected result is wrong based on the given requirement (at least form my point of view)... :w00t:
@daglugub: what have you tried so far and where did you get stuck?
We'll be glad to help you better understand SQL Server and/or T-SQL programming. But most of us will have a hard time to simply do your homework.
Providing help and assitance for SQL Server is our passion and "free of charge". But to do some work for you is called a "job", usually being paid for...
February 23, 2013 at 8:59 am
What I can't forgive is the lack of effort to even disguise the question lol. It's like here's my assignment for Monday morning, I'm off to the pub with my mates, see you when you've got the answer.
:w00t: :w00t: :w00t: :w00t:
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 25, 2013 at 11:38 am
I took the time to create that scenario because I am asking for help so I wanted to be specific as possible. I guess that backfired huh.
Here is what I have come up with so far.
select LTRIM(RTRIM(SUBSTRING (Assignment, PATINDEX('% %',Assignment), 100))) AS LastName, *
into dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP
from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE
select userid
into #existingusers
from [devdb4\dev].mnetreportup.dbo.tblusers
where email in (select email from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)
OR Lname in (select LastName from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)
select *
from #existingusers
select distinct companyid
into #existingcompanies
from [devdb4\dev].mnetreportup.dbo.tblcompanies
where [name] in (select [Customer Name]from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)
select *
from #existingcompanies
So from the imported data stage table I have record sets for which users are in the system and which companies are in the system.
Where I am stuck(because I am a novice) is I don't know how to write an insert from the stage table into the usercompany table while using the temp tables I have built up.
For the record, I am not looking for code to be provided, more so some fundamental education
February 26, 2013 at 10:48 am
daglugub 60457 (2/25/2013)
I took the time to create that scenario because I am asking for help so I wanted to be specific as possible. I guess that backfired huh.Here is what I have come up with so far.
select LTRIM(RTRIM(SUBSTRING (Assignment, PATINDEX('% %',Assignment), 100))) AS LastName, *
into dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP
from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE
select userid
into #existingusers
from [devdb4\dev].mnetreportup.dbo.tblusers
where email in (select email from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)
OR Lname in (select LastName from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)
select *
from #existingusers
select distinct companyid
into #existingcompanies
from [devdb4\dev].mnetreportup.dbo.tblcompanies
where [name] in (select [Customer Name]from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)
select *
from #existingcompanies
So from the imported data stage table I have record sets for which users are in the system and which companies are in the system.
Where I am stuck(because I am a novice) is I don't know how to write an insert from the stage table into the usercompany table while using the temp tables I have built up.
For the record, I am not looking for code to be provided, more so some fundamental education
Sounds like you should look into the MERGE statement. It allows you to insert, update, or delete rows in a target table based on whether they match rows in the source table and satisfy any additional conditions you specify.
Jason Wolfkill
February 26, 2013 at 6:40 pm
wolfkillj (2/26/2013)
daglugub 60457 (2/25/2013)
I took the time to create that scenario because I am asking for help so I wanted to be specific as possible. I guess that backfired huh.Here is what I have come up with so far.
select LTRIM(RTRIM(SUBSTRING (Assignment, PATINDEX('% %',Assignment), 100))) AS LastName, *
into dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP
from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE
select userid
into #existingusers
from [devdb4\dev].mnetreportup.dbo.tblusers
where email in (select email from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)
OR Lname in (select LastName from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)
select *
from #existingusers
select distinct companyid
into #existingcompanies
from [devdb4\dev].mnetreportup.dbo.tblcompanies
where [name] in (select [Customer Name]from dbo.CDW_Rep_Assignment_AutoUpdate_STAGE_TEMP)
select *
from #existingcompanies
So from the imported data stage table I have record sets for which users are in the system and which companies are in the system.
Where I am stuck(because I am a novice) is I don't know how to write an insert from the stage table into the usercompany table while using the temp tables I have built up.
For the record, I am not looking for code to be provided, more so some fundamental education
Sounds like you should look into the MERGE statement. It allows you to insert, update, or delete rows in a target table based on whether they match rows in the source table and satisfy any additional conditions you specify.
+1 to Wolfy for suggesting MERGE. At the very least, the suggested solution should be enclosed in a TRANSACTION with error handling (TRY/CATCH blocks).
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
February 26, 2013 at 10:41 pm
Daglugub, how about this?
--DDL to set up environment
create table userassignments (id int, userid int, companyname varchar(50));
insert into userassignments
values
(1, '1', 'companyA'),
(2, '1', 'companyB'),
(3, '2', 'companyB'),
(4, '2', 'companyC'),
(5, '3', 'companyA'),
(6, '3', 'companyB');
create table existingusers (userid int, username varchar(10));
insert into existingusers values
(1, 'userA'),
(2, 'userB');
create table existingcompanies (companyid int, companyname varchar(50));
insert into existingcompanies values
(1, 'companyA'),
(2, 'companyB'),
(3, 'companyC');
create table companyassignments (username varchar(10), companyname varchar(50));
insert into companyassignments values
('userA', 'companyA'),
('userB', 'companyB');
--DDL for Merge/Update statement
MERGE companyassignments T
USING
(SELECT ua.userid, eu.username, ua.companyname
FROM userassignments ua
JOIN existingusers eu on
eu.userid = ua.id
where companyname in (
SELECT companyname
FROM existingcompanies)) as S
on t.username = s.username
and t.companyname = s.companyname
WHEN NOT MATCHED BY Target
THEN INSERT
(username, companyname)
VALUES
(s.username, s.companyname);
--Quote me
February 26, 2013 at 11:00 pm
polkadot (2/26/2013)
...--DDL for Merge/Update statement
MERGE companyassignments T
USING
(SELECT ua.userid, eu.username, ua.companyname
FROM userassignments ua
JOIN existingusers eu on
eu.userid = ua.id
where companyname in (
SELECT companyname
FROM existingcompanies)) as S
on t.username = s.username
and t.companyname = s.companyname
WHEN NOT MATCHED BY Target
THEN INSERT
(username, companyname)
VALUES
(s.username, s.companyname);
That would be DML.:-)
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
February 27, 2013 at 12:21 am
ooh man, you got my back. Thanks. would'a goofed with that in my next interview
--Quote me
March 1, 2013 at 11:42 am
thanks a lot Polka Dot for the DDL and DML. This is definitely the right kind of solution for me. Unfortunately this code fell a little short. It only inserted 2 rows.
User A Company A
User B Company B
But this is a great starting point for me, and I can research and test from here.
Thanks again
March 1, 2013 at 11:49 am
polkadot (2/27/2013)
ooh man, you got my back. Thanks. would'a goofed with that in my next interview
I'm usually looking for ways to disseminate my superiority...
March 1, 2013 at 12:43 pm
I figured it out
eu.userid = ua.id
needed to be
eu.userid = ua.userid
I am going to start using Merge for a few different tasks I have.
March 3, 2013 at 10:16 am
Hi Daglugub, sorry I didn't see this sooner. Need to get notifications sent to a more often used email inbox, which I will do right way. But, I am glad you figured it out. PS. NICE WORK
--Quote me
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy