Insert multiple records from one string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Insert multiple records from one string

Author

Topic

MikeSaunders
Starting Member

11 Posts

Posted - 2013-10-07 : 15:30:38

Hello again,

I am running into a problem importing a string containing an array into a table.

My string looks like '[{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]'

I want to import this into a temp table that looks like:

ID, Fname, Lname, Sex
1, mark, peters, mr
2, jane, fonda, mrs
3, john,doo,mr
4, james,bond,mr

Can someone help me with this? I really have no clue how to solve this.

Thanks a lot!
Mike

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 15:55:57

Not a very pleasant task, but something like this example:

DECLARE @x VARCHAR(256) = '[{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]';
SELECT
	*
FROM 
(
SELECT
	*
FROM
	dbo.delimitedsplit8k(
		REPLACE(
			REPLACE(
				REPLACE(
					REPLACE(
						REPLACE(@x,'[',''),
					']',''),
				'}{','}'),
			'{',''),
		'{]','')
	,'}')
WHERE Item <> ''
) a
CROSS APPLY dbo.DelimitedSplit8K(Item,',') b
PIVOT (MAX(b.Item) FOR b.ItemNumber IN ([1],[2],[3]))P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 00:10:19

DelimitedSplit8K function is from here

http://www.sqlservercentral.com/articles/Tally+Table/72993/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MikeSaunders
Starting Member

11 Posts

Posted - 2013-10-08 : 02:44:50

Hello all,

Thanks for your help. The solutions you've provided are super and far beyond my current skill level.
Learned a lot, Thanks again!!!

Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources