Wednesday, October 27, 2010

SQL Server: INSERT INTO with SubQuery


Introduction

This is a short tutorial on how to insert data with a subquery on SQL Server databases.
The reason I am writing about it is that the required syntax is not that obvious!

Sample tables and data

Let's create two simple tables for this tutorial with the names 'Customers' and 'Orders'.

view source
1CREATE TABLE [dbo].[Customers](
2    [id] [int] IDENTITY(1,1) NOT NULL,
3    [name] [varchar](100) NOT NULL,
4    [username] [varchar](20) NOT NULL
5)
view source
1CREATE TABLE [dbo].[Orders](
2    [order_id] [int] IDENTITY(1,1) NOT NULL,
3    [customer_id] [int] NOT NULL,
4    [order_date] [datetime] NOT NULL
5)

We are also going to insert a new row on table 'Customers':

view source
1INSERT INTO Customers
2VALUES ('John Smith','john.smith')

Inserting data with a subquery

The obvious syntax to insert data into the orders table with a subquery would be:

view source
1INSERT INTO Orders
2VALUES ((SELECT id FROM Customers WHERE username = 'john.smith'), GETDATE())

However if you try the above query, you would end up with the following message:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

The correct syntax to accomplish our task is:

view source
1INSERT INTO Orders
2SELECT id, GETDATE() FROM Customers
3WHERE username = 'john.smith'

or if you would like to specify the columns:

view source
1INSERT INTO Orders (customer_id, order_date)
2SELECT id, GETDATE() FROM Customers
3WHERE username = 'john.smith'

Conclusion

Hope you find it useful, especially if you are getting the "Subqueries are not allowed …" error.


Gmail chat – how to type words in bold and italics


Today I have been typing on Gmail chat, and by accident I have found out how to type a word in bold. With a bit more of a search, I have also found out how to type in italics, and also strikethrough a word.

Here is the cheat sheet:

Bold: Surround the word with asterisks, e.g. *word* -> word
Italics: Surround the word with underscores, e.g. _word_ -> word
Strikethrough: Surround the word with hyphens, e.g. -word- -> word

If you know more "secret codes", feel free to leave a comment!


Wednesday, October 20, 2010

Get Current Page Name In Javascript

<script type="text/javascript">

var sPath = window.location.pathname;

var sPage = sPath.substring(sPath.lastIndexOf('/') + 1);

alert(sPage);

</script>