Adding data to a SQL Server Database

I’m back from vacation, and it always seems to take an extra week to clear out the inbox, catch up on the meetings you missed, etc. So, time to dive back in to the blog and actually put some data in the database and the table we created last time. My goal here over the next few posts is to put some data into the table, then create a very rudimentary web page to view the data, which will be some photos in my case.

There are several ways to so this, of course, including writing code, which I will not go into just yet. This week we will start with getting the data into the table using SQL Server Management Studio, which is probably the easiest solution, but it is not so good for doing large imports. In this case, though, I have about 10 photos to add to the Table for demonstration purposes, so SSMS will work just fine.

To avoid you having to go back and read previous posts, I have a database named Pictures that I created, then a table named Photos with 5 fields:

PK – numeric

Description – text

DateTaken – date

Photo – image

Subject – text

So, open up SSMS, navigate to the table, then you can right-click it to bring up the context menu. We’ll want “Script Table as”, then “INSERT to” and finally “New Query Editor Window”, and this will bring up a stub of a query with the fields listed:

INSERT INTO [Pictures].[dbo].[Photos]

           ([PK]

           ,[Description]

           ,[DateTaken]

           ,[Photo]

           ,[Subject])

     VALUES

           (<PK, numeric(18,0),>

           ,<Description, text,>

           ,<DateTaken, date,>

          ,<Photo, image,>

           ,<Subject, text,>)

GO

The INSERT INTO clause if fine, we don’t need to mess with that, but we will want to put our actual values into the VALUES clause, but there’s a small gotcha here – the syntax is not correct, here’s what I put in to make it actually work:

VALUES

           (000001

           ,'Challenger Pic'

           ,'04/01/2009'

           ,'c:\EdStuff\Pics\ChallyPic.jpg'

           ,'Eds new car')

GO

There are a few things to note here – first off, the angle brackets in the original stub are not needed, and we don’t need to explicitly call out the Field names either, as they are called out in the INSERT INTO clause already. The other thing that threw me for a loop was the fact that any single-quoted field in SSMS shows up by default in a red color, and that, coming from the Dev world, means “error” to me, but it works perfectly well. Click Execute and it will tell you that it updated successfully. Notice I used a path to an image file on my hard drive, the rest of the fields are pretty self explanatory.

To confirm that you actually did get some data into the table, you will again want to right-click the table in SSMS, then select “Script Table as”, then “SELECT to” and finally “New Query Editor Window”. This will again bring up a new query template:

SELECT [PK]

      ,[Description]

      ,[DateTaken]

      ,[Photo]

      ,[Subject]

  FROM [Pictures].[dbo].[Photos]

GO

This does not need modification, once you hit Execute on this query, it will show you the results in the Results tab below the Query Editor window.

That’s it for this week, for next week I plan on using a free download called WebMatrix to build my client for showing the data, I will report on my experiences with that tool then.