Insert Data From One Table To Another

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 Data From One Table To Another

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2011-03-24 : 03:46:24

I want To Copy Data From One Table To Another My Data is Like This
table Name= ABC
(UserID) (CheckTime) (Checktype)
(1) (10/18/2010 2:39:08 PM) (I)
(1) (10/18/2010 9:12:46 PM) (o)
(1) (10/19/2010 8:56:21 AM) (I)
(2) (10/20/2010 8:50:21 AM) (I)
(2) (10/20/2010 8:24:13 PM) (O)

I want this Data To Be Insert into Another Table naming XYZ in this Table I have a Triiger which Check Condition and then Insert into table I have Tried like this

Insert into XYZ
Select * from ABC
but Error Occured

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

any Ideas????
Thanks In Advance.....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-24 : 04:21:50

Post the code used in the trigger

Madhivanan

Failing to plan is Planning to fail

asif372
Posting Yak Master

100 Posts

Posted - 2011-03-24 : 04:34:33

I Have Use This Trigger IN second Table In Which Data has To be save

CREATE Trigger [dbo].[InsertCheck]
On [dbo].[CHECKINOUT]
INSTEAD OF INSERT
AS
BEGIN
Declare @CtypeULess varchar(1);
Declare @Ctype varchar(1);
Declare @CUtype varchar(1);
DECLARE @Date Datetime;
DECLARE @ID int;
SET @ID =(select USERID from Inserted);
SET @CtypeULess= (select CHECKTYPE from Inserted);
SET @Date = (Select CheckTime from Inserted);
SET @Ctype =(SELECT Top 1 CheckTypeUpdated FROM CHECKINOUT where USERID = @Id ORDER BY CHECKTIME DESC);
if(@Ctype='I')
SET @CUtype='O'
Else if (@Ctype='O')
SET @CUtype='I'
Else
SET @CUtype='I'
Insert Into CHECKINOUT Values(@ID,@Date,@CtypeULess,@CUtype,1,'i','o ',1,'o','A','HeadOffice')
End

matty
Posting Yak Master

161 Posts

Posted - 2011-03-24 : 05:18:35

Since you are inserting more than 1 row, the subqueries will return more than one row which causes the error.(INSERTED table will have more than 1 row)

asif372
Posting Yak Master

100 Posts

Posted - 2011-03-24 : 08:57:24

Can We Do This By Looping If Yes So Whats The Procedure

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-24 : 10:03:11

Try this

CREATE Trigger [dbo].[InsertCheck]
On [dbo].[CHECKINOUT]
INSTEAD OF INSERT
AS
BEGIN
Insert Into CHECKINOUT
select USERID,CheckTime,CHECKTYPE,case when CheckTypeUpdated='I' then 'O' else 'I' end,1,'i','o ',1,'o','A','HeadOffice'
from Inserted
END

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources