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

Lookup - no matched records

Hi All,

I have two tables:TableA and TableB, both of the two tables have two fields: c_IDA char(10) and c_IDB char(10); A import text file includes the ID data, the data will insert into TableB only when the ID existed in TableA

The line in the import text file like this:

00000023450000012345

in the text file: 1-10 is the c_IDA and 11-20 is the c_IDB

In the Derived Column transformation,

set the column name IDA as expression: SUBSTRING(LINE,1,10)

set the column name IDB as expression: SUBSTRING(LINE,11,10)

In the followed Lookup transfornation, I created the reference table with a sql: Select c_IDA,c_IDB from TableA, the IDA and IDB in the pipeline linked to the reference table's c_IDA and c_IDB, i then setup the error output to another log file.

The problem is even though the ID existed in the TableA, the Lookup always generate the error out put, that means the ID not been found in the TableA at all.

In the sample above, if i run the sql in the SSMS:

Select * from TableA where c_IDA = '0000002345' and c_IDB = '0000012345'

there is one record retrived.

Any idea?

TIA

Are you casting the Derived Column to a DT_STR to match your char(10), or is it still set to DT_WSTR?

|||

I am using DT_STR

Thanks

|||

Have you used a data viewer immediately before the Lookup to validate that the values are what you expect?

Another potential issue is the collation settings on the database. Do you know what they are?

|||

Thanks again.

I put the data viewer before the Lookup and the data are displayed correctly. I am using the digit to present string, is it related with collation?

|||Collation affects sorting. It's one of the setting made when the SQL Server is set up, and under 2005, it can be configured for each database individually. It affects string comparisons, in some cases.

|||

But why I am able to retrive the correct records via Select in SSMS?

jwelch wrote:

Collation affects sorting. It's one of the setting made when the SQL Server is set up, and under 2005, it can be configured for each database individually. It affects string comparisons, in some cases.

|||

Because there the entire string is being interpeted by the database engine, versus in SSIS where some data is coming in from a flat file, and the other data is retrieved from the database engine.

That being said, I don't know for certain that this is your problem, but it is something to check.

|||

Whe i use Select SERVERPROPERTY(N'Collation'), it returns: Latin1_General_CI_AS

That means:

Case-insensitive, accent-sensitive, kana-insensitive, width-insensitive

is that?

jwelch wrote:

Because there the entire string is being interpeted by the database engine, versus in SSIS where some data is coming in from a flat file, and the other data is retrieved from the database engine.

That being said, I don't know for certain that this is your problem, but it is something to check.

|||

That one is unlikely to be causing this problem.