#sql #sql-server
#sql #sql-сервер
Вопрос:
Я ищу способ создания инструкции SQL из результата запроса
На основе результатов моего запроса, который содержит имена столбцов, rowId и Value, я хотел бы сгенерировать запрос, который будет включать имя столбца и значение, которое нужно вставить для этой строки.
Пример:
Id | Row_Id | Column_Name | Value | Table
50 | 1 | Employee_Name | 'Joel' | Employee
51 | 1 | Employee_Age | '54' | Employee
52 | 1 | Address | '425 Baker Ave' | Employee
53 | 2 | Employee_Name | 'Jaret' | Employee
54 | 2 | Employee_Age | '29' | Employee
55 | 2 | Address | '423 Loma Rd' | Employee
56 | 3 | Employee_Name | 'Jolie' | Employee
57 | 3 | Employee_Age | '37' | Employee
58 | 3 | Address | '896 Baren Blvd' | Employee
59 | 4 | Location | 'Chicago' | Office
60 | 4 | Address | '264 Taler Ave' | Office
61 | 5 | Location | 'Detroit' | Office
62 | 5 | Address | '296 Forest Ln' | Office
Ожидаемый результат
INSERT INTO Employee (Employee_Name, Employee_Age, Address) VALUES ('Joel', '54', '425 Baker Ave')
INSERT INTO Employee (Employee_Name, Employee_Age, Address) VALUES ('Jaret', '29', '423 Loma Rd')
INSERT INTO Employee (Employee_Name, Employee_Age, Address) VALUES ('Jolie', '37', '896 Baren Blvd')
INSERT INTO Office (Location, Address) VALUES ('Chicago', '264 Taler Ave')
INSERT INTO Office (Location, Address) VALUES ('Detroit', '296 Forest Ln')
Комментарии:
1. Существует ряд инструментов, которые сделают это за вас (например, SSMS Boost, RedGate SQL Prompt). Интересно, можете ли вы подробнее рассказать о варианте использования — почему вы не можете просто вставить результаты вашего выбора непосредственно в таблицу?
2. В этом сценарии я восстанавливаю базу данных и хочу сохранить исходное содержимое таблиц, для чего у меня есть другая хранимая процедура, которая будет собирать все необходимые данные перед восстановлением, и это часть хранимой процедуры восстановления, которая восстановит данные.
3. у вас есть другая таблица, которая определяет, что такое таблица
Column Namesin, какEmployeeилиOffice?
Ответ №1:
Вы можете сделать это с помощью некоторых забавных оконных функций. Вот пример.
(#x, выступающий в качестве таблицы с вашей структурой, row_id, ColumnName, columnValue, tableName)
WITH foo
AS (SELECT Id,
Row_Id,
columnName,
columnValue,
tableName,
DENSE_RANK() OVER (ORDER BY Row_Id, tableName) AS sectionNumber,
ROW_NUMBER() OVER (PARTITION BY Row_Id, tableName ORDER BY Id) AS rowNumberInSection
FROM #x)
SELECT 'INSERT INTO ' tableName ' (' SUBSTRING(columnNames, 0, LEN(FEE.columnNames) - 1) ' ) ' ' VALUES ('
SUBSTRING(columnValues, 0, LEN(FEE.columnValues) - 1) ' ) '
FROM
(
SELECT tableName,
(
SELECT columnName ','
FROM foo f2
WHERE f2.sectionNumber = foo.sectionNumber
ORDER BY f2.rowNumberInSection
FOR XML PATH('')
) AS columnNames,
(
SELECT columnValue ','
FROM foo f3
WHERE f3.sectionNumber = foo.sectionNumber
ORDER BY f3.rowNumberInSection
FOR XML PATH('')
) AS columnValues
FROM foo
WHERE foo.rowNumberInSection = 1
) FEE;
Ответ №2:
Я бы не стал генерировать INSERT инструкции. Просто вставьте то, что вы хотите использовать INSERT . . . SELECT с помощью условной агрегации:
INSERT INTO TABLENAME (Employee_Name, Employee_Age, Address)
SELECT MAX(CASE WHEN name = 'Employee_Name' THEN value end),
MAX(CASE WHEN name = 'Employee_Age' THEN value end),
MAX(CASE WHEN name = 'Address' THEN value end)
FROM <query> q
GROUP BY row_id;
Комментарии:
1. Проблема в том, что набор результатов может содержать информацию для нескольких таблиц, поэтому я не могу использовать столбцы в запросе, которые они должны создавать из таблицы результатов. Моя ошибка, возможно, набор примеров, который я включил, недостаточно полон. Я обновлю это.
Ответ №3:
мне нравятся вопросы такого типа 🙂 вот мое решение (с использованием динамического запроса)
select *,cast('' as nvarchar(max)) script into #T from YourTable
declare @Id int,@OldId int =0,@script nvarchar(300),@script2 nvarchar(300)
update #T
set @Id = row_Id,
@script=Case when @Id!=@OldId then 'insert into ' [Table] ' ( ' Column_Name ')' else left(@script,len(@script)-1) ',' Column_Name ')' end,
@script2=Case when @Id!=@OldId then ' Values(''' [Value] ''')' else left(@script2,len(@script2)-1) ',''' [Value] ''')' end,
script = @script @script2,
@OldId=@Id
select script from #T t join
(select row_Id,max(len(script)) lenScript from #T group by row_Id) X on t.row_Id=x.row_Id and X.lenScript=len(t.script)
drop table #T
Ответ №4:
Предполагая, что ID столбец имеет уникальные значения и Row_Id уникален по смыслу только для одного INSERT INTO оператора, он будет таким же, вы можете написать запрос как:
select distinct
'Insert into ' [Table]
'('
stuff((
select ',' t.[Column_Name]
from Base t
where t.Row_Id = t1.Row_Id
order by t.[id]
for xml path('')
),1,1,'')
') VALUES ('
stuff((
select ', ''' t.[Value] ''''
from Base t
where t.Row_Id = t1.Row_Id
order by t.[id]
for xml path('')
),1,1,'')
')'
from Base T1
Тестовый код здесь: http://sqlfiddle.com /#!18/77430/1