Update selected Records and insert into Table B

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

SQL Server 2005 Forums

Transact-SQL (2005)

Update selected Records and insert into Table B

Author

Topic

ronanggl
Starting Member

3 Posts

Posted - 2013-03-12 : 00:36:43

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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-03-12 : 00:43:11

Why does it have to be one query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-12 : 00:57:31

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/

ronanggl
Starting Member

3 Posts

Posted - 2013-03-12 : 01:02:49

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)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-03-12 : 01:15:41

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.

ronanggl
Starting Member

3 Posts

Posted - 2013-03-12 : 20:58:01

many thanks to all the help.

guess i will still use my script. not able to get the Output working.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-03-12 : 21:51:51

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).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources