master table and child table andfield concatenation and loop records ?

14 Jul 2006 8:39 PM

mdscorp

i have a main table as in:
tbl_main with field a,b,c,d,e
I have a child table as in tbl_child with fields a,b,c,d

I must go through tbl_main , then for each tbl_main record I must also
find child records related .
at the end I must create a string composed as follows:

tbl_main.a,tbl_main.b,tbl_child.c1,tbl_child.c2,tbl_child.c3,tbl_main.c,tbl_main.d

the tbl_child.c1 c2 and c3 are the children records related to the
main tbl_main record.

so final result should be something like:

tbl_main.a1,tbl_main.b1,tbl_child.c1,tbl_child.c2,tbl_child.c3,tbl_main.c1,tbl_main.d1
tbl_main.a2,tbl_main.b2,tbl_child.c1,tbl_child.c2,tbl_child.c3,tbl_main.c2,tbl_main.d2
tbl_main.a3,tbl_main.b3,tbl_child.c1,tbl_child.c2,tbl_child.c3,tbl_main.c3,tbl_main.d3

the result must go into a flat file.

I have been toying with several ways i could do this but i would like
to hear the opinion of the experts..

thansk a lot for your time and help.

14 Jul 2006 9:45 PM

Arnie Rowland

Here is one option about how to accomplish that.

http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html

--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."

Show quote

"mdscorp" <l***@mds-corp.com> wrote in message
news:1152909590.564310.167510@75g2000cwc.googlegroups.com...
>i have a main table as in:
> tbl_main with field a,b,c,d,e
> I have a child table as in tbl_child with fields a,b,c,d
>
> I must go through tbl_main , then for each tbl_main record I must also
> find child records related .
> at the end I must create a string composed as follows:
>
> tbl_main.a,tbl_main.b,tbl_child.c1,tbl_child.c2,tbl_child.c3,tbl_main.c,tbl_main.d
>
> the tbl_child.c1 c2 and c3 are the children records related to the
> main tbl_main record.
>
> so final result should be something like:
>
> tbl_main.a1,tbl_main.b1,tbl_child.c1,tbl_child.c2,tbl_child.c3,tbl_main.c1,tbl_main.d1
> tbl_main.a2,tbl_main.b2,tbl_child.c1,tbl_child.c2,tbl_child.c3,tbl_main.c2,tbl_main.d2
> tbl_main.a3,tbl_main.b3,tbl_child.c1,tbl_child.c2,tbl_child.c3,tbl_main.c3,tbl_main.d3
>
> the result must go into a flat file.
>
> I have been toying with several ways i could do this but i would like
> to hear the opinion of the experts..
>
> thansk a lot for your time and help.
>

15 Jul 2006 8:58 PM

--CELKO--

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

>> I have a main table as in: tbl_main with field [sic] a,b,c,d,e I have a child [sic] table as in tbl_child with fields [sic] a,b,c,d <<

In RDBMS, we say "referenced" and "referencing" tables, not children
and parents. We do not use the sily redundant "tbl-" affix in
violationof ISO-11179. Let's get back to thebasics of an RDBMS. Rows
are not records; fields are not columns; tables are not files. .

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.

16 Jul 2006 1:00 AM

Alexander Kuznetsov

>
> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.

Joe,

Unfortunately, in many real life situation following this "basic
programming principle" is very inefficient:

1. it is often a waste of valuable network bandwidth - and there are
quite a few environments where a split second delay might lose a
profitable oppurtunity. Think day trading, for instance.

2. it might require a skilled person instead of an entry level one.
Think Crystal Reports - many real life people doing them are
inexperienced. On the other hand, having a skilled database person is
way more important for the business in many environments. So it makes
perfect business sense to have one more skilled guy do more difficult
tasks.

That's just for the record, I'm sure you've already heard all this many
times...

17 Jul 2006 10:42 PM

--CELKO--

>> 1. it is often a waste of valuable network bandwidth - and there are quite a few environments where a split second delay might lose a profitable oppurtunity. Think day trading, for instance. <<

That is why they use StreamBase, Kx, Sybase or an optimistic
concurrency DB. In a good product, the result set is sent in a
compressed formatto savbe bandwidth.

>> 2. it might require a skilled person instead of an entry level one. Think Crystal Reports - many real life people doing them are inexperienced. <<

Yes, but if you have people who cannot work their way thru a report
writer, you have more3 trouble in your IT department that bandwidth :)

>> That's just for the record, I'm sure you've already heard all this many times...<<

Usually after some undocumented feature or "cowboy coding" has made a
mess of things; remember a lot of my consulting work is with disasters.
After a decade or two, you know the symptoms, such as this guy posted.

I don't know if you follow other languages, but C and C++ compilers
have so many vendor extensions the MISRA (Motor Industry Software
Reliability Association) has issued guidelines on programming in them,
as has the Air Force. Look at the August issue of DR. DOBBS for
articles on programming standards.

18 Jul 2006 1:09 AM

Alexander Kuznetsov

--CELKO-- wrote:

> >> 1. it is often a waste of valuable network bandwidth - and there are quite a few environments where a split second delay might lose a profitable oppurtunity. Think day trading, for instance. <<
>
> That is why they use StreamBase, Kx, Sybase or an optimistic
> concurrency DB. In a good product, the result set is sent in a
> compressed formatto savbe bandwidth.
>

Well, this might make sense from the technical point of view, but
hardly any sense form the business sense of view. SUppose I need to
provide a system fast - "fast" meaning faster than the competitors. By
the time I have found somebody knowledgeable in Kx, the competitors
will complete their product in MS SQL Server.
Besides, I have not seen a compressed result set like this

wide_parent_column1... wide_parent_column10, IL
wide_parent_column1... wide_parent_column10, MI
wide_parent_column1... wide_parent_column10, WI
wide_parent_column1... wide_parent_column10, MN

that would be smaller than this:

wide_parent_column1... wide_parent_column10, 'IL,MI,WI,MN'

no matter what kind of compression is used.

> >> 2. it might require a skilled person instead of an entry level one. Think Crystal Reports - many real life people doing them are inexperienced. <<
>
> Yes, but if you have people who cannot work their way thru a report
> writer, you have more3 trouble in your IT department that bandwidth :)
>

In fact: no. Under many circumstances a manager would rather spend
money on good database people - that is essential for the success of
the project. On the other hand, most reports can be done by a very very
junior people, and in many cases it makes no sense to hire a top notch
report writer if cosmetics are not essential. Think reports that are
only used internally, and not by CEO/CFO.
This all makes a lot of busines sense - should a senior DBA choose to
leave, that would mean a lot of hassle to put it mildly. Should a
report writer choose to leave, we could find another in half no time
and/or have anybody to write reports temporarily. So currently hiring
and retaining good DBAs is more important for the business. I mean
right now here in Midwest, of course the situation can change....

18 Jul 2006 2:16 PM

mdscorp

Alexander Kuznetsov wrote:
Show quote

> --CELKO-- wrote:
> > >> 1. it is often a waste of valuable network bandwidth - and there are quite a few environments where a split second delay might lose a profitable oppurtunity. Think day trading, for instance. <<
> >
> > That is why they use StreamBase, Kx, Sybase or an optimistic
> > concurrency DB. In a good product, the result set is sent in a
> > compressed formatto savbe bandwidth.
> >
>
> Well, this might make sense from the technical point of view, but
> hardly any sense form the business sense of view. SUppose I need to
> provide a system fast - "fast" meaning faster than the competitors. By
> the time I have found somebody knowledgeable in Kx, the competitors
> will complete their product in MS SQL Server.
> Besides, I have not seen a compressed result set like this
>
> wide_parent_column1... wide_parent_column10, IL
> wide_parent_column1... wide_parent_column10, MI
> wide_parent_column1... wide_parent_column10, WI
> wide_parent_column1... wide_parent_column10, MN
>
> that would be smaller than this:
>
> wide_parent_column1... wide_parent_column10, 'IL,MI,WI,MN'
>
> no matter what kind of compression is used.
>
> > >> 2. it might require a skilled person instead of an entry level one. Think Crystal Reports - many real life people doing them are inexperienced. <<
> >
> > Yes, but if you have people who cannot work their way thru a report
> > writer, you have more3 trouble in your IT department that bandwidth :)
> >
>
> In fact: no. Under many circumstances a manager would rather spend
> money on good database people - that is essential for the success of
> the project. On the other hand, most reports can be done by a very very
> junior people, and in many cases it makes no sense to hire a top notch
> report writer if cosmetics are not essential. Think reports that are
> only used internally, and not by CEO/CFO.
> This all makes a lot of busines sense - should a senior DBA choose to
> leave, that would mean a lot of hassle to put it mildly. Should a
> report writer choose to leave, we could find another in half no time
> and/or have anybody to write reports temporarily. So currently hiring
> and retaining good DBAs is more important for the business. I mean
> right now here in Midwest, of course the situation can change....

I am providing the code of the UDF which I thought solved my problem,
so far it has not fail but if you guys say it is dangerous to use it
then I am open to any work around in order to get this working.

CREATE FUNCTION Concat (@req_ID int)
RETURNS varchar(2000)
AS
BEGIN
DECLARE @resultStr varchar(2000)

DECLARE @a int
DECLARE @b int
DECLARE @i int
DECLARE @c varchar(2000)

SELECT @resultStr = COALESCE(@resultStr,'') + '|'+
CONVERT(char(10),RN_Create_date,110)+'|'+CONVERT(char(10),RN_Create_date,110)
+'|81|05|83520|91||||120|2|N|N||L8393' FROM requisition_items where
req_id = convert(char(20),@req_ID)

-- we must ensure the consistency in the length of the services fields
--ther must be 185 pipe delimited spaces as per Zirmed

SET @a=LEN( @resultStr) - LEN( REPLACE( @resultStr , '|' , '' ) )
SET @b=176-@a
SET @c=''
SET @i=0

WHILE (@i<@b)
   BEGIN
       SET @i=@i+1
       SET @c = @c + '|'
     CONTINUE
END
SET @resultStr = @resultStr+@c

RETURN stuff(@resultstr,1,1,'')
END

18 Jul 2006 7:56 PM

Hugo Kornelis

On 18 Jul 2006 07:16:32 -0700, mdscorp wrote:

>I am providing the code of the UDF which I thought solved my problem,
>so far it has not fail but if you guys say it is dangerous to use it
>then I am open to any work around in order to get this working.
(snip)
>SELECT @resultStr = COALESCE(@resultStr,'') + '|'+
>CONVERT(char(10),RN_Create_date,110)+'|'+CONVERT(char(10),RN_Create_date,110)
>+'|81|05|83520|91||||120|2|N|N||L8393' FROM requisition_items where
>req_id = convert(char(20),@req_ID)

Hi mdscorp,

This is indeed a syntax that is known to usually work. But it's not
documented and hence can't be relied on in a situation where errors cost
money or worse (i.e. most production databases).

If you're running SQL Server 2000, the only safe way is to set up a
cursor, iterate over it and append the values to a variable. Let me know
if you need an example.

If you're running SQL Server 2005, there's a nifty trick using FOR XML
PATH that will concatenate the rows from the table. Aaron Bertrand has
included it in this article: http://www.aspfaq.com/show.asp?id=2529.
Note that, since this functionality is officially intended to be used
for XML formatting, characters that have special meaning in XML (such as
<, >, & - maybe more, I don't know) will come out as XML-style escaped
characters (&lt, &gt, &amp). If those characters can be in your data,
then this trick is not for you and you'll still have to use the cursor.

Show quote

>-- we must ensure the consistency in the length of the services fields
>--ther must be 185 pipe delimited spaces as per Zirmed
>
>SET @a=LEN( @resultStr) - LEN( REPLACE( @resultStr , '|' , '' ) )
>SET @b=176-@a
>SET @c=''
>SET @i=0
>
>WHILE (@i<@b)
>   BEGIN
>       SET @i=@i+1
>       SET @c = @c + '|'
>     CONTINUE
> END
>SET @resultStr = @resultStr+@c
>
>RETURN stuff(@resultstr,1,1,'')

All the lines above can be replaced with this single line of code:

RETURN @resultStr + REPLICATE('|', 176 - LEN(@resultStr) +
LEN(REPLACE(@resultStr, '|' , ''))

--
Hugo Kornelis, SQL Server MVP

16 Jul 2006 8:58 PM

Tony Rogerson

> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/11/429.aspx

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1152997125.788242.114660@m73g2000cwd.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>
>>> I have a main table as in: tbl_main with field [sic] a,b,c,d,e I have a
>>> child [sic] table as in tbl_child with fields [sic] a,b,c,d <<
>
> In RDBMS, we say "referenced" and "referencing" tables, not children
> and parents. We do not use the sily redundant "tbl-" affix in
> violationof ISO-11179. Let's get back to thebasics of an RDBMS. Rows
> are not records; fields are not columns; tables are not files. .
>
> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
>

16 Jul 2006 10:11 PM

mdscorp

--CELKO-- wrote:
Show quote

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>
> >> I have a main table as in: tbl_main with field [sic] a,b,c,d,e I have a child [sic] table as in tbl_child with fields [sic] a,b,c,d <<
>
> In RDBMS, we say "referenced" and "referencing" tables, not children
> and parents. We do not use the sily redundant "tbl-" affix in
> violationof ISO-11179. Let's get back to thebasics of an RDBMS. Rows
> are not records; fields are not columns; tables are not files. .
>
> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.

I have valid reasons to format data in the backend, I am not looking
for a discussion on techniques of best programming techniques or why
doing this instaead of that. I am simply looking for advise in some
t-sql code to help me achieve this.

16 Jul 2006 10:15 PM

mdscorp

mdscorp wrote:
Show quote

> --CELKO-- wrote:
> > Please post DDL, so that people do not have to guess what the keys,
> > constraints, Declarative Referential Integrity, data types, etc. in
> > your schema are. Sample data is also a good idea, along with clear
> > specifications. It is very hard to debug code when you do not let us
> > see it.
> >
> > >> I have a main table as in: tbl_main with field [sic] a,b,c,d,e I have a child [sic] table as in tbl_child with fields [sic] a,b,c,d <<
> >
> > In RDBMS, we say "referenced" and "referencing" tables, not children
> > and parents. We do not use the sily redundant "tbl-" affix in
> > violationof ISO-11179. Let's get back to thebasics of an RDBMS. Rows
> > are not records; fields are not columns; tables are not files. .
> >
> > Why are you formatting data in the back end? The basic principle of a
> > tiered architecture is that display is done in the front end and never
> > in the back end. This a more basic programming principle than just SQL
> > and RDBMS.
>
> I have valid reasons to format data in the backend, I am not looking
> for a discussion on techniques of best programming techniques or why
> doing this instaead of that. I am simply looking for advise in some
> t-sql code to help me achieve this.

thanks to Arnie Rowland for the link to that example of code I was able
to accomplish what I needed.
I just had to create UDF that queries and concatenate the results,
then in my main SP in the main query I go as:

SELECT a,b,c,d,e,MyUDF(),f,g,h FROM table myTable WHERE a=x ORDER BY
whatever.

this was exactly what i needed and i got it done thanks to objective
and to the point help from Arnie Rowland.

Thanks Arnie !

16 Jul 2006 11:15 PM

David Portas

mdscorp wrote:

>
> thanks to Arnie Rowland for the link to that example of code I was able
> to accomplish what I needed.
> I just had to create UDF that queries and concatenate the results,
> then in my main SP in the main query I go as:
>
> SELECT a,b,c,d,e,MyUDF(),f,g,h FROM table myTable WHERE a=x ORDER BY
> whatever.
>
> this was exactly what i needed and i got it done thanks to objective
> and to the point help from Arnie Rowland.
>
> Thanks Arnie !

Please be aware that the SQL 2000 UDF solution given in the link that
Arnie posted is not totally reliable. It depends on certain
undocumented behaviour and is based on an assumption about the
execution plan. It is therefore potentially subject to fail without
warning so I suggest you use it with caution. Avoid using it in a
production environment. The SQL Server 2005 solution given on the same
page is safe however because it uses only documented and predictable
features.

--
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
--

17 Jul 2006 12:21 AM

mdscorp

David Portas wrote:
Show quote

> mdscorp wrote:
> >
> > thanks to Arnie Rowland for the link to that example of code I was able
> > to accomplish what I needed.
> > I just had to create UDF that queries and concatenate the results,
> > then in my main SP in the main query I go as:
> >
> > SELECT a,b,c,d,e,MyUDF(),f,g,h FROM table myTable WHERE a=x ORDER BY
> > whatever.
> >
> > this was exactly what i needed and i got it done thanks to objective
> > and to the point help from Arnie Rowland.
> >
> > Thanks Arnie !
>
> Please be aware that the SQL 2000 UDF solution given in the link that
> Arnie posted is not totally reliable. It depends on certain
> undocumented behaviour and is based on an assumption about the
> execution plan. It is therefore potentially subject to fail without
> warning so I suggest you use it with caution. Avoid using it in a
> production environment. The SQL Server 2005 solution given on the same
> page is safe however because it uses only documented and predictable
> features.
>
> --
> 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
> --

is this warning given just based on the fact that it is "un-documented"
feature ? do you have some real example of how it may fail ?
actually the code was needed urgently and it is runnign already in
production envrionment, so far it has not failed producing the expected
results every time.

17 Jul 2006 6:16 AM

David Portas

mdscorp wrote:

>
> is this warning given just based on the fact that it is "un-documented"
> feature ? do you have some real example of how it may fail ?
> actually the code was needed urgently and it is runnign already in
> production envrionment, so far it has not failed producing the expected
> results every time.

The correct behaviour isn't documented. The fact that it's undefined IS
documented

http://support.microsoft.com/kb/287515

Books Online also says this about assignment in a SELECT statement:

"It is recommended that SET @local_variable be used for variable
assignment rather than SELECT @local_variable."

"If the SELECT statement returns more than one value, the variable is
assigned the last value returned."

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_978l.asp

Given that the documentation specifically says "the last value" is the
returned one could argue that the correct behaviour is for the other
rows to be ignored - therefore no concatenation would take place. This
is what is demonstrated by the KB article above.

The idea of assignment over a result set is flawed in any case because
you can't control the order in which the assignement occurs (ORDER BY
only applies to the end result set, not necessarily to computations).
So the concatenation order of the string result must always be
undefined. The whole SELECT assignment syntax is just obscure and prone
to error and it deserves to be deprecated.

There are alternatives and you can Google for those. As others have
suggested, if you want more specific examples it really does help if
you can supply more information: DDL (including keys) and sample data
(INSERT statements). Aslo, tell us what version of SQL Server you are
using.

--
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
--

17 Jul 2006 6:52 AM

Tony Rogerson

> "It is recommended that SET @local_variable be used for variable
> assignment rather than SELECT @local_variable."

If they would only update SET to assign multiple variables on one query
execution rather than having to fire the query once for each variable you
are assigning.

I discuss both methods here:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/18/449.aspx

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

Show quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1153117000.565638.189230@m79g2000cwm.googlegroups.com...
> mdscorp wrote:
>>
>> is this warning given just based on the fact that it is "un-documented"
>> feature ? do you have some real example of how it may fail ?
>> actually the code was needed urgently and it is runnign already in
>> production envrionment, so far it has not failed producing the expected
>> results every time.
>
> The correct behaviour isn't documented. The fact that it's undefined IS
> documented
>
> http://support.microsoft.com/kb/287515
>
> Books Online also says this about assignment in a SELECT statement:
>
> "It is recommended that SET @local_variable be used for variable
> assignment rather than SELECT @local_variable."
>
> "If the SELECT statement returns more than one value, the variable is
> assigned the last value returned."
>
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_978l.asp
>
> Given that the documentation specifically says "the last value" is the
> returned one could argue that the correct behaviour is for the other
> rows to be ignored - therefore no concatenation would take place. This
> is what is demonstrated by the KB article above.
>
> The idea of assignment over a result set is flawed in any case because
> you can't control the order in which the assignement occurs (ORDER BY
> only applies to the end result set, not necessarily to computations).
> So the concatenation order of the string result must always be
> undefined. The whole SELECT assignment syntax is just obscure and prone
> to error and it deserves to be deprecated.
>
> There are alternatives and you can Google for those. As others have
> suggested, if you want more specific examples it really does help if
> you can supply more information: DDL (including keys) and sample data
> (INSERT statements). Aslo, tell us what version of SQL Server you are
> using.
>
> --
> 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
> --
>

17 Jul 2006 8:10 AM

David Portas

Tony Rogerson wrote:

> > "It is recommended that SET @local_variable be used for variable
> > assignment rather than SELECT @local_variable."
>
> If they would only update SET to assign multiple variables on one query
> execution rather than having to fire the query once for each variable you
> are assigning.
>
> I discuss both methods here:
> http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/18/449.aspx
>

Standard SQL already has a way to do this using SELECT INTO:

<select statement: single row> ::=
    SELECT [ <set quantifier> ] <select list>
    INTO <select target list>
    <table expression>

<select target list> ::=
    <target specification> [ { <comma> <target specification> }... ]

<target specification> ::=
<host parameter specification>

MS has already appropriated the SELECT INTO keywords for its own use
but it could be handy if support was added for the ANSI SELECT INTO as
well.

--
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
--

17 Jul 2006 8:23 AM

Tony Rogerson

I like that - why not suggest it on ladybug and we can get voting...

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

Show quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1153123832.743622.185660@m73g2000cwd.googlegroups.com...
> Tony Rogerson wrote:
>> > "It is recommended that SET @local_variable be used for variable
>> > assignment rather than SELECT @local_variable."
>>
>> If they would only update SET to assign multiple variables on one query
>> execution rather than having to fire the query once for each variable you
>> are assigning.
>>
>> I discuss both methods here:
>> http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/18/449.aspx
>>
>
> Standard SQL already has a way to do this using SELECT INTO:
>
> <select statement: single row> ::=
>    SELECT [ <set quantifier> ] <select list>
>    INTO <select target list>
>    <table expression>
>
> <select target list> ::=
>    <target specification> [ { <comma> <target specification> }... ]
>
> <target specification> ::=
> <host parameter specification>
>   | <SQL parameter reference>
>   | <column reference>
>   | <target array element specification>
>   | <dynamic parameter specification>
>   | <embedded variable specification>
>
> MS has already appropriated the SELECT INTO keywords for its own use
> but it could be handy if support was added for the ANSI SELECT INTO as
> well.
>
> --
> 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
> --
>

17 Jul 2006 6:50 AM

Tony Rogerson

I see you are using SQL 2005, use FOR XML, an example is on my blog...

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

Show quote

"mdscorp" <l***@mds-corp.com> wrote in message
news:1152909590.564310.167510@75g2000cwc.googlegroups.com...
>i have a main table as in:
> tbl_main with field a,b,c,d,e
> I have a child table as in tbl_child with fields a,b,c,d
>
> I must go through tbl_main , then for each tbl_main record I must also
> find child records related .
> at the end I must create a string composed as follows:
>
> tbl_main.a,tbl_main.b,tbl_child.c1,tbl_child.c2,tbl_child.c3,tbl_main.c,tbl_main.d
>
> the tbl_child.c1 c2 and c3 are the children records related to the
> main tbl_main record.
>
> so final result should be something like:
>
> tbl_main.a1,tbl_main.b1,tbl_child.c1,tbl_child.c2,tbl_child.c3,tbl_main.c1,tbl_main.d1
> tbl_main.a2,tbl_main.b2,tbl_child.c1,tbl_child.c2,tbl_child.c3,tbl_main.c2,tbl_main.d2
> tbl_main.a3,tbl_main.b3,tbl_child.c1,tbl_child.c2,tbl_child.c3,tbl_main.c3,tbl_main.d3
>
> the result must go into a flat file.
>
> I have been toying with several ways i could do this but i would like
> to hear the opinion of the experts..
>
> thansk a lot for your time and help.
>