Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

Продолжи текст скрипта так чтобы к каждой выведенной в результате выполнения…

  1. User

    Продолжи текст скрипта так чтобы к каждой выведенной в результате выполнения динамического запроса, содержащегося в переменной @sql записи добавился столбец с именем таблицы в которой найдена запись /* Данный скрипт выполняет запрос к базе данных для получения данных, которые не присутствуют в результатах пересечения шести таблиц. Он объявляет переменные для имен таблиц и использует их в запросах SELECT и EXCEPT. Затем он объединяет результаты с выборкой всех строк из каждой таблицы, которые не присутствуют в результатах пересечения остальных пяти таблиц. */

    -- Объявление переменных с именами таблиц DECLARE @table1_name NVARCHAR(50) = 'ASPKemZavod.dbo.S2' DECLARE @table2_name NVARCHAR(50) = 'ASPKemCent.dbo.S2' DECLARE @table3_name NVARCHAR(50) = 'ASPKemlen.dbo.S2' DECLARE @table4_name NVARCHAR(50) = 'ASPKemkir.dbo.S2' DECLARE @table5_name NVARCHAR(50) = 'ASPKemrud.dbo.S2' DECLARE @table6_name NVARCHAR(50) = 'ASPKemkedr.dbo.S2' -- Объявление переменной для хранения динамического SQL-зроса DECLARE @sql NVARCHAR(MAX) -- Формирование динамического SQL-запроса SET @sql = ' SELECT * FROM ' + @table1_name + ' EXCEPT ( SELECT * FROM ' + @table2_name + ' INTERSECT SELECT * FROM ' + @table3_name + ' INTERSECT SELECT * FROM ' + @table4_name + ' INTERSECT SELECT * FROM ' + @table5_name + ' INTERSECT SELECT * FROM ' + @table6_name + ' ) UNION SELECT * FROM ' + @table2_name + ' EXCEPT ( SELECT * FROM ' + @table1_name + ' INTERSECT SELECT * FROM ' + @table3_name + ' INTERSECT SELECT * FROM ' + @table4_name + ' INTERSECT SELECT * FROM ' + @table5_name + ' INTERSECT SELECT * FROM ' + @table6_name + ' ) UNION SELECT * FROM ' + @table3_name + ' EXCEPT ( SELECT * FROM ' + @table1_name + ' INTERSECT SELECT * FROM ' + @table2_name + ' INTERSECT SELECT * FROM ' + @table4_name + ' INTERSECT SELECT * FROM ' + @table5_name + ' INTERSECT SELECT * FROM ' + @table6_name + ' ) UNION SELECT * FROM ' + @table4_name + ' EXCEPT ( SELECT * FROM ' + @table1_name + ' INTERSECT SELECT * FROM ' + @table2_name + ' INTERSECT SELECT * FROM ' + @table3_name + ' INTERSECT SELECT * FROM ' + @table5_name + ' INTERSECT SELECT * FROM ' + @table6_name + ' ) UNION SELECT * FROM ' + @table5_name + ' EXCEPT ( SELECT * FROM ' + @table1_name + ' INTERSECT SELECT * FROM ' + @table2_name + ' INTERSECT SELECT * FROM ' + @table3_name + ' INTERSECT SELECT * FROM ' + @table4_name + ' INTERSECT SELECT * FROM ' + @table6_name + ' ) UNION SELECT * FROM ' + @table6_name + ' EXCEPT ( SELECT * FROM ' + @table1_name + ' INTERSECT SELECT * FROM ' + @table2_name + ' INTERSECT SELECT * FROM ' + @table3_name + ' INTERSECT SELECT * FROM ' + @table4_name + ' INTERSECT SELECT * FROM ' + @table5_name + ' )'

    --print @sql

    -- Выполнение динамического SQL-запроса EXEC sp_executesql @sql

  2. Assistant

    -- Добавляем столбец с именем таблицы в каждую выборку SET @sql = ' SELECT *, ''' + @table1_name + ''' AS TableName FROM ' + @table1_name + ' EXCEPT ( SELECT *, ''' + @table2_name + ''' AS TableName FROM ' + @table2_name + ' INTERSECT SELECT *, ''' + @table3_name + ''' AS TableName FROM ' + @table3_name + ' INTERSECT SELECT *, ''' + @table4_name + ''' AS TableName FROM ' + @table4_name + ' INTERSECT SELECT *, ''' + @table5_name + ''' AS TableName FROM ' + @table5_name + ' INTERSECT SELECT *, ''' + @table6_name + ''' AS TableName FROM ' + @table6_name + ' ) UNION SELECT *, ''' + @table2_name + ''' AS TableName FROM ' + @table2_name + ' EXCEPT ( SELECT *, ''' + @table1_name + ''' AS TableName FROM ' + @table1_name + ' INTERSECT SELECT *, ''' + @table3_name + ''' AS TableName FROM ' + @table3_name + ' INTERSECT SELECT *, ''' + @table4_name + ''' AS TableName FROM ' + @table4_name + ' INTERSECT SELECT *, ''' + @table5_name + ''' AS TableName FROM ' + @table5_name + ' INTERSECT SELECT *, ''' + @table6_name + ''' AS TableName FROM ' + @table6_name + ' ) UNION SELECT *, ''' + @table3_name + ''' AS TableName FROM ' + @table3_name + ' EXCEPT ( SELECT *, ''' + @table1_name + ''' AS TableName FROM ' + @table1_name + ' INTERSECT SELECT *, ''' + @table2_name + ''' AS TableName FROM ' + @table2_name + ' INTERSECT SELECT *, ''' + @table4_name + ''' AS TableName FROM ' + @table4_name + ' INTERSECT SELECT *, ''' + @table5_name + ''' AS TableName FROM ' + @table5_name + ' INTERSECT SELECT *, ''' + @table6_name + ''' AS TableName FROM ' + @table6_name + ' ) UNION SELECT *, ''' + @table4_name + ''' AS TableName FROM ' + @table4_name + ' EXCEPT ( SELECT *, ''' + @table1_name + ''' AS TableName FROM ' + @table1_name + ' INTERSECT SELECT *, ''' + @table2_name + ''' AS TableName FROM ' + @table2_name + ' INTERSECT SELECT *, ''' + @table3_name + ''' AS TableName FROM ' + @table3_name + ' INTERSECT SELECT *, ''' + @table5_name + ''' AS TableName FROM ' + @table5_name + ' INTERSECT SELECT *, ''' + @table6_name + ''' AS TableName FROM ' + @table6_name + ' ) UNION SELECT *, ''' + @table5_name + ''' AS TableName FROM ' + @table5_name + ' EXCEPT ( SELECT *, ''' + @table1_name + ''' AS TableName FROM ' + @table1_name + ' INTERSECT SELECT *, ''' + @table2_name + ''' AS TableName FROM ' + @table2_name + ' INTERSECT SELECT *, ''' + @table3_name + ''' AS TableName FROM ' + @table3_name + ' INTERSECT SELECT *, ''' + @table4_name + ''' AS TableName FROM ' + @table4_name + ' INTERSECT SELECT *, ''' + @table6_name + ''' AS TableName FROM ' + @table6_name + ' ) UNION SELECT *, ''' + @table6_name + ''' AS TableName FROM ' + @table6_name + ' EXCEPT ( SELECT *, ''' + @table1_name + ''' AS TableName FROM ' + @table1_name + ' INTERSECT SELECT *, ''' + @table2_name + ''' AS TableName FROM ' + @table2_name + ' INTERSECT SELECT *, ''' + @table3_name + ''' AS TableName FROM ' + @table3_name + ' INTERSECT SELECT *, ''' + @table4_name + ''' AS TableName FROM ' + @table4_name + ' INTERSECT SELECT *, ''' + @table5_name + ''' AS TableName FROM ' + @table5_name + ' )'

    -- Выполнение динамического SQL-запроса с добавлением столбца EXEC sp_executesql @sql