Select TOP x From table

Question

This is a rather unique problem and I don;t know if there is an answer. Im trying to input a variable amount of records to insert into a table depending on the value of another table. This is what I have tried

insert into TABLE1
select TOP cast((select Value from TABLE3 WHERE column_name = 'blah') as int) * from TABLE2 

So im trying to get the value from table 3, and use that value to grab the top x records from table 2 to be inserted into table 1.

the error listed is Incorrect syntax near cast.

Answer 1

OP doesn't specify the database they are using, but SQL Server allows a variable. try it out:

declare @YourTable table (RowID int identity(1,1), ColA char(4))
INSERT INTO @YourTable values ('a');INSERT INTO @YourTable values ('aa');INSERT INTO @YourTable values ('aaa');
INSERT INTO @YourTable values ('b');INSERT INTO @YourTable values ('bb');INSERT INTO @YourTable values ('bbb');
INSERT INTO @YourTable values ('c');INSERT INTO @YourTable values ('cc');INSERT INTO @YourTable values ('ccc');

declare @x int=5
select top (@x) * from @YourTable

output:

      RowID ColA
----------- ----
          1 a   
          2 aa  
          3 aaa 
          4 b   
          5 bb  

(5 row(s) affected)

Answer 2

TOP x cannot be specified in a variable.It actually can, see KM's answer (which is definitely better than mine).

An option here would be a dynamic query

DECLARE @topx AS integer;
SET @topx = (select Value from TABLE3 WHERE column_name = 'blah')

DECLARE @query AS varchar(max);
SET @query = 'INSERT INTO table1 SELECT TOP ' + convert(varchar(10),@topx) 
              + ' FROM table2'

exec sp_executesql query