La sous-requête renvoie plus de 1 valeur Erreur SQL sur la sélection multiple

Je voulais avoir une table temporaire qui va insert des valeurs en utilisant des instructions select. Mais chaque fois que j'ai couru la requête, j'ai toujours eu une erreur

La sous-requête renvoie plus de 1 valeur. Ceci n'est pas autorisé lorsque la requête suit =,! =, <, <=,>,> =, Ou lorsque la sous-requête est utilisée comme une expression. La déclaration a été résiliée. (0 rangée (s) touchée (s))

C'est bizarre car il semble qu'il n'y ait pas d'erreurs dans le code. Mais s'il y en a, corrigez-moi s'il vous plaît.

Voici la requête:

DECLARE @tblKeywords TABLE (Keyword1 VARCHAR(MAX), Keyword2 VARCHAR(MAX), Keyword3 VARCHAR(MAX)) Insert into @tblKeywords (Keyword1, Keyword2, Keyword3) Values( (Select k from (Select Keyword k, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable where mytable.RowNum BETWEEN 1 and 3), (Select kk from (Select Keyword kk, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable where mytable.RowNum BETWEEN 4 and 6), (Select kkk from (Select Keyword kkk, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable where mytable.RowNum BETWEEN 7 and 9) ) Select * from @tblKeywords

Dans SQLServer2005 + Vous pouvez utiliser l'option avec l'expression de table commune

 DECLARE @tblKeywords TABLE (Keyword1 VARCHAR(MAX), Keyword2 VARCHAR(MAX), Keyword3 VARCHAR(MAX)) ;WITH cte AS ( SELECT Keyword, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum FROM dbo.t_SMSKeyword ) INSERT @tblKeywords(Keyword1, Keyword2, Keyword3) SELECT c1.Keyword, c2.Keyword, c3.Keyword FROM cte c1 JOIN cte c2 ON c1.RowNum + 3 = c2.RowNum JOIN cte c3 ON c2.RowNum + 3 = c3.RowNum WHERE c1.RowNum BETWEEN 1 and 3

Voir l'exemple sur SQLFiddle

Sélectionnez 4 lignes dans la première colonne et 3 lignes pour les autres colonnes

 DECLARE @tblKeywords TABLE (Keyword1 VARCHAR(MAX), Keyword2 VARCHAR(MAX), Keyword3 VARCHAR(MAX)) ;WITH cte AS ( SELECT Keyword, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum FROM dbo.t_SMSKeyword ) INSERT @tblKeywords(Keyword1, Keyword2, Keyword3) SELECT c1.Keyword, c2.Keyword, c3.Keyword FROM cte c1 LEFT JOIN cte c2 ON c1.RowNum + 4 = c2.RowNum AND c2.RowNum < 8 LEFT JOIN cte c3 ON c2.RowNum + 3 = c3.RowNum WHERE c1.RowNum BETWEEN 1 and 4 SELECT * FROM @tblKeywords

Exemple pour la deuxième solution SQLFiddle

Vous obtiendrez le résultat souhaité avec la requête suivante

SQLFIDDLE

 DECLARE @tblKeywords TABLE (Keyword1 VARCHAR(MAX), Keyword2 VARCHAR(MAX), Keyword3 VARCHAR(MAX)) Insert into @tblKeywords (Keyword1, Keyword2, Keyword3) select k,kk,kkk from (Select k, RowNum from (Select Keyword k, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable where mytable.RowNum BETWEEN 1 and 3) a, (Select kk, RowNum from (Select Keyword kk, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable where mytable.RowNum BETWEEN 4 and 6) b, (Select kkk,RowNum from (Select Keyword kkk, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable where mytable.RowNum BETWEEN 7 and 9) c where a.RowNum = b.RowNum-3 and a.RowNum = c.RowNum-6; Select * from @tblKeywords;

essaye ça

 DECLARE @tblKeywords TABLE ( Keyword1 VARCHAR(MAX) , Keyword2 VARCHAR(MAX) , Keyword3 VARCHAR(MAX) ) INSERT INTO @tblKeywords ( Keyword1 , Keyword2 , Keyword3 ) SELECT k , ( SELECT kk FROM ( SELECT e.Keyword AS kk , ROW_NUMBER() OVER ( ORDER BY e.KeywordID ) AS RowNum1 FROM t_SMSKeyword AS e ) AS Emp1 WHERE Emp1.RowNum1 = ( RowNum + 3 ) ) , ( SELECT kkk FROM ( SELECT e.Keyword AS kkk , ROW_NUMBER() OVER ( ORDER BY e.KeywordID ) AS RowNum1 FROM t_SMSKeyword AS e ) AS Emp1 WHERE Emp1.RowNum1 = ( RowNum + 6 ) ) FROM ( SELECT e.Keyword AS k , ROW_NUMBER() OVER ( ORDER BY e.KeywordID ) AS RowNum FROM t_SMSKeyword AS e ) AS mytable WHERE mytable.RowNum BETWEEN 1 AND 3 SELECT * FROM @tblKeywords