loops oops

Hi,
How can I walk through a result of a select query? I have two tables.
TableA(id, name1, name2, notes), TableB(id, grade). I have to bulk insert
some data, (given: name1, name2, notes and grande too). The "bulk insert"
inserts datas in one step. So first I insert the values a temporary table
(TableTmp). Then I have to walk through this TableTmp and at each row I
have to do this:
1, insert name1, name2, notes into TableA
2, get back the actual @.@.identity
3, insert grade into TableB where is the last identity.
I would like to make it in sql query analizer...
I have problem with the loop. I have no idea how to make it.
Is it not overcomplicated? Is there any simple solution?
Thank you for help
chris
Message posted via http://www.webservertalk.comI don't think you'll need a loop. Usually it's best to avoid loops and row
by row processing.
Unfortunately you haven't given us any clues about keys or constraints or
shown us the table where this data comes from. So here is a wild guess:
INSERT INTO TableA (col1, col2, col3)
SELECT DISTINCT col1, col2, col3
FROM Unspecified
INSERT INTO TableB (id, col4, col5, col6)
SELECT DISTINCT
A.id /* the IDENTITY col from A */ ,
U.col4, U.col5, U.col6
FROM Unspecified AS U
JOIN TableA AS A
ON U.col1 = A.col1
AND U.col2 = A.col2
AND U.col3 = A.col3
David Portas
SQL Server MVP
--|||Mary,
To me it sounds like your database design is a bit odd.
You have the details of a student(?) in TableA, and the grade that the
student got in TableB. Presumably if you actually have:
TableA (students): id, name1, name2, notes...
TableC (subjects): id, code, description...
TableB (grades): studentid, subjectid, grade
then you could import your data into a temporary table... and then make sure
that all your subjects and students are listed:
insert into TableA (name1, ...)
select t.name1, ...
from TableTemp t
where not exists (select * from TableA a where a.name1 = t.name1 and a.name2
= t.name2 and ...)
(and similar for TableC)
And then insert the grades:
insert into TableC (studentid, subjectid, grade)
select stud.id, subj.id, t.grade
from TableTemp t JOIN TableA stud on stud.name1 = t.name1 and stud.name2...
JOIN TableC subj on subj.code = t.code ...
The '...' are the other fields and stuff that you're looking to identify
students by.
Of course, it's much nicer to have a separate file of "studentcode",
"subjectcode", "grade", if you can fetch the details like that.
Rob|||Thanx.
It seems working..Thank you very much

Looking for Wayne Snyder

Wayne,
Taking LearnKey course SQL 2000 Implementing DB Design. In session 8 you
use a file called "bulk insert package.dts", I have looked for the script on
all of the LearnKey downloads and the CD and can not find it. Is there any
place I can get it?
Actually I was able to recreate the bulk insert package, but the rest of the
*.dts files are missing, it would be real helpful to have then to go along
with the course.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:eD%23xNR7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Wayne,
> Taking LearnKey course SQL 2000 Implementing DB Design. In session 8
> you use a file called "bulk insert package.dts", I have looked for the
> script on all of the LearnKey downloads and the CD and can not find it. Is
> there any place I can get it?
>
|||hi
got his email
visit this site
http://www.solidqualitylearning.com/...AboutWayne.htm
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"DazedAndConfused" wrote:

> Actually I was able to recreate the bulk insert package, but the rest of the
> *.dts files are missing, it would be real helpful to have then to go along
> with the course.
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:eD%23xNR7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
>
>

Looking for Wayne Snyder

Wayne,
Taking LearnKey course SQL 2000 Implementing DB Design. In session 8 you
use a file called "bulk insert package.dts", I have looked for the script on
all of the LearnKey downloads and the CD and can not find it. Is there any
place I can get it?Actually I was able to recreate the bulk insert package, but the rest of the
*.dts files are missing, it would be real helpful to have then to go along
with the course.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:eD%23xNR7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Wayne,
> Taking LearnKey course SQL 2000 Implementing DB Design. In session 8
> you use a file called "bulk insert package.dts", I have looked for the
> script on all of the LearnKey downloads and the CD and can not find it. Is
> there any place I can get it?
>|||hi
got his email
visit this site
http://www.solidqualitylearning.com/Resumes/Wayne/AboutWayne.htm
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"DazedAndConfused" wrote:
> Actually I was able to recreate the bulk insert package, but the rest of the
> *.dts files are missing, it would be real helpful to have then to go along
> with the course.
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:eD%23xNR7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
> > Wayne,
> >
> > Taking LearnKey course SQL 2000 Implementing DB Design. In session 8
> > you use a file called "bulk insert package.dts", I have looked for the
> > script on all of the LearnKey downloads and the CD and can not find it. Is
> > there any place I can get it?
> >
>
>

Looking for Wayne Snyder

Wayne,
Taking LearnKey course SQL 2000 Implementing DB Design. In session 8 you
use a file called "bulk insert package.dts", I have looked for the script on
all of the LearnKey downloads and the CD and can not find it. Is there any
place I can get it?Actually I was able to recreate the bulk insert package, but the rest of the
*.dts files are missing, it would be real helpful to have then to go along
with the course.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:eD%23xNR7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Wayne,
> Taking LearnKey course SQL 2000 Implementing DB Design. In session 8
> you use a file called "bulk insert package.dts", I have looked for the
> script on all of the LearnKey downloads and the CD and can not find it. Is
> there any place I can get it?
>|||hi
got his email
visit this site
http://www.solidqualitylearning.com.../AboutWayne.htm
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"DazedAndConfused" wrote:

> Actually I was able to recreate the bulk insert package, but the rest of t
he
> *.dts files are missing, it would be real helpful to have then to go along
> with the course.
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:eD%23xNR7sFHA.2592@.TK2MSFTNGP09.phx.gbl...
>
>