Insert values from array into a table

Question

Post reply

Insert values from array into a table

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
July 28, 2009 at 5:40 am

#207381

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
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

shamassaeedmr SSC-Addicted Points: 441 More actions · Answer 1

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

Allister Reid SSCrazy Points: 2666 More actions · Answer 2

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

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.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

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? Everybody look what's going down. -- Stephen Stills

Allister Reid SSCrazy Points: 2666 More actions · Answer 4

Allister Reid

SSCrazy

Points: 2666

July 28, 2009 at 8:29 am

#1031052

Neat solution! Thanks

Jaat Say Hey Kid Points: 700 More actions · Answer 5

Jaat

Say Hey Kid

Points: 700

May 1, 2012 at 4:29 pm

#1482138

It´s better if u use an XML solution.

Jeff Moden SSC Guru Points: 1004568 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jaat Say Hey Kid Points: 700 More actions · Answer 7

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

Sean Lange SSC Guru Points: 286601 More actions · Answer 8

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Insert values from array into a table

Cookies on SQLServerCentral