INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Add values within IN clause to table

Add values within IN clause to table

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

Like:

CODE

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

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

RE: Add values within IN clause to table

I don't understand what you need.

Borislav Borissov
VFP9 SP2, SQL Server

RE: Add values within IN clause to table

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

RE: Add values within IN clause to table

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 field IN (val1,val2) and field IN Select * from atable 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.

RE: Add values within IN clause to table

TRY

CODE --> T-SQL

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 

RE: Add values within IN clause to table

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.

CODE

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 "),".

RE: Add values within IN clause to table

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

Simi

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close