SQL: Select many records into a new table

Question

Hi all.

I want to select at least 20 records from a table into a new table based on a number 1 to 20.

select * into blah from foo where fubar = '1'

how do i add 1 to 20 into that statement?

edit:

select * into blah from foo where fubar = '1' and where fubar = '2' and where fubar = '3'

Sorry. Let me see if i can make it more clear. Basically i want to select 20 record based on fubar = 1 to 20. I tried to use "and where" but i get a syntax error.

eidt: what if fubar is nvarchar(11)

Answer 1

selet top 20 * into blah from foo where fubar = '1'

I'm not really sure what you're asking though.

EDIT:

selet top 20 * into blah from foo where fubar IN ('1','2',...'20')

Answer 2

You looking at just inserting the top 20 records?

SELECT TOP 20 *
INTO
    blah
FROM
    foo
WHERE
    fubar = '1'

There are also many ways that you can parameterize the TOP clause too (the examples are for SQL Server).

EDIT:

In response to your update, if fubar column is numeric, then it's simply

SELECT * INTO blah FROM foo WHERE fubar BETWEEN 1 AND 20

if fubar is a character field but contains only numbers then you can cast the field to Integer and still use the BETWEEN clause

SELECT * INTO blah FROM foo WHERE CAST(fubar AS INT) BETWEEN 1 AND 20

This is for SQL Server

Answer 3

Two cheesy methods both involving changing the where clause:

If fubar is a numeric column try where fubar >0 and fubar < 20 if fubar is a character column try where fubar in ('1','2','3', ... '19','20')

I am sure there are more effective methods that are general but this might help you out in a particular circumstance.

Answer 4

It depends on database you are using. For MS SQL Server it will be

SELECT TOP 20 * into blah from foo order by fubar

For Oracle

SELECT * into blah from foo  order by fubar where rownum()<=20

Answer 5

SELECT * INTO blah FROM foo WHERE fubar BETWEEN 1 AND 20

Answer 6

Change your 'AND" to an 'OR':

select * into blah 
from foo 
where fubar = 1 
OR fubar = 2 
OR fubar = 3