Loading data is one of the most important aspects of working with any database system. A database is a container that store information, developers and application’s need to ensure that the data is stored correctly and with the correct associations.
If you think about a typical logon screen you would enter your user name and password in order to gain access to a application or system. Imagine if you went to logon to your Facebook or into your email and your username and password was not stored correctly in the database. Or worse image if your bank account balance was not stored properly and you was given my account balance with $1 instead of your account which had a lot more money. We can all agree this would not be a good thing hence the reason that loading data is a critical aspect of databases.
Data stored in a database typically come from one of two sources either user input or from a system application. System application usually create data behind the scenes while user input is data collected directly from user. When data is generated by the system it is typically done programmatically using some code that was developed specifically for the data that is to be loaded into the database.
In this post I want to discuss two of the simplest ways that data can be loaded into SQL Server using T-SQL. After reading though this post and reviewing the example you will be able to loaded data into SQL Server. For the purpose of this post we will keep the examples simple and focus on the T-SQL INSERT Statement. We are going to look at the different ways you can use the INSERT statement to load data into a database table.
Let suppose you have a table in your database that contain a list of attributes(columns). The table name is “dbo.Users” and the columns in this table are UserID, FirstName, LastName, EmailAddress and DateOfBirth. In the examples below we will review the different ways we can populate data into this table using T-SQL.
In this first example we will create a table called “dbo.Users” and use the INSERT keyword to load data into the table. The INSERT keyword will be used with the Values keyword to load the data.
/* Create Table dbo.Users */ Create table dbo.Users ( UserID INT Identity(1,1), FirstName NVARCHAR(50), LastName NVARCHAR(50), EmailAddress NVARCHAR(100), DateOfBirth date ) /* Basic one row Insert */ INSERT dbo.Users ( FirstName, LastName, EmailAddress, DateOfBirth) VALUES ('Tywan', 'Terrell', 'firstname.lastname@example.org', '06/13/1985') /* You can use the same syntax to insert multiple rows into the by separating the data into to be inserted with commas like below */ INSERT dbo.Users ( FirstName, LastName, EmailAddress, DateOfBirth) VALUES ('Tywan', 'Terrell', 'email@example.com', '05/13/1988'), ('Mike', 'Terrell', 'Mike.firstname.lastname@example.org', '04/24/1971'), ('Tim', 'Terrell', 'Tim.email@example.com', '03/15/1961') /* The Select code below will show the data that was inserted from the above Insert Statement you will notice that even though the UserID column was not included in the INSERT Statement it has a value in the database */ Select * from Users
In this next example we will look at loading data into “dbo.Users”from existing tables using data that from existing tables in a database. In the code below we used a SQL statement to join tables Person and Email Address to get the first name, last name and email address and then load the results of the select into the “dbo.Users” table
INSERT INTO [DBO].[Users]([FirstName], [LastName], EmailAddress) SELECT DISTINCT P.FirstName, p.LastName, e.EmailAddress FROM [AdventureWorks2012].Person.Person P inner join [AdventureWorks2012].Person.EmailAddress E on p.BusinessEntityID = e.BusinessEntityID
In this post we looked at two different ways that we can load data into SQL Server tables. There are may more ways that we can load data in to tables these are two of the simplest way. In future post we will look at more advance ways of loading data into SQL Server tables.