quote:

---

Originally posted by atmonline

To insert into masterfile i need the js_userid which i am getting from js_userdef. the list of the fields i am inserting from sheet1

sheet1

appkey name etc..
1 abcd
100 xyz
213 pqr


#sheet1
-------
rownumber appkey name etc..
1 1 abcd
2 100 xyz
3 213 pqr






js_userdef
---------
jobseekerid js_username js_passwrod

1 abcd abcd1
2 xyz abcd2
3 pqr abcd3


here i can link js_usrdef with #sheet1 using js_password(abcd+rownumber) and row number

i need to have the joobseekerid from each applicant along with other
data in #sheet1.so each time i need to acces the js_userdef for that. if i can store the jobsskerid along with the temp table(#sheet1) i can avoid that and improve performance.



Basically my question is how to add an extra column in temp table.

select column1,sheet1.* inot #sheet1 from sheet1

Here column1 is not defined in sheet1.

---

SELECT 
ROW_NUMBER() OVER (ORDER BY Appkey) Number ,
sheet1.*,NULL AS jobseekerid 
INTO #sheet1 FROM sheet1

---- now on #sheet1 means sheet1...

SELECT @count=0
WHILE (@count < @ApplicantCount)
BEGIN
SELECT @count = @count + 1
SELECT @Passcode = @Prefix + cast(@count as varchar(10))
EXEC temp_newuser @Passcode
END
-- new users added to js_userdef is now complete

UPDATE s
SET s.jobseekerid=j.jobseekerid
FROM #sheet1 s
INNER JOIN js_userdef j
on j.js_passwrod=s.name+cast(rownumber as varchar(5))