SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Insert 1 RowData to 2 Row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 01/10/2013 :  04:16:23  Show Profile  Reply with Quote
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
52317 Posts

Posted - 01/10/2013 :  04:27:22  Show Profile  Reply with Quote

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/

Go to Top of Page

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 01/10/2013 :  04:29:26  Show Profile  Reply with Quote
Thanks for your Responce,
I want to Insert Not Select Man...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/10/2013 :  04:32:34  Show Profile  Reply with Quote
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/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 01/10/2013 :  04:35:49  Show Profile  Reply with Quote
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
Go to Top of Page

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 01/10/2013 :  04:41:17  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/10/2013 :  04:47:21  Show Profile  Reply with Quote
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/

Go to Top of Page

asif372
Posting Yak Master

Pakistan
100 Posts

Posted - 01/10/2013 :  04:50:15  Show Profile  Reply with Quote
Yes Parameter will be Passed Rather then Selecting from Table
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 01/10/2013 :  04:51:47  Show Profile  Reply with Quote

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/10/2013 :  04:59:45  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/10/2013 :  05:00:49  Show Profile  Reply with Quote
one question is where are you capturing this data currently if its not in a table?

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000