select from static list

Question

1.73/5 (4 votes)

See more:

Hello ,

I need a solution that There is a static list
and I want to use this list as a table at select statement

like that :

SQL

SELECT * FROM (1,2,3,4)

result
------
1
2
3
4

thanks

[EDIT - moved from answers]
I need to run sql .
Indeed it is there is a list I need to insert it to a table
and I think I should use INSERT - SELECT
the list is like that :

(1,2,3,4,5 ... ) , I want to be able to use this list a table
and 1 is one row , 2 is an another row ....

am I able to explain ?

-----

It will just one time use-need .
it will not be used in sp and not with dinamic input.

My input is static and it is '17001','17002' ...

I just want to select from this list to use it for inserting another table as rows.

like that

INSERT INTO table1(col1)
(SELECT one_col_val FROM ('17001','17002' ... , '17007') )

NOT : one_col_val is one of '17001','17002' ...

I found a solution from internet but it is a little long solution but works
this is an example that I test does it work , and yes it works

SQL

create table xx (field1 varchar2(300) );
select * from xx;
insert into xx (select to_char(',17100,17300,17500,17700,') from dual);
 
INSERT INTO bddk_hesap_skont_pr(rapor_kod,satir,skont)
(
select 'MBFORM2',34,substr(field1, 
         instr(field1, ',', 1, rownum) + 1, -- start_pos
         instr(field1, ',', 1, rownum + 1) - 
             instr(field1, ',', 1, rownum) - 1 -- data_length
       ) new_data
   from xx ,    
        all_tables
  where rownum <= 
  (
  select length(
           ltrim(
             rtrim(
               translate(field1, ',1234567890', ',')
             )
           )
         ) - 1 rc
    from xx)
    
    )

[/EDIT]

Posted 20-Apr-10 21:59pm

fadime

Updated 29-May-18 4:23am

Maciej Los

v4

Add a Solution

Comments

[no name] 2-Dec-11 1:51am

did you mean you want a temporary table?

Maciej Los 10-Sep-12 16:36pm

@fadime, please, use "Improve question" widget to update your question.

Kuthuparakkal 10-Sep-12 22:06pm

Why are we looking at question that's posted on early 2010. You guyz think that 2+ years is an amble to time to wait for an answer. What a joke!

Santosh kumar Pithani 5-Jan-18 0:07am

Hi,as i know your have to use "string_split" function to get records as unpivot format.

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

supercat9 · Answer 1 · 2010-04-21T12:09:00

It sounds like you want to generate a multi-row table using a single statement, so that you can then insert the rows of that table into another.

I wish there were a standard way to accomplish that, but I am unaware of any. Most SQL implementations provide methods to add lots of data to a table without having to use a separate SQL statement for every row, but I don't know of any single method that will work on all of them.

Eduard Lu · Answer 2 · 2011-12-01T21:33:00

Solution 8

Try the following codes. Create a temporary table to store the static data

SQL

;WITH TempTable AS
{
   SELECT 1,2,3,4
}

-- now 1,2,3,4 is on a temporary table
SELECT * FROM TempTable

Please mark as answer and vote 5 if this solved your problem

Best regards,
Eduard

Posted 1-Dec-11 21:33pm

Eduard Lu

Comments

Santosh kumar Pithani 5-Jan-18 0:03am

This solution is wrong according to OP's requirement.

Member 13849618 · Answer 3 · 2018-05-29T04:24:00

Solution 11

SELECT *
FROM (VALUES(1),(2),(3),(4)) AS t (value)

Posted 29-May-18 4:24am

Member 13849618

Christian Graus · Answer 4 · 2010-04-20T23:50:00

Solution 1

What is the issue, exactly. Where is the 'static list' ? If it's a list class, in code, why do you want to run SQL on it ? I think you're probably trying to do the wrong thing to solve your core issue.

Posted 20-Apr-10 23:50pm

Christian Graus

HJo · Answer 5 · 2011-12-01T19:47:00

Old thread, but thought I could share this solution. I stumbled on the union part of this when looking for a way to set (Select All) as the default for a multi-value parameter with static values in Reporting Services. Combining it with an insert you get this:

SQL

INSERT INTO Table_1 (col1)
(
SELECT 1
UNION 
SELECT 2
)

Amir Mahfoozi · Answer 6 · 2011-12-01T21:22:00

Solution 7

It seems that you need a dynamic sequence generator, so try this one :

SQL

declare @start int
set @start = 17001
declare @finish int
set @finish = 17010;

with a(i)
as
(
select @start
union all
select a.i+1 from a where a.i<@finish
)
select * from a

Hope it helps.

Posted 1-Dec-11 21:22pm

Amir Mahfoozi