insert values

Question

insert values

paramjit4

Old Hand

Points: 339
More actions
April 6, 2012 at 1:29 am

#266884

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?

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

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

)

, 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

paramjit4 Old Hand Points: 339 More actions · Answer 3

paramjit4

Old Hand

Points: 339

April 6, 2012 at 3:06 am

#1469985

i want to pick values from database

paramjit4 Old Hand Points: 339 More actions · Answer 4

paramjit4

Old Hand

Points: 339

April 6, 2012 at 3:09 am

#1469988

is it will work on sql server

LutzM SSC Guru Points: 107049 More actions · Answer 5

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]

paramjit4 Old Hand Points: 339 More actions · Answer 6

paramjit4

Old Hand

Points: 339

April 6, 2012 at 3:20 am

#1469996

ok

paramjit4 Old Hand Points: 339 More actions · Answer 7

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

thx in advance

paramjit4 Old Hand Points: 339 More actions · Answer 8

paramjit4

Old Hand

Points: 339

April 6, 2012 at 3:46 am

#1470006

it's not working on sql server 2000

Phil Parkin SSC Guru Points: 247158 More actions · Answer 9

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?

LutzM SSC Guru Points: 107049 More actions · Answer 10

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]

paramjit4 Old Hand Points: 339 More actions · Answer 11

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

sorry :

LutzM SSC Guru Points: 107049 More actions · Answer 12

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]

paramjit4 Old Hand Points: 339 More actions · Answer 13

paramjit4

Old Hand

Points: 339

April 6, 2012 at 4:56 am

#1470025

thx alot it working on 2005

insert values

Cookies on SQLServerCentral