Populate one DB field with a list from a "storage" DB

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

Populate one DB field with a list from a "storage" DB

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-25 : 10:38:56

Mike writes "I am trying to learn SQL programming and would like to see if you can provide me with some direction on a small project.

The project that I would like to work on has 2 SQL DBs and entails looking in a database for empty CLASSCODE fields, populate these fields from a “storage” database that contains un-used CLASSCODE codes and tag the assigned codes from the storage DB as used (“Y”)

The main DB called books has the following relevant fields, bookid, bookname, CLASSCODE (besides a number of other fields which are irrelevant to what I need to do). The “storage DB called classcode has 3 fields, CLASSCODE, Used (single character intended to contain either a Y or NULL).

My idea is to create a script to accomplish this that I can run periodically when new books have been added to the DB. I can setup the update routine I am stuck with is trying to do the following:

Pick the first record (classcode) from the classcode DB
Go to the books DB and update the first record that does not contain a code with the above classcode

Go back to the classcode DB and mark the used record with a Y in the Used field
Loop though the books DB and perform the same operation until no more records exist with an empty classcode field. The existing data would look something like:

dbo.Books

Bookid Classcode

A1234 00001
B1234 00002
C1234 00003
D1234
E1234

dbo.classcode

classcode Used Date

00001 Y 5/17/2006 12:00:00 AM
00002 Y 5/17/2006 12:00:00 AM
00003 Y 5/17/2006 12:00:00 AM
00004
00005
00006
00007
00008

I need to pick the next classcode.classcode record that does not have a Y in the Used field and apply it to the first books.classcode record that has a NULL classcode record and then mark the used classcode.classcode record with a "Y"

Hopefully this will make some sense, I will greatly appreciate any feedback.

Mike."

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-08-25 : 18:04:00

Try this

declare @ClassCode char(5)
declare @BookId char(5)
set @BookId = NULL
select @ClassCode = min(ClassCode) from ClassCode where Used is NULL
select @BookId = min(BookId) from Books where ClassCode is NULL
while @BookId IS NOT NULL
begin
begin tran
update Books set ClassCode = @ClassCode where BookId = @BookId
update ClassCode set Used = 'Y', [Date] = getdate() where ClassCode = @ClassCode
commit
select @ClassCode = min(ClassCode) from ClassCode where Used is NULL
select @BookId = min(BookId) from Books where ClassCode is NULL
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources