February 6, 2008 at 7:31 am
Hi,
Please help in accomplishing this.
i have a table like agentstatus(agentid,agenttype,cityid)
i need to insert collection of cityid to the same agentid and agentype which means agentid got register to the same cityid.
with the programming if i do , it is using for loops.
i want to accomplish the same thing in stored procedure so that it should remove the forloop in programming.
i want to pass the values in this way:
agentID - 10
agentType = 3
cityID = 1/2/5/12/20
the table has to be inserted with this values then
10-3-1
10-3-2
10-3-5
10-3-12
10-3-20
February 6, 2008 at 8:13 am
You have to loop somewhere, either in the client code or the stored procedure code. There's no good way to split the string up and insert the values in one step.
You can use charindex/patindex to find the delimiters and split them.
February 6, 2008 at 11:34 am
You could use a parse function with a select statement to do something like this.
Note: that this particular function strives off a numbers table which I have provided the DDL and data.
Numbers table
/****** Object: Table [dbo].[Tally] Script Date: 02/06/2008 12:14:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Numbers](
[Nbr] [int] IDENTITY(1,1) NOT NULL
CONSTRAINT [PK_Tally_N12] PRIMARY KEY CLUSTERED
(
[Nbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY];
INSERT INTO Numbers (n) VALUES (0);
WHILE (SELECT MAX(n) FROM numbers)<65535
INSERT INTO numbers
SELECT n+(SELECT MAX(n)+1 FROM numbers)
FROM numbers ;
The code to insert the required data
CREATE FUNCTION [dbo].[StringParser]
(@String_in varchar(max),
@Delimiter_in char(1))
returns table
as
return (
SELECT top 100 percent
SUBSTRING(@String_in+@Delimiter_in, Nbr,
CHARINDEX(@Delimiter_in, @String_in+@Delimiter_in, Nbr) - Nbr) as Parsed
FROM dbo.Numbers
WHERE Nbr <= LEN(@String_in)
AND SUBSTRING(@Delimiter_in + @String_in, Nbr, 1) = @Delimiter_in
ORDER BY Nbr
);
CREATE PROCEDURE InsertIntoMyTable
@AgentId INT,
@AgentType INT,
@STR NVARCHAR(1000),
@Delim CHAR(1)
AS
BEGIN
INSERT INTO MyTable
SELECT @AgentId,@AgentType, Parsed
FROM dbo.StringParser(@Str,@Delim)
END
exec InsertIntoMyTable 10,3,'1/2/5/12/20','/'
February 6, 2008 at 2:06 pm
Hi,
You need to have loop condition in some place. It would be better to move this loop condition to database side otherwise you will run in performance issues. There is no straight forward way to achieve this.
Check out the below link
http://searchwindevelopment.techtarget.com/tip/0,289483,sid8_gci932171,00.html
Thanks -- Vj
February 6, 2008 at 2:23 pm
I forgot to mention that the solution I posted operates one record at a time. As all of the previous posters have suggested you will need a looping mechanism to process the data ,even if you use the sample solution I gave you, as it only inserts one record at a time.
February 6, 2008 at 3:35 pm
If you use the string parser function from Adam's post (I seem to recognize it from somewhere), and use the Cross Apply capabilities of SQL 2005, you can do the whole thing in one step.
create table Delimited (
ID int identity primary key,
Val varchar(100))
go
insert into dbo.delimited (val)
select '5,7,12'
union all
select '6,92,71'
go
select id, [1], [2], [3]
from
(select id, row_number() over (partition by id order by id) c,
cast(parsed as int) as parsed
from dbo.delimited
cross apply common.dbo.stringparser(val, ',')) Sub
pivot
(max(parsed)
for c in ([1],[2],[3])) Pvt
Produced:
id123
15712
269271
If you modify the string parser function to return the row number in it (simple modification), you can take that part out of the final query and replace it with the column from the function. That will give more reliable results.
I think that will give you what you need. Should be pretty okay on performance, though I haven't tested it fully on that.
Because of the cross-apply, you'll end up with quite a few scans of the Numbers table in this case, but it should work okay because it will cache and should run more rapidly than a cursor or row-by-row loop.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 6, 2008 at 8:20 pm
Adam's post (I seem to recognize it from somewhere),
I got the string parser script from the script section of this site, I believe 😀
February 7, 2008 at 2:16 am
Hi All,
every one gives finest solution but one different solution that i use in my code is xml based
like
Declare @vxml xml
set @vxml = '
'
Insert into table1 (Column1,Column2)
Select 1, tbl.value('@id','tinyint')
from @vxml.nodes('/column/Record') as testtbl(tbl)
and you get the result
table1
Column1 Column2
1 1
1 2
1 3
there is no need of looping or anything else but data will be inserted .
but xml will be provided to sp from front end.
February 7, 2008 at 6:56 am
Just a couple of suggestions, folks...
First, there is no need for a loop in the creation of a "Tally" or "Numbers" table. Second, to be truly effective and to eliminate the need for embedded TOP clauses and Order By's, it must have a Clustered Primary Key on the "N" column (like Adam did). With that in mind, here's the quickest way to make a permanent Tally table in SS2K5...
-===== Create and populate the Tally table on the fly (2k5 version)
SELECT TOP 11000 --More than 30 years worth of days if converted to dates
N = IDENTITY(INT,1,1)
INTO dbo.Tally
FROM Master.sys.All_Columns ac1
CROSS JOIN Master.sys.All_Columns ac2
--===== Add the necessary Clustered PK for blinding speed
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow everyone to use the table
GRANT SELECT ON dbo.Tally TO PUBLIC
See? No explicit loop and runs like an ape with it's hair on fire. Of course, if you want your Tally table to contain more numbers, just change the number after the TOP clause.
You also don't need explicit loops to solve the problem. In fact, you don't even need a UDF and, contrary to what everyone has said, you can still solve the problem in a single step...
--=======================================================================================
-- Create and populate a test table... THIS IS NOT PART OF THE SOLUTION
--=======================================================================================
CREATE TABLE #AgentStatus
(
AgentID INT,
AgentType INT,
CityID VARCHAR(1000)
)
INSERT INTO #AgentStatus
(AgentID,AgentType,CityID)
SELECT 10,3,'1/2/5/12/20' UNION ALL
SELECT 20,1,'2/4/6/8/10' UNION ALL
SELECT 30,2,'1/3/5/7/9/11/35' UNION ALL
SELECT 40,4,'5' UNION ALL
SELECT 50,9,'20/30'
--=======================================================================================
-- Solve the problem in a "single step" using the Tally table
--=======================================================================================
--===== Declare a variable to hold the delimiter
DECLARE @Delim CHAR(1)
SET @Delim = '/'
--===== Do the split and required concatenation all at once
SELECT CAST(AgentID AS VARCHAR(10))+'-'
+ CAST(AgentType AS VARCHAR(10))+'-'
+ SUBSTRING(@Delim+h.CityID+@Delim, t.N+1, CHARINDEX(@Delim, @Delim+h.CityID+@Delim, t.N+1)-t.N-1)
FROM dbo.Tally t
RIGHT OUTER JOIN --Necessary in case CityID is NULL
dbo.#AgentStatus h
ON SUBSTRING(@Delim+h.CityID+@Delim, t.N, 1) = @Delim
AND t.N < LEN(@Delim+h.CityID+@Delim)
And, look ma, still no explicit loop... no XML required, either 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2008 at 7:44 am
Good solution Jeff, simply amazing. 😉
February 7, 2008 at 7:26 pm
Adam Haines (2/7/2008)
Good solution Jeff, simply amazing. 😉
Thanks, Adam :blush:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply