Help in SQl Query

Hi all,

Greetings!! I am Migrating data from one database to a new one, both the database are in SQL Server 2005. The tables in the old DB are;

Users (UserName (PK,varchar),ISAdmin(bool),IsGuest(bool))

Groups table(GroupName (pk Varchar))

UserGroupmapping (UserName,GroupName)

In our new DB these tables are modified as

Users (userId (pk), UserName (varchar))

Groups table(GroupId(int),GroupName (pk Varchar))

UserGroupmapping (UserId(FK),GroupId(FK),AccessId(int))

AccessMaster(AccessId(PK,int),Desc(varchar))

The idea now is, all the bool value previously in the User table (IsAdmin etc...) are now to be represented as a entry in the UserGroupMappingTable.

for E.g

if the Old users table was like

Sam 1 1

and the old UserGroupMapping was like

Sam, 'Player'

then the new UserGroupMapping should be populated like;

1,1,1(this last 1 will be from the AccessMaster table for the Access Admin)

1,1,2(2 should be from AccessMaster for Guest etc.)

pffff.. Sorry if am confusing here :(

i was trying with a quert of the sort to first select the first row only.

/*************/

select Users.UserId, Grp.GroupId,
case when Usr.IsAdmin=1 then 8 else 0
End as accesslevel
from
(
select distinct UG.UserName from [Old_db]..[UserGroupMapping] UG
join
[Old_db]..[Users]Usr
on
UG.login = Usr.login
join
[NewDB]..[Groups]Grp
on
UG.GroupName = Grp.GroupName
join
[NEWDB]..[Users]Users
on
Users.UserName=UG.UserName) as mytable

/*************/

but am getting error like;

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Users.UserId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Grp.GroupId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Usr.IsAdmin" could not be bound.

Can someone help?

Thanks in Advance.

[2319 byte] By [MShetty] at [2007-12-24]