A SQL / STored procedure question.

I have following two tables.

Table1 (Columns: ID, SessionID, URL) Note: URL Column currently is empty. Table1 has one record per SessionID.

Table2 (Columns: ID, SessionID, fromURL) Note: Table 2 has multiple records per Session ID.

So in essence, for one SessionID in Table1 there are multiple records in Table2.

Also the value in fromURL column in Table2 is unique for a SessionID.

What I want to do:

1). Select Table2.fromURL where Table1.SessionID = Table2.SessionID with DISTINCT Table2.fromURL.(I will get lot of records from the above statement)

2). For each record in step 1,Insert value from Table2.fromURL into Table1.URL where Table2.SessionID = Table1.SessionID

Phew.. difficult to explain.. i am sure.. easy to implement.Any help would be appreciated. Regards.

UPDATE Table1 SET URL = fromURL FROM Table2 WHERE Table2.SessionID = Table1.SessionID

a small problem

hi people,
my query goes something like this...
select a.*,b.*,c.* from table1 a ,table2 b,table3 c where a.col1 = b.col2
and a.col1 = c.col4 for xml auto, elements,base binary64
everything works fine except tht the elements of tables table2,table3 are
being added as child/nested nodes...instead of coming in the main flow..
ex- this is the output iam getting
<a>
<a-fld1>...</a-fld1>
<a-fld2>...</a-fld2>
<a-fld3>...</a-fld3>
<b>
<b-fld1>...</b-fld1>
<b-fld2>...</b-fld2>
<b-fld3>...</b-fld3>
<c>
<c-fld1>...</c-fld1>
<c-fld2>...</c-fld2>
<c-fld3>...</c-fld3>
</c>
</b
</a>
but i want only one top level & one child level...like
<a>
<a-fld1>...</a-fld1>
<a-fld2>...</a-fld2>
<a-fld3>...</a-fld3>
<b>
<b-fld1>...</b-fld1>
<b-fld2>...</b-fld2>
<b-fld3>...</b-fld3>
<c-fld1>...</c-fld1>
<c-fld2>...</c-fld2>
<c-fld3>...</c-fld3>
.....
.....
</b>
</a>....is it possible...please suggest metry Using XML Explicit|||xml explicit needs that all the select statements have the joining
columns...but my joining columns as not the same in all the tables as u can
see
its where a.col1 = b.col2
and a.col1 = c.col4 for xml auto, elements,base binary64
not
where a.col1 = b.col1
and a.col1 = c.col1 for xml auto, elements,base binary64...
so problem with it...atleast please tell me wether we can construct xml
from raw string in SQL Stored procedures...i will read all the neccessary
data and format it accordingly in xml
"Omnibuzz" wrote:

> try Using XML Explicit|||Can you post the DDL, insert script for some sample data and the expected
result for the sample data?
Also I presume u have SQL Server 2000 right?|||It's hard to come up with a good answer if we can't see the tables, so pleas
e
post DDL (and maybe sample data).
ML
http://milambda.blogspot.com/|||Anyways.. try this and let me know if this is what you wanted...
create table xmltbl1
(t1col1 int, t1col2 varchar(10))
create table xmltbl2
(t2col1 int, t2col2 varchar(10))
create table xmltbl3
(t3col1 int, t3col2 varchar(10),t3col3 int)
insert into xmltbl1 values (1,'abcde')
insert into xmltbl2 values (1,'fghij')
insert into xmltbl3 values (2,'klmno',1)
select a.*,b.* from xmltbl1 a,
(select b.* ,c.* from xmltbl2 b,xmltbl3 c
where b.t2col1 = c.t3col3) as b
where a.t1col1 = b.t2col1
for xml auto, elements,BINARY BASE64

A new @@Error question.

Here is what I really need to accomplish:
CREATE TABLE [dbo].[Table1] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare @.Error1 int
declare @.Error2 int
begin transaction
drop table Table1
select @.Error1 = @.@.Error
exec sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0
commit transaction
else
rollback transaction
Im not understanding why Table1 still gets dropped? It seems to me like
@.@.Error2 should not be 0, therefore a Rollback should occur? Why is @.@.Error2
not 0?
TIA,
ChrisR
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
@.@.ERROR returns the error number of the last Transact-SQL statement
executed. You need to also check the proc return code to determine whether
or not the rename succeeded:
declare @.Error1 int
declare @.Error2 int
declare @.ReturnCode int
begin transaction
drop table Table1x
select @.Error1 = @.@.Error
exec @.ReturnCode = sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0 AND @.ReturnCode = 0
commit transaction
else
rollback transaction
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A3F5C0A-9EC9-492D-9205-04031D36BFAA@.microsoft.com...
> Here is what I really need to accomplish:
> CREATE TABLE [dbo].[Table1] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table2] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare @.Error1 int
> declare @.Error2 int
> begin transaction
> drop table Table1
> select @.Error1 = @.@.Error
> exec sp_rename 'Table3','Table1'
> select @.Error2 = @.@.Error
> if @.Error1 = 0
> if @.Error2 = 0
> commit transaction
> else
> rollback transaction
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
> --
> TIA,
> ChrisR
|||Look at the procedure code. Then look at the documentation regarding the
raiserror function. Then look at the documentation for @.@.error. The
following demonstrates why this happens. As Dan suggested, you also need
to refer to the documentation of the procedure to know how to use it.
set nocount on
go
create procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror with following statement', @.sev, 20)
return 1
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
alter procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror, no following statement', @.sev, 20)
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
drop procedure xx_test
go

A new @@Error question.

Here is what I really need to accomplish:
CREATE TABLE [dbo].[Table1] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare @.Error1 int
declare @.Error2 int
begin transaction
drop table Table1
select @.Error1 = @.@.Error
exec sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0
commit transaction
else
rollback transaction
Im not understanding why Table1 still gets dropped? It seems to me like
@.@.Error2 should not be 0, therefore a Rollback should occur? Why is @.@.Error2
not 0?
--
TIA,
ChrisR> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
@.@.ERROR returns the error number of the last Transact-SQL statement
executed. You need to also check the proc return code to determine whether
or not the rename succeeded:
declare @.Error1 int
declare @.Error2 int
declare @.ReturnCode int
begin transaction
drop table Table1x
select @.Error1 = @.@.Error
exec @.ReturnCode = sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0 AND @.ReturnCode = 0
commit transaction
else
rollback transaction
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A3F5C0A-9EC9-492D-9205-04031D36BFAA@.microsoft.com...
> Here is what I really need to accomplish:
> CREATE TABLE [dbo].[Table1] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table2] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare @.Error1 int
> declare @.Error2 int
> begin transaction
> drop table Table1
> select @.Error1 = @.@.Error
> exec sp_rename 'Table3','Table1'
> select @.Error2 = @.@.Error
> if @.Error1 = 0
> if @.Error2 = 0
> commit transaction
> else
> rollback transaction
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
> --
> TIA,
> ChrisR|||Look at the procedure code. Then look at the documentation regarding the
raiserror function. Then look at the documentation for @.@.error. The
following demonstrates why this happens. As Dan suggested, you also need
to refer to the documentation of the procedure to know how to use it.
set nocount on
go
create procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror with following statement', @.sev, 20)
return 1
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
alter procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror, no following statement', @.sev, 20)
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
drop procedure xx_test
go