Script for comma seperated values

[muzamil]

To get rid of redundant data in a table, my cleint will be providing
something like this:

IDtokeep Ids to delete
34 24,35,49
12 14,178,1457
54 32,65,68
I have to write a script for each of the above rows which looks like
this:
-----------------------------------
update sometable
set id = 34
where id in (24,35,49)

delete from sometable
where id in (24,35,49)
-----------------------------------
As I said I have to do this for EACH row. Can I somehow automate this
or will I need to write to same script for each row (there are about
5000 rows in this audit table)

Any help is highly appreciated.

Here is the DDL and inserts for the audit table.

IF object_id(N'dbo .dataclean','U' ) is not null
DROP TABLE [dbo].[dataclean]
GO
CREATE TABLE [dataclean] (
[IdTokeep] int NULL ,
[IdsTodelete] varchar (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL )
GO

INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])
VALUES(34,'24,3 5,49')
INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])
VALUES(12,'14,1 78,1457')
INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])
VALUES(54,'32,6 5,68')
GO

[RT]

If this is a one time thing then please use the following sql server
function to parse this.

The syntax would be:

update sometable set id = 34 where id in
dbo.fnStringToT able('24,35,49' ,',')
delete from sometable where id in dbo.fnStringToT able('24,35,49' ,',')

What I recommend is create a table in SQL from CSV file and populate
another table like the structure below. you can use this function to
populate this table.
MyTable:
IDToKeep IDToDelete
34 24
34 35
34 49
12 14
12 178
12 1457

and run the following statement
update sometable set id = b.idtokeep
from mytable where sometable.id=my table.idtodelet e

delete sometable where id in (select idtodelete from mytable)
The above script is not tested. so make sure you test them before you
do anythign with that. Below is the code to create the function
dbo.fnStringToT able. I hope this helps.

CREATE FUNCTION dbo.fnStringToT able
(
@str varchar(8000), @delim varchar(5)
)
RETURNS @ValueStr TABLE (value varchar(500))
AS
/*************** *************** *************** *************** *************** ***
** Name: fnStringToTable
** Desc: Parses the input parameter string with the delimiter
**
** Return values: table @valuestr (value varchar(500))
**
**
** Parameters:
** Input
** ----------
** @str - delimited string ex. . 1,2,3 max length is 8000 characters
** @delim - delimiter to parse @str ex. ",","-" max length is 5
characters
** Auth: Ramesh Thalluru
** Date: 07/29/2003
*************** *************** *************** *************** *************** ****
** Change History
*************** *************** *************** *************** *************** ****
** Date: Author: Description:
** -------- -------- -------------------------------------------
**
*************** *************** *************** *************** *************** ****/
BEGIN
declare @str1 varchar(2000), @len int, @endPos int, @stPos int,
@rightLen int, @tmpint int, @tmpstr varchar(8000)
-- if the string is empty or null return without anything
if ( @str=NULL or len(ltrim(rtrim (@str)))=0 )
return

select @str1=rtrim(ltr im(@str))
select @str=@str1
select @len=len(@str), @endPos=0, @stPos=-1, @rightLen=0
while @stPos <> 0
begin
select @str1=right(@st r, @len-@rightLen)
select @stPos=charinde x(@delim,@str1)
select @rightLen=@righ tLen+@stPos
if @stPos <> 0
begin
insert into @ValueStr(value )
select rtrim(ltrim(lef t(@str1,@stPos-1)))
end
else
begin
insert into @ValueStr(value )
select ltrim(rtrim(@st r1))
end
end
RETURN
END

muza...@hotmail .com wrote:

To get rid of redundant data in a table, my cleint will be providing
something like this:

IDtokeep Ids to delete
34 24,35,49
12 14,178,1457
54 32,65,68
I have to write a script for each of the above rows which looks like
this:
-----------------------------------
update sometable
set id = 34
where id in (24,35,49)

delete from sometable
where id in (24,35,49)
-----------------------------------
As I said I have to do this for EACH row. Can I somehow automate this
or will I need to write to same script for each row (there are about
5000 rows in this audit table)

Any help is highly appreciated.

Here is the DDL and inserts for the audit table.

IF object_id(N'dbo .dataclean','U' ) is not null
DROP TABLE [dbo].[dataclean]
GO
CREATE TABLE [dataclean] (
[IdTokeep] int NULL ,
[IdsTodelete] varchar (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ) GO

INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])
VALUES(34,'24,3 5,49')
INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])
VALUES(12,'14,1 78,1457')
INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])
VALUES(54,'32,6 5,68')
GO

[--CELKO--]

I am sorry that you have sucha bad client. You should break this apart
in the front end, but if you are totally screwed, try this:

Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896' );
INSERT INTO InputStrings VALUES ('second', '312,534,997,89 6');
...

This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.

CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL PRIMARY KEY,
parm INTEGER NOT NULL);

It makes life easier if the lists in the input strings start and end
with a comma. You will need a talbe of sequential numbers -- a
standard SQL programming trick, Now, the real query, in SQL-92 syntax:
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_strin g
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING ( ',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string , S1.seq;

The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma.

You can then write:

SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);

You would never write a T-SQL procedure, if you can avoid it.

[Erland Sommarskog]

(mu*****@hotmai l.com) writes:

To get rid of redundant data in a table, my cleint will be providing
something like this:

IDtokeep Ids to delete
34 24,35,49
12 14,178,1457
54 32,65,68
Undoubtedly it would be a whole lot easier if your client could just
give you plain tuples:

34 24
34 35
34 49
12 14
12 178

Then it's all a plain update statement and a plain delete.

With the current scheme, you need to run a string-to-table function,
and you need to loop row by row. (In SQL 2000. In SQL 2005 you can
do it in one statement, but you still need the string-to-table
function.)
I have to write a script for each of the above rows which looks like
this:
-----------------------------------
update sometable
set id = 34
where id in (24,35,49)

delete from sometable
where id in (24,35,49)
-----------------------------------
As I said I have to do this for EACH row. Can I somehow automate this
or will I need to write to same script for each row (there are about
5000 rows in this audit table)

Well, you can actually do it without the string-to-table function,
with some manual intervention:

SELECT 'UPDATE somtable SET id = ' + ltrim(str(IdTok eep)) +
' where id in (' +
IdsTodelete + ')
DELETE sometable where id in (' + IdsTodelete + ')'
FROM dataclean

And then cut and paste result.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp