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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Update selected Records and insert into Table B
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ronanggl
Starting Member

Singapore
3 Posts

Posted - 03/12/2013 :  00:36:43  Show Profile  Reply with Quote
I am trying to see if there are any way that i can do the following function.

I have 2 tables as follows:
TableA (ID varchar(10), FirstName varchar(100), Status bit)
TableB (ID varchar(10), FirstName varchar(100))

How is it possible that using 1 query to select all the rows from TableA which [Status] = '0' and insert it into TableB. At the same time to update those rows that was queried out from TableA and update it's status to '1'

Initial result:

TableA
ID FirstName Status
01 Person A 1
02 Person B 1
03 Person C 0
04 Person D 0

TableB
ID FirstName
01 Person A
02 Person B

After running query:

TableA
ID FirstName Status
01 Person A 1
02 Person B 1
03 Person C 1
04 Person D 1

TableB
ID FirstName
01 Person A
02 Person B
03 Person C
04 Person D

Edited by - ronanggl on 03/12/2013 00:37:27

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 03/12/2013 :  00:43:11  Show Profile  Reply with Quote
Why does it have to be one query?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/12/2013 :  00:57:31  Show Profile  Reply with Quote
you need at least a table variable in between
like below


declare @TableA table
(
ID varchar(10),
FirstName varchar(50),
[Status] int
)

insert @TableA
values('01', 'Person A', 1),
('02', 'Person B', 1),
('03', 'Person C', 0),
('04', 'Person D', 0)

declare @TableB table
(
ID varchar(10),
FirstName varchar(50)
)
insert @TableB
values('01', 'Person A'),
('02', 'Person B')

declare @Inserted_IDs table
(
ID varchar(10)
)

insert @TableB
OUTPUT INSERTED.ID INTO @Inserted_IDs
select ID,Firstname
FROM @TableA a
WHERE NOT EXISTS ( SELECT 1 FROM @TableB WHERE FirstName = a.FirstName)

UPDATE a
SET Status=1
FROM @TableA a
JOIN @Inserted_IDs i
ON i.ID = a.ID

SELECT * FROM @TableA

SELECT * FROM @TableB



output
---------------------------------------------------
ID	FirstName	Status
-------------------------------------
01	Person A	1
02	Person B	1
03	Person C	1
04	Person D	1


ID	FirstName
-----------------------
01	Person A
02	Person B
03	Person C
04	Person D



Also not sure why you're having ID field as varchar.
Its better to make it integer type otherwise you may have difficulty in manipulations using it

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

Go to Top of Page

ronanggl
Starting Member

Singapore
3 Posts

Posted - 03/12/2013 :  01:02:49  Show Profile  Reply with Quote
I just like to check if there are any other methods that is more simplify and will not take up much resources as i need to run this query for a few other tables as well and each of those tables consist of 3000-4000 records daily.

Currently i can do it via the following queries:

INSERT into TableB (ID, FirstName)
SELECT ID, FirstName from TableA where status='0'

update TableA set [status]=1 where [status]=0 and ID in (select ID from TableB)



Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 03/12/2013 :  01:15:41  Show Profile  Reply with Quote
So just do that! Personally I'd probably set an intermediate status so you don't accidentally pick up more incoming status=0:

update tableA set status='3' where status='0';
INSERT into TableB (ID, FirstName)
SELECT ID, FirstName from TableA where status='3'

update tableA set status='1' where status='3';
or something.

I'm pretty sure you can do the job with the OUTPUT clause too but I've never had cause to use it.

update tableA set status=1 where status=0
OUTPUT INSERTED.ID, INSERTED.Firstname INTO tableB

You'll have to play around with that - I'm not able to.
Go to Top of Page

ronanggl
Starting Member

Singapore
3 Posts

Posted - 03/12/2013 :  20:58:01  Show Profile  Reply with Quote
many thanks to all the help.

guess i will still use my script. not able to get the Output working.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 03/12/2013 :  21:51:51  Show Profile  Reply with Quote
Actual statement I suggested should be

update tableA set status=1
OUTPUT INSERTED.ID, INSERTED.Firstname INTO tableB
where status=0

(I wasn't at SQL Server so could not check).
If you persist with your original then take heed of my suggestion to update to an intermediate status so you don't pick up any status 0 committed between your two statements (or use transaction level read consistency).
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.06 seconds. Powered By: Snitz Forums 2000