通过

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.

Comments

  • Anonymous
    August 29, 2010
    Just another option for retrieving the rows in the table; You can right clilck a table and choose "select top 1000 rows". If you have more than 1000 rows, you  can still use this and then just edit the query that is generated afterwards. -- eliasen

  • Anonymous
    April 23, 2012
    hi i am having a problem with my sql queries i hope if any of you could answer this thank you. INSERT into MY_employees VAlUES ( 120 , ALVI , Farrukh, Farrukhalvi , 15000); this is my sql statement and i have figured out that the columns which are initialized as varchar i am unable to insert anything into that and the error message is "column not allowed here " please help thank you my email address is farukh_alvi88@hotmail.com. i am not a frequent user so please email me your answers thank you.

  • Anonymous
    February 14, 2013
    Sir how to import data only from Excel Sheet  to Sql Server 2008 by using asp.net with C#