How to create simple insert trigger

I have just one table but need to create a trigger that takes place after an update on the Orders table. I need it to multiply two columns and populate the 3rd column (total cost) with the result as so:

Orders

ProductPrice ProductQuantity TotalCost
-- --
￡2.50 2
￡1.75 3
￡12.99 2

Can anyone please help me?You don't need a trigger, you need a computed column:

CREATE TABLE [dbo].[xxx](
[col_a] [int] NOT NULL default(2),
[col_b] [int] NOT NULL default(2),
[col_axb] as a * b,
) ON [PRIMARY]
GO|||Thank you for the help!

how to create rule which update other columns

Hi,

I have a table with the following columns:
ID INTEGEDR,
Name VARCHAR(32),
Surname VARCHAR(32),
GroupID INTEGER,
SubGroupOneID INTEGER,
SubGroupTwoID INTEGER

How can I create a rule/default/check which update SubGroupOneID &
SubGroupTwoID columns when GroupID for example is equal 15 on
MSSQL2000.

It is imposible to make changes on client, so I need to check
inserted/updated value of GroupID column and automaticly update
SubGroupOneID & SubGroupTwoID columns.

Sincerely,
Rustam BogubaevYou haven't explained what value(s) you want the subgroup columns updated
to.

If the two subgroup columns are solely determined by the the Groupid then
the answer is not to put those columns in the table at all because to do so
destroys normalisation in your schema. Put the subgroups in a separate,
related Groups table.

Rules and Check constraints don't actually change data - they just validate
it. Use a trigger update dependent columns when data is inserted or updated.
See CREATE TRIGGER in Books Online for details.

--
David Portas
----
Please reply only to the newsgroup
--|||You can do this in a trigger, assuming the primary key value is never
changed. For example:

CREATE TABLE MyTable
(
ID int
CONSTRAINT PK_MyTable PRIMARY KEY,
Name varchar(32),
Surname varchar(32),
GroupID int,
SubGroupOneID int,
SubGroupTwoID int
)
GO

CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE
AS
UPDATE t
SET
SubGroupOneID = 1,
SubGroupTwoID = 1
FROM MyTable t
JOIN inserted i ON t.ID = i.ID
WHERE i.GroupID = 15
GO

INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
'surname', 1)
INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
'surname', 2)
INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
'surname', 15)
SELECT * FROM MyTable
UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
SELECT * FROM MyTable
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rustam Bogubaev" <rbogubaev@.bookinturkey.com> wrote in message
news:20046852.0401202332.386aa33c@.posting.google.c om...
> Hi,
> I have a table with the following columns:
> ID INTEGEDR,
> Name VARCHAR(32),
> Surname VARCHAR(32),
> GroupID INTEGER,
> SubGroupOneID INTEGER,
> SubGroupTwoID INTEGER
> How can I create a rule/default/check which update SubGroupOneID &
> SubGroupTwoID columns when GroupID for example is equal 15 on
> MSSQL2000.
> It is imposible to make changes on client, so I need to check
> inserted/updated value of GroupID column and automaticly update
> SubGroupOneID & SubGroupTwoID columns.
> Sincerely,
> Rustam Bogubaev|||Hi dan

This problem was one I am grappling with, and despite its apparent
simplicity, is not touched on in any simple way in SQL books online, or
other 3rd party books, or rarely in Deja archives.

Could I impose on you a little more to elaborate on a couple of points in
this eample please ?

> For example:
> CREATE TABLE MyTable
> (
> ID int
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Name varchar(32),
> Surname varchar(32),
> GroupID int,
> SubGroupOneID int,
> SubGroupTwoID int
> )
> GO

Yup, I can handle that, lets make a table

> CREATE TRIGGER TR_MyTable
> ON MyTable FOR INSERT, UPDATE
> AS
> UPDATE t
> SET
> SubGroupOneID = 1,
> SubGroupTwoID = 1
> FROM MyTable t
> JOIN inserted i ON t.ID = i.ID
> WHERE i.GroupID = 15
> GO

Making a trigger i can handle, but ..
Bits that puzzle me
UPDATE t, can you explain the reason for and the use of 't' ?
Is that a temporary table where data is stored in the process?

the MyTable t bit, assuming t is a table, should that be MyTable, t -
joining two different tables ?

> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
> 'surname', 1)
> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
> 'surname', 2)
> INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
> 'surname', 15)
> SELECT * FROM MyTable
> UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
> SELECT * FROM MyTable
> GO
I presume this next bit is an alternative method, where we insert 3 records,
and then do a bulk update to make
the coumn GroupID = 15 when GroupID IN(1, 2) - i dont really understand the
"GroupID IN(1, 2)" logic. I cant even find the IN function using SQL Books
online, as it rarely gives me any usefull results from my inquiries :-)

The other bit that puzzles me is why the "SELECT * FROM MyTable" is needed.
Does the UPDATE row not process all records automatically when the GO is
encountered?

Many thanks for any help you can provide.

> "Rustam Bogubaev" <rbogubaev@.bookinturkey.com> wrote in message
> news:20046852.0401202332.386aa33c@.posting.google.c om...
> > Hi,
> > I have a table with the following columns:
> > ID INTEGEDR,
> > Name VARCHAR(32),
> > Surname VARCHAR(32),
> > GroupID INTEGER,
> > SubGroupOneID INTEGER,
> > SubGroupTwoID INTEGER
> > How can I create a rule/default/check which update SubGroupOneID &
> > SubGroupTwoID columns when GroupID for example is equal 15 on
> > MSSQL2000.
> > It is imposible to make changes on client, so I need to check
> > inserted/updated value of GroupID column and automaticly update
> > SubGroupOneID & SubGroupTwoID columns.
> > Sincerely,
> > Rustam Bogubaev|||> > CREATE TRIGGER TR_MyTable
> > ON MyTable FOR INSERT, UPDATE
> > AS
> > UPDATE t
> > SET
> > SubGroupOneID = 1,
> > SubGroupTwoID = 1
> > FROM MyTable t
> > JOIN inserted i ON t.ID = i.ID
> > WHERE i.GroupID = 15
> > GO
> Making a trigger i can handle, but ..
> Bits that puzzle me
> UPDATE t, can you explain the reason for and the use of 't' ?
> Is that a temporary table where data is stored in the process?
> the MyTable t bit, assuming t is a table, should that be MyTable, t -
> joining two different tables ?

The 't' is simply an alias declared for MyTable so that I didn't need to
specify the full table name when qualifying column names. The following is
functionally identical. Both examples join MyTable with the inserted table
in order to identify newly inserted or updated rows.

CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE
AS
UPDATE MyTable
SET
SubGroupOneID = 1,
SubGroupTwoID = 1
FROM inserted
WHERE MyTable.ID = inserted.ID AND
inserted.GroupID = 15
GO

> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(1,'name',
> > 'surname', 1)
> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(2,'name',
> > 'surname', 2)
> > INSERT INTO MyTable (ID, Name, SurName, GroupID) VALUES(3,'name',
> > 'surname', 15)
> > SELECT * FROM MyTable
> > UPDATE MyTable SET GroupID = 15 WHERE GroupID IN(1, 2)
> > SELECT * FROM MyTable
> > GO
> I presume this next bit is an alternative method, where we insert 3
records,
> and then do a bulk update to make
> the coumn GroupID = 15 when GroupID IN(1, 2) - i dont really understand
the
> "GroupID IN(1, 2)" logic. I cant even find the IN function using SQL Books
> online, as it rarely gives me any usefull results from my inquiries :-)

'GroupID IN(1, 2)' is equivalent to 'GroupID = 1 OR GroupID = 2'. You can
find details of 'IN' in the SQL 2000 Books Online. I was able to find the
BOL topic by clicking the index tab, typing 'IN' and double-clicking on the
'IN' keyword in the list. I then selected the 'IN' title from the topic
list.

> The other bit that puzzles me is why the "SELECT * FROM MyTable" is
needed.

The SELECT statements before and after the UPDATE are to display the data
before and after the UPDATE. These are only for illustration.

> Does the UPDATE row not process all records automatically when the GO is
> encountered?

GO is a batch separator. Tools like Query Analyzer execute the preceding
batch of SQL statements when a GO is encountered in the script. The insert,
update and select statements are executed sequentially as part of the same
batch.

--
Hope this helps.

Dan Guzman
SQL Server MVPsql

How to create reports dinamically?

Hello. Can I make a report for example with all the columns and make a
program in asp.net in which the user can select the columns he wants to see?
Thanks.Yes of course, unless RDL is a open Language you can stick your parts
together as the User wants. But I would keep in mind that there are already
thrid party tools to do that, therefore looking at these will eventually
save you time and money fordeveloping that on your own.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Luis Esteban Valencia" <levalencia@.avansoft.com> schrieb im Newsbeitrag
news:OZDxaTydFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Hello. Can I make a report for example with all the columns and make a
> program in asp.net in which the user can select the columns he wants to
> see?
> Thanks.
>|||Your page doesnt work
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> escribió
en el mensaje news:elyDct0dFHA.220@.TK2MSFTNGP12.phx.gbl...
> Yes of course, unless RDL is a open Language you can stick your parts
> together as the User wants. But I would keep in mind that there are
already
> thrid party tools to do that, therefore looking at these will eventually
> save you time and money fordeveloping that on your own.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Luis Esteban Valencia" <levalencia@.avansoft.com> schrieb im Newsbeitrag
> news:OZDxaTydFHA.1684@.TK2MSFTNGP09.phx.gbl...
> > Hello. Can I make a report for example with all the columns and make a
> > program in asp.net in which the user can select the columns he wants to
> > see?
> >
> > Thanks.
> >
> >
>sql

How to Create Index

If I run query mostly on column expressions, like I have two columns in table
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized?
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.
The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized?
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>
|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:

> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized?
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>
|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:

> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>
>
|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
[vbcol=seagreen]
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) =
'12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:

How to Create Index

If I run query mostly on column expressions, like I have two columns in table
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:
> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:
> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > If I run query mostly on column expressions, like I have two columns in
> > table
> > one for date and other is for time, when i want to build index, then i
> > build
> > index on both columns, but will these separate index work when i write
> > query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be
> > utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
> > If I run query mostly on column expressions, like I have two columns in table
> > one for date and other is for time, when i want to build index, then i build
> > index on both columns, but will these separate index work when i write query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
>> The expression is not sargable because you are applying functions to the
>> column value. If your data contains the default values for the time and
>> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
>> WHERE
>> saleDate = '20050101' AND
>> saleTime = '11:30:00'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Imran" <Imran@.discussions.microsoft.com> wrote in message
>> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>> > If I run query mostly on column expressions, like I have two columns in
>> > table
>> > one for date and other is for time, when i want to build index, then i
>> > build
>> > index on both columns, but will these separate index work when i write
>> > query
>> > with this expression,
>> > where convert(varchar(10),saleDate,121) + ' ' +
>> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
>> > both columns have data type of datetime.
>> > My question is, will the separate indexes of both columns will be
>> > utilized'
>> > Or what is the best practice, when you are searching on expressions in
>> > queries? not on just simply column.
>> >
>> >
>> >
>>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) ='12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:
> > The expression is not sargable because you are applying functions to the
> > column value. If your data contains the default values for the time and
> > date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> >
> > WHERE
> > saleDate = '20050101' AND
> > saleTime = '11:30:00'
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Imran" <Imran@.discussions.microsoft.com> wrote in message
> > news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > > If I run query mostly on column expressions, like I have two columns in
> > > table
> > > one for date and other is for time, when i want to build index, then i
> > > build
> > > index on both columns, but will these separate index work when i write
> > > query
> > > with this expression,
> > > where convert(varchar(10),saleDate,121) + ' ' +
> > > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > > both columns have data type of datetime.
> > > My question is, will the separate indexes of both columns will be
> > > utilized'
> > > Or what is the best practice, when you are searching on expressions in
> > > queries? not on just simply column.
> > >
> > >
> > >
> >
> >
> >sql

How to Create Index

If I run query mostly on column expressions, like I have two columns in tabl
e
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:

> If I run query mostly on column expressions, like I have two columns in ta
ble
> one for date and other is for time, when i want to build index, then i bui
ld
> index on both columns, but will these separate index work when i write que
ry
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized
'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on som
e
expression and when i provide same expression in query then sql server shoul
d
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of tha
t
expr
"Dan Guzman" wrote:

> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>
>|||sorry my question was, how to create index, as i want to create index on som
e
expression and when i provide same expression in query then sql server shoul
d
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of tha
t
expr
"SQL" wrote:
[vbcol=seagreen]
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
>|||You would have to create a computed column containing that expression and th
en index that computed
column. Or, create view with the expression and index the view (note that op
timizer will only use
indexes on view by itself if you have enterprise edition).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on s
ome
> expression and when i provide same expression in query then sql server sho
uld
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detec
t
> that if expression exists for given expression then i search in index of t
hat
> expr
>
> "Dan Guzman" wrote:
>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) =
'12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on s
ome
> expression and when i provide same expression in query then sql server sho
uld
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detec
t
> that if expression exists for given expression then i search in index of t
hat
> expr
> "Dan Guzman" wrote:
>

how to create dynamic columns in a temporary table

Hi there,
i have a requirement that a temporary table contains dynamic columns depending on where condition.

my actual table is like

Key Value X1 x X3 x X5 x Y1 y Y2 y

when user select x, the input variable passed to stored proc and the result is shown like

column names
X1 X3 X5 as column headers.

the select query is from temporary table.

these out put is based on the user selection. so the temporary table created with columns dynamically.

please help me out.
please let me know if you didn't understand.

thanks
Praveen.

Here the sample script,

Code Snippet

use tempdb

go

Create Table data (

[Key] Varchar(100) ,

[Value] Varchar(100)

);

Insert Into data Values('X1','x');

Insert Into data Values('X3','x');

Insert Into data Values('X5','x');

Insert Into data Values('Y1','y');

Insert Into data Values('Y2','y');

Code Snippet

create table #temp(dummy bit);

Declare @.Script as Varchar(8000);

Declare @.Script_prepare as Varchar(8000);

Set @.Script_prepare = 'Alter table #temp Add [?] varchar(100);'

Set @.Script = ''

Select

@.Script = @.Script + Replace(@.Script_prepare, '?', [Key])

From

data

Where

[Value] = 'X'

Exec (@.Script)

Alter table #temp drop column dummy;

Select * from #temp;

drop table #temp

|||Hi sekaran,
very nice and thanks alot.

but, i don't know that [key]. it's not static.
it is based on the result from a select query.
how to replace that '?' with the key, i need to write some other logic to get that key...

thanks
PRaveen.|||Can you post more information like result query & etc.?

How to create computed columns(dynamic) in matrix

Hello,
I am using matrix control for which the rows, columns are all dynamic(
based on the group i specify). I am able to get the Subtotal for both
the rows and colums, which is Great!!..
my problem is i need to add more columns(some formula columns) which
might have "% difference", "varience" etc..
the report should look something like this (M1, M2 are model_id's)
Div/Sec M1 M2 Variance % Difference
----
O3580 0.71 1.47 -0.76 -107.04
8040 1.33 1.33 0 0
8110 9.98 14.47 -4.49 -44.99
11210 5.44 6.36 -0.92 -16.91
and my query is :
select div_sec, model_ID, model_time from table1
Any help is greatly appreciated.
Thanks,
VenkatSeems like this is not possible with reporting services!!...
venkat.oar@.gmail.com wrote:
> Hello,
> I am using matrix control for which the rows, columns are all dynamic(
> based on the group i specify). I am able to get the Subtotal for both
> the rows and colums, which is Great!!..
> my problem is i need to add more columns(some formula columns) which
> might have "% difference", "varience" etc..
> the report should look something like this (M1, M2 are model_id's)
> Div/Sec M1 M2 Variance % Difference
> ----
> O3580 0.71 1.47 -0.76 -107.04
> 8040 1.33 1.33 0 0
> 8110 9.98 14.47 -4.49 -44.99
> 11210 5.44 6.36 -0.92 -16.91
> and my query is :
> select div_sec, model_ID, model_time from table1
> Any help is greatly appreciated.
> Thanks,
> Venkat|||Yes when you select datasets from view menu and right click on the datasets
window you can see a add option. click and you can add calculated fields.
Amarnath
"venkat.oar@.gmail.com" wrote:
> Seems like this is not possible with reporting services!!...
> venkat.oar@.gmail.com wrote:
> > Hello,
> >
> > I am using matrix control for which the rows, columns are all dynamic(
> > based on the group i specify). I am able to get the Subtotal for both
> > the rows and colums, which is Great!!..
> >
> > my problem is i need to add more columns(some formula columns) which
> > might have "% difference", "varience" etc..
> >
> > the report should look something like this (M1, M2 are model_id's)
> >
> > Div/Sec M1 M2 Variance % Difference
> > ----
> > O3580 0.71 1.47 -0.76 -107.04
> > 8040 1.33 1.33 0 0
> > 8110 9.98 14.47 -4.49 -44.99
> > 11210 5.44 6.36 -0.92 -16.91
> >
> > and my query is :
> >
> > select div_sec, model_ID, model_time from table1
> >
> > Any help is greatly appreciated.
> >
> > Thanks,
> > Venkat
>|||Sorry.. i think u got me wrong here. I am able to add calculated fields
but the data what i have is part of 2 rows.
here's a brief description for you.
I am using matrix control for which the rows, columns are all dynamic(
based on the group i specify). I am able to get the Subtotal for both
the rows and colums, which is Great!!..
my problem is i need to add more columns(some formula columns) which
might have "% difference", "varience" etc..
the report should look something like this (M1, M2 are model_id's)
Div/Sec M1 M2 Variance % Difference
----==AD--
O3580 0.71 1.47 -0.76 -107.04
8040 1.33 1.33 0 0
8110 9.98 14.47 -4.49 -44.99
11210 5.44 6.36 -0.92 -16.91
and my query is :
** *** READ BELOW QUERY..
select div_sec, model_ID, model_time from table1
the output of my query is below( seperated by commas)
div_sec, model_ID, model_time
O3580,M1,0.7
O3580,M2,1.47
8040 ,M1,1.33
8040 ,M2,1.33
8110 ,M1,9.98
8110 ,M2,14.47
11210 ,M1,5.44
11210 ,M2,6.36
Any help is greatly appreciated.
Thanks,
Venkat
Amarnath wrote:
> Yes when you select datasets from view menu and right click on the datase=ts
> window you can see a add option. click and you can add calculated fields.
> Amarnath
> "venkat.oar@.gmail.com" wrote:
> > Seems like this is not possible with reporting services!!...
> >
> > venkat.oar@.gmail.com wrote:
> > > Hello,
> > >
> > > I am using matrix control for which the rows, columns are all dynamic(
> > > based on the group i specify). I am able to get the Subtotal for both
> > > the rows and colums, which is Great!!..
> > >
> > > my problem is i need to add more columns(some formula columns) which
> > > might have "% difference", "varience" etc..
> > >
> > > the report should look something like this (M1, M2 are model_id's)
> > >
> > > Div/Sec M1 M2 Variance % Difference
> > > ---=--
> > > O3580 0.71 1.47 -0.76 -107.04
> > > 8040 1.33 1.33 0 0
> > > 8110 9.98 14.47 -4.49 -44.99
> > > 11210 5.44 6.36 -0.92 -16.91
> > >
> > > and my query is :
> > >
> > > select div_sec, model_ID, model_time from table1
> > >
> > > Any help is greatly appreciated.
> > > > > > Thanks,
> > > Venkat
> > > >|||Is there a way in this scenario to use the ReportItems!<textbox>.Value
syntax? Or, does it fall apart because it's a matrix?
venkat.oar@.gmail.com wrote:
> Sorry.. i think u got me wrong here. I am able to add calculated fields
> but the data what i have is part of 2 rows.
> here's a brief description for you.
>
> I am using matrix control for which the rows, columns are all dynamic(
> based on the group i specify). I am able to get the Subtotal for both
> the rows and colums, which is Great!!..
>
> my problem is i need to add more columns(some formula columns) which
> might have "% difference", "varience" etc..
>
> the report should look something like this (M1, M2 are model_id's)
>
> Div/Sec M1 M2 Variance % Difference
> ----­--
> O3580 0.71 1.47 -0.76 -107.04
> 8040 1.33 1.33 0 0
> 8110 9.98 14.47 -4.49 -44.99
> 11210 5.44 6.36 -0.92 -16.91
>
> and my query is :
> ** *** READ BELOW QUERY..
> select div_sec, model_ID, model_time from table1
> the output of my query is below( seperated by commas)
> div_sec, model_ID, model_time
> O3580,M1,0.7
> O3580,M2,1.47
> 8040 ,M1,1.33
> 8040 ,M2,1.33
> 8110 ,M1,9.98
> 8110 ,M2,14.47
> 11210 ,M1,5.44
> 11210 ,M2,6.36
> Any help is greatly appreciated.
>
> Thanks,
> Venkat
> Amarnath wrote:
>>Yes when you select datasets from view menu and right click on the datasets
>>window you can see a add option. click and you can add calculated fields.
>>Amarnath
>>"venkat.oar@.gmail.com" wrote:
>>
>>Seems like this is not possible with reporting services!!...
>>venkat.oar@.gmail.com wrote:
>>Hello,
>>I am using matrix control for which the rows, columns are all dynamic(
>>based on the group i specify). I am able to get the Subtotal for both
>>the rows and colums, which is Great!!..
>>my problem is i need to add more columns(some formula columns) which
>>might have "% difference", "varience" etc..
>>the report should look something like this (M1, M2 are model_id's)
>>Div/Sec M1 M2 Variance % Difference
>>----
>>O3580 0.71 1.47 -0.76 -107.04
>>8040 1.33 1.33 0 0
>>8110 9.98 14.47 -4.49 -44.99
>>11210 5.44 6.36 -0.92 -16.91
>>and my query is :
>>select div_sec, model_ID, model_time from table1
>>Any help is greatly appreciated.
>>Thanks,
>>Venkat
>>
>|||Mike,
I can't even go that far to use ReportItems!<txtbox>.value, the reason
is when i add column to the matrix, the columns appear for each column
it is created dynamically( in my case the columns appear for each
model_ID).
Still no clue how to do this or is it anyway possible?.
Michael Cervantes wrote:
> Is there a way in this scenario to use the ReportItems!<textbox>.Value
> syntax? Or, does it fall apart because it's a matrix?
>
> venkat.oar@.gmail.com wrote:
> > Sorry.. i think u got me wrong here. I am able to add calculated fields
> > but the data what i have is part of 2 rows.
> > here's a brief description for you.
> >
> >
> > I am using matrix control for which the rows, columns are all dynamic(
> > based on the group i specify). I am able to get the Subtotal for both
> > the rows and colums, which is Great!!..
> >
> >
> > my problem is i need to add more columns(some formula columns) which
> > might have "% difference", "varience" etc..
> >
> >
> > the report should look something like this (M1, M2 are model_id's)
> >
> >
> > Div/Sec M1 M2 Variance % Difference
> > ----=--=AD--
> >
> > O3580 0.71 1.47 -0.76 -107.04
> > 8040 1.33 1.33 0 0
> > 8110 9.98 14.47 -4.49 -44.99
> > 11210 5.44 6.36 -0.92 -16.91
> >
> >
> > and my query is :
> >
> > ** *** READ BELOW QUERY..
> > select div_sec, model_ID, model_time from table1
> > the output of my query is below( seperated by commas)
> > div_sec, model_ID, model_time
> > O3580,M1,0.7
> > O3580,M2,1.47
> > 8040 ,M1,1.33
> > 8040 ,M2,1.33
> > 8110 ,M1,9.98
> > 8110 ,M2,14.47
> > 11210 ,M1,5.44
> > 11210 ,M2,6.36
> >
> > Any help is greatly appreciated.
> >
> >
> > Thanks,
> > Venkat
> >
> > Amarnath wrote:
> >
> >>Yes when you select datasets from view menu and right click on the data=sets
> >>window you can see a add option. click and you can add calculated field=s=2E
> >>
> >>Amarnath
> >>
> >>"venkat.oar@.gmail.com" wrote:
> >>
> >>
> >>Seems like this is not possible with reporting services!!...
> >>
> >>venkat.oar@.gmail.com wrote:
> >>
> >>Hello,
> >>
> >>I am using matrix control for which the rows, columns are all dynamic(
> >>based on the group i specify). I am able to get the Subtotal for both
> >>the rows and colums, which is Great!!..
> >>
> >>my problem is i need to add more columns(some formula columns) which
> >>might have "% difference", "varience" etc..
> >>
> >>the report should look something like this (M1, M2 are model_id's)
> >>
> >>Div/Sec M1 M2 Variance % Difference
> >>---=--
> >>O3580 0.71 1.47 -0.76 -107.04
> >>8040 1.33 1.33 0 0
> >>8110 9.98 14.47 -4.49 -44.99
> >>11210 5.44 6.36 -0.92 -16.91
> >>
> >>and my query is :
> >>
> >>select div_sec, model_ID, model_time from table1
> >>
> >>Any help is greatly appreciated.
> >>
> >>Thanks,
> >>Venkat
> >>
> >>
> >|||Hi try getting all the required data from query, here is the query for that.
see if it works for you.. I think you can put this in table object in SSRS as
well.
select div_sec,
sum((case (model_ID) when 'M1' then (model_time) end)) M1,
sum((case (model_ID) when 'M2' then (model_time) end)) M2,
sum((case (model_ID) when 'M1' then (model_time) end)) - sum((case
(model_ID) when 'M2' then (model_time) end)) Variance
from #temp
group by div_sec
order by 1
You can replace #temp with your table and you can add more columns as well.
This is basically converting all vertical data to horizontal ie pivoting.
Amarnath
"venkat.oar@.gmail.com" wrote:
> Mike,
> I can't even go that far to use ReportItems!<txtbox>.value, the reason
> is when i add column to the matrix, the columns appear for each column
> it is created dynamically( in my case the columns appear for each
> model_ID).
> Still no clue how to do this or is it anyway possible?.
>
> Michael Cervantes wrote:
> > Is there a way in this scenario to use the ReportItems!<textbox>.Value
> > syntax? Or, does it fall apart because it's a matrix?
> >
> >
> >
> > venkat.oar@.gmail.com wrote:
> >
> > > Sorry.. i think u got me wrong here. I am able to add calculated fields
> > > but the data what i have is part of 2 rows.
> > > here's a brief description for you.
> > >
> > >
> > > I am using matrix control for which the rows, columns are all dynamic(
> > > based on the group i specify). I am able to get the Subtotal for both
> > > the rows and colums, which is Great!!..
> > >
> > >
> > > my problem is i need to add more columns(some formula columns) which
> > > might have "% difference", "varience" etc..
> > >
> > >
> > > the report should look something like this (M1, M2 are model_id's)
> > >
> > >
> > > Div/Sec M1 M2 Variance % Difference
> > > ----­--
> > >
> > > O3580 0.71 1.47 -0.76 -107.04
> > > 8040 1.33 1.33 0 0
> > > 8110 9.98 14.47 -4.49 -44.99
> > > 11210 5.44 6.36 -0.92 -16.91
> > >
> > >
> > > and my query is :
> > >
> > > ** *** READ BELOW QUERY..
> > > select div_sec, model_ID, model_time from table1
> > > the output of my query is below( seperated by commas)
> > > div_sec, model_ID, model_time
> > > O3580,M1,0.7
> > > O3580,M2,1.47
> > > 8040 ,M1,1.33
> > > 8040 ,M2,1.33
> > > 8110 ,M1,9.98
> > > 8110 ,M2,14.47
> > > 11210 ,M1,5.44
> > > 11210 ,M2,6.36
> > >
> > > Any help is greatly appreciated.
> > >
> > >
> > > Thanks,
> > > Venkat
> > >
> > > Amarnath wrote:
> > >
> > >>Yes when you select datasets from view menu and right click on the datasets
> > >>window you can see a add option. click and you can add calculated fields.
> > >>
> > >>Amarnath
> > >>
> > >>"venkat.oar@.gmail.com" wrote:
> > >>
> > >>
> > >>Seems like this is not possible with reporting services!!...
> > >>
> > >>venkat.oar@.gmail.com wrote:
> > >>
> > >>Hello,
> > >>
> > >>I am using matrix control for which the rows, columns are all dynamic(
> > >>based on the group i specify). I am able to get the Subtotal for both
> > >>the rows and colums, which is Great!!..
> > >>
> > >>my problem is i need to add more columns(some formula columns) which
> > >>might have "% difference", "varience" etc..
> > >>
> > >>the report should look something like this (M1, M2 are model_id's)
> > >>
> > >>Div/Sec M1 M2 Variance % Difference
> > >>----
> > >>O3580 0.71 1.47 -0.76 -107.04
> > >>8040 1.33 1.33 0 0
> > >>8110 9.98 14.47 -4.49 -44.99
> > >>11210 5.44 6.36 -0.92 -16.91
> > >>
> > >>and my query is :
> > >>
> > >>select div_sec, model_ID, model_time from table1
> > >>
> > >>Any help is greatly appreciated.
> > >>
> > >>Thanks,
> > >>Venkat
> > >>
> > >>
> > >
>|||Hi Venkat, my problem (as posted on 5th July, "Calculations in Matrix
report") is almost identical, so I'll let you know if a solution/workaround
can be found.
Pete
<venkat.oar@.gmail.com> wrote in message
news:1152882808.590984.275540@.i42g2000cwa.googlegroups.com...
Mike,
I can't even go that far to use ReportItems!<txtbox>.value, the reason
is when i add column to the matrix, the columns appear for each column
it is created dynamically( in my case the columns appear for each
model_ID).
Still no clue how to do this or is it anyway possible?.
Michael Cervantes wrote:
> Is there a way in this scenario to use the ReportItems!<textbox>.Value
> syntax? Or, does it fall apart because it's a matrix?
>
> venkat.oar@.gmail.com wrote:
> > Sorry.. i think u got me wrong here. I am able to add calculated fields
> > but the data what i have is part of 2 rows.
> > here's a brief description for you.
> >
> >
> > I am using matrix control for which the rows, columns are all dynamic(
> > based on the group i specify). I am able to get the Subtotal for both
> > the rows and colums, which is Great!!..
> >
> >
> > my problem is i need to add more columns(some formula columns) which
> > might have "% difference", "varience" etc..
> >
> >
> > the report should look something like this (M1, M2 are model_id's)
> >
> >
> > Div/Sec M1 M2 Variance % Difference
> > ----­--
> >
> > O3580 0.71 1.47 -0.76 -107.04
> > 8040 1.33 1.33 0 0
> > 8110 9.98 14.47 -4.49 -44.99
> > 11210 5.44 6.36 -0.92 -16.91
> >
> >
> > and my query is :
> >
> > ** *** READ BELOW QUERY..
> > select div_sec, model_ID, model_time from table1
> > the output of my query is below( seperated by commas)
> > div_sec, model_ID, model_time
> > O3580,M1,0.7
> > O3580,M2,1.47
> > 8040 ,M1,1.33
> > 8040 ,M2,1.33
> > 8110 ,M1,9.98
> > 8110 ,M2,14.47
> > 11210 ,M1,5.44
> > 11210 ,M2,6.36
> >
> > Any help is greatly appreciated.
> >
> >
> > Thanks,
> > Venkat
> >
> > Amarnath wrote:
> >
> >>Yes when you select datasets from view menu and right click on the
> >>datasets
> >>window you can see a add option. click and you can add calculated
> >>fields.
> >>
> >>Amarnath
> >>
> >>"venkat.oar@.gmail.com" wrote:
> >>
> >>
> >>Seems like this is not possible with reporting services!!...
> >>
> >>venkat.oar@.gmail.com wrote:
> >>
> >>Hello,
> >>
> >>I am using matrix control for which the rows, columns are all dynamic(
> >>based on the group i specify). I am able to get the Subtotal for both
> >>the rows and colums, which is Great!!..
> >>
> >>my problem is i need to add more columns(some formula columns) which
> >>might have "% difference", "varience" etc..
> >>
> >>the report should look something like this (M1, M2 are model_id's)
> >>
> >>Div/Sec M1 M2 Variance % Difference
> >>----
> >>O3580 0.71 1.47 -0.76 -107.04
> >>8040 1.33 1.33 0 0
> >>8110 9.98 14.47 -4.49 -44.99
> >>11210 5.44 6.36 -0.92 -16.91
> >>
> >>and my query is :
> >>
> >>select div_sec, model_ID, model_time from table1
> >>
> >>Any help is greatly appreciated.
> >>
> >>Thanks,
> >>Venkat
> >>
> >>
> >

how to create an copy of a certain record except one specific column that must be different &

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

try this:

insert into your_table ( user , field2 , field3 , ... )

select 'user2', field2 , field3, ....

from your_table

where user = 'user1'

|||

I WANTED TO AVOID SPECIFYING ALL THE COLUMNS SINCE THERE ARE MANY COLUMNS.

THANKS.

|||

Here is a little trick

in Query Analuzer Or SSMS press F8, this will display the Object Explorer

Drill down to the table that you need, click on the Columns folder (hold the button down) and drag it into the query window

Now all your columns will be listed in the query window, just exclude the one that you don;'t want

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Beautiful trick thanks a lot for sharing it

how to create an copy of a certain record except one specific column that must be different

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

try this:

insert into your_table ( user , field2 , field3 , ... )

select 'user2', field2 , field3, ....

from your_table

where user = 'user1'

|||

I WANTED TO AVOID SPECIFYING ALL THE COLUMNS SINCE THERE ARE MANY COLUMNS.

THANKS.

|||

Here is a little trick

in Query Analuzer Or SSMS press F8, this will display the Object Explorer

Drill down to the table that you need, click on the Columns folder (hold the button down) and drag it into the query window

Now all your columns will be listed in the query window, just exclude the one that you don;'t want

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Beautiful trick thanks a lot for sharing it

How to create an Audit Table

Can anyone help, I am able to create a trigger that will populate a audit table everytime one of my tables columns data changes, but I have an applications from another user that has a stored proceudre and when that is called from an application it hit the original table twice, so the audit table will get a duplicate entry. How do you prevent an AUDIT TABLE from inserting a duplicate entry
Here is my trigger:
Create TRIGGER tg_audit_task_order_awardees on xxx
for INSERT,UPDATE,DELETE as

INSERT INTO audit_task_order_awardees(
audit_log_type,
to_awardee,
solicitation_id,
contract_id,
order_number,
amount,
show_public,
audit_changedatetime,
audit_user)

Select
'OLD',
del.to_awardee,
del.solicitation_id,
del.contract_id,
del.order_number,
del.amount,
del.show_public,
getdate(),
del.modified_user
FROM deleted del

/* for a new record */

INSERT INTO audit_task_order_awardees(
audit_log_type,
to_awardee,
solicitation_id,
contract_id,
order_number,
amount,
show_public,
audit_changedatetime,
audit_user)
Select
'NEW',
ins.to_awardee,
ins.solicitation_id,
ins.contract_id,
ins.order_number,
ins.amount,
ins.show_public,
getdate(),
ins.modified_user
FROM inserted ins

Take a look at this article... offcourse there are other ways to do it, but this might give you some ideas to do it... I believe the only problem is it can't handle text and ntext fields:http://www.codeproject.com/database/AuditTriggers.asp

How to create a table (structure) based on a result of a stored procedure

Dear all,
There is a stored procedure that returns a result set consisting of some 30
columns of different types.
How can I automatically (i.e. not by means of CREATE TABLE statement) create
a table with a structure that correspons to the returned result set?
I mean something like:
SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
(The above unfortunately doesn't work).
To be specific: the stored procedure in question is:
sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
Any help would be greatly appreciated!
Thank you in advance.
Best regards,
AndrewAndrew
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
No , you have to know how many "columns" stored procedures will be returned
insert into tablename exec sp
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> There is a stored procedure that returns a result set consisting of some
> 30
> columns of different types.
> How can I automatically (i.e. not by means of CREATE TABLE statement)
> create
> a table with a structure that correspons to the returned result set?
> I mean something like:
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
> To be specific: the stored procedure in question is:
> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
> Any help would be greatly appreciated!
> Thank you in advance.
> Best regards,
> Andrew
>|||following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
sp_storedprocedure)
instead of select you can use INSERT INTO will work
eg:
create table #t(i int)
insert into #t exec myproc
create proc myproc
as
select 1
vinu
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> There is a stored procedure that returns a result set consisting of some
> 30
> columns of different types.
> How can I automatically (i.e. not by means of CREATE TABLE statement)
> create
> a table with a structure that correspons to the returned result set?
> I mean something like:
> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
> (The above unfortunately doesn't work).
> To be specific: the stored procedure in question is:
> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
> Any help would be greatly appreciated!
> Thank you in advance.
> Best regards,
> Andrew
>|||Dear Uri,
As I wrote, the idea is to create the table automatically, i.e. NOT to use
the CREATE TABLE statement.
Best regards,
Andrew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
> No , you have to know how many "columns" stored procedures will be
> returned
> insert into tablename exec sp
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of some
>> 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>|||As I wrote, the idea is to create the table automatically, i.e. NOT to use
the CREATE TABLE statement.
Best regards,
Andrew
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:%23GSuhRyAHHA.4672@.TK2MSFTNGP02.phx.gbl...
> following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
> sp_storedprocedure)
> instead of select you can use INSERT INTO will work
> eg:
> create table #t(i int)
> insert into #t exec myproc
>
> create proc myproc
> as
> select 1
>
> vinu
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of some
>> 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>|||Andrew
Well, in that case take a look into dynamic sql
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> As I wrote, the idea is to create the table automatically, i.e. NOT to use
> the CREATE TABLE statement.
> Best regards,
> Andrew
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of some
>> 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>|||Dear Uri,
Thank you for the hint. If I understand you correctly, you suggest that I
should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
That's OK, but how can I get (programmatically) the list of "columns" that
are returned by the stored procedure?
My basic question is
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
So, the situation is as follows: I have got a stored procedure and want to
put its result set into a new table.
Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
but instead of ExistingTableOrView there is a stored procedure.
Best regards,
Andrew
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Andrew
> Well, in that case take a look into dynamic sql
>
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of
>> some 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>>
>|||Andrew
--This procedure returns three or two columns from Orders Table depends on
parameter
create proc usp_test
@.orderid int =10248
as
if @.orderid=10248
select orderid,orderdate from orders where orderid=@.orderid
else
select orderid,orderdate,CustomerID from orders where orderid=@.orderid
--I use OPENROWSET command to run this SP and get the data out to temporary
table
select * into #t
from
openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
'set fmtonly off; exec usp_test 10249') as p
select * from #t
drop table #t
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Thank you for the hint. If I understand you correctly, you suggest that I
> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
> That's OK, but how can I get (programmatically) the list of "columns" that
> are returned by the stored procedure?
> My basic question is
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
> So, the situation is as follows: I have got a stored procedure and want to
> put its result set into a new table.
> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
> but instead of ExistingTableOrView there is a stored procedure.
> Best regards,
> Andrew
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of
>> some 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>>
>>
>
>|||Well .. it is not possible to use select into...exec
vt
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:OXdJJayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
> As I wrote, the idea is to create the table automatically, i.e. NOT to use
> the CREATE TABLE statement.
> Best regards,
> Andrew
>
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:%23GSuhRyAHHA.4672@.TK2MSFTNGP02.phx.gbl...
>> following statement will not work ..SELECT * INTO #xxx FROM (EXECUTE
>> sp_storedprocedure)
>> instead of select you can use INSERT INTO will work
>> eg:
>> create table #t(i int)
>> insert into #t exec myproc
>>
>> create proc myproc
>> as
>> select 1
>>
>> vinu
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of some
>> 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>|||Dear Uri,
Great! It works!
Thank you very much for your help!
Best regards,
Andrew
P.S. Isn't it a bit strange that we have to refer to our own SQL server via
'openrowset'?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
> Andrew
> --This procedure returns three or two columns from Orders Table depends
> on parameter
> create proc usp_test
> @.orderid int =10248
> as
> if @.orderid=10248
> select orderid,orderdate from orders where orderid=@.orderid
> else
> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
> --I use OPENROWSET command to run this SP and get the data out to
> temporary table
> select * into #t
> from
> openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
> 'set fmtonly off; exec usp_test 10249') as p
> select * from #t
> drop table #t
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for the hint. If I understand you correctly, you suggest that
>> I
>> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
>> That's OK, but how can I get (programmatically) the list of "columns"
>> that
>> are returned by the stored procedure?
>> My basic question is
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> So, the situation is as follows: I have got a stored procedure and want
>> to
>> put its result set into a new table.
>> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
>> but instead of ExistingTableOrView there is a stored procedure.
>> Best regards,
>> Andrew
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>> Dear all,
>> There is a stored procedure that returns a result set consisting of
>> some 30
>> columns of different types.
>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>> create
>> a table with a structure that correspons to the returned result set?
>> I mean something like:
>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>> (The above unfortunately doesn't work).
>> To be specific: the stored procedure in question is:
>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>> Any help would be greatly appreciated!
>> Thank you in advance.
>> Best regards,
>> Andrew
>>
>>
>>
>>
>>
>>
>|||> P.S. Isn't it a bit strange that we have to refer to our own SQL server
> via
> 'openrowset'?
:-)
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ezPsolzAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> Great! It works!
> Thank you very much for your help!
> Best regards,
> Andrew
> P.S. Isn't it a bit strange that we have to refer to our own SQL server
> via
> 'openrowset'?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> --This procedure returns three or two columns from Orders Table depends
>> on parameter
>> create proc usp_test
>> @.orderid int =10248
>> as
>> if @.orderid=10248
>> select orderid,orderdate from orders where orderid=@.orderid
>> else
>> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
>> --I use OPENROWSET command to run this SP and get the data out to
>> temporary table
>> select * into #t
>> from
>> openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
>> 'set fmtonly off; exec usp_test 10249') as p
>> select * from #t
>> drop table #t
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for the hint. If I understand you correctly, you suggest that
>> I
>> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
>> That's OK, but how can I get (programmatically) the list of "columns"
>> that
>> are returned by the stored procedure?
>> My basic question is
>>> How can I automatically (i.e. not by means of CREATE TABLE
>>> statement)
>>> create
>>> a table with a structure that correspons to the returned result set?
>> So, the situation is as follows: I have got a stored procedure and want
>> to
>> put its result set into a new table.
>> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
>> but instead of ExistingTableOrView there is a stored procedure.
>> Best regards,
>> Andrew
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>>> Dear all,
>>>
>>> There is a stored procedure that returns a result set consisting of
>>> some 30
>>> columns of different types.
>>> How can I automatically (i.e. not by means of CREATE TABLE
>>> statement)
>>> create
>>> a table with a structure that correspons to the returned result set?
>>>
>>> I mean something like:
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>>>
>>> To be specific: the stored procedure in question is:
>>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>>>
>>> Any help would be greatly appreciated!
>>> Thank you in advance.
>>>
>>> Best regards,
>>> Andrew
>>>
>>>
>>
>>
>>
>>
>>
>>
>
>|||> P.S. Isn't it a bit strange that we have to refer to our own SQL server via
> 'openrowset'?
OPENQUERY() is another option...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:ezPsolzAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Dear Uri,
> Great! It works!
> Thank you very much for your help!
> Best regards,
> Andrew
> P.S. Isn't it a bit strange that we have to refer to our own SQL server via
> 'openrowset'?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> --This procedure returns three or two columns from Orders Table depends
>> on parameter
>> create proc usp_test
>> @.orderid int =10248
>> as
>> if @.orderid=10248
>> select orderid,orderdate from orders where orderid=@.orderid
>> else
>> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
>> --I use OPENROWSET command to run this SP and get the data out to
>> temporary table
>> select * into #t
>> from
>> openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
>> 'set fmtonly off; exec usp_test 10249') as p
>> select * from #t
>> drop table #t
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for the hint. If I understand you correctly, you suggest that
>> I
>> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
>> That's OK, but how can I get (programmatically) the list of "columns"
>> that
>> are returned by the stored procedure?
>> My basic question is
>>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>>> create
>>> a table with a structure that correspons to the returned result set?
>> So, the situation is as follows: I have got a stored procedure and want
>> to
>> put its result set into a new table.
>> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
>> but instead of ExistingTableOrView there is a stored procedure.
>> Best regards,
>> Andrew
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>> Andrew
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>> No , you have to know how many "columns" stored procedures will be
>> returned
>> insert into tablename exec sp
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>>> Dear all,
>>>
>>> There is a stored procedure that returns a result set consisting of
>>> some 30
>>> columns of different types.
>>> How can I automatically (i.e. not by means of CREATE TABLE statement)
>>> create
>>> a table with a structure that correspons to the returned result set?
>>>
>>> I mean something like:
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>>>
>>> To be specific: the stored procedure in question is:
>>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>>>
>>> Any help would be greatly appreciated!
>>> Thank you in advance.
>>>
>>> Best regards,
>>> Andrew
>>>
>>>
>>
>>
>>
>>
>>
>>
>
>|||Tibor
I did not want to create a linked server that OPENQUERY required unlike
OPENROWSET when you can reference to the server without having a linked
server
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfQkCm0AHHA.1224@.TK2MSFTNGP04.phx.gbl...
>> P.S. Isn't it a bit strange that we have to refer to our own SQL server
>> via
>> 'openrowset'?
> OPENQUERY() is another option...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
> news:ezPsolzAHHA.3540@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> Great! It works!
>> Thank you very much for your help!
>> Best regards,
>> Andrew
>> P.S. Isn't it a bit strange that we have to refer to our own SQL server
>> via
>> 'openrowset'?
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OboMuXzAHHA.5060@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> --This procedure returns three or two columns from Orders Table
>> depends
>> on parameter
>> create proc usp_test
>> @.orderid int =10248
>> as
>> if @.orderid=10248
>> select orderid,orderdate from orders where orderid=@.orderid
>> else
>> select orderid,orderdate,CustomerID from orders where orderid=@.orderid
>> --I use OPENROWSET command to run this SP and get the data out to
>> temporary table
>> select * into #t
>> from
>> openrowset('SQLOLEDB','SERVER=NT_SQL_4;DATABASE=Northwind;UID=appdev;PWD=np,jho;',
>> 'set fmtonly off; exec usp_test 10249') as p
>> select * from #t
>> drop table #t
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:OzpHB2yAHHA.1220@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for the hint. If I understand you correctly, you suggest
>> that
>> I
>> should CREATE TABLE dynamically (EXEC ('CREATE TABLE...')).
>> That's OK, but how can I get (programmatically) the list of "columns"
>> that
>> are returned by the stored procedure?
>> My basic question is
>>> How can I automatically (i.e. not by means of CREATE TABLE
>>> statement)
>>> create
>>> a table with a structure that correspons to the returned result
>>> set?
>> So, the situation is as follows: I have got a stored procedure and want
>> to
>> put its result set into a new table.
>> Something like SELECT * INTO MyNewTable FROM ExistingTableOrView,
>> but instead of ExistingTableOrView there is a stored procedure.
>> Best regards,
>> Andrew
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23N7N5hyAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Andrew
>> Well, in that case take a look into dynamic sql
>>
>>
>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>> news:u7PBTayAHHA.4328@.TK2MSFTNGP03.phx.gbl...
>> Dear Uri,
>> As I wrote, the idea is to create the table automatically, i.e. NOT
>> to
>> use the CREATE TABLE statement.
>> Best regards,
>> Andrew
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:esBsGPyAHHA.3536@.TK2MSFTNGP03.phx.gbl...
>>> Andrew
>>>
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>>>
>>> No , you have to know how many "columns" stored procedures will be
>>> returned
>>>
>>> insert into tablename exec sp
>>>
>>>
>>> "Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
>>> news:ueWGWKyAHHA.1012@.TK2MSFTNGP04.phx.gbl...
>>> Dear all,
>>>
>>> There is a stored procedure that returns a result set consisting of
>>> some 30
>>> columns of different types.
>>> How can I automatically (i.e. not by means of CREATE TABLE
>>> statement)
>>> create
>>> a table with a structure that correspons to the returned result
>>> set?
>>>
>>> I mean something like:
>>> SELECT * INTO #xxx FROM (EXECUTE sp_storedprocedure)
>>> (The above unfortunately doesn't work).
>>>
>>> To be specific: the stored procedure in question is:
>>> sp_MSenum_replication_agents @.type = 4, @.exclude_anonymous = 1
>>>
>>> Any help would be greatly appreciated!
>>> Thank you in advance.
>>>
>>> Best regards,
>>> Andrew
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>>
>