Losing Temp Tables Between Tasks

I have a control flow setup with 5 tasks.

1. Create Temp Table (Execute SQL)
2. Load Temp Table (Execute SQL)
3. Data Flow Task using the temp table
4. Data Flow Task using the temp table
5. Drop Temp Table (Execute SQL)

Tasks 3 & 4 are currently setup to run parallel/concurrently - meaning they both branch directly off of task #2. They both also lead to task #5. Also, my connection manager is set to retain its connection.

Here's the problem. When executing this flow, task #3 will sometimes fail, other times it will succeed - without me changing anything between runs (I simply end debugging and run it again). When it does fail it is always because it cannot find the temp table. One thing to note is that task #4 (running at the same time as #3 and using the same temp table) always finishes first because it does quite a bit less work. Last thing to note: if I set up task 4 to run after 3 it works everytime, but of course it takes much longer for the total package to execute.

How is it that the task can find the temp table on some runs, but not on others? If this is just quirky behavior that I am going to have to live with for the time being, is there a way to force a task to re-run X number of times before giving up and moving on?

Thanks,
David Martin

Temp tables are only guaranteed to remain in existence as long as they are referenced. Is it possible that the connection is being dropped and reopened by SSIS for some reason between tasks?

Or that there is some time during the processing where Sql Server cannot detect the temp table is still being referrenced, so drops it even if the connection is still open?

Seems like using temp tables is risky if you don't have full and explicit control over their existence. I want to use them also but not if I cannot guarantee they will be there when I need them!

|||

Make sure the "RetainSameConnection" property is set to "True" for the connection. This will allow the Execute SQL tasks to share the connection so the temp table can be accessed.

Frank

sql

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

Go

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

Go

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

sql

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

Go

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

Looping through temp table to produce final result set

I have a temp table that is populated elsewhere in the stored proc:
@.table
(
userid,
spec1,
spec2
)
Spec1 = specialization 1, Spec2 = specialization2
Each user has two specializations, and a number of associated skills
for that specialization in a table.
now I need to loop through this table, pulling out only one userid at
a time, for my final query that displays a bunch of other things. I
have a UDF that concatenates the string of values from the different
rows from the skill table so that it displays the skills as one list:
Specialization: DBA
Skills: SQL, Data Migration, etc.
How do I loop through the @.table to pull out only one userid at a
time, then do the insert to another @.secondtable, then loop back
through to get another userid'
StacyOn Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:
>I have a temp table that is populated elsewhere in the stored proc:
>@.table
> (
> userid,
> spec1,
> spec2
> )
>
>Spec1 = specialization 1, Spec2 = specialization2
>Each user has two specializations, and a number of associated skills
>for that specialization in a table.
>now I need to loop through this table, pulling out only one userid at
>a time, for my final query that displays a bunch of other things. I
>have a UDF that concatenates the string of values from the different
>rows from the skill table so that it displays the skills as one list:
>Specialization: DBA
>Skills: SQL, Data Migration, etc.
>How do I loop through the @.table to pull out only one userid at a
>time, then do the insert to another @.secondtable, then loop back
>through to get another userid'
>Stacy
Hi Stacy,
Why would you want to loop?
INSERT INTO SecondTable (UserID, ConcatSkillList)
SELECT UserID, dbo.MyUDF(UserID)
FROM FirstTable;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jun 1, 2:31 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:
> >I have a temp table that is populated elsewhere in the stored proc:
> >@.table
> > (
> > userid,
> > spec1,
> > spec2
> > )
> >Spec1 = specialization 1, Spec2 = specialization2
> >Each user has two specializations, and a number of associated skills
> >for that specialization in a table.
> >now I need to loop through this table, pulling out only one userid at
> >a time, for my final query that displays a bunch of other things. I
> >have a UDF that concatenates the string of values from the different
> >rows from the skill table so that it displays the skills as one list:
> >Specialization: DBA
> >Skills: SQL, Data Migration, etc.
> >How do I loop through the @.table to pull out only one userid at a
> >time, then do the insert to another @.secondtable, then loop back
> >through to get another userid'
> >Stacy
> Hi Stacy,
> Why would you want to loop?
> INSERT INTO SecondTable (UserID, ConcatSkillList)
> SELECT UserID, dbo.MyUDF(UserID)
> FROM FirstTable;
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> - Show quoted text -
I need to loop because the first table holds just the specializations,
and I need to concatenate the x number of skills, my UDF can only take
one userid at a time...
CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @.SkillList varchar(1000)
SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
skill.skillname + ' (' + picklist.title + ')'
FROM candidate_skillsmatrix
inner join skill on skill.skillid = candidate_skillsmatrix.skillid
inner join specialization on specialization.specializationid =candidate_skillsmatrix.specid
inner join picklist on picklist.picklistid =candidate_skillsmatrix.expid
WHERE candidate_skillsmatrix.userid = @.UserID
and candidate_skillsmatrix.specid = @.specid
order by isrole desc, picklist.var3 desc,skillname
RETURN @.SkillList
END
This turns this:
userid1, spec 1, skill1
userid1, spec1, skill2
userid1, spec1, skill 3
userid2, spec1, skill1
userid2, spec1, skill2
into this:
userid 1, 'skill1, skill2, skill3'
when spec1 = @.spec1
and userid = @.userid
it works great when I've got only one userid, but I'm being given a
comma delimited list of values...|||On Fri, 01 Jun 2007 13:53:38 -0700, CalgaryDataGrl wrote:
>On Jun 1, 2:31 pm, Hugo Kornelis
><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
(snip)
>> Why would you want to loop?
(snip)
>I need to loop because the first table holds just the specializations,
>and I need to concatenate the x number of skills, my UDF can only take
>one userid at a time...
Hi Stacy,
I still think that you don't need to loop. Looping is only very rarely
the correct solution in SQL Server. If you include a call to the UDF in
a query, the query engine will make sure that the query gets called as
often as needed.
However, for further help I reallly need to know how your tables and
data look. You have given some descriptions in English, but description
is real SQL code are much less prone to communication errors and
misunderstanding. Could you please post CREATE TABLE statements for all
tables used for this problem (irrelevant columns may be omitted, but
please do include all constraints, indexesm and properties!), some
INSERT statements with sample data, along with expected output? That
makes it much easier to help you.
>CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
>RETURNS VARCHAR(1000) AS
>BEGIN
> DECLARE @.SkillList varchar(1000)
> SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
>skill.skillname + ' (' + picklist.title + ')'
> FROM candidate_skillsmatrix
> inner join skill on skill.skillid = candidate_skillsmatrix.skillid
> inner join specialization on specialization.specializationid =>candidate_skillsmatrix.specid
> inner join picklist on picklist.picklistid =>candidate_skillsmatrix.expid
> WHERE candidate_skillsmatrix.userid = @.UserID
> and candidate_skillsmatrix.specid = @.specid
>order by isrole desc, picklist.var3 desc,skillname
> RETURN @.SkillList
>END
Just for the record - this method of string concatenation, though widely
used, is not documented, nor is it guaranteed to allways produce the
expected results. If you're on SQL Server 2005, you can use a trick with
the FOR XML options to concatenate the data (Google for it - let me know
if you fail to find it). In SQL Server 2000 and older versions, you have
no other choice but to use a cursor to loop over the rows to concatenate
them.
That is the main reason that most SQL Server experts will advice you to
do string concatenation on the front end instead of in the database.
>it works great when I've got only one userid, but I'm being given a
>comma delimited list of values...
As input? Containing the userids that you need to select? In that case,
you'll also need to read Erlands article:
http://www.sommarskog.se/arrays-in-sql.html
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 1 Jun, 21:53, CalgaryDataGrl <calgarydata...@.gmail.com> wrote:
> I need to loop because the first table holds just the specializations,
> and I need to concatenate the x number of skills, my UDF can only take
> one userid at a time...
>
That doesn't mean you need to loop. You just need to think about a
more set-based approach. BTW the method of concatenation you have used
in your proc is unsupported and unreliable. I don't recommend it.
Please post DDL, sample data and expected results if you need more
help. Also tell us what version of SQL Server you are using. Don't
expect a loop to be in the answer you get! :)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.4ax.com...
> You have given some descriptions in English, but description
> is real SQL code are much less prone to communication errors and
> misunderstanding.
Hello Hugo,
An industry that expresses itself with at most grade school clarity
and you choose to reinforce it. This is why users get things to work
without the slightest understanding of what they are doing or why
things work. This is why I have called sql 'faith based'. Understanding
is replaced by faith. The ridiculous claim that sql is a religion
gains credence. Mentors should not be high priests. And this is why
I argue for application development based on science, relational.
best,
steve|||On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> I argue for application development based on science, relational.
>
Naturally I agree:
http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
Meantime SQL is what we have so there's nothing wrong with what Hugo
said.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegroups.com...
> On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
>> I argue for application development based on science, relational.
> Naturally I agree:
> http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
'Once or twice I have floated the idea that a new relational database
language should be added to SQL Server as an alternative to the T-SQL
language. In my opinion the time is right for it.
What I'm talking about would be a relational language that lives up
to Date and Darwen's description of a "D" language.'
The 'D' language is already here! It is what Alphora has implemented
in Dataphor. Sql server can be used as the data repository and you
can freely communicate with it via pass-thru queries. The result
of such a query will be treated just like any other table variable
in D(4). If you understand query chunking you already know tactily how
the Dataphor server will work with Sql Server. The idea is to get the
performance of sql server and work with the logic of D.
Why wait for tomorrow when it is here today? -:)
www.alphora.com|||On Sat, 2 Jun 2007 16:25:54 -0700, Steve Dassin wrote:
>"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
>news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.4ax.com...
>> You have given some descriptions in English, but description
>> is real SQL code are much less prone to communication errors and
>> misunderstanding.
>Hello Hugo,
>An industry that expresses itself with at most grade school clarity
>and you choose to reinforce it.
Hi Steve,
That's nonsense. Stacy posts a problem, and I choose to ask her to
restate it in an unambiguous language. And since both she and I use SQL
Server as our DB platform, T-SQL is the obvious choice of unambiguous
language.
Anything that you *think* I reinforce with that, is solely a figment of
your imagination. Maybe you should go back to just advertising Rac4Sql?
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:tqj6635t8lo4dqdsr670n4jpgcl8mc2cht@.4ax.com...
>.
> Anything that you *think* I reinforce with that, is solely a figment of
> your imagination. Maybe you should go back to just advertising Rac4Sql?
>
You are an big advocate of sql and an expert. I am an sql
critic from a very clear point of view. That makes us adversaries.
Not children. Deal with it.
Here is a figment of mine that might serve as a reality check -:)
http://beyondsql.blogspot.com/2007/06/dataphor-string-differences-operator.html|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegroups.com...
>.
> http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
>.
'Naturally the new language would imply a new data model as well - the
relational data model instead of the SQL one - but I don't think it
would be difficult to build it on top of SQL Server's existing engine.'
Now I see where your coming from.
All your expertise, education and insight is secondary to your
allegience to MS. So much for your intellectual integrity, you
are, in the end, just another MS fanboy. MS see, David do.
Hypocrite.|||On 5 Jun, 21:01, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> 'Naturally the new language would imply a new data model as well - the
> relational data model instead of the SQL one - but I don't think it
> would be difficult to build it on top of SQL Server's existing engine.'
> Now I see where your coming from.
> All your expertise, education and insight is secondary to your
> allegience to MS. So much for your intellectual integrity, you
> are, in the end, just another MS fanboy. MS see, David do.
> Hypocrite.
Nothing of the kind. I'm suggesting that Microsoft improve their
product by remedying some of its defficiencies. The hypocracy is
committed by those who advocate another commercial product while
accusing others who propose a Microsoft-based solution of lacking
intellectual integrity. My opinion of you just hit rock bottom I'm
afraid.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Looping through temp table to produce final result set

I have a temp table that is populated elsewhere in the stored proc:
@.table
(
userid,
spec1,
spec2
)
Spec1 = specialization 1, Spec2 = specialization2
Each user has two specializations, and a number of associated skills
for that specialization in a table.
now I need to loop through this table, pulling out only one userid at
a time, for my final query that displays a bunch of other things. I
have a UDF that concatenates the string of values from the different
rows from the skill table so that it displays the skills as one list:
Specialization: DBA
Skills: SQL, Data Migration, etc.
How do I loop through the @.table to pull out only one userid at a
time, then do the insert to another @.secondtable, then loop back
through to get another userid?
Stacy
On Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:

>I have a temp table that is populated elsewhere in the stored proc:
>@.table
>(
> userid,
>spec1,
>spec2
> )
>
>Spec1 = specialization 1, Spec2 = specialization2
>Each user has two specializations, and a number of associated skills
>for that specialization in a table.
>now I need to loop through this table, pulling out only one userid at
>a time, for my final query that displays a bunch of other things. I
>have a UDF that concatenates the string of values from the different
>rows from the skill table so that it displays the skills as one list:
>Specialization: DBA
>Skills: SQL, Data Migration, etc.
>How do I loop through the @.table to pull out only one userid at a
>time, then do the insert to another @.secondtable, then loop back
>through to get another userid?
>Stacy
Hi Stacy,
Why would you want to loop?
INSERT INTO SecondTable (UserID, ConcatSkillList)
SELECT UserID, dbo.MyUDF(UserID)
FROM FirstTable;
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Jun 1, 2:31 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:
>
>
>
> Hi Stacy,
> Why would you want to loop?
> INSERT INTO SecondTable (UserID, ConcatSkillList)
> SELECT UserID, dbo.MyUDF(UserID)
> FROM FirstTable;
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> - Show quoted text -
I need to loop because the first table holds just the specializations,
and I need to concatenate the x number of skills, my UDF can only take
one userid at a time...
CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @.SkillList varchar(1000)
SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
skill.skillname + ' (' + picklist.title + ')'
FROM candidate_skillsmatrix
inner join skill on skill.skillid = candidate_skillsmatrix.skillid
inner join specialization on specialization.specializationid =
candidate_skillsmatrix.specid
inner join picklist on picklist.picklistid =
candidate_skillsmatrix.expid
WHERE candidate_skillsmatrix.userid = @.UserID
andcandidate_skillsmatrix.specid = @.specid
order by isrole desc, picklist.var3 desc,skillname
RETURN @.SkillList
END
This turns this:
userid1, spec 1, skill1
userid1, spec1, skill2
userid1, spec1, skill 3
userid2, spec1, skill1
userid2, spec1, skill2
into this:
userid 1, 'skill1, skill2, skill3'
when spec1 = @.spec1
and userid = @.userid
it works great when I've got only one userid, but I'm being given a
comma delimited list of values...
|||On Fri, 01 Jun 2007 13:53:38 -0700, CalgaryDataGrl wrote:

>On Jun 1, 2:31 pm, Hugo Kornelis
><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
(snip)[vbcol=seagreen]
(snip)
>I need to loop because the first table holds just the specializations,
>and I need to concatenate the x number of skills, my UDF can only take
>one userid at a time...
Hi Stacy,
I still think that you don't need to loop. Looping is only very rarely
the correct solution in SQL Server. If you include a call to the UDF in
a query, the query engine will make sure that the query gets called as
often as needed.
However, for further help I reallly need to know how your tables and
data look. You have given some descriptions in English, but description
is real SQL code are much less prone to communication errors and
misunderstanding. Could you please post CREATE TABLE statements for all
tables used for this problem (irrelevant columns may be omitted, but
please do include all constraints, indexesm and properties!), some
INSERT statements with sample data, along with expected output? That
makes it much easier to help you.

>CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
>RETURNS VARCHAR(1000) AS
>BEGIN
> DECLARE @.SkillList varchar(1000)
> SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
>skill.skillname + ' (' + picklist.title + ')'
> FROM candidate_skillsmatrix
>inner join skill on skill.skillid = candidate_skillsmatrix.skillid
>inner join specialization on specialization.specializationid =
>candidate_skillsmatrix.specid
>inner join picklist on picklist.picklistid =
>candidate_skillsmatrix.expid
> WHERE candidate_skillsmatrix.userid = @.UserID
>andcandidate_skillsmatrix.specid = @.specid
>order by isrole desc, picklist.var3 desc,skillname
> RETURN @.SkillList
>END
Just for the record - this method of string concatenation, though widely
used, is not documented, nor is it guaranteed to allways produce the
expected results. If you're on SQL Server 2005, you can use a trick with
the FOR XML options to concatenate the data (Google for it - let me know
if you fail to find it). In SQL Server 2000 and older versions, you have
no other choice but to use a cursor to loop over the rows to concatenate
them.
That is the main reason that most SQL Server experts will advice you to
do string concatenation on the front end instead of in the database.

>it works great when I've got only one userid, but I'm being given a
>comma delimited list of values...
As input? Containing the userids that you need to select? In that case,
you'll also need to read Erlands article:
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On 1 Jun, 21:53, CalgaryDataGrl <calgarydata...@.gmail.com> wrote:
> I need to loop because the first table holds just the specializations,
> and I need to concatenate the x number of skills, my UDF can only take
> one userid at a time...
>
That doesn't mean you need to loop. You just need to think about a
more set-based approach. BTW the method of concatenation you have used
in your proc is unsupported and unreliable. I don't recommend it.
Please post DDL, sample data and expected results if you need more
help. Also tell us what version of SQL Server you are using. Don't
expect a loop to be in the answer you get!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.4ax.com...
> You have given some descriptions in English, but description
> is real SQL code are much less prone to communication errors and
> misunderstanding.
Hello Hugo,
An industry that expresses itself with at most grade school clarity
and you choose to reinforce it. This is why users get things to work
without the slightest understanding of what they are doing or why
things work. This is why I have called sql 'faith based'. Understanding
is replaced by faith. The ridiculous claim that sql is a religion
gains credence. Mentors should not be high priests. And this is why
I argue for application development based on science, relational.
best,
steve
|||On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> I argue for application development based on science, relational.
>
Naturally I agree:
http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
Meantime SQL is what we have so there's nothing wrong with what Hugo
said.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegr oups.com...
> On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> Naturally I agree:
> http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
'Once or twice I have floated the idea that a new relational database
language should be added to SQL Server as an alternative to the T-SQL
language. In my opinion the time is right for it.
What I'm talking about would be a relational language that lives up
to Date and Darwen's description of a "D" language.'
The 'D' language is already here! It is what Alphora has implemented
in Dataphor. Sql server can be used as the data repository and you
can freely communicate with it via pass-thru queries. The result
of such a query will be treated just like any other table variable
in D(4). If you understand query chunking you already know tactily how
the Dataphor server will work with Sql Server. The idea is to get the
performance of sql server and work with the logic of D.
Why wait for tomorrow when it is here today? -
www.alphora.com
|||On Sat, 2 Jun 2007 16:25:54 -0700, Steve Dassin wrote:

>"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
>news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.4ax.com.. .
>Hello Hugo,
>An industry that expresses itself with at most grade school clarity
>and you choose to reinforce it.
Hi Steve,
That's nonsense. Stacy posts a problem, and I choose to ask her to
restate it in an unambiguous language. And since both she and I use SQL
Server as our DB platform, T-SQL is the obvious choice of unambiguous
language.
Anything that you *think* I reinforce with that, is solely a figment of
your imagination. Maybe you should go back to just advertising Rac4Sql?
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:tqj6635t8lo4dqdsr670n4jpgcl8mc2cht@.4ax.com...
>.
> Anything that you *think* I reinforce with that, is solely a figment of
> your imagination. Maybe you should go back to just advertising Rac4Sql?
>
You are an big advocate of sql and an expert. I am an sql
critic from a very clear point of view. That makes us adversaries.
Not children. Deal with it.
Here is a figment of mine that might serve as a reality check -
http://beyondsql.blogspot.com/2007/06/dataphor-string-differences-operator.html
sql

Looping through temp table to produce final result set

I have a temp table that is populated elsewhere in the stored proc:
@.table
(
userid,
spec1,
spec2
)
Spec1 = specialization 1, Spec2 = specialization2
Each user has two specializations, and a number of associated skills
for that specialization in a table.
now I need to loop through this table, pulling out only one userid at
a time, for my final query that displays a bunch of other things. I
have a UDF that concatenates the string of values from the different
rows from the skill table so that it displays the skills as one list:
Specialization: DBA
Skills: SQL, Data Migration, etc.
How do I loop through the @.table to pull out only one userid at a
time, then do the insert to another @.secondtable, then loop back
through to get another userid'
StacyOn Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:

>I have a temp table that is populated elsewhere in the stored proc:
>@.table
> (
> userid,
> spec1,
> spec2
> )
>
>Spec1 = specialization 1, Spec2 = specialization2
>Each user has two specializations, and a number of associated skills
>for that specialization in a table.
>now I need to loop through this table, pulling out only one userid at
>a time, for my final query that displays a bunch of other things. I
>have a UDF that concatenates the string of values from the different
>rows from the skill table so that it displays the skills as one list:
>Specialization: DBA
>Skills: SQL, Data Migration, etc.
>How do I loop through the @.table to pull out only one userid at a
>time, then do the insert to another @.secondtable, then loop back
>through to get another userid'
>Stacy
Hi Stacy,
Why would you want to loop?
INSERT INTO SecondTable (UserID, ConcatSkillList)
SELECT UserID, dbo.MyUDF(UserID)
FROM FirstTable;
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jun 1, 2:31 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:
>
>
>
>
>
>
> Hi Stacy,
> Why would you want to loop?
> INSERT INTO SecondTable (UserID, ConcatSkillList)
> SELECT UserID, dbo.MyUDF(UserID)
> FROM FirstTable;
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted tex
t -
> - Show quoted text -
I need to loop because the first table holds just the specializations,
and I need to concatenate the x number of skills, my UDF can only take
one userid at a time...
CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @.SkillList varchar(1000)
SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
skill.skillname + ' (' + picklist.title + ')'
FROM candidate_skillsmatrix
inner join skill on skill.skillid = candidate_skillsmatrix.skillid
inner join specialization on specialization.specializationid =
candidate_skillsmatrix.specid
inner join picklist on picklist.picklistid =
candidate_skillsmatrix.expid
WHERE candidate_skillsmatrix.userid = @.UserID
and candidate_skillsmatrix.specid = @.specid
order by isrole desc, picklist.var3 desc,skillname
RETURN @.SkillList
END
This turns this:
userid1, spec 1, skill1
userid1, spec1, skill2
userid1, spec1, skill 3
userid2, spec1, skill1
userid2, spec1, skill2
into this:
userid 1, 'skill1, skill2, skill3'
when spec1 = @.spec1
and userid = @.userid
it works great when I've got only one userid, but I'm being given a
comma delimited list of values...|||On Fri, 01 Jun 2007 13:53:38 -0700, CalgaryDataGrl wrote:

>On Jun 1, 2:31 pm, Hugo Kornelis
><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
(snip)
(snip)[vbcol=seagreen]
>I need to loop because the first table holds just the specializations,
>and I need to concatenate the x number of skills, my UDF can only take
>one userid at a time...
Hi Stacy,
I still think that you don't need to loop. Looping is only very rarely
the correct solution in SQL Server. If you include a call to the UDF in
a query, the query engine will make sure that the query gets called as
often as needed.
However, for further help I reallly need to know how your tables and
data look. You have given some descriptions in English, but description
is real SQL code are much less prone to communication errors and
misunderstanding. Could you please post CREATE TABLE statements for all
tables used for this problem (irrelevant columns may be omitted, but
please do include all constraints, indexesm and properties!), some
INSERT statements with sample data, along with expected output? That
makes it much easier to help you.

>CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
>RETURNS VARCHAR(1000) AS
>BEGIN
> DECLARE @.SkillList varchar(1000)
> SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
>skill.skillname + ' (' + picklist.title + ')'
> FROM candidate_skillsmatrix
> inner join skill on skill.skillid = candidate_skillsmatrix.skillid
> inner join specialization on specialization.specializationid =
>candidate_skillsmatrix.specid
> inner join picklist on picklist.picklistid =
>candidate_skillsmatrix.expid
> WHERE candidate_skillsmatrix.userid = @.UserID
> and candidate_skillsmatrix.specid = @.specid
>order by isrole desc, picklist.var3 desc,skillname
> RETURN @.SkillList
>END
Just for the record - this method of string concatenation, though widely
used, is not documented, nor is it guaranteed to allways produce the
expected results. If you're on SQL Server 2005, you can use a trick with
the FOR XML options to concatenate the data (Google for it - let me know
if you fail to find it). In SQL Server 2000 and older versions, you have
no other choice but to use a cursor to loop over the rows to concatenate
them.
That is the main reason that most SQL Server experts will advice you to
do string concatenation on the front end instead of in the database.

>it works great when I've got only one userid, but I'm being given a
>comma delimited list of values...
As input? Containing the userids that you need to select? In that case,
you'll also need to read Erlands article:
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 1 Jun, 21:53, CalgaryDataGrl <calgarydata...@.gmail.com> wrote:
> I need to loop because the first table holds just the specializations,
> and I need to concatenate the x number of skills, my UDF can only take
> one userid at a time...
>
That doesn't mean you need to loop. You just need to think about a
more set-based approach. BTW the method of concatenation you have used
in your proc is unsupported and unreliable. I don't recommend it.
Please post DDL, sample data and expected results if you need more
help. Also tell us what version of SQL Server you are using. Don't
expect a loop to be in the answer you get!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.
4ax.com...
> You have given some descriptions in English, but description
> is real SQL code are much less prone to communication errors and
> misunderstanding.
Hello Hugo,
An industry that expresses itself with at most grade school clarity
and you choose to reinforce it. This is why users get things to work
without the slightest understanding of what they are doing or why
things work. This is why I have called sql 'faith based'. Understanding
is replaced by faith. The ridiculous claim that sql is a religion
gains credence. Mentors should not be high priests. And this is why
I argue for application development based on science, relational.
best,
steve|||On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> I argue for application development based on science, relational.
>
Naturally I agree:
http://blogs.conchango.com/davidpor...ibrary/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegroups.com...[vbcol=seagreen]
> On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> Naturally I agree:
> http://blogs.conchango.com/davidpor...al-Futures.aspx[
/vbcol]
'Once or twice I have floated the idea that a new relational database
language should be added to SQL Server as an alternative to the T-SQL
language. In my opinion the time is right for it.
What I'm talking about would be a relational language that lives up
to Date and Darwen's description of a "D" language.'
The 'D' language is already here! It is what Alphora has implemented
in Dataphor. Sql server can be used as the data repository and you
can freely communicate with it via pass-thru queries. The result
of such a query will be treated just like any other table variable
in D(4). If you understand query chunking you already know tactily how
the Dataphor server will work with Sql Server. The idea is to get the
performance of sql server and work with the logic of D.
Why wait for tomorrow when it is here today? -
www.alphora.com|||On Sat, 2 Jun 2007 16:25:54 -0700, Steve Dassin wrote:

>"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.
4ax.com...
>Hello Hugo,
>An industry that expresses itself with at most grade school clarity
>and you choose to reinforce it.
Hi Steve,
That's nonsense. Stacy posts a problem, and I choose to ask her to
restate it in an unambiguous language. And since both she and I use SQL
Server as our DB platform, T-SQL is the obvious choice of unambiguous
language.
Anything that you *think* I reinforce with that, is solely a figment of
your imagination. Maybe you should go back to just advertising Rac4Sql?
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:tqj6635t8lo4dqdsr670n4jpgcl8mc2cht@.
4ax.com...
>.
> Anything that you *think* I reinforce with that, is solely a figment of
> your imagination. Maybe you should go back to just advertising Rac4Sql?
>
You are an big advocate of sql and an expert. I am an sql
critic from a very clear point of view. That makes us adversaries.
Not children. Deal with it.
Here is a figment of mine that might serve as a reality check -
[url]http://beyondsql.blogspot.com/2007/06/dataphor-string-differences-operator.html[/u
rl]

Loop through a recordset to populate columns in a temp table

I want to take the contents from a table of appointments and insert the
appointments for any given month into a temp table where all the
appointments for each day are inserted into a single row with a column
for each day of the month.
Is there a simple way to do this?

I have a recordset that looks like:

SELECT
a.Date,
a.Client --contents: Joe, Frank, Fred, Pete, Oscar
FROM
dbo.tblAppointments a
WHERE
a.date between ...(first and last day of the selected month)

What I want to do is to create a temp table that has 31 columns
to hold appointments and insert into each column any appointments for
the date...

CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2
nvarchar(500), Day3 nvarchar(500), etc...)

Then loop through the recordset above to insert into Day1, Day 2, Day3,
etc. all the appointments for that day, with multiple appointments
separated by a comma.

INSERT INTO
#Appointments(Day1)
SELECT
a.Client
FROM
dbo.tblAppointments a
WHERE
a.date = (...first day of the month)

(LOOP to Day31)

The results would look like
Day1 Day2 Day3 ...
Row1 Joe, Pete
Frank,
Fred

Maybe there's an even better way to handle this sort of situation?
Thanks,
lqYou can use a query to do reports like this without looping or temp tables.
As specified it seems a little strange to list the dates horizontally since
there is apparently no data on the vertical axis but the rest is really just
a matter of formatting once you have the basic query. Formatting is best
done client-side rather than in the database.

DECLARE @.dt DATETIME
/* First date of the month */
SET @.dt = '20050501'

SELECT
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=0 THEN client END),
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=1 THEN client END),
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=2 THEN client END),
...
MAX(CASE WHEN DATEDIFF(DAY,@.dt,date)=30 THEN client END)
FROM tblAppointments
WHERE date >= @.dt
AND date < DATEADD(MONTH,1,@.dt)
GROUP BY client

--
David Portas
SQL Server MVP
--|||laurenq uantrell wrote:
> I want to take the contents from a table of appointments and insert the
> appointments for any given month into a temp table where all the
> appointments for each day are inserted into a single row with a column
> for each day of the month.
> Is there a simple way to do this?
> I have a recordset that looks like:
> SELECT
> a.Date,
> a.Client --contents: Joe, Frank, Fred, Pete, Oscar
> FROM
> dbo.tblAppointments a
> WHERE
> a.date between ...(first and last day of the selected month)
> What I want to do is to create a temp table that has 31 columns
> to hold appointments and insert into each column any appointments for
> the date...
> CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2
> nvarchar(500), Day3 nvarchar(500), etc...)
> Then loop through the recordset above to insert into Day1, Day 2, Day3,
> etc. all the appointments for that day, with multiple appointments
> separated by a comma.
> INSERT INTO
> #Appointments(Day1)
> SELECT
> a.Client
> FROM
> dbo.tblAppointments a
> WHERE
> a.date = (...first day of the month)
> (LOOP to Day31)
>
> The results would look like
> Day1 Day2 Day3 ...
> Row1 Joe, Pete
> Frank,
> Fred
> Maybe there's an even better way to handle this sort of situation?
> Thanks,
> lq

You're talking about crosstab queries. Here's a page of links that may
be of use:

http://www.google.com/custom?q=cros...ID%3A1%3B&hl=en

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I want to take the contents from a table of appointments and insert the
> appointments for any given month into a temp table where all the
> appointments for each day are inserted into a single row with a column
> for each day of the month.
> Is there a simple way to do this?
>...
> Then loop through the recordset above to insert into Day1, Day 2, Day3,
> etc. all the appointments for that day, with multiple appointments
> separated by a comma.

I'm a afraid that loop is what you will have to do. And write 31
UPDATE statements, one for each day of the month. There are ways to
build comma-separated lists with set-based statements, but the
methods used are unsupported and undefined, and cannot be trusted.
So the only way to build a CSV is to run a cursor.

OK, you don't really need 31 UPDATE statements. You could aggregate
data into a table with one per date, and then at then end run a
31-way self cross-join to produce the final result.

Certainly, a client program is much better apt to do this sort
of thing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||David, thanks for that. I thought it must be pretty straignt forward.
The reason I want it all in one row and the days data in columns is
that I want to populate a sincle record on a form that resembles a
monthly calendar but cwhich in fact is simply 31 text boxes. Previously
I had a form that populated 31 different subforms by looping through
the dates on the client side and it is too slow.THanks,
lq|||David, thanks for that. I thought it must be pretty straight forward.
The reason I want it all in one row and the days data in columns is
that I want to populate a sincle record on a form that resembles a
monthly calendar but cwhich in fact is simply 31 text boxes. Previously
I had a form that populated 31 different subforms by looping through
the dates on the client side and it is too slow.THanks,
lq|||Erland,
My solution is to run a UDFwithin a View that creates the comma
separated list for each date of appointments. That part works fine on a
single date, and now I'm just figuring out how to loop through the
dates: first day of the month + 31 days.
Thanks.
lq|||Erland,
You got me in the right direction and the solution works very fast:

The stored procedure:

@.ClienID int,
@.dt datetime /* first day of the selected month */

AS

DECLARE @.dtEnd datetime
SET @.dtEnd = DATEADD(DAY,-1,DATEADD(MONTH,1,@.dt)) /* last day of the
selected month */

SELECT
dbo.fn_ClientSked(@.dt, @.ClientID) AS D1,
dbo.fn_ClientSked(DATEADD(DAY,1,@.dt), @.ClientID) AS D2,
dbo.fn_ClientSked(DATEADD(DAY,2,@.dt), @.ClientID) AS D3,
etc...(for D4-D28)
CASE WHEN DATEADD(DAY,28,@.dt) <= @.dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,28,@.dt), @.ClientID) END AS D29,
CASE WHEN DATEADD(DAY,29,@.dt) <= @.dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,29,@.dt), @.ClientID) END AS D30,
CASE WHEN DATEADD(DAY,30,@.dt) <= @.dtEnd THEN
dbo.fn_ClientSked(DATEADD(DAY,30,@.dt), @.ClientID) END AS D31

The UDF:

CREATE function dbo.fn_ClientSked(@.dtX as DateTime, @.ClientID as int)
returns
nvarchar(500)
AS
begin
declare @.ret_value nvarchar(500)
SET @.ret_value=''
Select @.ret_value=@.ret_value + '; ' + AppointmentTitle
FROM dbo.tblAppointments
WHERE
tblAppointments.ClientID = @.ClientID
AND
@.dtX Between tblAppointments.StartDate AND tblAppointments.EndDate
RETURN CASE WHEN LEN(@.ret_value)>0 THEN
RIGHT(@.ret_value,LEN(@.ret_value)-2) ELSE '' END
end

Note: This particular UDF returns all appointments by day of the month
for the same client to populate a monthly calendar. It can be easily
modified to show appointments by employee, etc.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> You got me in the right direction and the solution works very fast:
>...
> declare @.ret_value nvarchar(500)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + '; ' + AppointmentTitle
> FROM dbo.tblAppointments
> WHERE
> tblAppointments.ClientID = @.ClientID
> AND
> @.dtX Between tblAppointments.StartDate AND tblAppointments.EndDate
> RETURN CASE WHEN LEN(@.ret_value)>0 THEN
> RIGHT(@.ret_value,LEN(@.ret_value)-2) ELSE '' END
> end

While it may work and be fast, it relies on undefined behaviour. See
http://support.microsoft.com/default.aspx?scid=287515.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Now you have me worried. Because I am using a UDF within the SELECT
statement? Isn't that what they're for?
I chose this solution because the other solutions I conceived required
31 server calls to populate 31 subforms (one for each day of the week.)
With the method above the data is all shoved down the pipe as one row.
Isn't that a preferable solution where a simple string output is all
that's required for each date?
lq|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Now you have me worried. Because I am using a UDF within the SELECT
> statement? Isn't that what they're for?

No, because of

Select @.ret_value=@.ret_value + '; ' + AppointmentTitle
FROM dbo.tblAppointments

This can give you want you want, or just one appointment. Or something
else. The result of this construct "concatentate aggregation" is not
defined.

If you want to play safe, write a cursor instead. (But you can still
do this in a UDF.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, thanks for the info. I hate messing with cursors...
lqsql

lookup values in another table

alright, I'm sure this is a simple solution, but I really don't know much about T-SQL. I have two tables, [temp], and [SectionChanges]. I am using SQL 2005 SP2. Here are the schemas:

Code Snippet

[dbo].[temp](

[Section] [smallint] NOT NULL,

[Mnemonic] [nvarchar](50) NOT NULL,

[Test Name] [nvarchar](200) NOT NULL,

[Date] [smalldatetime] NOT NULL,

[Patient Type] [smallint] NOT NULL,

[Nurse Unit] [nvarchar](20) NULL,

[Client#] [int] NOT NULL,

[Item_Qty] [smallint] NOT NULL,

[InputDate] [smalldatetime] NOT NULL,

[Bill_Item_ID] [int] NOT NULL,

[Charge_Item_ID] [int] NOT NULL

)

[dbo].[SectionChanges](

[Mnemonic] [nvarchar](50) NOT NULL,

[Test Name] [nvarchar](200) NOT NULL,

[Section] [smallint] NOT NULL

)

The data I want to use is:

Code Snippet

SELECT Mnemonic, [Test Name], Section, Bill_Item_ID
FROM temp
WHERE (Section = 0) OR
(Section >= 18)

I want to match the two tables on Mnemonic AND [Test Name] and then update the temp table with the value of section from SectionChanges

Thank you all.

Try this:

Code Snippet

UPDATE t

SET Section = sc.Section

FROM temp t

INNER JOIN SectionChanges sc

ON t.Mnemonic = sc.Mnemonic

AND t.[Test Name] = sc.[Test Name]

WHERE (Section = 0) OR

(Section >= 18)

|||Thanks much!|||

I'm sorry, it worked once, but now it isn't working any more. I now get the error:

Column or expression 'Section' cannot be updated.

This is the exact query I was using when it worked before

Code Snippet

UPDATE t
SET Section = sc.Section
FROM temp t
INNER JOIN SectionChanges sc
ON t.Mnemonic = sc.Mnemonic
AND t.[Test Name] = sc.[Test Name]
WHERE (t.Section = 0) OR
(t.Section >= 17)

|||

Have you changed anything (ie, table definition, etc.) since the first time you ran it?

No changes to the update code?

|||

no

|||alright I got it to work again but I'm baffled. The result changes depending on the query window i enter it. In Management studio, if i right click a table and click open table, then open up the query text and replace it with the update query, it gives that error. If i right click the table and goto script table as update and then paste the query, it works fine. It also doesnt work in a SQL task in SSIS.|||

I had to change the where clause to add the alias to the Section column name (I had just copied it from your code before)

but this works:

Code Snippet

create table [dbo].[temp](

[Section] [smallint] NOT NULL,

[Mnemonic] [nvarchar](50) NOT NULL,

[Test Name] [nvarchar](200) NOT NULL,

[Date] [smalldatetime] NOT NULL,

[Patient Type] [smallint] NOT NULL,

[Nurse Unit] [nvarchar](20) NULL,

[Client#] [int] NOT NULL,

[Item_Qty] [smallint] NOT NULL,

[InputDate] [smalldatetime] NOT NULL,

[Bill_Item_ID] [int] NOT NULL,

[Charge_Item_ID] [int] NOT NULL

)

create table [dbo].[SectionChanges](

[Mnemonic] [nvarchar](50) NOT NULL,

[Test Name] [nvarchar](200) NOT NULL,

[Section] [smallint] NOT NULL

)

insert into dbo.temp values (0, 'code1', 'test1', getdate(), 0, '1', 1, 1, getdate(), 0, 0)

insert into dbo.temp values (0, 'code2', 'test2', getdate(), 0, '1', 1, 1, getdate(), 0, 0)

insert into dbo.temp values (0, 'code3', 'test3', getdate(), 0, '1', 1, 1, getdate(), 0, 0)

insert into dbo.SectionChanges values('code1', 'test1', 100)

insert into dbo.SectionChanges values('code2', 'test2', 200)

insert into dbo.SectionChanges values('code3', 'test3', 300)

UPDATE t

SET Section = sc.Section

FROM temp t

INNER JOIN SectionChanges sc

ON t.Mnemonic = sc.Mnemonic

AND t.[Test Name] = sc.[Test Name]

WHERE (sc.Section = 0) OR

(sc.Section >= 18)

select *

from temp

|||

When I right clicked on [temp] and chose "Open Table" and then replaced the SQL with the update code, it gave me the same error that you received.

I noticed though that SSMS modified the script by inserting "CROSS APPLY t" before the WHERE clause.

Removing this cross apply gets rid of the problem.

(I've never used the SQL window on an OPEN TABLE to do anything like this, I usually use "New Query"...so I have no idea why SSMS decided to change what was pasted in the window.)

As for the SSIS, can you tell me what about it isn't working? Is there an error message? What all is going on in and around the SQL Task causing the problem. Please post anything can about it.

|||

Thank you very much for your help, I found out that SSIS was just a dumb mistake on my part. It's really strange that that query window gives different results and different errors. This was 1000 times more efficent than what I was trying to do before in SSIS.

Looking for some advice.....

I have a couple of files that I have that are comma seperated, and am looking for the best way to take those files, but them in a temp location to run some sql up against, and than update or insert a production sql database based on a SP i have written that takes a number of variable. I have played around with using the recordset destination and defining all the variables and than using a for each loop but seem to be stuck. Somewhat new to the whole programming field, and to be honest, seems a little intimidating with the little I know of the programming side. Just looking for some thoughts.You could use a data flow to read the flat file, and use an OLEDB Destination to save it to a "temp" table. Use an Execute SQL to update the temp table, then use a second data flow to retrieve the data and send it to an OLE DB Command to call your stored procedure.