Data Entry
By Richard Rost

33 days ago

Lesson 6: Entering Data in SQL Server Tables

S M L XL FS | Slo Reg Fast 2x | Bookmark

In lesson 6, we will walk through how to enter and edit data directly in SQL Server tables using SQL Server Management Studio (SSMS). I will show you how to add new records, handle required fields, and explain when identity IDs are assigned. We will discuss how to format and paste sample data from Access, using Excel to help align the columns, and address common issues such as formatting dates and numbers. This lesson prepares you to have real data available for future query practice.

Navigation

Prev: Lesson 05
Next: Lesson 07

Keywords

SQL Server for Access, Microsoft Access, SQL Server for Access Users, msaccess, sqlserver, enter data SSMS, edit data SSMS, identity ID assignment, copy data from Excel, required fields, commit records, ISO date format, true false bit field, sample data i

Comments for Data Entry

Age	Subject	From
31 days	SQL Server	Rodney Maedke
36 days	Reset an AutoNumber	Alex Hedley
36 days	SQL Lesson 6	Juan Rivera

Subscribe to Data Entry
Get notifications when this page is updated

Intro

Transcript

In lesson 6, we are going to learn how to enter and edit data directly into your SQL Server tables using SSMS. I will show you how to add new records, handle required fields, and understand when identity IDs get assigned. We will also copy some sample data from Access into SQL Server by using Excel as a middleman so we can format everything nicely. This way, you have real information to work with as we move on to queries.

Let's see how we get some data into this table. Normally, you are going to be interacting with this table through your front end, through Access. But sometimes, as a developer, it is nice to be able to get in here and make some changes, add some stuff. It is just like with your Access database - you do not let the end users mess around directly in the tables. Normally, you will not be messing around in the tables directly either, but for testing, you can come in here. It is good to know how to get some data into the table.

The easiest thing to do: right-click on the table, edit top 200 rows. Why 200? I do not know. That is just what they picked. Also, select top 1000 rows runs a real quick select query and shows you the top 1000 rows. Edit top 200 rows puts you in, like, an edit mode. It is like the datasheet mode from Access.

Now, just like in Access, if you click here, you cannot type anything in. It is locked. But we can tab over here to first name and put myself in. Richard, look at that. Now, in order, a couple of things to notice here.

First of all, unlike Access, which assigns the customer ID immediately as soon as you type that first character into a new record, you will get assigned that autonumber. That is not the way SQL Server works. This can cause some weird side effects depending on how your code works and how you are used to doing things. Normally, that customer ID is going to get assigned when you save the record, when you leave the record. We will see that in just a minute.

Notice the little pencil icon over there, just the same as in Access. That means the record is dirty. That little red exclamation point is basically SSMS saying this row has changes that have not been written to the table yet. In other words, the record is still dirty. It has not been committed. Once you move off the row, it will get saved, and those little red exclamation points will go away. So do not panic. I know they look scary. It is good.

No, it is fine. It just means it is dirty still. In fact, if I try to move off the record now, I get an error message. It says no row was updated. The data in row one was not committed because you have to read this. The error message, as in Access, can be a little difficult to understand, especially for beginners. The ones in SQL Server are worse. You really have to read. But basically, last name does not allow nulls.

So we have to have a last name in here. Let's put my last name in there. Try it now. Let's see if I can move off it now. There we go. Now, still got a little exclamation point here. If you look at it, it says the row is successfully committed to the database. However, a problem occurred when attempting to retrieve the data back after commit. I see this once in a while. Because of this, the data displayed is read-only. To fix this problem, rerun the query. It means that it wrote it out to the table, but it could not read it back for whatever reason. That is normal. So close this and then open it back up again.

There we go. And there is our customer ID 3. I added a couple of records. I was goofing around earlier. Same thing over here. I am [email protected]. Hi there. Family size: two, four if you include the puppies. Discount rate: I will give myself a 50. Discount.

For discount rate, we are going to store it the same way that most Access developers are used to thinking about percentages: as a fraction of one. So, for example, 10 would be 0.1. 0.1. 50: 0.5. You could store it however you want to. I like to stay consistent with Access. This makes the math really clean in your queries and calculations. You can also simply multiply the rate without having to constantly divide by 100. Do not worry about how it looks now because we can always format it when we get back to Access.

Credit limit is simple: 5,000. Customer since: now, if you type in an address, if you type in a customer since, like 1/1, you get this. There is your big long customer since. Remember, we are going to format this in our front end in Access, or you can even format it in your queries here if you want to. But it is going to display this big long, crazy-looking date. Do not worry about it. It shows a bunch of milliseconds. Nothing is wrong with your data. It is just the fractional seconds portion of that datetime format. Remember, since we only typed in a date, there is no time involved.

SSMS is not going to be our final user interface, and when we link this into Access, we will format it. Notice I have the ISO date standard here, year, month, day. That is because of my Windows system settings. If you are new to my lessons, go watch this. It is a universal date format. I have lots of students from all over the world, different countries, different date formats. This is something that everybody can understand: year, month, day. I have a mission to convert the whole world to using this, as well as the metric system, but I am starting with this first.

So this is the date I am using, and that is something you set at the Windows level. If you see my dates looking like that, that is why I suggest strongly that you set your date settings to that as well.

What we got here: is active says true. Now, I know in Microsoft Access, you can get away with typing things like yes, no, true, false, one, and zero, negative one, and zero. Access is a little more forgiving. Even though SSMS displays a bit field as true and false, I want you to get in the habit of typing in ones and zeros. Type in a one there and Access, or I am so used to saying Access, will convert it. SQL Server will convert it to true, but I want you to get used to using ones and zeros. You will save yourself some confusion later because in your SQL statements, I want you to use ones and zeros.

As I said a moment ago, that customer ID gets assigned when you commit the record to the table. Most of the time that does not matter, but it can be a big deal with VBA code. If you are inserting a new record and you need that new customer ID right away so you can create child records, for example, you have to insert the row first, get the ID back, and then continue updating the fields afterward. It feels a little weird sometimes coming from Access. I know I do this all the time with recordsets. You can add the whole order record, get the ID, and then add the child records, but here you cannot. You have to create the record first, read the ID, then go back and fill in all the fields. It is a little bit more work, but it is not that bad.

We are going to cover this. I am going to get to the developer stuff. I just know most of you watching this right now are more advanced Access users, so you are probably familiar with what I am talking about. If not, just ignore it.

I am just going to rearrange a few things on the screen. I do not like to do too much off camera because then people complain, but there is a reason why I am doing this. I will show you in just a minute.

We are going to copy some data from an Excel sheet and fill it in here so we have some sample data, but I want to make sure I can see all the fields on the screen. Just like in Access, you can highlight a row here and then paste data in from another data source as long as your columns match up. Unfortunately, you cannot select stuff like you can in Access where you can select multiple columns or multiple fields, because in Access, you can do things like that.

What we are going to do is get yourself a copy of the TechHelp Free Template. It is a free database you can download from my website. I will put a link down below. You can grab it right on this page if you are watching on my website. We are going to copy the customer data right out of this table and paste it into our SQL Server database. It is easier to get all this to match up since we do not have a lot of these fields in our SQL Server database if we drop this in Excel first. It is just easier. You can make a query and do it all in here, but it is so much easier in here. Watch.

I am going to copy everything right there. Click on this little box right there. Copy, Ctrl+C. I am going to switch over to Excel and hit paste. There is all my data in here. Then we are going to fix these rows and columns real quick so we can read everything.

Now, the goal here is to match this stuff up with the columns in our SQL Server. So, customer ID: we do not want to paste any values in those, so I am just going to hit delete. Blank that column. Then we have first name, last name, email, address, city, state, zip, country, phone. We do not have phone, so I am just going to delete, right-click delete, get rid of those. Then we have notes, family size.

Discount rate goes here, so right-click, insert. I am going to insert a column here and just leave it blank. Because discount rate is new. Then we have customer since and credit limit, but these are flipped because they are in a different order, so we are going to cut this out. Where is cut? There is cut. We are going to insert cut cells here. Then is active as true. That should paste in just fine.

Now I have everything set here exactly the way I want it in my server. Do this: get rid of this header row to delete that. Shrink this up so we can see it.

Now, click in the first column here or the first cell there, then the last cell down here. Copy. Go back over to SQL Server, highlight that row, and paste. There you go. My discount rate and my credit limit did not come over. What happened? Let's see. Let's take a look.

Here we have some problems. We have to convert some stuff. First off, our credit limits - it is likely that the dollar sign is throwing it off because SQL Server, believe it or not, does not like those dollar signs. So let's format this as just a number. For the date, it looks like the date came in okay. Before, it did not because it does not like that. Format this as just a short date. There you go. That should come in better. The true/false usually reads okay.

Let's try this again. Now I am going to delete. Let's delete everybody. Click up here, hit delete. Yes. Goodbye. Be very careful doing that, by the way.

Come back to Excel one more time. Pick the first one, come down to the bottom, shift-click the last one. Copy. Back in here, highlight the row. That is the step people miss. You cannot just click in the cell. Now paste. There we go. That came in better that time. 3000, 4000, 4339. Let me make sure these numbers are right. Yep, all these numbers are good.

What did I do? Hold on, I accidentally double-clicked up here and it resized the window. So we got all of our data, and that is the quick way you can get data in from Access or Excel or wherever. Just drop it right into your table.

Now, you are only going to do this for sample data, small sets of data, data that is not important. I just wanted to show you how to get some stuff in here so we have some data to play with when we start doing queries, just like in the beginner series in Access.

So we got our table built. We got some sample data in here. Now we are all set to start running some queries, and we will start doing that in the next lesson.

Today we learned how to enter and edit data directly in our SQL Server tables. We learned how those identity IDs or autonumbers get assigned, and how to take some data from Access, copy it into Excel, pretty it all up, and then paste it right into our tables.

Do not forget, you can order parts 2 and 3 right from my website. They are now available. You can also add this course to your Learning Connection if you are currently a member. As a reminder, Silver Members and up get one free class every month, so you can use them to get these classes.

That is going to do it for lesson 6. I hope you learned something. Live long and prosper, my friends. I will see you soon for part 7.

Quiz

Q1. What is the primary method shown in the video for editing and entering data directly into a SQL Server table using SSMS?
A. Using SQL INSERT INTO statements
B. Editing data in the Edit Top 200 Rows mode
C. Importing data through the SQL Server Import Wizard
D. Writing VBA code in Access

Q2. When does SQL Server assign an identity or autonumber ID to a new record?
A. As soon as you start typing in the new row
B. When you save or move off the new row
C. When you close the table
D. As soon as you open Edit Top 200 Rows

Q3. What do the pencil icon and the red exclamation point next to a row in SSMS indicate?
A. The record is committed and final
B. The row is selected for deletion
C. The record has been modified but not yet saved (dirty)
D. The record contains invalid data

Q4. What will happen if you try to commit a record that is missing data in a required field?
A. The record is saved with null values
B. SSMS will save the record but mark it as incomplete
C. An error will occur preventing you from saving the record
D. The record is saved with default values

Q5. In the context of SQL Server and Access, how is a Boolean (bit) value recommended to be entered for consistency?
A. Using TRUE and FALSE only
B. Using negative one and zero
C. Using YES and NO
D. Using one and zero

Q6. When entering a Discount Rate in the video, how is the value suggested to be represented in the table?
A. As an integer percent (e.g., 50 for 50 percent)
B. As a decimal fraction (e.g., 0.5 for 50 percent)
C. As a string
D. As a whole number without a decimal

Q7. Why is Excel used as an intermediate step when transferring data from Access to SQL Server?
A. Excel can automatically fix all data type issues
B. It allows for easy formatting and alignment of data columns before pasting into SQL Server
C. It provides a secure way to transfer sensitive data
D. SQL Server can only import data from Excel

Q8. What should be done to the header row in Excel before pasting data into the SQL Server table?
A. Leave it as is for column names
B. Move it to the last row
C. Delete the header row
D. Highlight it for formatting

Q9. What formatting issue must be addressed before pasting credit limit values from Excel to SQL Server?
A. Remove all decimal places
B. Remove the dollar sign formatting
C. Format the values as text
D. Change numbers to percentages

Q10. What is the recommended universal date format promoted in the lesson?
A. Day/Month/Year
B. Month/Day/Year
C. Year/Month/Day
D. Year/Day/Month

Q11. What is a limitation of direct table edits in SSMS compared to Access, as described in the lesson?
A. You cannot edit any data
B. You can only paste one record at a time
C. You cannot select and paste multiple columns or fields simultaneously
D. You cannot delete rows

Q12. Why is the process shown in the lesson recommended only for sample or unimportant data?
A. It is too slow for production use
B. It may bypass necessary data integrity checks
C. It is the only way to enter any data
D. It automatically creates backups

Answers: 1-B; 2-B; 3-C; 4-C; 5-D; 6-B; 7-B; 8-C; 9-B; 10-C; 11-C; 12-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from SQL Server Learning Zone covers how to enter and edit data directly into your SQL Server tables using SQL Server Management Studio, or SSMS. I am going to walk you through the basics of adding new records directly, handling required fields, understanding when identity IDs are assigned, and how to import some sample data from Access using Excel as an intermediary for easier formatting. This will set you up with data to practice queries as we progress.

Usually, when you are working with SQL Server tables, most of your interaction comes through your front end, typically Access. However, as a developer, there are times when it is helpful to make changes or add records directly in the backend for testing or setup purposes. Just like with Access, you do not want users editing tables directly, and ideally, as the developer, you will not do it much either, but it is important to know how.

The quickest way to input data is to use the "Edit Top 200 Rows" option on your table in SSMS. That allows you to edit the table somewhat like Access's Datasheet View. Keep in mind, "Select Top 1000 Rows" will just show you data in read-only mode, but "Edit Top 200 Rows" lets you make changes.

When you enter data, you might notice that you cannot just click into any cell; for instance, some fields may be locked. You can tab across to the fields where input is allowed, like First Name, and type in your data. A couple of key differences from Access become apparent at this point. In Access, the autonumber field is assigned as soon as you start entering a record, but SQL Server waits until you actually save the record before assigning its identity value. This timing can matter, especially if you rely on those IDs for related records.

As you type, you will see icons indicating the state of your record. The pencil symbol means the record is being edited; the red exclamation mark means the row has not yet been committed or saved. Once you move off the row, the changes are saved, and those warning icons go away. If you try to save without completing all required fields (for example, leaving Last Name blank if it cannot be null), you will get an error message. Read these carefully - theirs can be even less clear than Access's at times. Just correct your entry, and continue.

Sometimes, you will see a message saying the row was committed but could not be read back. That is a quirk that occasionally happens - simply close and reopen the table editor to see your changes.

For fields like Discount Rate, we are sticking with the convention familiar to Access users: storing percentages as fractions. Ten percent is stored as 0.1, fifty percent as 0.5, and so on. This matches how calculations work in Access and keeps things consistent across your applications. If you need to display the value as a percentage later, you can always format it as needed in Access or SSMS.

Dates might show up in detailed or odd-looking formats with a lot of numbers beyond just the date. SSMS simply displays the full database value, including milliseconds, but you will format these properly for your end users in Access or in your queries.

Speaking of dates, I use the ISO format (year, month, day), set at the Windows level. This avoids confusion, especially in international environments, and ensures everyone reads the date the same way. I encourage you to do the same.

For Boolean or yes/no type fields, even though SSMS might display as True/False, SQL Server stores these as bits. Get in the habit of entering 1 for true and 0 for false. This will make your T-SQL and Access queries easier later since Access is forgiving with values like Yes/No or True/False, but SQL Server is not always so flexible.

Another important distinction with SQL Server is when identity values are assigned. Unlike Access, SQL Server does not create the ID until the record is saved. For most situations this is not critical, but for those working with VBA code that needs an ID for a new record (for instance, to create child records), you need to save your record first, get the new ID, and then proceed with related data.

At this point, I wanted to bring in some sample data from Access. The easiest way is to first export your data to Excel and tidy it up there. In Excel, you can rearrange your columns to match the SQL Server table, delete extra fields that do not exist in SQL Server, and make sure there are no values in the customer ID column since SQL Server will generate those automatically. For new fields like Discount Rate, you can insert a blank column, leaving it empty if you have no data for that field.

After setting up your data in Excel, remove the header row. Copy the range of data, and then paste it directly into your SQL Server table editor in SSMS. Make sure you have properly formatted your numbers and dates; for example, remove dollar signs from numbers and format dates as short dates to avoid errors. If you encounter any issues (such as credit limits or dates not pasting correctly), double-check formatting in Excel and try again.

Remember, this method of copying data is only recommended for small sets of sample data or importing starter information. It is not meant for ongoing or critical data entry but is very handy to populate your tables for testing and learning purposes.

With sample data now loaded into your SQL Server tables, you are ready to move on to running queries, which we will start covering in the next lesson.

In summary, today I showed you how to enter and edit data in SQL Server tables using SSMS, highlighted important differences from Access like when identity IDs are generated, and demonstrated how to transfer data from Access to SQL Server using Excel to help with formatting.

Parts 2 and 3 of this course are now available for order on my website, and if you are a member, you can add them directly to your Learning Connection. Silver Members and above can get a free class each month, so you might use that on these lessons.

You will find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Entering data directly into SQL Server tables using SSMS
Editing existing data in SQL Server tables
Understanding how identity IDs are assigned in SQL Server
Handling required fields and null errors during data entry
Recognizing and interpreting dirty record indicators in SSMS
Committing and saving changes to records in SSMS
Formatting percentage fields to match Access conventions
Understanding SQL Server datetime display formats
Working with bit fields using 1 and 0 for true and false
Copying data from Access tables to Excel for formatting
Adjusting columns and formatting data in Excel for import
Deleting unnecessary columns and headers for SQL Server compatibility
Fixing currency and date formats for SQL Server import
Copying and pasting formatted data from Excel into SQL Server
Troubleshooting common issues when pasting data into SQL Server
Deleting existing records before importing new data
Best practices for entering sample data for queries and testing

Article

In this lesson, you will learn how to enter and edit data directly in your SQL Server tables using SQL Server Management Studio, or SSMS. This process is handy when you want to manually add or modify records for development or testing purposes, rather than setting up all data entry through your front-end application. While end users should never interact directly with the tables, knowing how to edit data in SSMS is a valuable skill for developers.

To begin, open SSMS and connect to your SQL Server database. Find the table you want to work with in the Object Explorer. Right-click the table and choose "Edit Top 200 Rows." This option opens a grid interface similar to datasheet view in Access, allowing you to edit data directly. Note that the "Top 200 Rows" part is simply a default; you can change this setting if needed.

In the editable grid, some fields may be locked, particularly identity fields like the primary key. You will not be able to enter values into these columns because SQL Server manages them automatically. Move to another column, such as "FirstName," and enter your data. For example, you might start with your own name: type "Richard" in the FirstName column.

A key difference from Access is how identity columns, like an auto-number primary key, are handled. In Access, as soon as you start typing a new row, an ID is assigned immediately. In SQL Server, the identity column value is not assigned until you save the record, which generally happens when you move off the row. While editing, you might notice a pencil icon indicating the row is "dirty," meaning there are unsaved changes. Alongside the pencil, you might see a red exclamation point. This just means the row has been edited but not yet saved to the database. Once you move to a different row or otherwise commit the change, the data is saved, and these icons will disappear.

If you try to move off a new row without filling in required (non-nullable) fields, SSMS will show an error message. For example, if your table requires "LastName" and you leave it blank, you will get an error indicating that the record could not be saved because "LastName does not allow nulls." Enter a value for each required field, such as your last name, and then move off the row to save it.

Sometimes, after saving a new row, you may see an additional warning: "The row was successfully committed to the database. However, a problem occurred when attempting to retrieve the data back after commit. As a result, the data displayed is read-only. To fix this problem, rerun the query." While this looks alarming, it usually just means that SSMS had a temporary problem refreshing the display, not that the data is lost. Simply close the grid and re-open it to see your changes, complete with the assigned identity value.

When entering data, consider the formatting of specific fields. For example, if you have a "DiscountRate" field intended to store percentages, you might be used to entering 10 for 10 percent in Access. In SQL Server (and best practice in Access as well), store this value as a fraction of one, so 10 percent becomes 0.1, 50 percent is 0.5, and so on. This makes math in your queries simpler, as you will not have to remember to divide or multiply by 100. You can always format the number for display purposes in your application.

For currency fields such as "CreditLimit," just enter the number without a dollar sign or any commas (for example: 5000). SQL Server expects raw numbers and will not accept formatting symbols. For date fields, like "CustomerSince," enter the date in ISO format: year-month-day (for example: 2024-06-10). Depending on your Windows regional settings, the input and display formats may vary, but the ISO format is understood by SQL Server and should work anywhere. If you see the date with a long string including milliseconds, do not worry; SQL Server stores dates with that level of precision. You can format it later in your application or in your SQL queries.

Boolean fields in SQL Server are called "bit" fields. While Access might accept "Yes", "No", "True", or "False" for these values, in SQL Server you should get used to using 1 for true and 0 for false. This habit will help you avoid confusion in your SQL code and make your statements more portable and predictable.

Remember, when you add a new record, the server assigns the identity column value (like "CustomerID") after inserting the row into the database. Sometimes, especially in coding, you will need immediate access to this new ID - for example, if you are going to add related records. The typical process in SQL Server is to insert the row first, then retrieve the assigned identity value using a query, and then use that ID as needed.

You can also quickly load sample data into your table from another source, like Access, using Excel as a go-between. This approach is especially helpful when you need to set up a base of test data or move customer information from one database to another.

First, export your data from Access: open the table in Access, select all rows, and copy them (Ctrl+C). Next, open a blank worksheet in Excel and paste the data. Clean up the data in Excel so the columns match those in your SQL Server table. For example, remove the ID column because SQL Server will assign it, delete any columns that do not exist in your target table (like "Phone" if it is not needed), and add new columns if necessary (such as a blank column for "DiscountRate" if that did not exist in Access). Make sure the column order in Excel matches exactly the order in your SQL Server table.

Format any problematic fields in Excel before copying. For example, ensure currency fields are just numbers, not formatted with dollar signs or extra decimal places, and that date fields are set to short date format for easy import. Remove headers so you only have raw data.

Go back to SSMS, open "Edit Top 200 Rows" on your target table, and select the first empty cell. Paste your data directly from Excel into the grid. If you see errors when pasting, double-check that the data types of your source data match those in the SQL Server table. For example, if your "CreditLimit" column in SQL Server is numeric but your Excel data includes a dollar sign, SQL Server will reject it. Fix these issues in Excel and try again.

This paste method is only suitable for small amounts of sample data or initial loads for testing. For larger datasets or production use, consider more robust import tools or use SSMS's built-in "Import Data" wizard.

Once your data is loaded, you will see the records complete with assigned identity values and everything formatted as needed for use in your database and applications. This gives you a set of real information to work with for future queries and analysis.

In summary, with SSMS you can quickly enter or edit data directly, observe how identity columns are assigned, and transfer data in from Access or other sources using Excel as an intermediary. Always be sure your data types and formats match your SQL Server table, and use this approach primarily for setup, testing, or learning - not for ongoing production data entry. With these skills, you are now prepared to start building and running queries on real data in your SQL Server database.

Navigation

Links

Keywords