Combining records/Foreach Loop

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

cboom wrote:

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

Won't the following work:

UPDATE h

SET h.DentalConcern = c.MaxDentalConcern,

h.VisionConcern = c.MaxVisionConcern,

c.HearingConcern = c.MaxHearingConcern

FROM Health h

INNER JOIN (

SELECT ChildID,

CAST(MAX(CAST(DentalConcern as tinyint)) AS bit) as MaxDentalConcern,

CAST(MAX(CAST(VisionConcern as tinyint)) AS bit) as MaxVisionConcern,

CAST(MAX(CAST(HearingConcern as tinyint)) AS bit) as MaxHearingConcern,

FROM concerns

GROUP BY ChildID

) c

ON h.ChildID = c.ChildID

?

-Jamie

|||

Well, back to basic Transact-SQL for me. Did play with doing Max on the boolean fields which obviously didn't work, and didn't even think to Cast to integer. Many, many thanks.

Chera

Collation problem after migration from 2000 to 2005

Hi. Wondered if I might get a bit of advice on a minor collation problem I've experienced after migrating a database form SQL Server 2000 to 2005?

Users reported an error appearing in pages in a web-based application and I traced the problem to a stored procedure that was using a temporary table. I retained the original collation - SQL_Latin1_General_CP1_CI_AS - after migration and the error showing up was 'Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.' This makes sense as I guess temporary tables are assuming tempdb's collation. Resolution in this case was fairly simple, which was to apply an explicit collation to one of the columns in the temporary table (it only had three columns, only one requiring the collation). However the longer-term question is should I: (a) resolve similar issues in the same way as and when they arise; (b) change the collation of the migrated database to that of the default for system and new databases under 2005 - i.e. Latin1_General_CI_AS; or (b) change the collation of tempdb and the other system databases to match that of the migrated databases (sounds risky)?

I would probably go for (a) or perhaps (b) but could use some expert advise on the matter.

Regards,

YaHozna.I would try to avoid working with different collations on your server/db/objects unless absolutely necessary. One option is to script your database and all objects contained within it without specifying the collation, run the script on your 2005 server so your db and all objects pick up that servers default collation, then export the data from the 2000 db into the 2005 db. Once complete, you should be all sync'd up.|||Thanks DBriles10. Is this something I can do post-migration? If so what would be the desired order of events? I'm assuming something like this:

1. script the database in its new location under 2005 without collation
2. back up and detach the database
3. run the script to create a new database

I was going to say next, restore the original database under a different name in order to be able to import data from it to the new database however wouldn't that cause conflicts with object names? I guess restoring the original database to a different server to do the data import. Any thoughts?

Regards,

YaHozna.|||Your plan looks good to me. The restore of the database as a new name will not cause conflicts with object names.

If we call the existing DB with the bad collation "FixMe", you will...

1. Script "FixMe" and be sure to not include collation settings
2. Backup and detach "FixMe" >> "FixMe" no longer exists on server
3. Run the script from step 1 to recreate "FixMe" >> "FixMe" now exists with the server's default collation settings
4. Restore database from step 2 as "FixMe_Import" >> No object name conflicts as the objects reside in separate DB's and therefore are unaware of each other.
5. Import data from "FixMe_Import" to "FixMe"

For the import, I suggest setting all contraints to NoCheck, disabling all triggers and be aware of which tables have identities so you can turn identity_insert on.

Good luck.|||Many thanks for the input.

Regards,

YaHozna.

Collation Problem - Migration 7.0 to 2000

Hi,
I have a SQL7 user database backup that I load into the
SQL2000 without problem. When the restore operation is
finished I got the database loaded with
Compatibility_52_409_300003 COLLATION.
How could I change the COLLATION set for that database?
I am trying to use this following command
alter database Glb
COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
the on-line book but I got this message
Line 1: Incorrect syntax
near 'SQL_Latin1_General_CP1_CI_AS'.
In fact, I am trying to do this to have my database with
the same tempdb COLLATION to avoid further problem
like "Cannot resolve collation conflict for equal to
operation."This is a multi-part message in MIME format.
--=_NextPart_000_0101_01C35748.686076C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Leave out the quotes:
alter database Glb COLLATION SQL_Latin1_General_CP1_CI_AS
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message =news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
Hi,
I have a SQL7 user database backup that I load into the SQL2000 without problem. When the restore operation is finished I got the database loaded with Compatibility_52_409_300003 COLLATION.
How could I change the COLLATION set for that database?
I am trying to use this following command alter database Glb COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in the on-line book but I got this message
Line 1: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
In fact, I am trying to do this to have my database with the same tempdb COLLATION to avoid further problem like "Cannot resolve collation conflict for equal to operation."
--=_NextPart_000_0101_01C35748.686076C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Leave out the quotes:
alter database Glb =COLLATION SQL_Latin1_General_CP1_CI_AS
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Marcelo Sponchiado" wrote =in message news:0f3101c35768$1e=efe780$a101280a@.phx.gbl...Hi,I have a SQL7 user database backup that I load into the SQL2000 =without problem. When the restore operation is finished I got the database =loaded with Compatibility_52_409_300003 COLLATION.How could I =change the COLLATION set for that database?I am trying to use this following =command alter database Glb COLLATION 'SQL_Latin1_General_CP1_CI_AS' as =described in the on-line book but I got this messageLine 1: Incorrect =syntax near 'SQL_Latin1_General_CP1_CI_AS'.In fact, I am trying to =do this to have my database with the same tempdb COLLATION to avoid further =problem like "Cannot resolve collation conflict for equal to operation."

---

--=_NextPart_000_0101_01C35748.686076C0--|||Thanks Tom
I got the same error without the quotes. I tried in the
ISQL too and put the database in the restricted use...
and nothing.
Thanks in advance and if you have another suggestion,
please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb
>COLLATION SQL_Latin1_General_CP1_CI_AS
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in
message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the
>SQL2000 without problem. When the restore operation is
>finished I got the database loaded with
>Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command
>alter database Glb
>COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
>the on-line book but I got this message
>Line 1: Incorrect syntax
>near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with
>the same tempdb COLLATION to avoid further problem
>like "Cannot resolve collation conflict for equal to
>operation."
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0172_01C3574D.102E6480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just curious, what is the database compatibility level? Have you =changed it to 80? Check out sp_dbcmptlevel in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message =news:0e7301c3576d$ae023d60$a301280a@.phx.gbl...
Thanks Tom
I got the same error without the quotes. I tried in the ISQL too and put the database in the restricted use... and nothing.
Thanks in advance and if you have another suggestion, please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb >COLLATION SQL_Latin1_General_CP1_CI_AS
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the >SQL2000 without problem. When the restore operation is >finished I got the database loaded with >Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command >alter database Glb >COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in >the on-line book but I got this message
>Line 1: Incorrect syntax >near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with >the same tempdb COLLATION to avoid further problem >like "Cannot resolve collation conflict for equal to >operation."
>
>
--=_NextPart_000_0172_01C3574D.102E6480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Just curious, what is the database =compatibility level? Have you changed it to 80? Check out sp_dbcmptlevel =in the BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Marcelo Sponchiado" wrote =in message news:0e7301c3576d$ae=023d60$a301280a@.phx.gbl...Thanks TomI got the same error without the quotes. I tried in the =ISQL too and put the database in the restricted use... and =nothing.Thanks in advance and if you have another suggestion, please send it.>--Original Message-->Leave out the quotes:>>alter database Glb >COLLATION SQL_Latin1_General_CP1_CI_AS>>-- >Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Marc=elo Sponchiado" wrote =in message news:0f3101c35768$1e=efe780$a101280a@.phx.gbl...>Hi,>>I have a SQL7 user database backup that I load into the >SQL2000 =without problem. When the restore operation is >finished I got the =database loaded with >Compatibility_52_409_300003 =COLLATION.>>How could I change the COLLATION set for that database?>I am trying =to use this following command >alter database Glb >COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in >the on-line book =but I got this message>Line 1: Incorrect syntax >near 'SQL_Latin1_General_CP1_CI_AS'.>>In fact, I am trying to =do this to have my database with >the same tempdb COLLATION to avoid =further problem >like "Cannot resolve collation conflict for equal to >operation.">>>

---

--=_NextPart_000_0172_01C3574D.102E6480--|||Unfortunately, changing the database's default collation won't change the
collation of any existing tables or columns; it only affects new tables
that are created in that database. In other words, it won't have any
effect on your collation conflict errors.
It is not trivial to change the collation of all the columns in an existing
database. If you have the option, probably the easiest solution by far is
to modify your stored procs slightly so that they use "COLLATE
database_default" when create temp tables. This will tell SQL that the
temp table's collation should be inherited from the current user database,
not tempdb. From that point forward your code will be "server
collation-agnostic", meaning you can move this database to any server with
any collation and the code will run fine. An example:
CREATE #temptbl (
col1 int,
col2 varchar(20) COLLATE database_default,
col3 nchar(4) COLLATE database_default
)
HTH,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: "Tom Moreau" <tom@.dont.spam.me.cips.ca>
References: <0f3101c35768$1eefe780$a101280a@.phx.gbl>
<#J4n2n2VDHA.392@.TK2MSFTNGP11.phx.gbl>
<0e7301c3576d$ae023d60$a301280a@.phx.gbl>
Subject: Re: Collation Problem - Migration 7.0 to 2000
Date: Thu, 31 Jul 2003 10:18:19 -0400
Lines: 154
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="--=_NextPart_000_0172_01C3574D.102E6480"
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4927.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200
Message-ID: <e78Jk62VDHA.3220@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: 142.108.149.181
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:298816
X-Tomcat-NG: microsoft.public.sqlserver.server
Just curious, what is the database compatibility level? Have you changed
it to 80? Check out sp_dbcmptlevel in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message
news:0e7301c3576d$ae023d60$a301280a@.phx.gbl...
Thanks Tom
I got the same error without the quotes. I tried in the
ISQL too and put the database in the restricted use...
and nothing.
Thanks in advance and if you have another suggestion,
please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb
>COLLATION SQL_Latin1_General_CP1_CI_AS
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in
message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the
>SQL2000 without problem. When the restore operation is
>finished I got the database loaded with
>Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command
>alter database Glb
>COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
>the on-line book but I got this message
>Line 1: Incorrect syntax
>near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with
>the same tempdb COLLATION to avoid further problem
>like "Cannot resolve collation conflict for equal to
>operation."
>
>