Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Проверка наличия/вставка слова в таблицу. Как сделать для нескольких слов в 1-м запросе?  [new]
Malyav
Member

Откуда:
Сообщений: 183
Задача:
1. если слово есть в таблице, вернуть его WordID.
2. если слова нет в таблице, вставить это слово в таблицу и вернуть WordID. WordID создается автоматически с автоинкрементом.

Я сделал это с помощью хранимой процедуры для одного слова:

ALTER PROCEDURE [dbo].[InsertOwnWord] 
	@MyWord varchar(40) 
AS
BEGIN
	DECLARE @WordID INT;

	SET NOCOUNT ON;

SET @WordID = (SELECT TOP 1 WordID From WordList WITH (HOLDLOCK) WHERE [Word] = @MyWord);
IF (@WordID IS NULL)
BEGIN
	INSERT INTO WordList ([Word]) VALUES (@MyWord);
	SET @WordID = (SELECT TOP 1 WordID From WordList WHERE [Word] = @MyWord);
END   
SELECT @WordID; 
END


При выполнении запроса
EXEC InsertOwnWord 'example';
я получаю WordID слова 'example'.

Подскажите, как сделать то же самое, но сразу для 10 - 20 слов в этом аргументе процедуры (разделитель пробел или запятая, не важно). Вот так:
EXEC InsertOwnWord 'example home work table phone';

чтобы процедура вернула либо одно значение в виде строки со списком WordID этих слов:
"120 217 20 83 333"

либо таблицу
WordID
------
120
217
20
83
333

PS. У меня MS SQL 2000 MSDE.
31 окт 18, 12:39    [21720186]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия/вставка слова в таблицу. Как сделать для нескольких слов в 1-м запросе?  [new]
court
Member

Откуда:
Сообщений: 2016
нуу с этим
Malyav
PS. У меня MS SQL 2000 MSDE.

"красиво" не получится :)

всё будет "тупо" и "деревянно"
- табличная функция, которая разбивает список на строки (примеров на форуме валом)
- цикл по результату запроса этой таб.функции
- временная табл. в которую "ложаться" найденные/вставленные ID
- в конце ХП - select из времянки

пс
автор
	INSERT INTO WordList ([Word]) VALUES (@MyWord);
--	SET @WordID = (SELECT TOP 1 WordID From WordList WHERE [Word] = @MyWord);
	SET @WordID = @@identity;
31 окт 18, 12:49    [21720204]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия/вставка слова в таблицу. Как сделать для нескольких слов в 1-м запросе?  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
Поможет Функция, которая делит строку на слова

В 2000-м как раз появились функции.
Хотя, можно и просто использовать тело предлагаемой функции.
Её результат вставляйте в таблицу по условию NOT EXISTS().
ID слов в таблице выбирайте по условию WHERE EXISTS(SELECT [ЗначениеСтроки] FROM [функция])
31 окт 18, 13:00    [21720215]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия/вставка слова в таблицу. Как сделать для нескольких слов в 1-м запросе?  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
iap
ID слов в таблице выбирайте по условию WHERE EXISTS(SELECT [ЗначениеСтроки] FROM [функция])
Или вместо EXISTS()
WHERE ' '+@Str+' ' LIKE '% '+Field+' %' FROM T
@Str - это строка с разделителем-пробелом, T и Field - таблица, в которую вставляем и поле со словом.
31 окт 18, 13:08    [21720230]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия/вставка слова в таблицу. Как сделать для нескольких слов в 1-м запросе?  [new]
Malyav
Member

Откуда:
Сообщений: 183
Спасибо за советы. Буду разбираться с функциями.

Вообще, SQL 2000 у меня стоит "спокон веку" на компьютере, где я пишу и проверяю код. Когда два месяца назад разворачивал вебсайт на виртуальной машине, мне пришлось устанавливать Microsoft SQL Server 2017, так как 2000-й "на себя" она устанавливать отказалась. Потом переносить базу с 2000-го на 2017 сервер.
Чувствую, скоро придется переходить на него и на машине для разработки. Чтобы не иметь проблем с несовместимостью.
31 окт 18, 13:24    [21720254]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия/вставка слова в таблицу. Как сделать для нескольких слов в 1-м запросе?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Malyav
Спасибо за советы. Буду разбираться с функциями.

Вообще, SQL 2000 у меня стоит "спокон веку" на компьютере, где я пишу и проверяю код. Когда два месяца назад разворачивал вебсайт на виртуальной машине, мне пришлось устанавливать Microsoft SQL Server 2017, так как 2000-й "на себя" она устанавливать отказалась. Потом переносить базу с 2000-го на 2017 сервер.
Чувствую, скоро придется переходить на него и на машине для разработки. Чтобы не иметь проблем с несовместимостью.


это как вы смогли так продержаться 18 лет чтобы ниразу не поднять версию сиквела?:))
31 окт 18, 15:17    [21720433]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия/вставка слова в таблицу. Как сделать для нескольких слов в 1-м запросе?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Malyav
Чувствую, скоро придется переходить на него и на машине для разработки. Чтобы не иметь проблем с несовместимостью.
Конечно, версии девелоперской среды должны соответствовать рабочим версиям, как же иначе? Это же банально неудобно, даже если не использовать новых фич.
31 окт 18, 15:26    [21720449]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия/вставка слова в таблицу. Как сделать для нескольких слов в 1-м запросе?  [new]
Malyav
Member

Откуда:
Сообщений: 183
Я обычно не стремлюсь к обновлению без необходимости. Работает, и ладно. Когда прижмет - обновляюсь.

По сути моего вопроса - поставленную задачу я решил. Начал действовать по предложенному плану:

court
нуу с этим
Malyav
PS. У меня MS SQL 2000 MSDE.

"красиво" не получится :)

всё будет "тупо" и "деревянно"
- табличная функция, которая разбивает список на строки (примеров на форуме валом)
- цикл по результату запроса этой таб.функции
- временная табл. в которую "ложаться" найденные/вставленные ID
- в конце ХП - select из времянки


Табличную функцию я взял из статьи Массивы и Списки в SQL Server. Вот эта функция:

SET QUOTED_IDENTIFIER ON
GO

        
         CREATE FUNCTION [dbo].[iter_charlist_to_table]
                    (@list      ntext,
                     @delimiter nchar(1) = N',')
         RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                             str     varchar(4000),
                             nstr    nvarchar(2000)) AS

   BEGIN
      DECLARE @pos      int,
              @textpos  int,
              @chunklen smallint,
              @tmpstr   nvarchar(4000),
              @leftover nvarchar(4000),
              @tmpval   nvarchar(4000)

      SET @textpos = 1
      SET @leftover = ''
      WHILE @textpos <= datalength(@list) / 2
      BEGIN
         SET @chunklen = 4000 - datalength(@leftover) / 2
         SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
         SET @textpos = @textpos + @chunklen

         SET @pos = charindex(@delimiter, @tmpstr)

         WHILE @pos > 0
         BEGIN
            SET @tmpval = ltrim(rtrim(left(@tmpstr, charindex(@delimiter, @tmpstr) - 1)))
            INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
            SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
            SET @pos = charindex(@delimiter, @tmpstr)
         END

         SET @leftover = @tmpstr
      END

      INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
   RETURN
   END
GO


На выходе у нее таблица из трех полей, номер по порядку и два одинаковых списка слов из распарсенной строки в varchar и nvarchar. Номер п/п мне пригодился для возврата полного списка WordID в том порядке, в каком слова шли в строке.

Из этой же статьи я взял и код, как получить список WordID тех слов, которые уже есть в таблице WordList.

SELECT  C.WordID, C.Word  FROM WordList C
JOIN  dbo.iter_charlist_to_table('main,pen,work,cat,notebook', DEFAULT) s ON C.Word = s.nstr
ORDER BY s.listpos


Следующей задачей было: 1-определить слова, которых в таблице нет. Тут пришлось мне повозиться, так как я не большой спец по SQL. Решение нашел такое (по подсказке iap про использование NOT EXISTS):

SELECT [nstr] as NewWords from dbo.iter_charlist_to_table('main,pen,work,cat,notebook', DEFAULT) AS T1 
WHERE NOT EXISTS(SELECT Word FROM dbo.WordList where Word=T1.[nstr]) 


и 2-вставить новые слова в таблицу WordList. Тут я сначала пытался делать перебором строк из этого запроса, созданием временной таблицы, но в результате нашел более простой вариант:

INSERT INTO WordList (Word)
SELECT [nstr] as NewWords from dbo.iter_charlist_to_table('main,pen,work,cat,notebook', DEFAULT) AS T1 
WHERE NOT EXISTS (SELECT Word FROM dbo.WordList where Word=T1.[nstr]) 


Теперь, когда я знаю, что все слова из исходной строки есть в таблице, я возвращаюсь к "список WordID тех слов, которые уже есть в таблице WordList" и использую его в конце ХП.

Таким образом, ХП для получения WordID соответствующему перечисленным в строке словам (с одновременным добавлением отсутствующих в таблице слов) у меня получилась такая:

GO
CREATE PROCEDURE [dbo].[GetWordsID] 
	@MyWords varchar(8000) 
AS
BEGIN
	--Insert new words into table
	INSERT INTO WordList (Word)
	SELECT [nstr] as NewWords from dbo.iter_charlist_to_table(@MyWords, DEFAULT) AS T1 
	WHERE NOT EXISTS (SELECT Word FROM dbo.WordList where Word=T1.[nstr]) 

	--Select all IDs of words
	SELECT  C.WordID FROM WordList C
	JOIN  dbo.iter_charlist_to_table(@MyWords, DEFAULT) s ON C.Word = s.nstr
	ORDER BY s.listpos
END
GO


Использование:
strSQL = "exec GetWordsID '" + sAllEng + "'";
// где sAllEng='main,pen,work,cat,notebook' и далее выполнение этой строки запроса.



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

PS. Это решение работает и на 2000-м и на 2017- сервере. Но court как бы намекает, что есть более красивое решение, не доступное для 2000 сервера. Если у кого есть желание, напишите сюда, что это за решение...

Благодарю всех ответивших на мой вопрос, вы дали мне верное направление.
3 ноя 18, 17:52    [21723336]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия/вставка слова в таблицу. Как сделать для нескольких слов в 1-м запросе?  [new]
court
Member

Откуда:
Сообщений: 2016
Malyav
Но court как бы намекает, что есть более красивое решение, не доступное для 2000 сервера. Если у кого есть желание, напишите сюда, что это за решение...
в 2017-ом есть стандартный STRING_SPLIT
+ есть merge с output
Так что, при желании, действительно можно было бы всё сделать "в 1-м запросе", как в сабже
Типа такого:
--create table #WordList (WordID integer identity, Word varchar(100));

declare @str varchar(1000)='example home work table phone';

--
merge #WordList as target
using(select value from STRING_SPLIT(@str,' ') where ltrim(value)<>'') as source
	on target.Word=source.value

when matched then
	update 
		set target.Word=source.value

when not matched then
	insert (Word) values (source.value)

output inserted.WordID, $action; 

пс
update set target.Word=source.value , конечно "не очень" хорошо
5 ноя 18, 11:50    [21724028]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить