Update single rows with multiple values
-
I need to create the following temporary table from two tables:
tUserID tName tClass 1 Mary HomeEc 1 Mary English 2 Sue English 2 Sue French 2 Sue Calculus 3 Joe Shop Table1 UserID Name 1 Mary 2 Sue 3 Joe Table2 UserID Class 1 HomeEc 1 English 2 English 2 French 2 Calculus 3 ShopWhat I hoped might work was:
Insert Into @TempTable (tUserID, tName) Select UserID, Name From Table1 Update @TempTable Set tClass = Class From @TempTable Inner Join Table2 on tUserID = UserIDbut it didn't. I got:
1 Mary HomeEc 2 Sue English 3 Joe Shopwhich is the first value for each UserID.
I'm thinking that changing the join is part of the solution, but that's as far as I can get.
I'd appreciate whatever help you can provide.
Thanks,
Mattie
-
Insert Into @TempTable (tUserID, tName, tClass) Select t1.UserID, t1.Name, t2.Class From Table1 As t1
Inner Join Table2 As t2
On (t1.UserID = t2.UserID)
Homework assignment ?
-
No, incredibly simplified example.
I'm rewriting a stored procedure that ended up building four different recordsets. Crystal Reports, as far as I know, can only handle one recordset. So my thought was to convert the four recordsets into table variables, and then combine them into a fifth recordset to be used by the Crystal Report. It worked great until I ran into a one-to-many condition.
Thanks for your prompt reply. Now the only thing I have to worry about this evening is whether Schilling can beat the Yankees.
Mattie
-
Can you run SQL2005??
If you can you can use a couple of CTE's.
With Table1(UserID,Name) as
(
your select statement to grab data
),
Table2(UserID,Class)
(
again your select statement
)
Select Table1.UserID,Name,Class Into NewTab
From Table1,Table2
Where Table1.UserID=Table2.UserID
I wasn't thinking very hard when I posted the above because I was thinking about my current project but you could also:
Create your final temptable with its 3 columns and then
Insert into TempTable
(
Select Table1.UserID as tUserID,Name,Class
From Table1,Table2
Where Table1.UserID=Table2.UserID
)
The only advantage to using the CTE is that you can wrap that final join statement into a 3rd CTE and then do a forth query on the combination..
-
No, we're not using SQL 2005, so CTEs are not an option.
Thanks to everyone who put some thought into this. And even more thanks for coming up with the answer.
Mattie
-
A bit oversimplified on my part put if you have 4 working stored procedures, why not just use UNION ALL to convert it all to a single record set?
SELECT yada-yada
FROM yada-yada
UNION ALL
SELECT yada-yada
FROM yada-yada
UNION ALL
SELECT yada-yada
FROM yada-yada
UNION ALL
SELECT yada-yada
FROM yada-yada
ORDER BY wugga-wugga
...of course, the columns from each SELECT should provide the same datatype and must be in the same order as the first SELECT.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff,
I guess I wasn't clear. It wasn't four stored procedures producing identically structured recordsets, it was one stored procedure producing four different, but related, recordsets.
I think the reason I had my 'doh' moment was because I was thinking too much about how to use the code as written. If I had written the thing from scratch, with the requirement that there be only one recordset, I would have structured things differently (which I have subsequently done) and not viewed this as an insert/update problem.
Every so often I have to remind myself that starting from scratch may actually be faster in the long run.
Mattie
-
By the way, if you aren't adding indexes to the data after you are done, and if it's always rebuilt from scratch each time you run the report, you might modify the procedure to be a simple SELECT statement. Crystal will happily use a stored procedure as a datasource without any need for tables, temp tables, or table variables.
In other words, taking part of PW's code above, the statement:
Select t1.UserID, t1.Name, t2.Class
From Table1 As t1
Inner Join Table2 As t2
On (t1.UserID = t2.UserID)
inside of a stored procedure would keep Crystal all smiles, while also executing faster.
-
Absolutely, although the format of the select statement is somewhat more complex than the test data would suggest. I can't imagine trying to debug, much less construct, a complex recordset using just the Crystal environment. Using a stored procedure as the data source allows me to build and view my data in QA before I get anywhere near Crystal.
Mattie
-
Every so often I have to remind myself that starting from scratch may actually be faster in the long run.
How true, how true. Good call...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Yes, you want a big flat denormalised recordset for Crystal - unless you want to use subreports, but they are a pain, though good if you need to cause side-eefects from running the report - you can put them in a stored proc called by the subreport.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Hi Jeff,
isn't there some mistake? I tested your SQL and I'm getting this error:
Invalid column name 'wugga-wugga'.
(Nevermind, just kidding
... need to joke because I'm nervous today - Czechs are playing with US in the soccer World Cup this afternoon. And sorry for the off-topic, but as I understand it, problem was already solved). -
Vladan! Good to "see" you... Now that was funny! Thanks for the laugh...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply