If you had a need to save the resulting data set returned by an SQL query, then this article will be of interest to you, since we will consider SELECT INTO statement, with which Microsoft SQL Server you can create a new table and populate it with the result of an SQL query.

We'll start, of course, by describing the SELECT INTO statement itself, and then move on to the examples.

SELECT INTO statement in Transact-SQL

SELECT INTO- a statement in the language in T-SQL, which creates a new table and inserts the resulting rows from the SQL query into it. Table structure, i.e. the number and names of columns, as well as data types and nullability properties, will be column-based ( expressions) specified in the source select list in the SELECT statement. Typically, the SELECT INTO statement is used to combine data in one table from several tables, views, including some calculated data.

The SELECT INTO statement requires CREATE TABLE permission on the database in which the new table will be created.

The SELECT INTO statement has two arguments:

• new_table - name new table;
• filegroup - filegroup. If no argument is given, the default filegroup is used. This opportunity available starting with Microsoft SQL Server 2017.

Important points about the SELECT INTO statement

• The statement can be used to create a table on the current server; creating a table on a remote server is not supported;
• You can fill a new table with data both from the current database and the current server, and from another database or from a remote server. For example, specify the full database name in the form database.schema.table_name or in the case of a remote server, linked_server.database.schema.table_name;
• An identity column in a new table does not inherit the IDENTITY property if: the statement contains a join (JOIN, UNION), a GROUP BY clause, an aggregate function, also if the identity column is part of an expression, comes from a remote data source, or occurs more than once in a list choice. In all such cases, the identity column does not inherit the IDENTITY property and is created as NOT NULL;
• A SELECT INTO statement cannot create a partitioned table, even if the source table is partitioned;
• You can specify a regular table as a new table, as well as a temporary table, but you cannot specify a table variable or a table-valued parameter;
• A calculated column, if there is one in the select list of the SELECT INTO statement, it becomes normal in the new table, i.e. not computable;
• SELECT INTO cannot be used with a COMPUTE clause;
• Using SELECT INTO, indexes, constraints and triggers are not transferred to a new table, they must be created additionally, after the statement is executed, if they are needed;
• The ORDER BY clause does not guarantee that the rows in the new table will be inserted in the specified order.
• The FILESTREAM attribute is not transferred to the new table. The FILESTREAM BLOBs in the new table will be as varbinary(max) BLOBs and have a 2 GB limit;
• The amount of data written to the transaction log during SELECT INTO operations depends on the recovery model. In databases that use the bulk-logged recovery model and the simple recovery model, bulk operations such as SELECT INTO are minimally logged. As a result, a SELECT INTO statement can be more efficient than separate statements to create a table and an INSERT statement to populate it with data.

SELECT INTO Examples

I will run all examples in the Microsoft SQL Server 2016 Express DBMS.

Initial data

To begin with, let's create two tables and fill them with data, we will combine these tables in the examples.

CREATE TABLE TestTable( IDENTITY(1,1) NOT NULL, NOT NULL, (100) NOT NULL, NULL) ON GO CREATE TABLE TestTable2( IDENTITY(1,1) NOT NULL, (100) NOT NULL) ON GO INSERT INTO TestTable VALUES (1,"Keyboard", 100), (1, "Mouse", 50), (2, "Phone", 300) GO INSERT INTO TestTable2 VALUES (" Computer components"), ("Mobile devices") GO SELECT * FROM TestTable SELECT * FROM TestTable2

Example 1 - Creating a table with a SELECT INTO statement with data join

Let's imagine that we need to merge two tables and store the result in a new table ( for example, we need to get products with the name of the category they belong to).

Operation SELECT INTO SELECT T1.ProductId, T2.CategoryName, T1.ProductName, T1.Price INTO TestTable3 FROM TestTable T1 LEFT JOIN TestTable2 T2 ON T1.CategoryId = T2.CategoryId --Select data from a new table SELECT * FROM TestTable3

As a result, we created a table called TestTable3 and filled it with the combined data.

Example 2 - Creating a temporary table using the SELECT INTO statement with data grouping

Now let's say that we need grouped data, for example, information about the number of products in a certain category, while we need to store this data in a temporary table, for example, we will use this information only in SQL statements, so we do not need to create a full-fledged table.

Create a temporary table (#TestTable) using SELECT INTO SELECT T2.CategoryName, COUNT(T1.ProductId) AS CntProduct INTO #TestTable FROM TestTable T1 LEFT JOIN TestTable2 T2 ON T1.CategoryId = T2.CategoryId GROUP BY T2.CategoryName -- Fetching data from a temporary table SELECT * FROM #TestTable

As you can see, we managed to create a temporary table #TestTable and fill it with grouped data.

So you and I have examined the SELECT INTO statement in the T-SQL language, in my book “ The Way of the T-SQL Programmer" I talk in detail about all the constructions of the T-SQL language ( I recommend reading), and that's all for now!

• How can I combine SELECT statements so that I can calculate percentages, successes and failures in SQL Server?
• SQL: How to select only individual rows based on some property values
• How to choose the product with the highest price of each category?

I need to duplicate the same data in the database, changing only the product id from 1 2.3.... 40

LevelId Min Product 1 x 2 2 y 2 3 z 2 4 a 2

I could do something like

INSERT INTO dbo.Levels SELECT top 4 * fROM dbo.Levels but that will just copy the data. Is there a way I can copy the data and paste it while only changing the Product value?

You are the most on the way - you need to take one more logical step:

INSERT INTO dbo.Levels (LevelID, Min, Product) SELECT LevelID, Min, 2 FROM dbo.Levels WHERE Product = 1

... will duplicate your rows with a different product id.

Also consider that WHERE Product = 1 will be more reliable than TOP 4 . Once you have more than four rows in the table, you can't guarantee that TOP 4 will return the same four rows unless you also add an ORDER BY to the select, however WHERE Product = ... will always return the same and keep going work even if you add an extra row with product id 1 (where you would need to consider changing TOP 4 to TOP 5 etc if you add extra rows).

You can generate a product ID and then upload it to:

< 40) INSERT INTO dbo.Levels(`min`, product) SELECT `min`, cte.n as product fROM dbo.Levels l cross join cte where l.productId = 1;

This assumes that LevelId is an id column that is automatically incremented on insertion. If not:

With cte as (select 2 as n union all select n + 1 from cte where n< 40) INSERT INTO dbo.Levels(levelid, `min`, product) SELECT l.levelid+(cte.n-1)*4, `min`, cte.n as product fROM dbo.Levels l cross join cte where l.productId = 1;

INSERT INTO dbo.Levels (LevelId, Min, Product) SELECT TOP 4 LevelId, Min, 2 FROM dbo.Levels

You can include expressions in the SELECT , either hard-coded values, or something like Product + 1 or whatever.

I expect you probably don't want to put in a LevelId, but leave it there to match your pattern. If you don't want it just strip it from the INSERT and SELECT sections.

For example, you can use CROSS JOIN on a table of numbers.

WITH L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows L1 AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows Nums AS(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM L1) SELECT lvl., lvl., num.[N] FROM dbo. lvl CROSS JOIN Nums num

This will repeat 4 times.

SQL - Lesson 3. Creating tables and filling them with information

Id_user - integer values, so the type will be int, let's limit it to 10 characters - int (10).
name is a varchar string value, let's limit it to 20 characters - varchar(20).
email is a varchar string value, let's limit it to 50 characters - varchar(50).
password - varchar string value, let's limit it to 15 characters - varchar(15).

All field values ​​are required, so you need to add the NOT NULL type.

id_user int(10) NOT NULL
name varchar(20) NOT NULL
email varchar(50) NOT NULL

The first column, as you remember from conceptual model our database is the primary key (i.e. its values ​​are unique and they uniquely identify a record). It is possible to follow the uniqueness on your own, but it is not rational. There is a special attribute in SQL for this - AUTO_INCREMENT, which, when accessing the table to add data, calculates the maximum value of this column, increases the resulting value by 1 and puts it in the column. Thus, a unique number is automatically generated in this column, and therefore the NOT NULL type is redundant. So, let's assign an attribute to a column with a primary key:

name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL

Now we need to specify that the id_user field is the primary key. To do this, SQL uses keyword PRIMARY-KEY(), the name of the key field is indicated in parentheses. Let's make changes:

id_user int(10) AUTO_INCREMENT
name varchar(20) NOT NULL
email varchar(50) NOT NULL
password varchar(15) NOT NULL
PRIMARY KEY (id_user)

So, the table is ready, and its final version looks like this:

Create table users (
id_user int(10) AUTO_INCREMENT,
name varchar(20) NOT NULL,
email varchar(50) NOT NULL,
password varchar(15) NOT NULL,
PRIMARY KEY (id_user)
);

Now let's deal with the second table - topics (topics). Arguing similarly, we have the following fields:

id_author int(10) NOT NULL
PRIMARY KEY (id_topic)

But in our database model, the id_author field is a foreign key, i.e. it can only have values ​​that are in the id_user field of the users table. In order to specify this in SQL there is a keyword FOREIGN-KEY(), which has the following syntax:

FOREIGN KEY (column_name_which_is_foreign_key) REFERENCES parent_table_name (parent_column_name);

Let's specify that id_author is a foreign key:

id_topic int(10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int(10) NOT NULL
PRIMARY KEY (id_topic)
FOREIGN KEY (id_author) REFERENCES users (id_user)

The table is ready, and its final version looks like this:

Create table topics (
id_topic int(10) AUTO_INCREMENT,
topic_name varchar(100) NOT NULL,

PRIMARY KEY(id_topic),
FOREIGN KEY (id_author) REFERENCES users (id_user)
);

The last table remains - posts (messages). Everything is similar here, only two foreign keys:

Create table posts (
id_post int(10) AUTO_INCREMENT,
message text NOT NULL,
id_author int(10) NOT NULL,
id_topic int(10) NOT NULL,
PRIMARY KEY (id_post),
FOREIGN KEY (id_author) REFERENCES users (id_user),
FOREIGN KEY (id_topic) REFERENCES topics (id_topic)
);

Please note that a table can have several foreign keys, and there can be only one primary key in MySQL. In the first lesson, we deleted our forum database, it's time to create it again.

We launch MySQL server(Start - Programs - MySQL - MySQL Server 5.1 - MySQL Command Line Client), enter the password, create the forum database (create database forum;), select it for use (use forum;) and create our three tables:

Please note that one command can be written in several lines using the Enter key (MySQL automatically substitutes a newline character ->), and only after the separator (semicolon) pressing the Enter key causes the query to be executed.

Remember, if you do something wrong, you can always drop a table or the entire database using the DROP statement. fix something in command line extremely inconvenient, so sometimes (especially on initial stage) it's easier to write queries in some editor, such as Notepad, and then copy and paste them into the black box.

So, the tables are created to make sure of this, let's remember the team show tables:

And finally, let's look at the structure of our last posts table:

Now the meanings of all fields of the structure, except for the DEFAULT field, become clear. This is the default value field. We could specify a default value for some column (or for all). For example, if we had a field named "Married\Married" and of type ENUM ("yes", "no"), then it would make sense to make one of the values ​​the default value. The syntax would be:

Married enum ("yes", "no") NOT NULL default("yes")

Those. this keyword is written with a space after the data type, and the default value is indicated in brackets.

But back to our tables. Now we need to enter data into our tables. On websites, you usually enter information into some html forms, then a script in some language (php, java ...) extracts this data from the form and enters it into the database. He does this by means of a SQL query to enter data into the database. We do not yet know how to write scripts in php, but now we will learn how to send SQL queries for entering data.

For this, the operator is used INSERT. Syntax can be used in two ways. The first option is used to enter data into all fields of the table:

INSERT INTO table_name VALUES ("first_column_value", "second_column_value", ..., "last_column_value");

INSERT INTO users VALUES ("1","sergey", " [email protected]", "1111");

The second option is used to enter data into some fields of the table:

INSERT INTO table_name ("column_name", "column_name") VALUES ("first_column_value", "second_column_value");

INSERT INTO users (name, email, password) VALUES ("valera", " [email protected]", "2222");

If we had fields with the NULL type, i.e. optional, we could also ignore them. But if you try to leave the field with the value NOT NULL empty, the server will issue an error message and will not fulfill the request. In addition, when entering data, the server checks the relationships between the tables. Therefore, you cannot populate a field that is a foreign key with a value that is not in the related table. You will verify this by entering data in the remaining two tables.

But first, let's add information about a few more users. To add several lines at once, you just need to list the brackets with values ​​separated by commas:

Now let's add data to the second table - topics. Everything is the same, but we must remember that the values ​​in the id_author field must be present in the users table (users):

Now let's try to add another topic, but with an id_author that is not in the users table (since we only added 5 users to the users table, id=6 does not exist):

The server gives an error and says that it cannot enter such a line, because the foreign key field has a value that is not in the related users table.

Now let's add a few rows to the posts table (messages), remembering that we have 2 foreign keys in it, i.e. The id_author and id_topic that we will enter must be present in their associated tables:

So we have 3 tables that have data. The question arises - how to see what data is stored in the tables. This is what we will do in the next lesson.

Question: filling tables sql queries

INSERT INTO tApartments([apartment#], [booth_code]) SELECT * FROM tHouses
throws an error "The INSERT INTO statement contains an unknown name for the field #budinku. Please check that the name was specified without errors, and retry the operation"
Am I missing something in my request? how to fix this error???

2nd mistake
I filled in the tTarifi table manually.
The request for the last rate using the max function looks like this:
SELECT Set_date, Tariff1, rate_code FROM tTariff WHERE Set_date = (SELECT MAX (Set_date) FROM (tTariff));
it works as a separate query, but as I understand it, from this query, the value for the tariff_code should be copied to the table tpayment_plan, i.e. I create the following query:

INSERT INTO tPayPlan(rate_code) SELECT Rate_Code FROM tRate WHERE (SELECT Rate_Code FROM tRate WHERE Set_Date = (SELECT MAX (Set_Date) FROM (tRate));)

but here is an error (I didn’t figure out how to throw pictures here, but I hope it will be clear anyway):

what am I doing wrong??? tell me please ... otherwise it’s hard for a teapot to sort out your mistakes

Attached file ( in.txt- 2Kb)

Answer: lisica198808,

In the first query, you most likely have the structure (number and types of columns) of tApartments and tHouses do not match. Instead of an asterisk, explicitly list in SELECT the names of those columns from the tBudinka table that you want to use in tApartments.

In the second query, you most likely have several columns in topay_plan, some of which are not allowed to use the NULL value. You add a value to only one column (rate_code), in the rest of the query you have, either default values ​​(if they are defined) or NULL are substituted. Hence the error.

Be sure to listen to the advice of sdku.

Question: Problems with the counter when filling out the table

Error - The value of an identity column in table "dbo.Clients" can only be explicitly specified when using a column list and when IDENTITY_INSERT is set to ON.

Answer:

Message from _JoNi_

Tried like this, now when executed it gives this
Sorry for stupid questions, just started learning sql)

Can you write the field names separated by commas after the name of the table you are inserting into?

Question: filling Word tables with bookmark data from Access query- looking for a solution

Somewhere on the Internet I found a working example of filling a table Word data from Access using bookmarks. It works (Example.zip).
Unfortunately, I'm self-taught in Access and don't know much...
No matter how I tried in my database (WORD.zip) to implement a solution from a working example, nothing works for me.
I insert elements from the example in the form of functions, as it is implemented in it, and I turn to them - I swear at everyone.
I insert parts of these functions into my code - it swears again!

What needs to be inserted not into the table, it is wonderfully substituted for the bookmarks (it works if you remove from the code everything that relates to filling in the table).

My strength is no more

Please, kind people, fix my code so that everything works!
And if you also intelligibly explain why I didn’t succeed, I will consider you the wisest and most sympathetic
Access2010

Thank you in advance!!!

P.S. In my database there are tables with Moscow streets, Surnames, First names, Patronymic names, Units of measurement (official abbreviations) - use whoever needs it

Answer: then there will be a break after 2 lines (at the insert line)
--
I couldn't get this second option to work yesterday( corbis. point 2)
the first (initial) one hour persuaded (about 10 errors)

Question: Error filling the table with data

Create a table
USE NewCompany_Ezh;
GO

CREATE TABLE EmployeeSchema.Employee
EmpID int NOT NULL,
LName varchar(20) NOT NULL,
FName varchar(20) NOT NULL,
Title varchar(20) NULL,
BirthDate date NULL,
EmpDate date NULL,
Salary decimal(18, 2) NOT NULL,
DepID int NOT NULL,
OrgNode hierarchyid NULL,
GO

filling
USE NewCompany_Ezh;
GO
DECLARE @child hierarchyid,
@manager hierarchyid = hierarchyid::GetRoot()

Root level - Director

(1, N"Ivanov", N"Ivan", N"Director", "1975-05-07", "2009-05-06", 30000.00, @manager)

Next level - Deputies

INSERT INTO EmployeeSchema.Employee VALUES
(2, N"Petrov", N"Petr", N"Deputy director", "1969-10-07", "2005-07-07", 25000.00, @child)

INSERT INTO EmployeeSchema.Employee VALUES
(3, N"Sidorov", N"Sidor", N"Deputy Director", "1981-05-05", "2009-09-09", 25000.00, @child)

SELECT @child = @manager.GetDescendant(@child, NULL)

INSERT INTO EmployeeSchema.Employee VALUES
(4, N"Eremin", N"Erema", N"Deputy director", "1986-11-01", "2009-10-10", 25000.00, @child)

The next level of the hierarchy
SELECT @manager = OrgNode
FROM EmployeeSchema.Employee WHERE EmpID = 4
SELECT @child = @manager.GetDescendant(NULL, NULL)

INSERT INTO EmployeeSchema.Employee VALUES
(5, N"Alexandrov", N"Alexander", N"Assistant", "1979-01-01", "2008-01-01", 20000.00, @child)

SELECT @child = @manager.GetDescendant(@child, NULL)
INSERT INTO EmployeeSchema.Employee VALUES
(6, N"Andreev", N"Andrey", N"Assistant", "1985-04-12", "2008-01-01", 20000.00, @child)
GO

Answer:

Message from Margaret98

NewCompany database created

It's definitely about MySQL, not about MS SQL

Question: Error #1062 when filling in a table

Answer: Everything was decided!)) you need to delete the topic!

Question: Filling a table in the database from a field (TextBox) in a form

and I don't quite understand why you need to write this? if the form and the table are in the same database, then this part of the code will look different?
Dim z As New OleDb.OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:1Application Microsoft office Access.mdb") "Establishing a connection to the database. z.Open() "Open connection.
and second

Dim t As New OleDb.OleDbCommand( "UPDATE [Table1] SET [Field1] = " "& " " & TextBox1.Text & " " WHERE [Code] = 1" , z)
why do i need the z variable?

Question: Filling tables automatically

Working with databases is directly related to changing tables and the data they contain. But before starting the actions, the table must be created. To automate this process, there is a special - "CREATE TABLE".

First thing!

Before dealing with the process of creating tables using the MS SQL "CREATE TABLE" command, it is worth dwelling on what you need to know before using the function.

First of all, you need to come up with a name for the table - it must be unique compared to others in the database, and follow a few rules. The name must begin with a letter (a-z), followed by any letters, numbers, and underscores, and the resulting phrase must not be a reserved word. The length of the table name should not exceed 18 characters.

Having decided on the name, you should develop a structure: come up with names for the columns, think over the data type used in them and which fields must be filled in. It is also worth immediately defining the fields of foreign and primary keys, as well as possible restrictions on data values.

The remaining nuances of the table can be easily corrected, so at the stage of creating a table, they may not be fully thought out.

Syntax

Having developed the structure of the table, you can proceed to its creation. This is quite easy to do using SQL function"CREATE TABLE". In it, the user is required to specify the previously invented table name and the list of columns, indicating the type and name for each of them. The function syntax is as follows:

CREATE TABLE table_name
((column_name datatype …| table_constraint)
[,(column_name datatype …| table_constraint)]…)

The arguments used in the construction of a function mean the following:

• table_name - table name
• column_name - column name
• datatype - the data type used in this field
• DEFAULT is the expression used in the default column.

It is also possible to use two more function arguments:

• colum_constraint - column parameters
• table_constraint - table parameters

In them, the user can specify the restrictions required for work or conditions for filling the table.

Features of creating tables

When writing a query with a function, it is sometimes necessary to establish rules for filling fields. To do this, you need to add special function attributes that define a particular set of conditions.

In order to determine whether a cell can contain an empty value, after specifying the name and type of the column, one of the keywords should be written: NULL (there may be empty values) or NOT NULL (the field must be filled).

When creating a table, in most cases it is necessary to unify each record in order to avoid having two identical ones. For this, line numbering is most often used. And, in order not to require the user to know the last number available in the table, it is enough to specify the column in the "CREATE TABLE" function primary key by writing the keyword "Primary key" after the corresponding field. Most often, it is by the primary key that the tables are joined to each other.

The foreign key property "FOREIGN KEY" is used to provide a linkage with the Primary key. By specifying this property for a column, you can ensure that the field contains a value that matches one of those found in the primary key column of the same or another table. In this way, data consistency can be ensured.

To provide a check against a given set or definition, use the CHECK attribute. It is written last in the list of function arguments and has some value as a private parameter. boolean expression. With it, you can limit the list of possible values, for example, using only the letters "M" and "F" in the field of the table "Gender".

In addition to those presented, the function has many more specific attributes, but they are used much less often in practice.

Examples

To fully understand how the function works, it is worth considering in practice how CREATE TABLE (SQL) works. The example below creates the table shown in the figure:

CREATE TABLE Custom
(ID CHAR(10) NOT NULL Primary key,
Custom_name CHAR(20),
Custom_addressCHAR(30),
Custom_city CHAR(20),
Custom_CountryCHAR(20),
ArcDateCHAR(20))

As you can see, the parameter possible absence cell values ​​(NULL) can be omitted as it is the default.