Add values within IN clause to table

[G12Consult]

Can I run a select to retrieve all the records from an IN clause

Like:

SELECT * FROM IN('3344444','43332333')

I have 5000 numbers in the clause that I need added as rows to a table

 

[bborissov]

I don't understand what you need.

Borislav Borissov
VFP9 SP2, SQL Server

 

[simian336]

You are saying two different things...

If you want to retrieve records you can do that with WHERE clause

SELECT *
FROM YourTable
where YourField in ('3344444','43332333')

If you want to add records to a table that will require in INSERT statement.

Simian

 

[Olaf Doschke]

Your major misunderstanding must be that the comma separated values of an IN clause are usable as a table. No, it's merely a list of values.

So while syntax of [tt]field IN (val1,val2)[/tt] and [tt]field IN Select * from atable[/tt] are similar, this doesn't compare and IN (value list) can't use (value list) as a table to insert into another table.

If you have a file with CSV data the command to use to import it is BULK INSERT. For BULK INSERT you'll need one row per record, so a file with merely one list of numbers all comma separated and line breaks only due to word wrap will be read in as one record only, with 5000 columns, if that would be possible at all. You need another file format to import such data, turn every comma into a line break

Bye, Olaf.

 

[PWise]

TRY

DECLARE @Start int 
DECLARE @End int 
 
SELECT @Start =1, 
       @End = 5000;


-- recursive CTE
WITH Alll (num)
AS (
    SELECT 1  
    UNION ALL
    SELECT  num+1
    FROM Alll
    WHERE num < @End
    )
insert into yourtable(yourfield)
select * from Alll
option (maxrecursion 0)
select * from @tab

 

[DaveInIowa]

If you were going to hardcode 5000 numbers (by-the-way; what you have shown are not numbers but strings), you may as well insert them into a table.

DECLARE @LotsoNumbers TABLE (Number INT)

INSERT INTO @LotsoNumbers VALUES
(3344444),
(43332333)

SELECT * FROM @LotsoNumbers

After pasting the numbers into SSMS, you can use some editing techniques to add the parenthesis and commas between values. Click before the first number, hold down the Alt key and drag straight down to extend the "cursor" to include multiple lines. You can then type a single "(" to insert it at the same column location in every line. Do the same at the right of each value to insert "),".

 

[simian336]

Post an example of your data and we can be more specific...

Simi