Subquery returns more than 1 SQL error value for multiple selection

Question

I wanted to have a temporary table that would insert values ​​using select statements. But every time I ran the request, I always got an error

A subquery returns more than 1 value. This is not valid when the request follows = ,! =, <, <=,>,> = or when the subquery is used as an expression. The application has been discontinued. (0 rows (rows) affected)

This is strange because there are no errors in the code. But if there is, please correct me.

Here's the request:

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 

Answer 1

In SQLServer2005 + you can use the option with a common table expression

 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 

See SQLFiddle Example

Select 4 rows in the first column and 3 rows for the other columns

 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 

Example for second SQLFiddle solution

Answer 2

You will get the desired result with the following query

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; 

Answer 3

try it

 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