need help writing batch without cursor

i'm trying to write a batch that will perform a complex task using
set-based selects instead of a row-based cursor. let me know if you can
help me figure out how.
description of what i'm trying to do:
there is TABLE1, TABLE2, and TABLE3
i want to select each row from TABLE1, do some analysis on the data of
that row, and then perform an insert of some data into TABLE2, and some
data into TABLE3
how do i do this in a T-SQL batch?
thanks in advance!What kind of analysis?
AMB
"iaesun@.yahoo.com" wrote:

> i'm trying to write a batch that will perform a complex task using
> set-based selects instead of a row-based cursor. let me know if you can
> help me figure out how.
> description of what i'm trying to do:
> there is TABLE1, TABLE2, and TABLE3
> i want to select each row from TABLE1, do some analysis on the data of
> that row, and then perform an insert of some data into TABLE2, and some
> data into TABLE3
> how do i do this in a T-SQL batch?
> thanks in advance!
>|||i'd be curious how to do it even if there were no analysis, since it's
the row-by-row part that i'm not sure how to do in a set-based
solution.
but! in case it helps, here's the analysis i was thinking of (pardon
the psuedo-code for the row-by-row portion)
for each ROW in TABLE1
{
if not exists (select * from TABLE2 where COLUMNA = ROW.COLUMN1)
begin
insert into TABLE2 (COLUMNA) values (ROW.COLUMN1)
end
set @.table2id = select ID from TABLE2 where COLUMNA = ROW.COLUMN1
insert into TABLE3 values (ROW.COLUMN2, ROW.COLUMN3, @.table2id)
}|||>> i want to select each row from TABLE1, do some analysis on the data
of
that row, and then perform an insert of some data into TABLE2, and some
data into TABLE3 <<
Read what you wrote! What kind of spec is that? How do we debug code
which is not here. Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity,
datatypes, etc. in your schema are. Sample data is also a good idea,
along with clear specifications.
Frankly, it sounds likeyou are splitting this vague Table1 into two
tables when you should be using a VIEW or a column with whatever the
criteria for this split is. The whole idea of databases was to avoid
redundant data.|||You don't know Joe.
Maybe he's actually normalizing table1.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112986095.913817.293370@.g14g2000cwa.googlegroups.com...
> of
> that row, and then perform an insert of some data into TABLE2, and some
> data into TABLE3 <<
> Read what you wrote! What kind of spec is that? How do we debug code
> which is not here. Please post DDL, so that people do not have to guess
> what the keys, constraints, Declarative Referential Integrity,
> datatypes, etc. in your schema are. Sample data is also a good idea,
> along with clear specifications.
> Frankly, it sounds likeyou are splitting this vague Table1 into two
> tables when you should be using a VIEW or a column with whatever the
> criteria for this split is. The whole idea of databases was to avoid
> redundant data.
>|||Try,
insert into t2 (colA)
select col1
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
insert into t3 (colB, colC)
select col2, col3
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
AMB
"iaesun@.yahoo.com" wrote:

> i'd be curious how to do it even if there were no analysis, since it's
> the row-by-row part that i'm not sure how to do in a set-based
> solution.
> but! in case it helps, here's the analysis i was thinking of (pardon
> the psuedo-code for the row-by-row portion)
> for each ROW in TABLE1
> {
> if not exists (select * from TABLE2 where COLUMNA = ROW.COLUMN1)
> begin
> insert into TABLE2 (COLUMNA) values (ROW.COLUMN1)
> end
> set @.table2id = select ID from TABLE2 where COLUMNA = ROW.COLUMN1
> insert into TABLE3 values (ROW.COLUMN2, ROW.COLUMN3, @.table2id)
> }
>|||Correction,
Swith the order of the statements.
insert into t3 (colB, colC)
select col2, col3
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
insert into t2 (colA)
select col1
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
AMB
"Alejandro Mesa" wrote:
> Try,
> insert into t2 (colA)
> select col1
> from t1
> where not exists (select * from t2 where t2.colA = t1.col1)
> insert into t3 (colB, colC)
> select col2, col3
> from t1
> where not exists (select * from t2 where t2.colA = t1.col1)
>
> AMB
> "iaesun@.yahoo.com" wrote:
>|||well, i'm trying to keep the topic abstract, because i was hoping for a
more general description of how to do row-by-row processing in a
set-based solution. but, if such details are needed in this case, then
let me try to invent some. regarding the latter portion of your
message: this is, in a manner of speaking, splitting table1 into two
tables. however, it is more of a complex tranformation, not redundant
information. table1 is a staging table, and will be dropped after this
process is complete.
first, the three table definitions:
CREATE TABLE [sourcetable] (
[ID] [int] NOT NULL,
[column1] [int] NULL,
[column2] [int] NULL,
) ON [primary]
GO
CREATE TABLE [destinationtable1] (
[ID] [int] NOT NULL,
[columnA] [int] NULL,
[columnB] [int] NULL,
) ON [primary]
GO
CREATE TABLE [destinationtable2] (
[ID] [int] NOT NULL,
[columnY] [int] NULL,
[columnZ] [int] NULL,
) ON [primary]
GO
what i would like to do, read [sourcetable] row-by-row. for each row, i
would like to perform the following batch:
IF NOT EXISTS (SELECT * FROM destinationtable1 WHERE
destinationtable1.columnA = sourcetable.column1)
begin
INSERT INTO destinationtable1 (columnA) VALUES
(sourcetable.column1)
end
SELECT @.idvariable = ID FROM destinationtable1 WHERE
destinationtable1.columnA = sourcetable.column1
INSERT INTO destinationtable2 (columnY, columnZ) VALUES
(sourcetable.column2, @.table2id)
is that any clearer?|||yes, that is precisely the nature of this task. table1 is an imported
table from an outside system. i'm just splicing it into its logical,
normalized form.|||> what i would like to do, read [sourcetable] row-by-row. for each row,
i
would like to perform the following batch
Wrong. The idea is precisely to AVOID processing anything row-by-row.
Try this:
INSERT INTO destinationtable1 (columna)
SELECT DISTINCT column1
FROM sourcetable
WHERE NOT EXISTS
(SELECT *
FROM destinationtable1
WHERE columna = sourcetable.column1)
INSERT INTO destinationtable2 (columny, columnz)
SELECT S.column2, D.id
FROM sourcetable AS S
JOIN destinationtable1 AS D
ON S.column1 = D.columna
David Portas
SQL Server MVP
--

Need help with TSQL syntax and JOIN.

Hey all,
I'm having problems with a TSQL query, and I know someone out there can
help.
Note the following:
--
CREATE TABLE #temp
( ID int,
ParentID int,
Text varchar(50)
)
INSERT INTO #temp (ID, ParentID, Text) VALUES (1,null,'Top')
INSERT INTO #temp (ID, ParentID, Text) VALUES (2,1,'Middle')
INSERT INTO #temp (ID, ParentID, Text) VALUES (3,2,'Bottom')
SELECT t1.Text + ':' + t2.Text + ':' + t3.Text
FROM #temp t1
INNER JOIN #temp t2 ON t1.ID = t2.ParentID
INNER JOIN #temp t3 ON t2.ID = t3.ParentID
DROP TABLE #temp
--
This temp table allows me to define a series of Text values, and change them
together within a Parent-Child relationship. The select returns
"Top:Middle:Bottom", which is exactly what I want.
But, let's pretend that I have 5 more levels. to get the select statement
to work, I'd have to have 5 more inner joins. Let's say I had X more
levels -- how am I to know how many levels to continue to add to my SQL
statement?
Hopefully you can see the problem.
I want to write a SELECT statement that accomplishes the same thing (i.e.
concatenating the TEXT together for X number of parent-child relationships),
but without having to explicitly account for every possible level.
Does anyone know how I can accomplish this?
Thank you very much for your help!
WadeFigured out a way:
use tempdb
CREATE TABLE temptbl
( ID int,
ParentID int,
Text varchar(50)
)
GO
CREATE FUNCTION f_categoryPath
( @.iID int,
@.sText varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.sRetval varchar(8000),
@.sSlash char(1),
@.t_id int,
@.t_parentid int,
@.t_text varchar(50)
SET @.sSlash = ''
SET @.sRetval = ''
SELECT @.t_id = ID,
@.t_parentid = ParentID,
@.t_text = Text
FROM temptbl
WHERE ID = @.iID
set @.sRetVal = @.sSlash + @.t_text + @.sText
if @.t_parentid is not null
begin
set @.sRetval = dbo.f_categoryPath(@.t_parentid,@.sRetVal)
end
return @.sRetval
END
GO
INSERT INTO temptbl (ID, ParentID, Text) VALUES (1,null,'Top')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (2,1,'Middle')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (3,2,'Bottom1')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (4,3,'Bottom2')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (5,4,'Bottom3')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (6,5,'Bottom4')
INSERT INTO temptbl (ID, ParentID, Text) VALUES (7,6,'Bottom5')
SELECT dbo.f_categoryPath(7,'')
DROP TABLE temptbl
DROP FUNCTION f_categoryPath
--
thanks anyway!
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:epq8QZpIGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hey all,
> I'm having problems with a TSQL query, and I know someone out there can
> help.
> Note the following:
> --
> CREATE TABLE #temp
> ( ID int,
> ParentID int,
> Text varchar(50)
> )
> INSERT INTO #temp (ID, ParentID, Text) VALUES (1,null,'Top')
> INSERT INTO #temp (ID, ParentID, Text) VALUES (2,1,'Middle')
> INSERT INTO #temp (ID, ParentID, Text) VALUES (3,2,'Bottom')
> SELECT t1.Text + ':' + t2.Text + ':' + t3.Text
> FROM #temp t1
> INNER JOIN #temp t2 ON t1.ID = t2.ParentID
> INNER JOIN #temp t3 ON t2.ID = t3.ParentID
> DROP TABLE #temp
> --
> This temp table allows me to define a series of Text values, and change
> them together within a Parent-Child relationship. The select returns
> "Top:Middle:Bottom", which is exactly what I want.
> But, let's pretend that I have 5 more levels. to get the select statement
> to work, I'd have to have 5 more inner joins. Let's say I had X more
> levels -- how am I to know how many levels to continue to add to my SQL
> statement?
> Hopefully you can see the problem.
> I want to write a SELECT statement that accomplishes the same thing (i.e.
> concatenating the TEXT together for X number of parent-child
> relationships), but without having to explicitly account for every
> possible level.
> Does anyone know how I can accomplish this?
> Thank you very much for your help!
> Wade
>|||Have you gotten a copy of TREES & HIERARCHIES IN SQL yet? There are
several other ways to do this without procedural code at all.