| Author |
 Topic  |
|
|
asif372
Yak Posting Veteran
Pakistan
94 Posts |
 Posted - 01/10/2013 : 04:16:23
|
My Data is like this
EID TIMEIN TIMEOUT
1 9:00 10:00
1 12:00 14:00
2 10:00 16:00
I want to Add this Data in Table naming NewTable my Table Columns are following
EID,
TIME,
TYPE
i want data to be fillid in my table using Stored Procedure like this
EID TIME Type
1 9:00 I
1 10:00 O
1 12:00 I
1 14:00 O
2 10:00 I
2 16:00 O
How can it be possible Thanks In Advance.... |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/10/2013 : 04:27:22
|
SELECT EID,TIMEIN AS [Time],'I' AS Type
FROM table
UNION ALL
SELECT EID,TIMEOUT,'O'
FROM Table
ORDER BY EID,[Time]
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
asif372
Yak Posting Veteran
Pakistan
94 Posts |
Posted - 01/10/2013 : 04:29:26
|
Thanks for your Responce,
I want to Insert Not Select Man... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/10/2013 : 04:32:34
|
quote:
Originally posted by asif372
Thanks for your Responce,
I want to Insert Not Select Man...
Is it so difficult to add a insert logic to it?
INSERT into NewTable (
EID,
TIME,
Type
)
SELECT EID,TIMEIN AS [Time],'I' AS Type
FROM table
UNION ALL
SELECT EID,TIMEOUT,'O'
FROM Table
ORDER BY EID,[Time]
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1441 Posts |
Posted - 01/10/2013 : 04:35:49
|
Alternate is:
INSERT ITNO NewTable
SELECT EID, Times, CASE keyCol WHEN 'TIMEIN' THEN 'I' ELSE 'O' END Type FROM
(SELECT * FROM @t) p
UNPIVOT (Times FOR keyCol IN (TIMEIN, TIMEOUT))pvt
--
Chandu |
|
|
|
asif372
Yak Posting Veteran
Pakistan
94 Posts |
Posted - 01/10/2013 : 04:41:17
|
visakh16 i think that i was unable to convey my point to you in a better way..
well i want to insert my data using logic
i will give Following Parameters to Procedure
@EID int,
@TimeIn Datetime,
@TimeOut Datetime,
@Remarks varchar(50)
now when inserting record to Table Logic will be implemented like if
Timein then Checktime and 'I' as Type
TimeOut then Checktime and 'O' as Type
I think Loop Scenario Should be added to work on this criteria
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/10/2013 : 04:47:21
|
why loop? so are you telling your values will be passed from users as parameters and not come from a table?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
asif372
Yak Posting Veteran
Pakistan
94 Posts |
Posted - 01/10/2013 : 04:50:15
|
| Yes Parameter will be Passed Rather then Selecting from Table |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1441 Posts |
Posted - 01/10/2013 : 04:51:47
|
CREATE PROCEDURE myProc
(@EID int,
@TimeIn Datetime,
@TimeOut Datetime--,@Remarks varchar(50)
)
AS
BEGIN
INSERT INTO t1
SELECT @EID,@TIMEIN AS [Time],'I' AS Type
UNION ALL
SELECT @EID,@TimeOut,'O'
END
GO
EXEC dbo.myProc 1, '9:00', '10:00'
SELECT * FROM t1
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/10/2013 : 04:59:45
|
in that case you've to call procedure each time for inserting a row of data and would be like this
CREATE PROC InsertData
@EID int,
@TimeIn Datetime,
@TimeOut Datetime,
@Remarks varchar(50)
AS
INSERT into NewTable (
EID,
TIME,
Type
)
SELECT @EID,
@TimeIn,
'I'
UNION ALL
SELECT @EID,
@TimeOut,
'O'
GO
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/10/2013 : 05:00:49
|
one question is where are you capturing this data currently if its not in a table?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Insert 1 RowData to 2 Row
