June 9, 2009 at 7:28 pm
I'm trying to combine data from 3 different tables into a single table in a stored procedure, but I'm not sure how to separate execution of the various steps:
(I have another problem with duplicates as well in this stored procedure)
ALTER PROCEDURE Blah
AS
GO
--Step one, empty all rows from maintable
DELETE FROM MasterList
GO
--Step two, populate main table from table1
INSERT INTO MasterList (UserName, Email)
SELECT (UserName, Email) FROM table1
GO
--Step three, same as above with table2, but ignore pre-existing emails (would be duplicates)
INSERT INTO maintable (UserName, Email)
SELECT UserName, Email FROM table2
WHERE not exists (SELECT * FROM maintable
WHERE maintable.Email = table2.Email);
GO
--Step 4, same exact thing for step 3, only with table3 now
INSERT INTO maintable (UserName, Email)
SELECT UserName, Email FROM table3
WHERE not exists (SELECT * FROM maintable
WHERE maintable.Email = table3.Email);
SELECT * FROM maintable
RETURN
The problem is that the stored procedure deletes everything in maintable at the complete of the execution... which leads me to think that maybe the delete takes longer, it finishes execution after the other statements. I thought 'GO' was suppose to separate actions but apparently it doesn't. I also tried BEGIN and END for each step, but somehow the DELETE still comes in last for the finish line. I want the DELETE to finish before the rest of the procedure starts.
I actually don't need a persistent "maintable." I tried to create a temp #maintable and then drop it. But the table wasn't recognized after a 'GO', so I figured a persistent table is acceptable for my needs.
As a secondary issue, I'm still getting duplicate email rows as well, so I must be doing two things wrong.
June 9, 2009 at 7:40 pm
bit of a logic hole there.
a GO statement cannot exist in a stored procedure. when you run that statement it creates a stored proc that only does one thing...deletes from your table. it doesn't do anything else. the "GO" statement is interprete as then end of the creation of procedure, and allows the compiler to run the next statement.
then, the three separate standalone select into statements are performed.
once you remove those GO statements, which are not doing what you thought they were doing, your temp table would work just fine, and there's no need for your permanent mainlist table.
Lowell
June 9, 2009 at 7:52 pm
also, i notice you are inserting into two tables : "MasterList" and "maintable";
i would think you would be inserting into just one temp table, right? and the exists should bee against the same table, right?
and if maintable had any records with the same email BUT different Names,"both" names would get inserted, because they did not exist in the table:
bob bob@yahoo.com
Robert bob@yahoo.com
Bob Jones bob@yahoo.com
Lowell
June 9, 2009 at 8:30 pm
Ok, thanks! That clarifies things for this newbie. I must have been doing something else wrong when I had the temp table.
I'm also getting duplicate results, is this something to mention in another thread?
EDIT... (saw your post after I wrote)
Oh, and about the two tables, masterlist and maintable... that was a typo in this forum. The actual SQL didn't have two tables.
That reminds me now why I wanted to create a permanent table.... I thought that would help me populate the table before the next statement went through. How can I "update" that temp table before the next insert goes in?
June 9, 2009 at 9:20 pm
i understand why you are breaking everything into step by step...it's easy to understand logically.
however, perfomance wise, you can do everything together in one swoop...get the emails, remove duplicates, and return the username/email.
here's how i would do it instead:
man, i love row_number function....it ranks up there with the Tally table for usefulness for me
SELECT UserName, Email
FROM (SELECT
UserName,
Email,
row_number() OVER (PARTITION BY email ORDER BY Email) AS RW
FROM (SELECT
UserName,
FROM TABLE1
UNION ALL
SELECT
UserName,
FROM TABLE2
UNION ALL
SELECT
UserName,
FROM TABLE3) SUPERUNION
)MyAlias
WHERE RW=1
Lowell
June 9, 2009 at 9:42 pm
Wow, that looks pretty awesome. I'll try that. I guess it's telling me to run sp_addlinkedserver to access to those stored procedures first.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply