Как заполнить таблицу в sql
Перейти к содержимому

Как заполнить таблицу в sql

  • автор:

Как заполнить таблицу интервалом дат?

Необходимо сделать таблицу со списком дат с шагом в месяц для каждого id от минимального к максимальному. И чтобы даты были последним днём месяца. По типу:

id Дата
01 31.01.2000
01 28.02.2000
01 31.03.2000
02 28.02.2001
02 31.03.2001
02 30.04.2001
02 31.05.2001

что-то по типу цикла: для каждого id добавить дату с шагом 1 месяц, пока дата меньше, чем максимум мой неработающий код:

WITH t AS( select id, EOMONTH(Минимум) AS min_v, Минимум, Максимум from my_table UNION ALL SELECT id, EOMONTH(DATEADD(m, 1, d)), Минимум, Максимум FROM t WHERE EOMONTH(Минимум)  

пишет ошибку "adding a value to a 'date' column caused an overflow"
Отслеживать
задан 17 апр 2023 в 15:03
208 1 1 серебряный знак 13 13 бронзовых знаков
у вас в примере даты есть больше чем максимум.
17 апр 2023 в 15:09
в целом тут про рекурсивные CTE
17 апр 2023 в 15:09

@teran месяц - всегда последней датой. в 01 - с января по март (3 месяца), в 02 с февраля по май (4 месяца)

17 апр 2023 в 15:54
@teran и как это можно сделать?
17 апр 2023 в 16:17

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Я немного поправлю Ваш код. Иначе получается бесконечная рекурсия или переполнение, что раньше.

WITH t AS( select id, EOMONTH(Минимум) AS d, Минимум, Максимум from my_table UNION ALL SELECT id, EOMONTH(DATEADD(m, 1, d)), Минимум, Максимум FROM t WHERE EOMONTH(d) < Максимум) 

Вот еще вариант, практически такой же:

create table test (id int,[Минимум] date,[Максимум] date); insert into test values (01,'2000.01.01','2000.03.03') ,(02,'2001.02.02','2001.05.31') ; with r as( select id, eomonth([Минимум]) [Дата],eomonth([Максимум]) [МаксДата] from test union all select id, eomonth(dateadd(m,1,[Дата])), [МаксДата] from r where eomonth(dateadd(m,1,[Дата])) 

�� Как заполнять таблицу SQL Server: подробное руководство для начинающих

Чтобы заполнить таблицу в SQL Server, вы можете использовать оператор INSERT. Этот оператор позволяет вставлять новые строки в таблицу. Пример использования оператора INSERT:

 INSERT INTO название_таблицы (столбец1, столбец2, столбец3) VALUES (значение1, значение2, значение3); 
  • название_таблицы - имя таблицы, в которую вы хотите вставить данные
  • столбец1, столбец2, столбец3 - список столбцов, в которые вы хотите вставить данные
  • значение1, значение2, значение3 - значения, которые вы хотите вставить в соответствующие столбцы
 INSERT INTO students (name, age, grade) VALUES ('John', 18, 'A'); 

В приведенном примере мы вставляем новую запись в таблицу "students" с указанными значениями для столбцов "name", "age" и "grade".

Вы также можете заполнять таблицу с помощью оператора SELECT. Например, вы можете выбрать данные из другой таблицы и вставить их в целевую таблицу.

Пример использования оператора SELECT для заполнения таблицы:

 INSERT INTO название_таблицы (столбец1, столбец2, столбец3) SELECT столбец1, столбец2, столбец3 FROM другая_таблица; 
  • другая_таблица - имя таблицы, из которой вы хотите выбрать данные
 INSERT INTO students (name, age, grade) SELECT name, age, grade FROM temp_students; 

В приведенном примере мы выбираем данные из таблицы "temp_students" и вставляем их в таблицу "students".

Надеюсь, это помогает! Если у вас есть еще вопросы, не стесняйтесь задавать!

Детальный ответ

Как заполнять таблицу SQL Server

Заполнение таблицы в SQL Server - это один из самых важных этапов работы с базами данных. В этой статье я расскажу вам о том, как правильно заполнять таблицы в SQL Server с использованием примеров кода.

Использование оператора INSERT для добавления одной строки

Оператор INSERT используется для добавления данных в таблицу. Давайте рассмотрим пример использования оператора INSERT для добавления одной строки в таблицу "Employees":

 INSERT INTO Employees (FirstName, LastName, Age) VALUES ('John', 'Doe', 25); 

В этом примере мы указываем имя таблицы "Employees" и столбцы, в которые мы хотим вставить значения: "FirstName", "LastName" и "Age". Затем мы используем ключевое слово "VALUES" для указания значений, которые мы хотим добавить в каждый из этих столбцов.

Использование оператора INSERT для добавления нескольких строк

Оператор INSERT также может использоваться для добавления нескольких строк в таблицу одним запросом. Давайте рассмотрим пример:

 INSERT INTO Employees (FirstName, LastName, Age) VALUES ('John', 'Doe', 25), ('Jane', 'Smith', 30), ('Mike', 'Johnson', 35); 

В этом примере мы добавляем три строки в таблицу "Employees". Мы перечисляем значения для каждого столбца в порядке, указанном в операторе INSERT, разделяя их запятыми.

Использование оператора INSERT и SELECT для добавления данных из другой таблицы

Вы также можете использовать оператор INSERT в сочетании с оператором SELECT, чтобы добавить данные из другой таблицы. Предположим, у нас есть таблица "EmployeesBackup", которая содержит резервную копию данных о сотрудниках. Мы хотим добавить эти данные в таблицу "Employees":

 INSERT INTO Employees (FirstName, LastName, Age) SELECT FirstName, LastName, Age FROM EmployeesBackup; 

В этом примере мы выбираем данные из таблицы "EmployeesBackup" с помощью оператора SELECT и затем используем оператор INSERT, чтобы добавить их в таблицу "Employees". Мы указываем столбцы, в которые мы хотим вставить значения, и затем указываем источник данных, с помощью которого мы выбираем эти значения.

Использование оператора INSERT с подзапросом

В SQL Server вы также можете использовать оператор INSERT с подзапросом. Предположим, у нас есть таблица "EmployeesTotal", которая содержит общее количество сотрудников. Мы хотим добавить это значение в таблицу "Summary". Мы можем сделать это, используя следующий запрос:

 INSERT INTO Summary (TotalEmployees) SELECT COUNT(*) FROM Employees; 

В этом примере мы считаем общее количество сотрудников в таблице "Employees" с помощью функции COUNT и затем использовать оператор INSERT, чтобы добавить это значение в таблицу "Summary".

Заключение

Заполнение таблицы в SQL Server - ключевой этап работы с базами данных. В этой статье мы рассмотрели различные способы заполнения таблицы с использованием оператора INSERT в SQL Server. Мы изучили, как добавлять одну строку или несколько строк, как добавлять данные из другой таблицы и как использовать подзапросы.

Надеюсь, эта статья была для вас полезной. Благодарю за внимание!

Заполнить SQL таблицу данными из другой

Есть 2 таблицы: "PS" PS "S" Как заполнить колонку "UserId" в таблице "S" на основании данных из таблицы "PS"? Другими словами, для каждого "PS"."subscriptionId" нужно посмотреть "PS"."UserId" и записать его значение в "S"."userID" так чтобы "PS"."subscriptionId" = "S"."id". Необходимо фактически заполнить таблицу "S" Результат который хочу получить: введите сюда описание изображения

Отслеживать
задан 6 дек 2020 в 15:05
27 7 7 бронзовых знаков

2 ответа 2

Сортировка: Сброс на вариант по умолчанию

Можно реализовать через UPDATE

UPDATE s SET userId = ps.userId FROM ps WHERE s.id = ps.subscriptionId 

Отслеживать
ответ дан 16 ноя 2022 в 15:07
Hleb Karpenka Hleb Karpenka
11 1 1 бронзовый знак

Начните с новой таблицы:

truncate table "S"; 

Затем заполните ее данными:

insert into "S" ("id", "userId") select t.subs_id, coalesce("PS"."userId", 0) from generate_series(1, (select max("subscriptionId") from "PS"), 1) t(subs_id) left outer join "PS" on t.subs_id = "PS"."subscriptionId"; 

На самом деле таблица "S" является избыточным, поскольку она не содержит никакой новой информации, является лишь представлением существующих данных.

Поэтому подумайте о создании представления (возможно, материализованного) вместо таблицы.

create view "SV" as select t.subs_id as "id", coalesce("PS"."userId", 0) as "userId" from generate_series(1, (select max("subscriptionId") from "PS"), 1) t(subs_id) left outer join "PS" on t.subs_id = "PS"."subscriptionId"; 

2.16. Добавление записей

С выборкой данных мы разобрались и узнали все, что необходимо знать об операторе SELECT. Я надеюсь, что смог показать вам всю мощь выборки данных и теперь вы сможете самостоятельно писать запросы для решения своих задач.

Теперь давайте познакомимся с командами добавления данных в таблицу. Когда мы вначале главы заполняли таблицу данными, то вы должны были просто выполнить сценарий. Сейчас мы познакомимся с командой INSERT, которая добавляет в таблицу запись и вы сможете понять, как работал сценарий заполнения таблицы Chapter2/fill_data.sql.

Листинг 2.4. Общий вид команды INSERT

INSERT [ INTO] < table_name WITH ( < table_hint_limited >[ . n ] ) | view_name | rowset_function_limited > < [ ( column_list ) ] < VALUES ( < DEFAULT | NULL | expression >[ . n] ) | derived_table | execute_statement > >

В принципе, ничего сложного в этой команде нет. Давайте рассмотрим различные варианты добавления данных в таблицу. Для начала добавим в таблицу строку, в которой все значения будут установлены сервером по умолчанию:

INSERT INTO tbPeoples DEFAULT VALUES

Посмотрите, что добавил сервер в таблицу tbPeoples. Как? Нужно посмотреть последнюю строку, а этот запрос мы рассматривали в разделе 2.14. Напоминаю, как он выглядел:

SELECT * FROM tbPeoples WHERE idPeoples= (SELECT MAX(idPeoples) FROM tbPeoples) В результате вы должны увидеть следующую теблицу: idPeoples vcFamil vcName vcSurname idPosition dDateBirthDay ---------------------------------------------------------------- 20 NULL NULL NULL NULL NULL (1 row(s) affected)

В таблице tbPeoples первое поле 'idPeoples' является автоматически увеличиваемым, и новой строке это поле стало равным 20. Остальные поля не имеют значений по умолчанию и тут можно видеть только нулевые значения NULL.

Вернемся к запросу добавления записи и рассмотрим его подробнее. В данном запросе выполняется оператор INSERT INTO (вставить в), после которого указывается имя таблицы, в которую нужно вставить запись. Далее может идти описание имен полей, которые нужно заполнять или список значений. В нашем случае нет ни того, ни другого, зато есть DEFAULT VALUES (значения по умолчанию), который указывает на необходимость заполнить все поля значениями по умолчанию.

В таблице tbPeoples только у одного поля указывается значение по умолчанию – это автоматически увеличиваемое поле первичного ключа. Хотя мы не указывали DEFAULT значение, автоматически увеличиваемое поле обязательно получает значение, тем более что это первичный ключ, и он должен быть заполнен. Все остальные поля будут нулевыми.

Следующий запрос пытается добавить запись с явно указанными данными:

INSERT INTO tbPeoples VALUES(122, 'ИВАНОВ', 'ИВАН', 'СЕРГЕЕВИЧ', 1, '01.01.1971')

Первая строка ничем не отличается от предыдущего запроса. Мы снова указываем, что строка добавляется в таблицу tbPeoples. Во второй строке идет ключевое слово VALUES, после которого в скобках должны быть перечислены значения для всех полей таблицы. Перечисление значений должно быть в том же порядке, что и список полей в таблице, и при этом, указываемые значения должны иметь необходимый тип данных. Если хотя бы одно поле не будет указано или указано, но не верно (например, вместо числа будет указана строка), то запрос не будет выполнен.

В данном запросе указаны все поля и указаны в соответствии со всеми типами, но почему я сказал, что этот запрос «пытается добавить строку»? Ничего в голову не приходит? Первое поле имеет тип автоматического увеличиваемого числа. Это поле изменять нельзя, поэтому сервер вернет ошибку:

An explicit value for the identity column in table 'tbPeoples' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Явное значение для авто увеличиваемой колонки в таблице 'tbPeoples' может быть указано только когда используется список колонок и свойство IDENTITY_INSERT установлено в ON.

Неужели нельзя добавить строку? Конечно же, есть способ решения этой проблемы, просто необходимо явным образом задать список колонок, которые должны изменяться и установить им нужные значения.

Список колонок указывается в скобках после имени таблицы:

INSERT INTO tbPeoples (idPeoples, vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) VALUES (122, 'ИВАНОВ', 'ИВАН', 'СЕРГЕЕВИЧ', 1, '01.01.1971')

В данном случае в скобках перечислены все поля в таком же порядке, в котором они описаны в таблице tbPeoples. Но это не является обязательным. Поля могут быть в любом порядке, но в этом случае значения в скобках после ключевого слова VALUES должны идти в том же порядке, в котором вы их перечислили. Но об этом мы еще поговорим.

Но этого не достаточно. Необходимо установить нужное свойство. Мы этого пока свойства не изменяли, но это делается с помощью оператора SET. Затем идет имя свойства (IDENTITY_INSERT), имя таблицы, свойство которой нужно изменить и напоследок – значение (чаще всего ON или OFF). В виде Transact-SQL команды это выглядит следующим образом:

SET IDENTITY_INSERT tbPeoples ON

Необходимо заметить, что изменение подобных свойств относится только к Transact-SQL и MS SQL Server.

Теперь посмотрим на полную SQL команду добавления записи с явным указанием значения для автоматически увеличиваемого значения:

SET IDENTITY_INSERT tbPeoples ON INSERT INTO tbPeoples (idPeoples, vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) VALUES(122, 'ИВАНОВ', 'ИВАН', 'СЕРГЕЕВИЧ', 1, '01.01.1971')

После выполнения команды желательно вернуть значение параметра IDENTITY_INSERT в OFF:

SET IDENTITY_INSERT tbPeoples OFF

Вот теперь команда будет выполнена корректно и новая запись будет успешно добавлена в таблицу. Но я не рекомендую вам явно указывать значение для автоматически увеличиваемого поля. Так вы можете нарушить целостность, поэтому доверьте выделение значения для IDENTITY поля серверу баз данных.

Как добавить строку, и при этом не указывать значение ключевого поля, чтобы сервер смог его установить самостоятельно? Очень просто. Дело в том, что перечислять все поля необязательно. Можно указать только те имена полей, значения которых вы хотите явно изменить. Например, в следующем запросе мы не указываем значение для ключевого поля "idPeoples":

INSERT INTO tbPeoples (vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) VALUES('ИВАНОВ', 'ИВАН', 'АЛЕКСЕЕВИЧ', 1, '01.01.1971')

В списке полей и в списке VALUES не указывается значение для поля "idPeoples". Да и в списке значений после ключевого слова VALUES мы указываем значения, начиная с поля "vcFamil". Вместо этого, сервер самостоятельно добавит очередное значение точно так же, как когда мы добавляли строку из всех значений по умолчанию.

Давайте добавим строку, в которой укажем только фамилию работника. Для этого, в списке полей нужно указать только имя поля "vcName", а в списке значений должно быть только одно строковое значение, ведь фамилия имеет строковый тип:

INSERT INTO tbPeoples (vcFamil) VALUES('ПЕТРОВ')

Если в вашей программе удобнее сделать перечисление всех полей, но при этом заполняются далеко не все значения, вместо не заполняемых значений можно указать значение NULL или DEFAULT. В следующем примере, вместо фамилии и даты рождения указано ключевое слово DEFAULT и этим полям сервер установит значение по умолчанию. Для поля фамилии указывается значение NULL:

INSERT INTO tbPeoples (vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) VALUES('СЕРГЕЕВ', DEFAULT, NULL, 1, DEFAULT)

Ключевые слова DEFAULT и NULL можно указывать для любых типов полей, кроме автоматически увеличиваемого, потому что это поле не может содержать NULL и не имеет значения по умолчанию. Автоматическое увеличение к значению по умолчанию не относиться.

В перечислении можно указывать поля в любом порядке, и только те, которые необходимы. При этом в списке VALUES значения должны идти в том же порядке, в котором вы их перечисляли. Например, в следующем примере поля фамилии, имени и отчества заполняются в обратном порядке. При этом между отчеством и именем идет поле "idPosition":

INSERT INTO tbPeoples (vcSurname, idPosition, vcName, vcFamil) VALUES('ПЕТРОВИЧ', 12, 'СЕРГЕЙ', 'СМИРНОВ')

В разделе 2.6 мы рассматривали, как можно использовать SELECT INTO для создания новой таблицы. Теперь давайте посмотрим, как можно импортировать данные в уже существующую таблицу, ведь SELECT INTO не может выбирать данные в таблицу, если она уже существует.

Для этого создадим новую таблицу tbPeoples2, которая будет состоять из таких же полей, как и у tbPeoples. Единственная разница, ключевое поле не будет автоматически увеличиваемым, чтобы сервер не ругался, на вставку значений в первичный ключ. Посмотрите на SQL код создания новой таблицы tbPeoples2, показан в листинге 2.5.

Листинг 2.5. Создание новой таблицы, копии tbPeoples

CREATE TABLE tbPeoples2 ( -- Описание полей idPeoples2 int, vcFamil varchar(50), vcName varchar(50), vcSurname varchar(50), idPosition int, dDateBirthDay datetime, -- Описание ключей CONSTRAINT PK_idPeoples2 PRIMARY KEY (idPeoples2), CONSTRAINT FK_idPosition2 FOREIGN KEY (idPosition) REFERENCES tbPosition (idPosition), -- Описание ограничений CONSTRAINT check_dDateBirthDay2 CHECK (dDateBirthDay

Так как имена ограничений в таблице должны быть уникальными внутри базы данных, к каждому имени ограничения добавлена цифра 2. В принципе, имя таблицы тоже содержит цифру два (tbPeoples2), т.е. все соответствует тому, чему мы договорились.

Теперь посмотрим, как заполнить новую таблицу записями работников из таблицы tbPooples, и при этом взять только те записи, у которых поле "idPosition" содержит значение более или равное 10. Все достаточно просто:

INSERT INTO tbPeoples2 SELECT * FROM tbPeoples WHERE idPosition>=10

В первой строке пишем оператор INSERT INTO и имя таблицы tbPeoples2, в которую необходимо вставить данные. Затем идет просто запрос SELECT, в котором мы получаем нужные данные из таблицы tbPeoples. Все строки, полученные с помощью этого запроса, будут добавлены в таблицу tbPeoples2.

Все так просто только потому, что обе таблицы содержат одинаковое количество полей, и типы полей совпадают. А если поля будут разными? Давайте создадим третью таблицу tbPeoples3, в которой будут только поля для хранения фамилии, имени и даты рождения.

CREATE TABLE tbPeoples3 ( -- Описание полей vcFamil varchar(50), vcName varchar(50), dDateBirthDay datetime, )

Теперь посмотрим, как можно заполнить эту таблицу значениями:

INSERT INTO tbPeoples3 SELECT vcFamil, vcName, dDateBirthDay FROM tbPeoples WHERE idPosition>=10

Обратите внимание, что мы выбираем с помощью запроса SELECT только три поля и именно в таком порядке, в котором они описаны в таблице tbPeoples3. Названия полей в таблицы, и выбираемые поля могут не совпадать, но типы данных обязаны совпадать полностью, иначе выполнение запроса завершиться ошибкой. Здесь сервер не может производить автоматического преобразования, поэтому, или типы должны совпадать, или вы должны их явно преобразовывать. О явном преобразовании мы поговорим в разделе 2.21.

С помощью оператора INSERT INTO вы можете копировать данные между таблицами или даже переносить их с сервера на сервер с помощью распределенных запросов, о которых мы поговорим в разделе 5.1.

Запрос SELECT в INSERT INTO может быть любой сложности, и тут ограничений на используемые операторы нет. Другое дело, что не всегда сортировка может иметь смысл. Например, если вставлять отсортированные данные в таблицу tbPeoples, то таким образом можно повлиять только на идентификатор добавляемых строк, потому что тут автоматически увеличиваемое поле, которое генерируется последовательно. Но если бы первичный ключ был GUID полем, то сортировка потеряла бы смысл, потому что GUID поле генерируется случайным образом.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *