I have one table have 30,000 phone Number and i need to path it to SMS services as one row to can send massage for this numbers in one time so i need to return all this rows in one row ,
we have two solution either you can return it as XML by using XML Path or using STUFF FUNCTION to return all this rows in one row , Stuff function can be used in more things and this case one of this usage.
The STUFF string function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. The syntax of the STUFF string function is as follows: for more information about STUFF function check this LINK
STUFF ( < character_expression >, < start >, < length >,
< character_expression > )
- Create table name PhoneNumber
- Insert one record.
- Filling table by 30.000 Record using Filling Table Stored procedure GO FOR Download (SP after the execute it will print T-SQL Script Execute it to filling the table)
- Return All record in one row as XML
- Return all record in one row using stuff function.
Create table phone_number
ID int primary KEY ,
Insert one Record
insert into phone_number
Fill the table by 30,000 of record
Exec FillingTable_v2 ‘dbo’,’phone_number’,’30000′
Select Count(1) from [phone_number] With(nolock)
Return Records in one Row as XML
FOR XML PATH(”)
Return Records in one Row without XML
SELECT STUFF((SELECT ‘,’ + [Number]
ORDER BY [Number]
FOR XML PATH(”)), 1, 1, ”) AS [Output]
Follow the author: