вставить записи в цикл MS SQL. Цикл ms sql

Циклы в динамическом SQL MS SQL Server

Вот один из способов сделать это:

--Declare a table variable to hold your table names (and column names in case needed) declare @listOfTablesToUpdate table (tableName varchar(100), columnNameToUpdate varchar(50)) --insert the tables that you want to work with. insert into @listOfTablesToUpdate values ('Table1', 'column2') insert into @listOfTablesToUpdate values ('Table2', 'column3') insert into @listOfTablesToUpdate values ('Table3', 'column4') --Cursor for iterating declare @tableCursor cursor, @tableName varchar(100), @columnName varchar(50) set @tableCursor = cursor for select * from @listOfTablesToUpdate open @tableCursor fetch next from @tableCursor into @tableName, @columnName while(@@fetch_status = 0) begin --dynamic sql declare @sql varchar(max) --Your logic here...this is just an example set @sql = 'update '+@tableName+' set '+@columnName+' = '+<value>+' where '+@columnName +' = '+<someothervalue> exec @sql fetch next from @tableCursor into @tableName, @columnName end close @tableCursor deallocate @tableCursor

Другой подход включает в себя подготовку вспомогательной функции и процедуру, позволяющую применять различные инструкции SQL к каждому объекту (таблице, базе данных и т. Д. ) В списке. Вспомогательная функция исходит из параметра параметра SSRS Parameter и разбивает список разделенных запятыми на таблицу.

-- from https://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services CREATE FUNCTION [dbo].[fn_MVParam] (@RepParam NVARCHAR(4000), @Delim CHAR(1)= ',') RETURNS @Values TABLE (Param NVARCHAR(4000))AS BEGIN DECLARE @chrind INT DECLARE @Piece NVARCHAR(100) SELECT @chrind = 1 WHILE @chrind > 0 BEGIN SELECT @chrind = CHARINDEX(@Delim,@RepParam) IF @chrind > 0 SELECT @Piece = LEFT(@RepParam,@chrind - 1) ELSE SELECT @Piece = @RepParam INSERT @Values(Param) VALUES(CAST(@Piece AS VARCHAR)) SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind) IF LEN(@RepParam) = 0 BREAK END RETURN END GO

Ниже приведен код процедуры ProcessListSQL.

-- @SQL to execute shall include {RP} as the replacement expression that -- will evaluate to all the items in the comma delimited list -- Also, please include a double quote " rather than two single quotes '' -- in the input statement. CREATE PROCEDURE [dbo].[ProcessListSQL] ( @CommaDelimitedList AS NVARCHAR(MAX), @SQLtoExecute AS NVARCHAR(MAX) ) AS BEGIN DECLARE @Statements TABLE ( PK INT IDENTITY(1,1) PRIMARY KEY, SQLObject NVARCHAR (MAX) ) SET @SQLtoExecute = REPLACE (@SQLtoExecute, '"', '''') INSERT INTO @Statements SELECT PARAM FROM [dbo].[fn_MVParam](@CommaDelimitedList,',') DECLARE @i INT SELECT @i = MIN(PK) FROM @Statements DECLARE @max INT SELECT @max = MAX(PK) FROM @Statements DECLARE @SQL AS NVARCHAR(MAX) = NULL DECLARE @Object AS NVARCHAR(MAX) = NULL WHILE @i <= @max BEGIN SELECT @Object = SQLObject FROM @Statements WHERE PK = @i SET @SQL = REPLACE(@SQLtoExecute, '{RP}', @Object) -- Uncommend below to check the SQL -- PRINT @SQL EXECUTE sp_executesql @SQL SELECT @Object = NULL SELECT @SQL = NULL SET @i = @i + 1 END END GO

Процедура ProcessListSQL принимает два параметра. Первая – это строка с разделителями-запятыми, которая содержит список объектов, которые будут проходить циклически. Второй параметр – это строка, содержащая SQL, который будет выполняться с каждым из объектов в первом параметре.

В приведенном ниже примере создаются четыре базы данных. Обратите внимание, что {rp} заменяется каждым из объектов в первом параметре, а двойные кавычки нужны в каждом месте, где в предложении SQL нужны одинарные кавычки.

EXECUTE ProcessListSQL 'rice,apples,cheese,tomatos', 'CREATE DATABASE [{rp}] CONTAINMENT = NONE ON PRIMARY ( NAME = N"{rp}", FILENAME = N"D:\data\user\{rp}.mdf" , SIZE = 4096KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N"{rp}_log", FILENAME = N"D:\DATA\USER\{rp}_log.ldf" , SIZE = 1024KB , FILEGROWTH = 10%)'

sqlserver.bilee.com

Цикл через столбцы SQL MS SQL Server

Ну, в случае известного количества столбцов вы можете сделать:

SELECT MyName + " ->" + case OPTION1 when 1 then ' OPTION1' else '' end + case OPTION2 when 1 then ' OPTION2' else '' end + ... FROM Table

Если столбцы неизвестны при создании запроса – я, вероятно, по-прежнему буду идти таким образом с помощью некоторого динамически созданного SQL. Преимущество в том, что код, вероятно, делает то, что вы хотите, и очень прост.

Возможно, вам стоит взглянуть на таблицы PIVOT.

Поскольку вы не вникаете в конкретные потребности, почему вы хотите это сделать, я не могу быть уверенным, но обычно, когда я вижу этот вопрос, есть две вещи, о которых я думаю:

Вам необходимо нормализовать вашу базу данных. Может быть, «Вариант 1», «Вариант 2» и т. Д. Не имеют ничего общего, но есть и хорошие шансы, что они являются повторяющейся группой внутри вашей таблицы.
Выводите проблемы с отображением на уровне дисплея вашего приложения, то есть переднего конца, а не базы данных.

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

Если вы используете сводную таблицу, вы должны убедиться, что все столбцы «Вариант» имеют одинаковый тип и длину данных.

Я бы предложил следующий ответ:

IF NOT EXISTS( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TABLE1' ) create table table1 ( name nvarchar(50), colvalue nvarchar(50) ) else truncate table table1 declare @table nvarchar(50) set @table = 'yourtable' declare @column table ( ID integer identity, colname nvarchar(20) ) insert into @column SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID WHERE t.Name = @table and c.name in ('Option1','Option2','Option3','Option4','Option5') declare @minID integer, @maxID integer declare @cmd nvarchar(max) declare @col nvarchar(20) declare @SQLStr nvarchar(max) select @minID = MIN(ID), @maxID= MAX(ID) from @column while @minID <= @maxID begin select @col = colname from @column where ID = @minID set @SQLStr = 'insert into table1 (name, colvalue) select name,' + @col + ' from ' + @table + ' where ' + @col + ' <> 0' exec(@SQLStr) set @minID = @minID + 1 end select distinct name, STUFF( (SELECT ',' + a.colvalue AS [text()] from Table1 a where a.name = b.name Order by a.colvalue for xml PATH('')),1,1,'' ) AS Comments_Concatenated from Table1 b group by name, colvalue ORDER BY name

Вам просто нужно изменить @table, вставив имя вашей таблицы и список столбца, который вам нужен, прежде чем переходить в @column.

Независимо от того, какой тип данных вы используете, он будет работать нормально.

Редактировать:

Есть ли способ создать функцию MSSQL для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Поиск различных решений предыдущих вопросов

sqlserver.bilee.com

циклы MS SQL Server

Создание новой временной таблицы из существующей таблицы в зависимости от значений столбца

Я застрял в запросе, чтобы создать временную таблицу со значениями в соответствии со следующим условием. У меня есть таблица следующим образом (Таблица 1) ID 1 5 9 3 10 Я хочу, чтобы новый набор записей соответствовал значению столбца ID , например, если у меня есть ID=1 , мне нужна 1 строка, если ID=5 Мне нужно […]

MSSQL: определение серий неизменных (плоских) значений во временных рядах

Я работаю с данными временных рядов, которые являются результатами измерений датчиков. Мне нужно определить случаи, когда плоские линии данных указывают на неисправность датчика. Я хочу выбрать, где за последние 24 часа больше трех неизменных значений в строке. Я думаю, что мне, возможно, понадобится цикл, но я не работал с циклами в sql. Я предполагаю, что […]

Offset & Fetch Next будет нормально использовать в Loop?

Мне нужно написать цикл для передачи данных, которые находятся во временной таблице SQL, в хранимую процедуру один за другим. Можно ли использовать подход ниже? Стоит ли много памяти? DECLARE @SomeData AS TABLE( Id INT, Name VARCHAR(MAX), Age INT ) INSERT INTO @SomeData ( Id, Name, Age ) VALUES ( 1, 'AAAAA', 10 ), ( 2, […]

Вызов конкретной операции хранимой процедуры / выбора на основе значения строки в SQL Server

В SQL Server существует ли способ как-то вызвать конкретную операцию select / inline select, основанную на значении столбца в таблице? Например, для каждой строки в таблице #IdNumbers (см. Ниже) проверьте значение столбца Number – если значение равно 1, тогда вызовите первую хранимую процедуру, если значение равно 2, затем вызовите вторую процедуру и так далее. Я […]

SQL Server. Решите это с помощью решения на основе набора вместо итерации строк.

Я пытаюсь переместить часть своей бизнес-логики из своих программ и в хранимые процедуры. Я действительно программист VB.NET, а не эксперт SQL, но я изучаю больше SQL и обнаруживаю, что во многих случаях быстрее SQL позволяет обрабатывать и возвращать небольшие объемы данных, а не давать мне тонна вещей, и мои программы перебирают ее. Итак, моя текущая […]

sqlserver.bilee.com

Как использовать цикл на SQL-сервере MS SQL Server

Мне нужно обновить несколько строк (29) в таблице на SQL Server 2005.

В этой таблице есть целочисленный столбец, который мне нужно присвоить числа, начинающиеся с 1 до 29. Например

Как я могу обновить каждую строку с возрастающим порядковым номером эффективно?

WITH sequenced_data AS ( SELECT ROW_NUMBER() OVER (ORDER BY StateCode ASC) AS sequence_id, * FROM tblState ) UPDATE sequenced_data SET integer_column = sequence_id

Поскольку вы просите цикл, я думаю, вы можете не понимать этот код.

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

Оператор WITH похож на подзапрос
В этом случае ROW_NUMBER() создает последовательные идентификаторы, которые вы хотите
Внешний запрос затем обновляет столбец (хотя он относится к подзапросу, реальная таблица действительно обновляется)

По возможности избегайте циклов в SQL. SQL Server сильно оптимизирован для операций на основе набора.

В этом случае вы можете использовать CTE с функцией ROW_NUMBER :

WITH CTE AS ( SELECT StateCode, IntColumn , RN = ROW_NUMBER() OVER (ORDER BY StateCode ASC) FROM dbo.tblState ) UPDATE CTE SET IntColumn = RN;

Плохие привычки к удару: мышление цикла WHILE не является CURSOR

Вы можете использовать общие выражения таблицы

with c as ( select ID, ROW_NUMBER() over (order by StateCode ASC) as cn from tblState ) update c set ID = cn

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

Просто делать

SELECT ROW_NUMBER() OVER (ORDER BY StateCode ASC) sequence_id , * FROM tblState

каждый раз, когда вы получаете данные.

Если у вас много строк, и вам нужно сделать это быстро, создайте индекс в StateCode ASC

попробуй это:

with CTE1 as(select StateCode,intcol,ROW_NUMBER() over (order by StateCode) as rn from tbln) update CTE1 set intcol=rn select * from tbln

Мой вариант без cte:

UPDATE d SET d.integer_column = d.sequence_id FROM ( SELECT integer_column, ROW_NUMBER() OVER (ORDER BY StateCode ASC) AS sequence_id FROM dbo.tblState ) d

sqlserver.bilee.com

Запрос SQL-запроса – цикл MS SQL Server

Я пытаюсь создать запрос, который будет генерировать таблицу перекрестной проверки с примерно 40 пользовательскими столбцами, которые показывают Y или N. Прямо сейчас у меня есть

Поэтому я задаю вопрос, как создать цикл (while? For?), Который будет циклически перебираться по переменной и назначать Y или N строке на основе условия, а не создавать 40+ операторов Case?

Вы не могли бы использовать цикл, но вы могли бы создать хранимую процедуру / функцию для выполнения выражения sub-select и case и вызвать его 40 раз.

Кроме того, вы можете улучшить производительность суб-выбора, изменив его на

SELECT 1 FROM Table2 WHERE EXISTS [Table2].[ID2] = [Table1.ID1] AND Variable = 3 AND Bit = 1

Цикл (т. Е. Итерация с помощью курсора) работает с строками, а не с столбцами. Вам все равно придется иметь 40 выражений, по одному для каждого столбца, а производительность будет ужасной.

Пусть SQL Server выполнит свою работу. И сделайте свой бит, указав именно то, что вам нужно, и создайте правильные индексы. То есть, замените

CASE WHEN [Table1].[ID1] IN (SELECT ID2 FROM Table2 WHERE Variable = 2 AND Bit = 1)

CASE WHEN EXISTS (SELECT 0 FROM Table2 WHERE ID2 = [Table1].[ID1] AND Variable = 2 AND Bit = 1)

Если выход настолько сильно отличается от схемы, возникает вопрос, правильно ли схема моделирует бизнес-требования. Тем не менее, я бы рекомендовал просто написать SQL. Вы можете упростить SQL так:

Select Company , Option1, Option2, Option3 , Case When T2.Variable = 1 Then 'Y' Else 'N' End As CustomCol1 , Case When T2.Variable = 2 Then 'Y' Else 'N' End As CustomCol2 , Case When T2.Variable = 3 Then 'Y' Else 'N' End As CustomCol3 , Case When T2.Variable = 4 Then 'Y' Else 'N' End As CustomCol4 ... From Table1 As T1 Left Join Table2 As T2 On T2.ID2 = T1.ID And T2.Bit = 1 Where T1.OtherCondition = 'True' Group By T1.Company Order By T1.Company

Если вы хотите написать что-то, что может помочь вам автогенерировать эти операторы Case (и вы используете SQL Server 2005+), вы можете сделать что-то вроде:

With Numbers As ( Select 0 As Value Union All Select Value + 1 From Numbers Where Value < 41 ) Select ', Case When T2.Variable = ' + Cast(N.Value As varchar(10)) + ' Then ''Y'' Else ''N'' End As CustomCol' + Cast(N.Value As varchar(10)) From Numbers As N

Вы будете запускать запрос и копировать и вставлять результаты в свою процедуру или код.

Один из способов мог заключаться в использовании инструкции Pivot, которая находится в MS SQL 2005+. Но даже в этом случае вы должны поставить 1 … 40 жестко закодированных столбцов в сводной инструкции.

Другой способ, который я могу придумать, – создать динамический SQL, но это не так рекомендуется, поэтому мы можем создать динамический SQL-запрос, запустив цикл while на столе и можем создать большой sql, а затем мы можем выполните его с помощью sp_execute. Таким образом, шаги будут.

int @loopVar SET @loopVar = 0 int @rowCount varchar @SQL SET @SQl = '' Select @rowcount = Count(ID2) from Table2 WHILE(@loopVar <= @rowCount) BEGIN // create ur SQL here END sp_execute(@SQL)

sqlserver.bilee.com

Курсоры в MSSQL — перебор выборки в цикле.

Команды манипулирования данными SELECT, UPDATE, DELETE работают сразу с группами строк. Эти группы, вплоть до отдельных строк, можно выбрать с помощью опции WHERE. А если надо перебрать строки некоторой таблицы последовательно, одну за другой?

На этот случай в языке SQL существуют курсоры. Курсор (current set of record) – временный набор строк, которые можно перебирать последовательно, с первой до последней.При работе с курсорами используются следующие команды.Объявление курсора:DECLARE имя_курсора CURSOR FOR SELECT текст_запросаЛюбой курсор создается на основе некоторого оператора SELECT.Открытие курсора:OPEN имя_курсораДля того чтобы с помощью курсора можно было читать строки, его надо обязательно открыть.Чтение следующей строки из курсора:FETCH имя_курсора INTO список_переменныхПеременные в списке должны быть в том же количестве и того е типа, что и столбцы курсора.Глобальная переменная @@FETCH_STATUS принимает ненулевое значение, если строк в курсоре больше нет. Если же набор строк еще не исчерпан, то @@FETCH_STATUS равна нулю, и оператор FETCH перепишет значения полей из текущей строки в переменные.Закрытие курсора:CLOSE имя_курсора

Для удаления курсора из памяти используется командаDEALLOCATE имя_курсора

Для иллюстрации использования курсора создадим процедуру, которая будет выбирать данные из одной таблицы, перебирать их в курсоре анализируя, есть ли такие данные во второй таблице и вставлять в третью таблицу, если данные записи удовлетворяют определённым критериям.

CREATE PROCEDURE [dbo].[MyProcedure] AS DECLARE @ID INT DECLARE @QUA INT DECLARE @VAL VARCHAR (500) DECLARE @NAM VARCHAR (500) /*Объявляем курсор*/ DECLARE @CURSOR CURSOR /*Заполняем курсор*/ SET @CURSOR = CURSOR SCROLL FOR SELECT INDEX, QUANTITY, VALUE, NAME FROM My_First_Table WHERE QUANTITY > 1 /*Открываем курсор*/ OPEN @CURSOR /*Выбираем первую строку*/ FETCH NEXT FROM @CURSOR INTO @ID, @QUA, @VAL, @NAM /*Выполняем в цикле перебор строк*/ WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS(SELECT VAL FROM My_Second_Table WHERE ID=@ID) BEGIN /*Вставляем параметры в третью таблицу если условие соблюдается*/ INSERT INTO My_Third_Table (VALUE, NAME) VALUE(@VAL, @NAM) END /*Выбираем следующую строку*/ FETCH NEXT FROM @CURSOR INTO @ID, @QUA, @VAL, @NAM END CLOSE @CURSOR

Вот собственно и всё.

Источник http://kbss.ru/blog/mssql/55.html

pomni.in.ua

sql-server - вставить записи в цикл MS SQL

И SQL снова. Моя ситуация. Мне нужно сделать вставку в таблицу. У меня есть условие. Некоторые вставки могут выполняться, если они равны их ставкам в неделю и в месяц. Например: для ставки категории 2 = 1 раз в месяц. Поэтому мне нужно вставить только один раз в апреле. Но в моем случае он вставляет 1 запись, которую я ожидал, и вставляет еще 5 раз.

SET @Rate_Init = 1 DECLARE @Min_Amount INT = (SELECT T.AccountMin FROM Transactions T FULL JOIN Marathon.dbo.Categories C ON C.CategoryName = T.OperationName WHERE C.CategoryID = @External_Loop_Counter) DECLARE @Max_Amount INT = (SELECT T.AccountMax FROM Transactions T FULL JOIN Marathon.dbo.Categories C ON C.CategoryName = T.OperationName WHERE C.CategoryID = @External_Loop_Counter) DECLARE @Check_Period NVARCHAR(10) = (SELECT Period FROM (SELECT * FROM Transactions T FULL JOIN Marathon.dbo.Categories C ON C.CategoryName = T.OperationName) X WHERE CategoryID = @External_Loop_Counter) DECLARE @Frequency INT = (SELECT Rate FROM (SELECT * FROM Transactions T FULL JOIN Marathon.dbo.Categories C ON C.CategoryName = T.OperationName) X WHERE CategoryID = @External_Loop_Counter) WHILE @Total_Dates >= @Internal_Loop_Counter BEGIN IF(@Frequency <= @Rate_Init) BEGIN INSERT INTO Transactions (TransactionDate, TransactionAmount, CategoryID) VALUES((DATEADD(DAY, @Internal_Loop_Counter, @Start_DT)), (@Max_Amount - CAST(((@Max_Amount - @Min_Amount)) AS FLOAT)*RAND()), (SELECT CategoryID FROM (SELECT * FROM Transactions T FULL JOIN Marathon.dbo.Categories C ON C.CategoryName = T.OperationName) X WHERE CategoryID = @External_Loop_Counter)) END SET @Rate_Init = @Rate_Init + 1 SET @Jump_Ratio = ( CASE WHEN @Check_Period = 'Week' THEN (@Internal_Loop_Counter + CAST(CAST(@Week_T AS FLOAT)*RAND() AS INT)) WHEN @Check_Period = 'Month' THEN (@Internal_Loop_Counter + CAST(CAST(@Month_T AS FLOAT)*RAND() AS INT)) WHEN @Check_Period = 'Year' THEN (@Internal_Loop_Counter + CAST(CAST(@Year_T AS FLOAT)*RAND() AS INT)) END ) SET @Internal_Loop_Counter = @Jump_Ratio + 1 END

Ожидайте 140 2012-03-28 00:00:00 628 2

141 2012-04-26 00:00:00 334 2

142 2012-05-03 00:00:00 418 2

Фактический результат, который я получил

140 2012-03-28 00:00:00 628 2

141 2012-04-03 00:00:00 592 2

142 2012-04-04 00:00:00 353 2

143 2012-04-10 00:00:00 403 2

144 2012-04-16 00:00:00 635 2

145 2012-04-22 00:00:00 673 2

146 2012-04-26 00:00:00 334 2

147 2012-05-03 00:00:00 418 2

Использовать MS SQL 2017

источник поделиться

qaru.site