Blog

SQL Tips: Insert Value using Identity from Previous Insert

Ever find yourself needing to write SQL code to run on multiple tables where one of the values in a secondary INSERT statement is dependent upon the identity value from previous insert? Well, if so, this should work for you…

[code language=”sql”]DECLARE @NewApiId int;
INSERT INTO APIs (Name, Description) VALUES (‘Social Links’,’The Social Links API, available only to corporate clients, will output a report all of the clients for the client”s ClientTypeID that includes all of the recorded social media properties’);
SELECT @NewApiId = CAST(scope_identity() AS int);
INSERT INTO ApiSubscriptions (ApiId, ClientId) VALUES (@NewApiId, 666);
select * from APIs;
Select * from APISubscriptions WHERE ClientId=666;[/code]

Pretty simple, no?

Billboard claiming that Chrome is the fastest browser

How to Put Pep Back Into Chrome’s Step

Tired of watching Chrome get slower and slower every day? If you are like me, you fell in love with it’s clean, simple design philosophy and the ecosystem around it (Extensions, Apps, etc) but let’s be honest, it truly is about the speed! The slowness was largely limited to the Omnibox but also seemed to plague File Open dialog in Chrome which lead me down the road of thinking it was a problem with Chrome and my SSD drive.

According to Make Use Of blog, the folder %localappdata%\Google\Chrome\User Data\Default is the key to the problem. I’ve uninstalled, re-installed, turned off extenstions and apps, deleted caches; all to no avail. I was about to put a bullet in this ol’ dog until I stumbled on this article.  I gave it a shot and it fixed it, with a few caveats.

Some things to consider when deleting the files in the folder above:

  • Bookmarks – In the folder, there is a file called Bookmarks that contains, not surprisingly, all of your bookmarks. Deleting all this data ’cause my collection of bookmarks and bookmarklets to disapear. Oddly enough, even logging into Chrome didn’t return them from my profile (wonder if they were just local and I hadn’t previously been logged into Chrome) so I went digging through the trash to see if I could find the file. I closed Chrome, restored the file from the trash and then reloaded chrome and my Bookmarks are now back. (Note that it took a little while after signing in but extensions started showing up shortly so perhaps had I been more patient, this step would not have been necessary.)
  • Web Apps – Similarly, all of my Chrome Apps were gone so I went looking for a similarly named file or folder to restore. I tried Web Applications and that didn’t seem to resolve the problem. However, within 5 minutes or so of signing in the apps appeared back where they belonged

So, if you truly do want to reset your Chrome Clock back to day one, go ahead and delete the whole folder. If you want the most seamless experience when you re-open it and you haven’t previously logged into Chrome with your primary account, go ahead and do so now so that after you “nuke it from orbit”, you can restore the settings that you want.

Anyone else been frustrated to no end by Chrome over time? Let me know in the comments below.

 

Image courtesy of Elliot Brown at Flickr used under CC BY 2.0

Insert From Table on Remote SQL Server

Ever have the need to populate a table from data that you have in a table on a different SQL server? This is a very handy thing to do when you are trying to add data to your dev or staging SQL databases from the live server.
Previously I would have exported the data from the one server and then imported that data into the new one.
Well brother (and sister) I’m her to tell you there is a better way!
Through the magic of Linked Servers (SQL Server 2005+ I believe) it can be done. And through the use of Synonyms, you can even make it less cumbersome if it is something that you do regularly.
How does this work you ask? Well here is how I did it.

  1. Ensure that in SQL Mangler (I mean SSMS) you are connected to both servers with a user that has access to both databases.
  2. Open a query window for your target database
  3. Run the following command replacing server.domain.com with the FQDN of the source server
    exec sp_addlinkedserver [server.domain.com];
  4. Run your INSERT INTO statement as follows:
    INSERT INTO targetTableName ([Col1], [Col2], [Col3], [etc])
    SELECT [Col1], [Col2], [Col3], [etc]
    FROM [server.domain.com].databaseName.dbo.tableName
  5. If this is something you are going to be doing a lot of, you can make a Synonym of to save you some typing:
    CREATE SYNONYM nickname FOR [server.domain.com].databaseName
    Which would give you the ability to shorten your FROM selector.

Of course, your results may vary, always test on a backup database, blah, blah blah. Enjoy coders!

Wget and URLs with Ampersands

I have been working around some issues with Wget recently and after modifying the URLs, I found that it would start failing if the URL was a querystring with more than one variable. This means that you would have an & in the string.

Well, Wget sees this as an additional command so you need to delimit it somehow. Googling turned up this article that indicates either enclosing the URL in single-quotes (‘) or delimiting the ampersand character with a backslash. The comments on this post all praise this solution.

Only problem… Neither worked for me. Then I thought, perhaps this is another “oddity” of running it on the Windows port. So, I tried enclosing the URL in double quotes and voila it worked! So, let that be a lesson to you: Don’t assume every ported program from Linux runs as you’d expect when under Windows.