SQLServerCentral / SQL Server 2005 / T-SQL (SS2K5) / Insert values from array into a table / Latest Posts

RE: Insert values from array into a table

Sean Lange — Wed, 02 May 2012 13:06:19 GMT

[quote][b]Jaat (5/2/2012)[/b][hr]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 NULLBEGIN CREATE TABLE #Tbl_TempTable ( ID INT IDENTITY ,FName VARCHAR(30) ,EnterDtm DATETIME DEFAULT GETDATE() )ENDDECLARE @XML XMLSET @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_TempTablelet me know if it works, if not i will try to provide a better solutions thanks[/quote]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. :-)

RE: Insert values from array into a table

Jaat — Wed, 02 May 2012 12:51:26 GMT

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 NULLBEGIN CREATE TABLE #Tbl_TempTable ( ID INT IDENTITY ,FName VARCHAR(30) ,EnterDtm DATETIME DEFAULT GETDATE() )ENDDECLARE @XML XMLSET @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_TempTablelet me know if it works, if not i will try to provide a better solutions thanks

RE: Insert values from array into a table

Jeff Moden — Tue, 01 May 2012 17:34:02 GMT

[quote][b]Jaat (5/1/2012)[/b][hr]It´s better if u use an XML solution.[/quote]Great. Let's see the code!

RE: Insert values from array into a table

Jaat — Tue, 01 May 2012 16:29:28 GMT

It´s better if u use an XML solution.

RE: Insert values from array into a table

Allister Reid — Tue, 28 Jul 2009 08:29:44 GMT

Neat solution! Thanks :)

RE: Insert values from array into a table

The Dixie Flatline — Tue, 28 Jul 2009 08:20:53 GMT

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. [code]DECLARE @input VARCHAR(2002)DECLARE @array VARCHAR(2000)DECLARE @sepchar CHAR(1)set @sepchar = ',' -- separation character is a commaset @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)[/code]

RE: Insert values from array into a table

Allister Reid — Tue, 28 Jul 2009 08:12:58 GMT

Hi,what about a set based solution? It'll run faster and scale better. This code courtesy of Jeff [url]http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]We'll need a tally table for this so, set one up:[code] 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[/code]Re-writing split function using tally table, again, this was lifted almost as is from article referenced above:[code]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 returnend[/code]Check it all works:[code] 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')[/code]@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 [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url] and someone'll be able to provide fully tested code.

RE: Insert values from array into a table

shamassaeedmr — Tue, 28 Jul 2009 05:44:15 GMT

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))asbegindeclare @spliterIndex intselect @str = @str + @spliterwhile 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) endreturnend

Insert values from array into a table

KBSK — Tue, 28 Jul 2009 05:40:25 GMT

Hi,Is there a way in SQL Server 2005 to achieve this?[code]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 @arrayinsert into myTable(Col1,Col2,Col3)select @array --only 3digits must be selected,col2,col3 from myOtherTable[/code]Thanks,KB