insert values

  • This my emp table i want to insert value in other table

    into emp_no table

    emp_id att_day lve_day

    ------ ------------------------------ ------------------------------

    1 1 10

    2 5 10

    3 10 15

    i want output like this

    emp_n days

    11

    12

    13

    14

    15

    16

    17

    18

    19

    110

    25

    26

    27

    28

    29

    210

    anyone can help?

  • Assuming you have a Tally table (see the related link in my signature if you don't know what that is) you could use this table in an OUTER APPLY query.

    If you'd like to see the coded version, please provide ready to use sample data as described in the first link in my signature.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Try this:

    DECLARE @Tab TABLE

    ( emp_id INT

    ,att_day INT

    ,lve_day INT

    )

    ;

    INSERT INTO @Tab

    SELECT 1, 1 ,10

    UNION ALL SELECT 2, 5 ,10

    UNION ALL SELECT 3, 10 ,15

    ;

    ; WITH Tens (N) AS

    (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1

    )

    , Hundreds(N) AS

    (

    SELECT T1.N

    FROM Tens T1

    CROSS JOIN Tens T2

    )

    , Thousands (N) AS

    (

    SELECT T1.N

    FROM Hundreds T1

    CROSS JOIN Hundreds T2

    )

    , Millions(N) AS

    (

    SELECT T1.N

    FROM Thousands T1

    CROSS JOIN Hundreds T2

    )

    , Numbers(N) AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY N)

    FROM Millions

    )

    SELECT T.emp_id , N.N

    FROM @Tab T

    CROSS JOIN Numbers N

    WHERE N.N BETWEEN T.att_day AND T.lve_day

    ORDER BY T.emp_id , N.N

  • i want to pick values from database

  • is it will work on sql server

  • paramjit4 (4/6/2012)


    is it will work on sql server

    Why don't you give it a try? :unsure:



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • ok

  • if you don't mind..can you tell me how it work!!

    thx in advance

  • it's not working on sql server 2000

  • So what? You're in a 2005 forum.

    If I told you that my car was 'not working', would you know how to fix it?


  • paramjit4 (4/6/2012)


    it's not working on sql server 2000

    You posted in the SQL 2005 forum and didn't mention the solution should work on SQL2000.

    Neither the table variable nor the CTE will work.

    As mentioned before I recommend to use a physical tally table (see the related link in my signature).

    Since the OUTER APPLY is available on SS2K5 and up, you'll need to use the CROSS JOIN approach ColdCoffee used.

    And to repeat what I stated before: if you'd like to see a coded solution, please provide ready to use sample data. It's called "help us to help you". 😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • can you give me coords on sql server 2000.,,if you don't mind

    sorry :

  • paramjit4 (4/6/2012)


    can you give me coords on sql server 2000.,,if you don't mind

    sorry :

    Yes, I'll post a coded solution for 2000 if you take te time and post ready to use sample data (again, see the first link in my signature how to do it).

    Also, I'll use a tally table.You might take the time to read the related article in the mean time.. 😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • thx alot it working on 2005

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply