Insert Data into the Table with SQL Script

A free video tutorial from Trevoir Williams
Top-Rated Instructor • 350,000+ Students
Rating: 4.5 out of 5Instructor rating
24 courses
385,397 students
Insert Data into the Table with SQL Script

Learn more from the full course

Microsoft SQL Server Development for Everyone

Learn the fundamentals of database design, development and querying using the latest Microsoft SQL Server

14:11:24 of on-demand video • Updated November 2022

Basic Database Design (Tables, Columns, Data Types, Primary Keys, Normalization)
Using Microsoft SQL Management Studio
SELECT Statements: Queries against single tables (Filtering, Ordering, Grouping)
DELETE: How to remove data from a database
INSERT INTO: How to insert data into a table
UPDATE: How to update existing data
Filter groups of data using the HAVING clause
Overall Database Development Best Practices
Creating Relationships and Foreign Keys
How to query related data from multiple tables
Using Aggregate Functions in SQL
How to export Query Results to Excel
How to craft SQL statements based on real scenarios
Create Complex Database Objects (Stored Procedures, Functions, Views, Triggers)
Backup and Restore Databases
Generate Database Scripts for Automation
How to manage database users and security
English [Auto]
Hey guys in this video we will start inserting some records into our database tables. And today our database table of choice is students. So we're going to create a few student records and we look at using a school script to do so as well as how we can use the editor to accomplish this task. Now there are a few things you have to do before you can just start writing your script and one of them is to select the database that we're about to use our targets for or scripts. So once again to select that need to be as we have to use the use keyword and we specify the database by name. So in this case we want school and then we go about creating or a script. Now the keywords involved in entering are our record or in searching our record are literally studying this really and truly so have insert and then we say into and then we're expected to see what database table and then what columns we're inserting into. So do recall when we created our table we created it with a few columns and you can always just drill down on that in the object explore and expand the columns and see and refresh yourself and you can see insert into and the table name is students and sometimes you'll actually see people write of the entire name Beebe or dot students. So BBDO is just the key word that is within the context of Microsoft Askew a server and it sits in front of most tables. So even if you didn't put it there chances are you will see it appearing there. The default settings. All right so you can always just see insert into what leave off the BBB for No. And the table name and then you open a parenthesis or you don't need to space it there and close. And then in these four indices you actually specify what columns you are going to be entering data into. Now this is important because remember when we were configuring our tables we set up one of the columns which we called Ida to be our primary key and all to incrementing that means when we're inserting our record we don't have to put anything in here we have to specify other columns that last name first name date of birth and enrollment date. And what we cannot put or shouldn't be attempting to put anything here. So we need to specify also do recall that one of these was a to be low which was enrollment. So I can just hover over and you would see or it does explain object explore and you see here last name not no first name not no date of birth not no but enrollment beat could be no which means I could specify up to three columns I don't have to specify all four of these. So I'm just showing you that this is why we specify the columns because not ever record me and get every bit of detail. Of course if the data point is not knowledgeable then it will not execute the script because I can't be putting in a student record I can't be inserting something into the students table we go to one of these mandatory data points. All right. So let's work through this. So the first one is last name and I know some people don't like to type so you can actually just drag and drop and then you would see that the school editor is actually putting these square brackets so that's also a feature that you would notice when if school management studio generates some of the Esko for you then you should put these in square brackets as we go on. Explain what those square brackets are for. So we can just mix and match and we just drag and drop across and fill this entire record with all the data points. So we're inserting a new store where inserting into the students table into the columns last name first name date of birth enrolment date and then you see that the red line is there. So that means the script is still not quite finished because we need to specify what values will go into each column. So we have to see values and I just break the line and bring that down. And then we open and close parentheses again in these part of this is we have to make sure that the values that we insert here line up with the columns that were ordered here. So the first value has to be the last name and sorry in school. I find it easier to just use the single quotes for string. I use double quotes and that was an accident use single quotation marks when using askew. Microsoft excuse that these so last name and I will insert Reynolds first name so Reynolds lines up with last name the next value should line up with first name. Maggie dates of birth the date time four months that is default in a school server is Year Month Day. And that comes in the y y y y dash m m dash E D. If you're familiar with Excel then you're familiar with these days for months if not that's fine. When entering a date in S2 into a column that is a date time or even dates once it's a date type the expected four months should be the year. So we're in the dispersal is born in nineteen eighty eight the first month and the 15th day. So once again that's year month day that is the expectation and for enrolment dates we will enter 2019 dash 0 5 dash 0 1. And so that is the first off me two thousand and nineteen. And then we can review this and ensure that all the values are correct and correctly aligned. Once again these values have to correspond with the order that the columns appear in. And then we can go ahead and execute. And once we execute we know what is successful and it tells us one rule affected. That means whatever it is we did. Our rule was somehow affected or that it was created in other examples you see where we can manipulate the data otherwise. So this is an indicator that the script was successful in manipulating that rule. So let us try and insert another record so let's read this script again from scratch. So once again we say insert into. And then we choose a table name students and then we open and close parentheses and then we list all the columns somewhere to mix up the order of the columns a bit and I to say first name first then last name then date of birth. That's a matter of fact I'm going to do enrollment dates I wanted to see the arrows that you would get when you don't follow the rules that you've set for the columns. So values. And then in the values listing we have to make sure that firstly we have a first name we can see Judy Mackintosh and these are purely fictional names and enrollment dates. Just say no. Because we can pass in no value. And then I'm going to execute this. The expectation is that we should get an arrow because we did see that date of birth is mandatory. It's we're here trying to insert a new students without a date of birth value. So if I say executes then we see cannot insert volume into that column. So we do get an error message fails. So we know we need to rectify our scripts to reflect our rules. So I have to say. Date of birth. And I have to have a value so 1990 for the third month. That is the year month date. And then we execute and then we'll see. Voila. One rule affected let's just make one more adjustments to this type of script. So I'm just going to instead of taking everything else I'm just going to erase the values and I'm also not going to insert the enrollment dates so first name last name date of birth to recall that enrollment date is notable so it is not a mandatory field. So this time we want Tricia Williamson and her dates of birth. Is the year 1995. If months seventh day and then we execute and there you have it. So this record went into the database without any enrollment date. So to this point we would have at least three records in our database. Now you're probably thinking this is tedious. Every time I have somebody to insert and when to have to erase or you know rearrange or something. But then there is a way to actually do a multiple inserts using one insert into statement and that's what we're about to do. So when we're going to be inserting multiple records in one statements what we do is we maintain this first line to insert into a table name and then we spell out all of the columns and let's just say all of these next two records have an enrollment date. So what we do is write the first one test and I'm just going to give very shorthanded no students and this person was born. Close in one month. If they and you kind of level two dashes that they'll use slashes it may be forgiving sometimes when you mix up your date for months but ultimately that's the date format it expects. So you can just stick to that and the enrollment beat is no no when I want multiple records in one statement what I do is just write comma and then go to the next line and write another one of these. I'm just going to take this line and copy and paste. And this person is tests test one student one and leave the same date of birth or just change it so that we can see the difference in the data and then as many records as you will have you just writes a comma and you can just duplicate that line. And what I just did to duplicate is another keyboard shortcuts I held on on control and c and v which is copy and paste. Well then without highlighting the line it just duplicates whatever line the cursor is on. So that's another neat trick that you can trial second dysfunctional CV and put in as many as possible. All right. Each one comma separated. Up until the very last one in the series and of course the editor will make no mistake in pointing it out to you that this is in. So I'm just going to go ahead and fix up these records a bit and we will then see how this works. All right. So I've made some adjustments to the scripts. We are inserting into the table and I just put some differences in the data so that we can move ahead and not be confused as to any repeating data so that you can see where each record is unique in its own right and left some of the enrollment data as No. Put in some and I'm just showing you that as many records as you have you can use this. Just use that technique and string them along and then we will execute one time and then we can see that we move from the usual Wanneroo afflicted to seven rows affected because we inserted 1 2 3 4 5 6 7 students. So as many records as you put in once it does not fail you will see a message like this indicating that you have done something right. No I know that we've been inserting inserting inserting and you're probably wondering you know is this icon. I'm not seeing the data I'm just seeing a sentence as is rules affected. I want to see the data. That's fine. We do have another video coming up where we start extracting data but then until then I'm going to show you very quickly to view the data and also how we use the editor to replicate this kind of functionality. So I did some comments to this script and I'm going to show you how we can save a script file so you've written a script like this maybe you were tasked with writing the script to actually put in all the student records and then you have to submit it to our database administrator or whoever is going to actually pull the trigger and you need to have this file exportable you can actually just save the file control s or go to file and click save and then that will come to us to where you want to put the files. I'm just going to put this one on the desktop and I'll see insert students and the default file extension for any Eskil file is thought if you will. So I can just see that. And then you will be able to come back or open this and execute it. On another computer or in another setting where you have a school database a database name school and all of the beach needs to go into those stables. So that is how you go about exporting your scripts and saving them for future reference. Maybe you're working on it. I need it for later this is how it's done.