assigning a record form one table to another

Question

assigning a record form one table to another

mrpolecat

SSCertifiable

Points: 7014
More actions
December 18, 2009 at 10:24 am

#218867

I have a table of download ids
create table #DownloadCode
(
dlc varchar(14) not null,
dlcmembernumber numeric(13,0)
)
alter table #downloadcode add primary key clustered (dlc)
insert into # Downloadcode(dlc)
select '2Kk6zf439f' union
select '2T3NhCD6b9' union
select '37NfDzD4J3' union
select '42kGCcHM9D' union
select '4H4jzDGG93' union
select '4yCH6MjjKk'
and a table of members who need to get a code assigned to them
create table #Members
(
membernumber numeric(13,0) not null
memberdlc varchar(14)
)
alter table #members add primary key clustered (membernumber)
insert into #members(membernumber)
select 1 union
select 2 union
select 3 union
select 4 union
select 5
I need to assign an individual code to each member record and then update the downloadcode table with the membernumber to burn the code and be able to identify who I gave it to. I could run this through with cursors but I am looking for the set based solution that I know must exist.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

How is the data in these 2 tables related? How can you tell which code goes with which member? Based on your sample data, what would you want the tables to look like after the updates?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

For my edification, this means that each download code can be assigned to one and only member, correct?

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

It doesn't matter which code gets assigned to which member. There is no relation until the code is assigned. It only matters that once it is assigned it cannot be assigned again.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

mrpolecat (12/18/2009)
It doesn't matter which code gets assigned to which member. There is no relation until the code is assigned. It only matters that once it is assigned it cannot be assigned again.

So, that means yes to my question?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Too bad your using SQL Server 2000, I think I have a neat way of doing this in SQL Server 2005/2008.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 6

Yes one code per person, one person per code. There will be more codes than people as they are created in batches.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 7

In writing the cursor for this I found the best way was to just update the download file with the member number it got assigned to so I didn't have to update both tables.

--create download code table

create table #DownloadCode

(

dlc varchar(14) not null,

dlcmembernumber numeric(13,0),

dlcdatereceivied datetime,

dlcdateexpires datetime,

dlcissuedby varchar(50)

)

alter table #downloadcode add primary key clustered (dlc)

insert into # Downloadcode(dlc)

select '2Kk6zf439f' union

select '2T3NhCD6b9' union

select '37NfDzD4J3' union

select '42kGCcHM9D' union

select '4H4jzDGG93' union

select '4yCH6MjjKk'

--create members table

create table #Members

(

membernumber numeric(13,0) not null

)

alter table #members add primary key clustered (membernumber)

insert into #members(membernumber)

select 1 union

select 2 union

select 3 union

select 4 union

select 5

--cursor to assign downloadcoeds to members

declare @memnum decimal(13,0)

declare mycursor cursor for

--select any member that has not been assigned a code

select [membernumber] from members left join downloadcode on [membernumber] = dlcmembernummber where dlcmembernumber is null

open mycursor

fetch next from mycursor into @memnum

while @@fetch_status = 0

begin

-- assign the next available code with the earliest issue date

update downloadcode set dlcmembernumber = @memnum , dlcdateissued = getdate(),dlcissuedby='DTS Package'

from downloadcode join

(select min(dlc) mindlc from downloadcode

where dlcmembernumber is null and dlcdatereceived in (select min(dlcdatereceived) from downloadcode where dlcmembernumber is null)) x on

dlc = mindlc

fetch next from mycursor into @memnum

end

close mycursor

deallocate mycursor

I'm still looking for a set based solution if it is out there.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

mrpolecat (12/21/2009)
I'm still looking for a set based solution if it is out there.

Do a SELECT/INTO a new table with the identity function... then take (MOD 5)+1 of the identity column. It'll be much faster than any cursor and it's set based.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mrpolecat SSCertifiable Points: 7014 More actions · Answer 9

Jeff,

I don't get what you are saying. What am I supposed to select into the new table?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

How about something like this?

--create download code table

create table #DownloadCode

(

dlc varchar(14) not null,

dlcmembernumber numeric(13,0),

dlcdatereceived datetime,

dlcdateexpires datetime,

dlcissuedby varchar(50)

)

alter table #DownloadCode add primary key clustered (dlc)

insert into #DownloadCode(dlc)

select '2Kk6zf439f' union

select '2T3NhCD6b9' union

select '37NfDzD4J3' union

select '42kGCcHM9D' union

select '4H4jzDGG93' union

select '4yCH6MjjKk'

--create members table

create table #Members

(

membernumber numeric(13,0) not null

)

alter table #Members add primary key clustered (membernumber)

insert into #Members(membernumber)

select 1 union

select 2 union

select 3 union

select 4 union

select 5

create table #tmpMembers(

MemberId int identity(1,1),

membernumber numeric(13,0)

);

create table #tmpDownloadCode(

DLCId int identity(1,1),

dlc varchar(14) not null

);

declare @MemberCount int;

insert into #tmpMembers(membernumber)

select

m.membernumber

from

#Members m

left outer join #DownloadCode dc

on m.membernumber = dc.dlcmembernumber

where

dc.dlcmembernumber is null;

set @MemberCount = @@rowcount; -- How many members with out download codes

set rowcount @MemberCount; -- SQL Server 2000

insert into #tmpDownloadCode(dlc)

select

dlc

from

#DownloadCode

where

dlcmembernumber is null;

set rowcount 0;

select

m.membernumber,

d.dlc,

dc.*

from

#DownloadCode dc

inner join #tmpDownloadCode d

on (dc.dlc = d.dlc)

inner join #tmpMembers m

on (d.DLCId = m.MemberId);

declare @DateReceived datetime;

update #DownloadCode set

dlcmembernumber = m.membernumber,

@DateReceived = dlcdatereceived = getdate(),

dlcissuedby = 'DTS Package',

dlcdateexpires = dateadd(yy, 1, @DateReceived)

from

#DownloadCode dc

inner join #tmpDownloadCode d

on (dc.dlc = d.dlc)

inner join #tmpMembers m

on (d.DLCId = m.MemberId);

select * from #DownloadCode;

Greg Snidow SSCoach Points: 16108 More actions · Answer 11

mrpolecat (12/21/2009)
update downloadcode set...dlcdateissued = getdate

mrpolecat, I don't see a column 'dlcdateissued' in either of your tables. Is it supposed to be dlcdatereceivied[SIC]? When I change it to dlcdatereceived nothing seems to happen. Just so I'm clear, it does not matter to you which member gets which download code, nor in which order they get assigned?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 12

Sorry the table has some columns that I didn't include originally because they weren't part of the problem.

create table #DownloadCode

(

dlc varchar(14) not null,

dlcmembernumber numeric(13,0),

dlcdatereceived as getdate(),

dlcdateexpires as dateadd(mm,1,getdate()),

dlcdateissued datetime,

dlcissuedby varchar(50)

)

The code expiration date and received date are put in the table when the codes are created.

It doesn't matter which code gets assigned to which member other than we get them in batches so earlier batches should get assigned first. Once a member gets a code they should not get another code. Once a code is assigned it cannot be assigned again.

Greg Snidow SSCoach Points: 16108 More actions · Answer 13

I think Lynn's solution meets all your needs then. I did not see it until after I started working on it.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 14

I think, using Jeff's hint for the select into (I still don't get the (mod 5) + 1) and Lynn's code, I simplified it a little

select identity(int,1,1) did,dlc into #tmp_dlc

from #downloadcode

where dlcmembernumber is null

order by dlcdatereceived

select identity(int,1,1) mid,membernumber into #tmp_mem

from #members

left join #downloadcode on dlcmembernumber = membernumber

where dlcmembernumber is null

update #downloadcode

set dlcmembernumber = membernumber,

dlcdateissued = getdate(),

dlcissuedby = 'DTS Package'

from #Downloadcode a

join #tmp_dlc b on a.dlc=b.dlc

join #tmp_mem on did=mid

select * from #downloadcode

I don't think the row counts are necessary because the joins will limit the the number of codes assigned. I'm not sure how much overhead is added by putting all of the downloadcodes into the temp table. I am only looking at tens of thousands of codes at any given time.

Thank you all for the help.