April 8, 2003 at 9:29 am
A tip posted before by marda and reposted by Antares686 said that to get comma-delimited list of values of a certain field we use this code..
declare @str varchar(8000)set @str = ''select @str = @str + colVal + ', ' from tblName
It worked fine with me but my problem is in large amount of data were records exceed 100,000. The resulting string is chopped and I cannot find any larger datatype. Text datatype is not allowed in local variable declarationsIs there any other solution?
Mohamad Ad-Deeb
Senior Software Developer, Microtech
3 Mossadak st. Dokki 12311
Giza, Egypt.
Tel. +20 2 336 9855 (Ext.112)
Fax +20 2 749 8784
http://www.microtech-eg.com
April 8, 2003 at 9:56 am
Yes. Varchar field has limitation of 8000 characters.
But I didn't understand the requirement to concatenate millions of field values in one variable.
Can you please elaborate as to what you are planning to do once you get all these values in a variable.
April 8, 2003 at 10:57 am
I don't think you would be able to concatenate so many records into a single variable. Maybe you should do it from outside SQL. You could copy to severals text files all the rows with bcp, and then put them together into one single file copying them together, and then bcp in the unique file.
April 8, 2003 at 12:39 pm
My system is 3-tier and want to minimize the interaction with the databaseI use a certain query to get a certain set of results through a complicated SQL full of joins.Whenever I need that set of results again, I would like to query using the primary keys only to eliminate the need to overload the server with that complicated query
Mohamad Ad-Deeb
Senior Software Developer, Microtech
3 Mossadak st. Dokki 12311
Giza, Egypt.
Tel. +20 2 336 9855 (Ext.112)
Fax +20 2 749 8784
http://www.microtech-eg.com
April 9, 2003 at 9:32 am
Does the following option help ?
1. Create a temporary table with only the primary key fields say #tmp_keys
2. Do a Insert into select query to run the complex query and populate
the temporary table #tmp_keys
3. When you need the data again join the #tmp_keys with actual table using the keys
to get the data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply