Place to Upload Tables and Execute Sql Code

In the previous article, nosotros've created two tables, and now we're set to utilise the SQL INSERT INTO TABLE command and populate these tables with data. In order to practise and then, we'll set statements in Excel and then paste these statements into SQL Server and execute them. Nosotros'll also cheque the contents of both tables earlier and after these commands using the SELECT statement. So, let's kickoff.

INSERT INTO TABLE statement

In that location is no point in creating database structures and non having whatsoever data in the database. The INSERT INTO TABLE argument is the one we'll use to solve this problem. It is one of the iv of import SQL DML (Data Manipulation Linguistic communication) statements SELECT … FROM, INSERT INTO Tabular array, UPDATE … SET, and DELETE FROM… The consummate syntax is pretty complex since the INSERT INTO could besides be a very complex argument. Please take a look at the T-SQL INSERT INTO Tabular array complete syntax here.

In this commodity we'll apply simplified, but also most common syntax:

INSERT INTO table_name (column_list) VALUES (column_values);

In such INSERT INTO statement, you'll demand to define the table_name where you lot're inserting data into, listing all columns (perhaps you lot'll utilise all of them, merely maybe merely a few of them), and then list all values. Discover that column values should match column types definition (eastward.g., yous can't insert a textual value into the numerical cavalcade/attribute).

In instance you're inserting all values in the table, y'all don't need to list all columns after the table_name and you could use even more simplified syntax:

INSERT INTO table_name VALUES (column_values);

I personally prefer listing all cavalcade names, because this approach would work even if we add new columns to the existing table.

• Notation: The INSERT INTO TABLE statement could be written in such manner we insert multiple rows with 1 statement or even combined with the SELECT statement.

The simplified syntax for one such case where INSERT and SELECT statements are combined is given below:

INSERT INTO destination_table (column_list, …)
SELECT column_list
FROM source_table
WHERE status;

INSERT INTO TABLE example

Earlier doing annihilation, permit's cheque what is stored in our tables. This is the model we have created in the previous article. Y'all can see that we take i tabular array where we'll shop data related to countries and some other i for information related to cities. They are as well related to each other, only we'll talk about that in the following article:

In gild to check the contents of these two tables, nosotros'll apply two unproblematic SELECT statements:

SELECT * FROM land ; SELECT * FROM metropolis ;

While SELECT is not the topic of this commodity, it should be mentioned that its' basic syntax is:

SELECT 1 or more attributes FROM tabular array;

The star (*) afterwards SELECT represents that nosotros want to show the values of all attributes/columns from that tabular array in the query result.

As expected, there is zero in these two tables, and SQL Server returns the consequence, as shown in the picture below. Statements return names of the columns from the tables we used in the SELECT query, but at that place is nothing nether these column names. Yous can look at this as an empty Excel sheet with divers column names (headers). Y'all know what type of information should exist at that place, simply there is null:

Now, we'll demand to modify that.

Kickoff, nosotros'll populate the country table using the following INSERT INTO TABLE statements:

INSERT INTO country ( country_name , country_name_eng , country_code ) VALUES ( 'Germany' , 'Frg' , 'DEU' ) ; INSERT INTO country ( country_name , country_name_eng , country_code ) VALUES ( 'Srbija' , 'Serbia' , 'SRB' ) ; INSERT INTO country ( country_name , country_name_eng , country_code ) VALUES ( 'Hrvatska' , 'Croatia' , 'HRV' ) ; INSERT INTO country ( country_name , country_name_eng , country_code ) VALUES ( 'United Stated of America' , 'United Stated of America' , 'USA' ) ; INSERT INTO land ( country_name , country_name_eng , country_code ) VALUES ( 'Polska' , 'Poland' , 'POL' ) ;

Data for v countries were successfully inserted. The result is shown in the movie below. Since we had 5 INSERT INTO Tabular array statements we have 1 "(ane row affected)" message for each of these 5 commands in the "Messages" section:

Delight notation that all values (after VALUES) were ordered in the same fashion in which we listed columns (after INSERT INTO land). All three values are texts. The query would work even if nosotros oasis't ordered them in the right manner because all of them have the aforementioned data type (text), but the data would be stored in the wrong columns. In that example, we would accept a semantic error.

The next matter we need to practise is to populate the city table. We'll exercise that using the following statements:

INSERT INTO urban center ( city_name , lat , long , country_id ) VALUES ( 'Berlin' , 52.520008 , xiii.404954 , one ) ; INSERT INTO city ( city_name , lat , long , country_id ) VALUES ( 'Belgrade' , 44.787197 , 20.457273 , ii ) ; INSERT INTO city ( city_name , lat , long , country_id ) VALUES ( 'Zagreb' , 45.815399 , fifteen.966568 , 3 ) ; INSERT INTO city ( city_name , lat , long , country_id ) VALUES ( 'New York' , 40.73061 , - 73.935242 , four ) ; INSERT INTO urban center ( city_name , lat , long , country_id ) VALUES ( 'Los Angeles' , 34.052235 , - 118.243683 , four ) ; INSERT INTO city ( city_name , lat , long , country_id ) VALUES ( 'Warsaw' , 52.237049 , 21.017532 , 5 ) ;

Afterward executing these statements, this was the result. As expected, 6 rows were added. And over again we have ane message for each insert in the Messages section:

In this case, we would have a problem if nosotros haven't listed values in the same manner, we listed columns considering their data types are not the same (they are – in order: text, decimal number, decimal number, integer). This type of mistake is called syntax error and the DBMS itself would prevent the query from running at all.

SELECT – Check what was inserted

Now nosotros'll once more check what is stored in our tables. We'll use the same two SELECT statements we have used previously:

SELECT * FROM land ; SELECT * FROM city ;

The result is shown in the moving-picture show beneath. Please discover that afterward executing queries at present nosotros have Results and Letters sections nether queries:

We can conclude that both tables in our database contain data and now we're prepare to "play" with something fashion cooler than this.

INSERT INTO TABLE using Excel

In many cases, you'll need to run multiple SQL statements based on the dataset provided to you. This stands not only for the INSERT INTO Table statement but also for UPDATE and DELETE statements. There is no point in typing these statements manually, simply you should rather go with a smarter approach – prepare formulas (or a script) that volition automate this part. In such situations, I prefer using Excel and formulas.

Notation: Personally, in the context of databases, I find Excel very useful when I demand to create multiple statements and when presenting results and/or creating dashboards.

Let's take a expect at these formulas:

The formula used to insert the first country (Germany) is:

="INSERT INTO land ( country_name , country_name_eng , country_code ) VALUES ( '" & B2 & "' , '" & C2 & "' , '" & D2 & "' ) ;"

The formula used to insert the first city (Berlin) is:

="INSERT INTO urban center ( city_name , lat , long , country_id ) VALUES ( '" & B2 & "' , " & C2 & ", " & D2 & ", " & E2 & ") ;"

Feel free to utilize these formulas to automate your tasks. We'll use a similar arroyo subsequently when we are running multiple UPDATE and DELETE statements (and even when creating SELECT statements).

Conclusion

In this article, we've covered 1 of the 4 nigh of import SQL statements –INSERT INTO TABLE statement. We've used information technology to populate tables created in the previous article. This was a prerequisite to movement to smarter stuff – like database theory, and more importantly, returning results from our database.

In the upcoming article, we'll talk near the primary cardinal – what it is and why is information technology of import in the databases.

Table of contents

Learn SQL: CREATE DATABASE & CREATE Table Operations
Acquire SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Primal
Learn SQL: SELECT statement
Acquire SQL: INNER Bring together vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Acquire SQL: Aggregate Functions
Acquire SQL: How to Write a Circuitous SELECT Query
Larn SQL: The INFORMATION_SCHEMA Database
Larn SQL: SQL Data Types
Larn SQL: Set up Theory
Learn SQL: User-Divers Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Larn SQL: SQL Query examples
Acquire SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Acquire SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL All-time Practices for Deleting and Updating information
Learn SQL: Naming Conventions
Larn SQL: SQL-Related Jobs
Larn SQL: Not-Equi Joins in SQL Server
Learn SQL: SQL Injection
Larn SQL: Dynamic SQL
Acquire SQL: How to prevent SQL Injection attacks

• Author
• Recent Posts

Emil is a database professional with ten+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works equally a freelancer.

His past and present engagements vary from database blueprint and coding to teaching, consulting, and writing about databases. Also non to forget, BI, creating algorithms, chess, philately, two dogs, ii cats, 1 wife, 1 baby...

You can find him on LinkedIn

View all posts by Emil Drkusic

byrnetrallese.blogspot.com

Source: https://www.sqlshack.com/learn-sql-insert-into-table/

Share this post