Insert 1 RowData to 2 Row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert 1 RowData to 2 Row

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2013-01-10 : 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

52326 Posts

Posted - 2013-01-10 : 04:27:22

[code]
SELECT EID,TIMEIN AS [Time],'I' AS Type
FROM table
UNION ALL
SELECT EID,TIMEOUT,'O'
FROM Table
ORDER BY EID,[Time]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asif372
Posting Yak Master

100 Posts

Posted - 2013-01-10 : 04:29:26

Thanks for your Responce,
I want to Insert Not Select Man...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-10 : 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
Posting Yak Master

100 Posts

Posted - 2013-01-10 : 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

52326 Posts

Posted - 2013-01-10 : 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
Posting Yak Master

100 Posts

Posted - 2013-01-10 : 04:50:15

Yes Parameter will be Passed Rather then Selecting from Table

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-10 : 04:51:47

[code]
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
[/code]

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 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

52326 Posts

Posted - 2013-01-10 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources