Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert values from array into a table Expand / Collapse
Author
Message
Posted Tuesday, July 28, 2009 5:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 21, 2014 4:12 AM
Points: 432, Visits: 632
Hi,
Is there a way in SQL Server 2005 to achieve this?
declare @array varchar(2000)
set @array='321,3AY,3LH,3PU,3PR,3SP,3JH,3VN,3VR,3VT,3VU,3VV,3VW,3VY,3UW,3WA,3WD,3WI,3WM,3WY,3WZ,3XB,3XJ,3XK'
--Loop until all the values in @array
insert into myTable(Col1,Col2,Col3)
select @array --only 3digits must be selected
,col2,col3 from myOtherTable

Thanks,
KB
Post #760680
Posted Tuesday, July 28, 2009 5:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 7, 2014 1:56 AM
Points: 41, Visits: 181
i think you want that all , seperated values must be returned in table format.
use following function to do this.


select * from fn_split('321,3AY,3LH,3PU,3PR,3SP,3JH,3VN,3VR,3VT,3VU,3VV,3VW,3VY,3UW,3WA,3WD,3WI,3WM,3WY,3WZ,3XB,3XJ,3XK')



create function [dbo].[fn_split](
@str varchar(8000),
@spliter char(1)
)
returns @returnTable table (idx int primary key identity, item varchar(8000))
as
begin
declare @spliterIndex int
select @str = @str + @spliter

while len(@str) > 0
begin
select @spliterIndex = charindex(@spliter,@str)
if @spliterIndex = 1
insert @returnTable (item)
values (null)
else
insert @returnTable (item)
values (substring(@str, 1, @spliterIndex-1))
select @str = substring(@str, @spliterIndex+1, len(@str)-@spliterIndex)
end
return
end
Post #760682
Posted Tuesday, July 28, 2009 8:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 9, 2014 9:24 AM
Points: 186, Visits: 3,073
Hi,
what about a set based solution? It'll run faster and scale better. This code courtesy of Jeff http://www.sqlservercentral.com/articles/T-SQL/62867/

We'll need a tally table for this so, set one up:

    USE TempDB     --DB that everyone has where we can cause no harm
SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed

--=============================================================================
-- Create and populate a Tally table
--=============================================================================
--===== Conditionally drop
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally

--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Let the public use it
GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

Re-writing split function using tally table, again, this was lifted almost as is from article referenced above:

create function [dbo].[fn_split](
@str varchar(8000),
@spliter char(1)
)
returns @returnTable table (idx int primary key identity, item varchar(8000))
as
begin
declare @spliterIndex int
select @str = @str + @spliter

SELECT @str = @spliter + @str + @spliter

INSERT @returnTable
SELECT SUBSTRING(@str,N+1,CHARINDEX(@spliter,@str,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@str)
AND SUBSTRING(@str,N,1) = @spliter
ORDER BY N

return
end

Check it all works:

	SELECT * FROM fn_split('321,3AY,3LH,3PU,3PR,3SP,3JH,3VN,3VR,3VT,3VU,3VV,3VW,3VY,3UW,3WA,3WD,3WI,3WM,3WY,3WZ,3XB,3XJ,3XK')

@KB,
In fact, for true scalability (how long/how many elements does comma separated list have?) I wouldn't bother with the table value function, I'd just do the split in insert into mytable statement, however, I don't know how you are doing the select/join with myothertable so I can't provide code for this!

Please provide table ddls and data as per http://www.sqlservercentral.com/articles/Best+Practices/61537/ and someone'll be able to provide fully tested code.
Post #760774
Posted Tuesday, July 28, 2009 8:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
Another tally table solution, but this one doesn't require the creation of a separate user defined function. CTEs are used to parse the "ARRAY" into a table which can be joined to "MyOtherTable" to support your insert.

DECLARE @input VARCHAR(2002)
DECLARE @array VARCHAR(2000)
DECLARE @sepchar CHAR(1)
set @sepchar = ',' -- separation character is a comma
set @array= '321,3AY,3LH,3PU,3PR,3SP,3JH,3VN,3VR,3VT,3VU,3VV,3VW,3VY,3UW,3WA,3WD,3WI,3WM,3WY,3WZ,3XB,3XJ,3XK'

set @input = @sepchar + @array + @sepchar -- begin and end with separation characters

;WITH tally (N) as
(SELECT TOP 1000000 row_number() OVER (ORDER BY sc1.id)
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2)

,ArrayAsTable AS
(SELECT substring(@input,N+1,CHARindex(@sepchar,@input,N+1)-(N+1)) as element
FROM tally
WHERE substring(@input,N,1) = @sepchar
and N < len(@input)
)
-- INSERT INTO MyTable (col1,col2,col3)
SELECT Element
FROM ArrayAsTable
-- JOIN MyOtherTable ON (whatever)




__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #760780
Posted Tuesday, July 28, 2009 8:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 9, 2014 9:24 AM
Points: 186, Visits: 3,073
Neat solution! Thanks :)
Post #760793
Posted Tuesday, May 1, 2012 4:29 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, November 30, 2014 9:46 AM
Points: 526, Visits: 123
It´s better if u use an XML solution.
Post #1293545
Posted Tuesday, May 1, 2012 5:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
Jaat (5/1/2012)
It´s better if u use an XML solution.


Great. Let's see the code!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1293559
Posted Wednesday, May 2, 2012 12:51 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, November 30, 2014 9:46 AM
Points: 526, Visits: 123
I´m sorry i did not know the rules to answer something, but based on best practices we should avoid making string operations on the database side for performance that´s my understanding, beside i add some code

IF OBJECT_ID('#Tbl_TempTable') IS NULL
BEGIN
CREATE TABLE #Tbl_TempTable
(
ID INT IDENTITY
,FName VARCHAR(30)
,EnterDtm DATETIME DEFAULT GETDATE()
)
END
DECLARE @XML XML
SET @XML =
'<Root>
<PrimerNombre>Juan Valdez Calambuco</PrimerNombre>
<PrimerNombre>Juan Primero</PrimerNombre>
<PrimerNombre>Juan Segundo</PrimerNombre>
<PrimerNombre>Juan Tercero</PrimerNombre>
<PrimerNombre>Juan Cuarto</PrimerNombre>
<PrimerNombre>Juan Quinto</PrimerNombre>
<PrimerNombre>Juan Sexto</PrimerNombre>
<PrimerNombre>Juan Septimo</PrimerNombre>
<PrimerNombre>Juan Octavo</PrimerNombre>
</Root>
'
INSERT INTO #Tbl_TempTable (FName)
SELECT T.c.value('.','VARCHAR(30)') from @XML.nodes('//Root/PrimerNombre') T(c)

SELECT * FROM #Tbl_TempTable

let me know if it works, if not i will try to provide a better solutions thanks
Post #1294136
Posted Wednesday, May 2, 2012 1:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
Jaat (5/2/2012)
I´m sorry i did not know the rules to answer something, but based on best practices we should avoid making string operations on the database side for performance that´s my understanding, beside i add some code

IF OBJECT_ID('#Tbl_TempTable') IS NULL
BEGIN
CREATE TABLE #Tbl_TempTable
(
ID INT IDENTITY
,FName VARCHAR(30)
,EnterDtm DATETIME DEFAULT GETDATE()
)
END
DECLARE @XML XML
SET @XML =
'<Root>
<PrimerNombre>Juan Valdez Calambuco</PrimerNombre>
<PrimerNombre>Juan Primero</PrimerNombre>
<PrimerNombre>Juan Segundo</PrimerNombre>
<PrimerNombre>Juan Tercero</PrimerNombre>
<PrimerNombre>Juan Cuarto</PrimerNombre>
<PrimerNombre>Juan Quinto</PrimerNombre>
<PrimerNombre>Juan Sexto</PrimerNombre>
<PrimerNombre>Juan Septimo</PrimerNombre>
<PrimerNombre>Juan Octavo</PrimerNombre>
</Root>
'
INSERT INTO #Tbl_TempTable (FName)
SELECT T.c.value('.','VARCHAR(30)') from @XML.nodes('//Root/PrimerNombre') T(c)

SELECT * FROM #Tbl_TempTable

let me know if it works, if not i will try to provide a better solutions thanks


That would be helpful but unfortunately it is not what the OP is trying to solve. They have a comma separated list and want to parse that into individual rows.

Not sure if you noticed but this thread is 3 years old and the OP has not logged in for about a 1 1/2 years.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1294148
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse