OUTPUT Clause

We have a table, which is partitioned on a computed column, based on a UDF function (custom logic). We populate this computed column in an INSTEAD OF Trigger. But when we try to "output" the computed column using OUTPUT caluse, NULL values are returned for computed column. SQL BOL is not clear on this part of using output clause in instead of triggers. This does not even work for a table that is not partitioned. Here is an example of what we are trying to do...

I would appreciate any help on this...

IF OBJECT_ID('dbo.Test') IS NOT NULL

DROP TABLE dbo.Test

GO

CREATE TABLE dbo.Test

( IdCol INT IDENTITY(1,1) NOT NULL,

Date DATETIME NOT NULL,

CalcCol CHAR(10) NOT NULL

)

GO

CREATE TRIGGER trgTest_InsUpd ON dbo.Test

INSTEAD OF INSERT AS

BEGIN

INSERT INTO dbo.Test

( Date, CalcCol ) SELECT Date, CONVERT(CHAR(10), Date, 101) FROM INSERTED

END

GO

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

DROP TABLE #tmp

GO

CREATE TABLE #tmp(IdCol INT, Date DATETIME, CalcCol VARCHAR(10))

TRUNCATE TABLE #tmp

INSERT INTO dbo.Test(Date) OUTPUT INSERTED.IdCol, INSERTED.Date, INSERTED.CalcCol INTO #tmp

VALUES(GETDATE())

--Here the identity and Computed column are returned as NULL

SELECT * FROM #tmp

SELECT * FROM dbo.Test

Instead of trigger would not have data in INSERTED and DELETED tables, because instead of the original insert statement your INSTEAD OF tRIGGER is getting fired. The tables will be populated if you write a AFTER TRIGGER.

OUTER JOIN with multiple tables and a plus sign?

I am trying to select specific columns from multiple tables based on a
common identifier found in each table.

For example, the three tables:

PUBACC_AC
PUBACC_AM
PUBACC_AN

each have a common column:

PUBACC_AC.unique_system_identifier
PUBACC_AM.unique_system_identifier
PUBACC_AN.unique_system_identifier

What I am trying to select, for example:

PUBACC_AC.name
PUBACC_AM.phone_number
PUBACC_AN.zip

where the TABLE.unique_system_identifier is common.

For example:

--------------
PUBACC_AC
=========
unique_system_identifier name
1234 JONES

--------------
PUBACC_AM
=========
unique_system_identifier phone_number
1234 555-1212

--------------
PUBACC_AN
=========
unique_system_identifier zip
1234 90210

When I run my query, I would like to see the following returned as one
blob, rather than the separate tables:

--------------------
unique_system_identifier name phone_number zip
1234 JONES 555-1212 90210
--------------------

I think this is an OUTER JOIN? I see examples on the net using a plus
sign, with mention of Oracle. I'm not running Oracle...I am using
Microsoft SQL Server 2000.

Help, please?

P. S. Will this work with several tables? I actually have about 15
tables in this mess, but I tried to keep it simple (!??!) for the above
example.

Thanks in advance for your help!

NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
ADDRESS.

Who actually BUYS the cr@.p that the spammers advertise, anyhow?!!!
(Rhetorical question only.)You do the following:

SELECT PUBACC_AC.Name + PUBACC_AM.Phone_number + PUBACC_AN.zip
FROM PUBACC_AC
INNER JOIN PUBACC_AM
ON PUBACC_AM.unique_col = PUBACC_AC.unique_col
INNER JOIN PUBACC_AN
ON PUBACC_AN.unique_col = PUBACC_AC.unique_col

That's it - you do inner join, if you want only those records, for which
unique_col value exists in all 3 tables.
Or, if you replace "INNER JOIN" with "FULL JOIN", which is the same as OUTER
JOIN in Oracle, you will get
as meny records as number of unique_col values.

Thats it!

Hope it helped,
Andrey aka Muzzy

"TeleTech1212" <tele_tech1212DELETE_THIS@.yahoo.com> wrote in message
news:Xns9556E99BEE9B4teletech1212DELETETH@.207.115. 63.158...
> I am trying to select specific columns from multiple tables based on a
> common identifier found in each table.
> For example, the three tables:
> PUBACC_AC
> PUBACC_AM
> PUBACC_AN
> each have a common column:
> PUBACC_AC.unique_system_identifier
> PUBACC_AM.unique_system_identifier
> PUBACC_AN.unique_system_identifier
>
> What I am trying to select, for example:
> PUBACC_AC.name
> PUBACC_AM.phone_number
> PUBACC_AN.zip
> where the TABLE.unique_system_identifier is common.
>
> For example:
> --------------
> PUBACC_AC
> =========
> unique_system_identifier name
> 1234 JONES
> --------------
> PUBACC_AM
> =========
> unique_system_identifier phone_number
> 1234 555-1212
> --------------
> PUBACC_AN
> =========
> unique_system_identifier zip
> 1234 90210
>
> When I run my query, I would like to see the following returned as one
> blob, rather than the separate tables:
> --------------------
> unique_system_identifier name phone_number zip
> 1234 JONES 555-1212 90210
> --------------------
>
> I think this is an OUTER JOIN? I see examples on the net using a plus
> sign, with mention of Oracle. I'm not running Oracle...I am using
> Microsoft SQL Server 2000.
> Help, please?
> P. S. Will this work with several tables? I actually have about 15
> tables in this mess, but I tried to keep it simple (!??!) for the above
> example.
> Thanks in advance for your help!
> NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
> ADDRESS.
> Who actually BUYS the cr@.p that the spammers advertise, anyhow?!!!
> (Rhetorical question only.)

OT: What news reader do you use

I have been out of the Usenet groups for some time. I have been hiding
on the DotNetNuke forum on ASP.Net.
I am impressed with the google web based reader but was wondering what
others are using these days.
BertOutlook Express
--
Keith
"bert" <bertcord@.gmail.com> wrote in message
news:1107354025.448962.275710@.z14g2000cwz.googlegroups.com...
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||I use Mozilla Thunderbird 1.0.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
bert wrote:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||Outlook Express.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"bert" <bertcord@.gmail.com> wrote in message
news:1107354025.448962.275710@.z14g2000cwz.googlegroups.com...
>I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||Outlook Express and Google
"bert" <bertcord@.gmail.com> wrote in message
news:1107354025.448962.275710@.z14g2000cwz.googlegroups.com...
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||"bert" <bertcord@.gmail.com> wrote in news:1107354025.448962.275710
@.z14g2000cwz.googlegroups.com:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>
Xnews.|||Hi Bert,
I use Agent. (Version 1.91, to be specific)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you like the Google Web interface than you may want to try
www.sqlmonster.com. In my opinion it is more convenient than Google.
--
Message posted via http://www.sqlmonster.com|||I am probably one of the few users that uses Netscape 4.79.
Gert-Jan
Hugo Kornelis wrote:
> Hi Bert,
> I use Agent. (Version 1.91, to be specific)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||bert,
Why not look through the headers of all the posts in this group? You
will then get a pretty good idea what people use. What you will notice
is that quite a few people post through the microsoft website.
For example on my postings, you will usually see something like this in
the headers:
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
For Tibor you will see this:
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
For Celko you will see he uses Google:
User-Agent: G2/0.2
For website users you will see this:
X-Newsreader: Microsoft CDO for Windows 2000
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
bert wrote:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||Gnus.
--
Galen Boyer

OT: What news reader do you use

I have been out of the Usenet groups for some time. I have been hiding
on the DotNetNuke forum on ASP.Net.
I am impressed with the google web based reader but was wondering what
others are using these days.
Bert
I use Mozilla Thunderbird 1.0.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
bert wrote:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>
|||"bert" <bertcord@.gmail.com> wrote in news:1107354025.448962.275710
@.z14g2000cwz.googlegroups.com:

> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>
Xnews.
|||Hi Bert,
I use Agent. (Version 1.91, to be specific)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||If you like the Google Web interface than you may want to try
www.sqlmonster.com. In my opinion it is more convenient than Google.
Message posted via http://www.sqlmonster.com
|||I am probably one of the few users that uses Netscape 4.79.
Gert-Jan
Hugo Kornelis wrote:
> Hi Bert,
> I use Agent. (Version 1.91, to be specific)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||bert,
Why not look through the headers of all the posts in this group? You
will then get a pretty good idea what people use. What you will notice
is that quite a few people post through the microsoft website.
For example on my postings, you will usually see something like this in
the headers:
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
For Tibor you will see this:
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
For Celko you will see he uses Google:
User-Agent: G2/0.2
For website users you will see this:
X-Newsreader: Microsoft CDO for Windows 2000
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
bert wrote:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>
|||Gnus.
Galen Boyer

OT: What news reader do you use

I have been out of the Usenet groups for some time. I have been hiding
on the DotNetNuke forum on ASP.Net.
I am impressed with the google web based reader but was wondering what
others are using these days.
BertI use Mozilla Thunderbird 1.0.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
bert wrote:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||"bert" <bertcord@.gmail.com> wrote in news:1107354025.448962.275710
@.z14g2000cwz.googlegroups.com:

> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>
Xnews.|||Hi Bert,
I use Agent. (Version 1.91, to be specific)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you like the Google Web interface than you may want to try
www.droptable.com. In my opinion it is more convenient than Google.
Message posted via http://www.droptable.com|||I am probably one of the few users that uses netscape 4.79.
Gert-Jan
Hugo Kornelis wrote:
> Hi Bert,
> I use Agent. (Version 1.91, to be specific)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||bert,
Why not look through the headers of all the posts in this group? You
will then get a pretty good idea what people use. What you will notice
is that quite a few people post through the microsoft website.
For example on my postings, you will usually see something like this in
the headers:
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
For Tibor you will see this:
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
For Celko you will see he uses Google:
User-Agent: G2/0.2
For website users you will see this:
X-Newsreader: Microsoft CDO for Windows 2000
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
bert wrote:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||Gnus.
--
Galen Boyer