SQL: Updating One Table with the Values of Another Table
By juliana | March 4, 2010
For one reason or another, I often have to update rows in one table with the values in another table, and every time I do, I end up Googling for the answer.
I don’t claim to have come up with this little code snippet* but I’m putting it up here so I don’t have to waste time looking for it again. And if it helps you, feel free to snag and use.
UPDATE T
SET
T.fieldOne= S.oneValue,
T.fieldTwo= S.twoValue
FROM [TargetTable] T
JOIN [SourceTable] S ON T.id = S.id
* I believe I first saw it on SQLTeam.com but I can’t find the original article now.
Topics: I Canz Code! | No Comments »
Virtual Paths, LinkButtons and A Hrefs
By juliana | October 9, 2008
One of the drawbacks of .NET, I feel, is that in making things easier for the developer, it also makes it very easy to “bloat” our pages. If you’re a drag-and-drop developer (which is part of the appeal of .NET) and you’re doing some really fancy stuff, you might want to check on your page sizes–especially your viewstate. Those things can get huge.
Anyway, one of the commonly unnecessarily used controls, I think, is the LinkButton. When I had to try and make our pages a little leaner, that’s one of the first things I check:
<asp:LinkButton ID="LinkButton1" runat="server" PostBackUrl="~/somefolder/somepage.aspx">LinkButton</asp:LinkButton>
In our company, we had an (unrelated) viewstate problem where all our linkbuttons kept failing, but our A HREF tags were fine. Now, this made no sense to me ’cause, basically, the above linkbutton was just doing the same job as an A HREF. There was no behind the code event subroutine, it was just a redirect.
I asked the programmer who did this why he didn’t use normal static A HREFs instead. He said that he wanted to be able to use the virtual path “~/” because the pages would be deployed in different plages at different stages. I introduced him to this nifty little library called VirtualPathUtility.
You can get the same function by using this code:
<a href="<%=VirtualPathUtility.ToAbsolute("~/somefolder/somepage.aspx")%>">No Longer a LinkButton</a>
Because of the unrelated issue — and I might be wrong — but it seems like using a LinkButton even with a different PostBackURL specified, a postback to the same page DID occur, processed on the server, PostBackURL value determined, and then a redirection issue. To me, that’s like a lot more stuff going on that just a simple A HREF where the user clicks and the browser knows straight away which page it needs to go.
Plus, I think not having a webcontrol just means less for the server to keep track of since it’s all HTML that comes out. I could be wrong, but in our team, we’ve decided to apply the following:
1. Unless your LinkButton needs to do behind-the-code processing, convert all LinkButtons to <a href> tags.
2. If you need to resolve virtual paths, use the nifty VirtualPathUtility.
I’ve also used it to replace ImageButtons with <a href> and <img> tags. I’m not sure if it’s made a huge difference in the grand scheme of things, but hopefully it doesn’t hurt and helps just a little.
Topics: I Canz Code! | 3 Comments »
Yikes! We went AWOL!
By juliana | September 5, 2008
Just realized it’s been ages since yuthavong or I have updated this blog and checked our comments! So sorry. I’ve responded to some comments on our most popular post about LINQ, I’m so sorry it was so late. Hopefully it can help future visitors. In the meantime, will try to check here more often. For some reason, the alerts on comments never reached us. So weird. Must bug yuthavong to get him to figure it out.
Topics: Uncategorized | No Comments »
“The file is too large for the destination file system.” …say what?
By juliana | September 5, 2008
A few months ago, yuthavong bought a Western Digital 500GB My Book external hard disk. He tested it out and found out it was a bit too noisy for his tastes. So I gets it. Yay! I’m more tolerant of buzzing noises because my desktop has a constant buzz–but I have to admit, the My Book is a lot noisier than I expected and even I switch it off when I’m not using it.
Today, though, I decided to move off some of my Virtual Machine files to the My Book and clear up some of my hard disk. So I switched it on, plugged it in and tried to copy and paste it into the disk…
Windows tells me the file is too large for the destination file system. Bu-wha-? My virtual hard disks is over 4GB…but I checked! My Book had over 350GB free space! I thought it was the network problem (so I plugged it in direct), I thought it was read-only thing (resetting it did nothing), I was scratchin’ my head until I Goggled and figured it out….
The darn thing was formatted in FAT32. Did you know FAT32 doesn’t accept files larger than 4GB? Now WHY a 500GB storage space is formatted in FAT32 is beyond me!
But now, I’ve got a dilemma.
1) if this was a new disk, I’d be able to reformat it (Right clicking the Drive in My Computer and Format)
2) but I got data, … luckily, on a Windows, you can convert your disk on the fly!
a) Run the command prompt as Administrator (Start->Command Prompt->Right-click->Run as Administrator)
b) At the command prompt, type
C:\Users\J>convert f: /fs:ntfs /nosecurity
where f: is the drive of your external hard disk.
And …tada! Two hours of trying to figure out what the heck was wrong, and fixed in 2 minutes.
Sooooo typical of a computer problem for ya.
UPDATE: When prompted to “Enter the current volume label for drive [Drive Letter Here]“, if your drive has a label, you need to enter it (e.g. “My Passport” without the quotations). If your drive doesn’t have a label, just press the enter key for blank.
To find the volume label (if there is one specified), go to ‘My Computer’, right-click the drive you’re trying to convert, select Properties. In the General tab, the volume label is the name in the first field.
Topics: Uncategorized | 81 Comments »
LINQ, Stored Procedures and Multiple Recordsets: How-To
By juliana | June 7, 2008
Okay, here’s my entire test-run of how to get LINQ to work with stored procedures that return multiple recordsets just a dummy project.
Topics: I Canz Code! | 9 Comments »
Maintenance Plans/Backup Jobs in SQL Server
By juliana | May 22, 2008
Nice little tutorial here:
Will need to try.
Have gone a little crazy over current project–will be back during the next possible commercial break to blog about multiple results stored procedures and LINQ. Honest.
Topics: Uncategorized | No Comments »
LINQ, Stored Procedures and Multiple Recordsets
By juliana | May 15, 2008
Update: This post has a follow up here: LINQ, Stored Procedures and Multiple Recordsets: How-To
I wanted to use a LINQ with a stored procedure that would return multiple recordsets, like this:
CREATE PROCEDURE spReturnMultiple
AS
--first set
SELECT value1 FROM table1
--second set
SELECT t1.value1, t2.value2
FROM table1 t1 LEFT JOIN
table2 t2 on t1.value1 = t2.value2
GO
I found ScottGu’s LINQ to SQL (Part 6 – Retrieving Data Using Stored Procedures) article and Guy Burstein’s Linq to SQL Stored Procedures with Multiple Results – IMultipleResults article that pointed me into the right direction, but not QUITE all the way.
ScottGu’s article showed how you can use a SPROC to return different types of recordset (usually due to an IF statement within the SPROC) and how IMultipleResults were used; in the comment trail someone asks about multiple tables and ScottGu said it could be done in the same way, calling GetResult twice. Burstein’s article showed exactly how you did this. My problem was that the stored procedures returned TABLES. However, my results are based on my own SELECT statements, so there was a little bit of extra that had to be done.
Read the rest of this entry »
Topics: I Canz Code! | 2 Comments »
ScottGu – Da LINQ Man!
By juliana | May 15, 2008
For my own reference, the various parts of ScottGu’s LINQ series:
- Part 1: Introduction to LINQ to SQL
- Part 2: Defining our Data Model Classes
- Part 3: Querying our Database
- Part 4: Updating our Database
- Part 5: Binding UI using the ASP:LinqDataSource Control
- Part 6: Retrieving Data Using Stored Procedures
- Part 7: Updating our Database using Stored Procedures
- Part 8: Executing Custom SQL Expressions
- Part 9: Using a Custom LINQ Expression with the <asp:LinqDatasource> control
Topics: I Canz Code! | No Comments »
SQL Server 2005’s Pivot…PIVOT, PIVVOOOOT – Ross Gellar, Friends
By juliana | May 9, 2008
Every time I see SQL Server’s 2005 PIVOT, I think of that episode of Friends where Ross tries to guide his friends as they bring up the couch to his apartment.
But serious, PIVOT is one of those things that I’m still trying to wrap my brain around.
I’m currently digesting this: Pivots with Dynamic Columns in SQL Server 2005
and modified this examples into one script with temporary tables so I can study it better:
Topics: I Canz Code! | No Comments »
Vroom, vrooom…. aka Making Pages Load Faster
By juliana | May 7, 2008
Optimizing Page Load Time [die.net]
Best Practicies for Speeding up your Web Site [Yahoo]
Interesting stuff, some excerpts for own reference below the more tag.
Read the rest of this entry »
Topics: Uncategorized | No Comments »
« Previous Entries