How to create a View from temporary table ?

Hi all,
Iam aware that #Temporary tables can not be used in creation of views, but
iam in a situation where in iam needed to do so.
can anybody provide me with a workaround for this, its very urgent....any
help would be highly appreciated.
Thanks in advance,
Kishore> Iam aware that #Temporary tables can not be used in creation of views, but
> iam in a situation where in iam needed to do so.
Can you elaborate? Why do you need a temporary table and a permanent view
of that temporary table? This sounds like buying a house in Florida because
you're going there for spring break.|||Well, you can't do that.
So if that view that you want is something you will be using in all queries,
then select the required rows and insert into another temporary table and
use this assuming you are using a view.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uWPH%23AedGHA.3632@.TK2MSFTNGP05.phx.gbl...
> This sounds like buying a house in Florida because
> you're going there for spring break.
Aaron,
One of these days I am going to put together a compilation of these
analogies of yours. You have a particular knack for them.
I'm not being sarcastic at all. You always manage to come up with a
comparison that suits the situation perfectly.|||Hi,
Thanks for your prompt reply.
As asked by you, i will try to elaborate a bit more, please pardon me for
this lengthy explanation as i am in very urgent need of it, any help would b
e
highly appreciated.
My Business need requires that i need to create a table whose columns names
are taken from data contained in different tables, in essence iam creating a
table dynamically, which will have different number of columns in each run.
For efficeincey purposes, my customers earlier implementation uses Temporary
tables.
finally that dynamic #Temptable is filled up with data and a select
statement applied on it returns the needed data in XML format.
Then they came across the SQL Server row size limit of some 8060 bytes,
since each of the column in my dynamic table is of type NVARCHAR(255), at
maximum i can add 15 columns only to it. so instead of having only single
table, i decided to host those columns in multiple tables, i mean 1-15
columns will be added to #temptable-1 then 16-30 columns will be added to
#temptable-2 and so on....
so that problem has been addressed, but the output of the final select
statement that works on the dynamic #Temptable is creating problem...earlie
r
there was only one table, but now there can be 'n' number of tables dependin
g
on the number of columns to be added, so the XML resulting from the select
statement on these 'n' number of dynamic #Temptables is different from the
earlier on when there was only one dynamic #Temptable, but i need to maintai
n
that format due to some other dependencies.
so i thought of creating a single view(since a table can not hold all the
columns resulting from the select statement on those 'n' number of tables)
from all those #Temptables and then generate XML from that single
view...which will be similar in syntax to the earlier case when there was
only a single #Temptable.
Hope this explains...
Thanks & Regards,
Kishore
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you elaborate? Why do you need a temporary table and a permanent view
> of that temporary table? This sounds like buying a house in Florida becau
se
> you're going there for spring break.
>
>|||becuase of the SQL Server 2000 row size limit of 8060 bytes i can put all my
columns taked from other #Temptables into a new #Temptable to be used as a
view as suggested by you, Total size of all my columns exceeds tht limit so
table option is ruled out.
"Omnibuzz" wrote:

> Well, you can't do that.
> So if that view that you want is something you will be using in all querie
s,
> then select the required rows and insert into another temporary table and
> use this assuming you are using a view.|||Mr Aaron,
have you seen my explanation, can you suggest me anything.
-Kishore
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you elaborate? Why do you need a temporary table and a permanent view
> of that temporary table? This sounds like buying a house in Florida becau
se
> you're going there for spring break.
>
>sql

How to correctly update a table which values can be either inserted/updated/deleted on upd

Hi SQL fans,
I realized that I often encounter the same situation in a relation
database context, where I really don't know what to do. Here is an
example, where I have 2 tables as follow:
________________________
__________________ | PortfolioTitle
|
| Portfolio |
+---+
+--+ | tfolio_id (int)
|
| folio_id (int) |<<-PK--FK--| tfolio_idfolio (int)
|
| folio_name (varchar) | | tfolio_idtitle (int)
|--FK--PK->>[ Titles]
+--+ | tfolio_weight
(decimal(6,5)) |
+---+
Note that I also have a "Titles" tables (hence the tfolio_idtitle
link).
My problem is : When I update a portfolio, I must update all the
associated titles in it. That means that titles can be either removed
from the portfolio (a folio does not support the title anymore), added
to it (a new title is supported by the folio) or simply updated (a
title stays in the portfolio, but has its weight changed)
For example, if the portfolio #2 would contain :
[ PortfolioTitle ]
id | idFolio | idTitre | poids
1 2 1 10
2 2 2 20
3 2 3 30
and I must update the PortfolioTitle based on these values :
idFolio | idTitre | poids
2 2 20
2 3 35
2 4 40
then I should
1 ) remove the title #1 from the folio by deleting its entry in the
PortfolioTitle table
2 ) update the title #2 (weight from 30 to 35)
3 ) add the title #4 to the folio
For now, the only way I've found to do this is delete all the entries
of the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio = 2), and then insert new values for each entry based on the new given
values.
Is there a way to better manage this by detecting which value has to be
inserted/updated/deleted?
And this applies to many situation :(
If you need other examples, I can give you.
thanks a lot!
ibizaerrr...I realized the diagram is all messed up :\
here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||errr...I realized the diagram is all messed up :\
here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||On 16 Feb 2006 10:39:45 -0800, ibiza wrote:
>Hi SQL fans,
(snip)
>My problem is : When I update a portfolio, I must update all the
>associated titles in it. That means that titles can be either removed
>from the portfolio (a folio does not support the title anymore), added
>to it (a new title is supported by the folio) or simply updated (a
>title stays in the portfolio, but has its weight changed)
>For example, if the portfolio #2 would contain :
>[ PortfolioTitle ]
>id | idFolio | idTitre | poids
>1 2 1 10
>2 2 2 20
>3 2 3 30
>and I must update the PortfolioTitle based on these values :
>idFolio | idTitre | poids
>2 2 20
>2 3 35
>2 4 40
>then I should
>1 ) remove the title #1 from the folio by deleting its entry in the
>PortfolioTitle table
>2 ) update the title #2 (weight from 30 to 35)
>3 ) add the title #4 to the folio
Hi ibiza,
Deleting all rows, then re-inserting (as you do now) is one of the two
popular ways to acheive this. The second is (assuming that the new
values are stored in the table NewFolioTitles):
-- Step 1: DELETE rows that are no longer needed
DELETE FROM FolioTitles
WHERE NOT EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
-- Step 2: UPDATE rows that have been changed
UPDATE FolioTitles
SET poids = (SELECT poids
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
WHERE EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre
AND n.poids <> FolioTitles.poids)
-- Or, as an alternative, use the version below;
-- this is shorter and often faster, but uses
-- proprietary code and is therefor less portable
-- UPDATE f
-- SET poids = n.poids
-- FROM FolioTitles AS f
-- INNER JOIN NewFolioTitles AS n
-- ON n.idFolio = f.idFolio
-- AND n.idTitre = f.idTitre
-- WHERE n.poids <> f.poids
-- Step 3: INSERT new rows
INSERT INTO FolioTitles (idFolio, idTitre, poids)
SELECT n.idFolio, n.idTitre, i.poids
FROM NewFolioTitles AS n
LEFT JOIN FolioTitles AS f
ON f.idFolio = n.idFolio
AND f.idTitre = n.idTitre
WHERE f.idFolio IS NULL
This is just the basic outline - you should enclose it in a transaction
and add proper error handling.
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
thank you very much for your excellent reply.
Is there a big performance difference between the two methods? I guess
the only difference is that the identity numbers increment a lot
quicker with the first method of deleting a batch then reinserting a
batch, even if only a few records differ.
If I have to run this kind of update quite frequently (once/twice a
day, with thousands of records), what method would you suggest me?
Thanks again! :)
ibiza|||On 16 Feb 2006 12:54:19 -0800, ibiza wrote:
>Hi Hugo,
>thank you very much for your excellent reply.
>Is there a big performance difference between the two methods? I guess
>the only difference is that the identity numbers increment a lot
>quicker with the first method of deleting a batch then reinserting a
>batch, even if only a few records differ.
Hi ibiza,
Why would you even want to have an identity on the linking table
FolioTitles? Are there many other tables that refer to rows in this
table? I don't think so, since that would make deleting and re-inserting
rows a very bad idea!
If not, then stick to using only the natural key (FolioID + TitreID) and
leave the identity column out.
>If I have to run this kind of update quite frequently (once/twice a
>day, with thousands of records), what method would you suggest me?
If performance matters, than you should test both versions on your
hardware, with your data. That's the only way to get relevant data.
(My recommended test procedure is: clear cache; run one method a few
times, then clear cache again and run second method the same number of
times. Do thin on a test system that is as much as possible like your
production system. Compare average execution times).
--
Hugo Kornelis, SQL Server MVP|||ibiza (lambertb@.gmail.com) writes:
> thank you very much for your excellent reply.
> Is there a big performance difference between the two methods? I guess
> the only difference is that the identity numbers increment a lot
> quicker with the first method of deleting a batch then reinserting a
> batch, even if only a few records differ.
Why is there an IDENTITY columns at all? Surely a pair of
(foliotitle_idfolio, foliotitle_idtable) can only appear once in table?
Then this should be the primary key, and not any identity column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Yes, you are both right, I will remove it then.
I thought it was always a good idea to have an identity primary key on
any table.
Thanks for the tip!|||ibiza (lambertb@.gmail.com) writes:
> Yes, you are both right, I will remove it then.
> I thought it was always a good idea to have an identity primary key on
> any table.
If you ask some people, they will tell you that it is never a good idea!
Personally, I say it is a good idea when there is no useful natural key,
which often is the case for base entities: customers, financial
instruments, addresses etc. However for connection table, or tables
describing composed entities, articifical keys are usually not needed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

How to correctly update a table which values can be either inserted/updated/deleted on upd

Hi SQL fans,

I realized that I often encounter the same situation in a relation
database context, where I really don't know what to do. Here is an
example, where I have 2 tables as follow:

________________________
__________________ | PortfolioTitle
|
| Portfolio |
+------------+
+----------+ | tfolio_id (int)
|
| folio_id (int) |<<-PK--FK--| tfolio_idfolio (int)
|
| folio_name (varchar) | | tfolio_idtitle (int)
|--FK--PK->>[ Titles]
+----------+ | tfolio_weight
(decimal(6,5)) |

+-------------+

Note that I also have a "Titles" tables (hence the tfolio_idtitle
link).

My problem is : When I update a portfolio, I must update all the
associated titles in it. That means that titles can be either removed
from the portfolio (a folio does not support the title anymore), added
to it (a new title is supported by the folio) or simply updated (a
title stays in the portfolio, but has its weight changed)

For example, if the portfolio #2 would contain :

[ PortfolioTitle ]
id | idFolio | idTitre | poids
1 2 1 10
2 2 2 20
3 2 3 30

and I must update the PortfolioTitle based on these values :

idFolio | idTitre | poids
2 2 20
2 3 35
2 4 40

then I should
1 ) remove the title #1 from the folio by deleting its entry in the
PortfolioTitle table
2 ) update the title #2 (weight from 30 to 35)
3 ) add the title #4 to the folio

For now, the only way I've found to do this is delete all the entries
of the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =
2), and then insert new values for each entry based on the new given
values.

Is there a way to better manage this by detecting which value has to be
inserted/updated/deleted?

And this applies to many situation :(

If you need other examples, I can give you.

thanks a lot!

ibizaerrr...I realized the diagram is all messed up :\

here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||errr...I realized the diagram is all messed up :\

here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||On 16 Feb 2006 10:39:45 -0800, ibiza wrote:

>Hi SQL fans,
(snip)
>My problem is : When I update a portfolio, I must update all the
>associated titles in it. That means that titles can be either removed
>from the portfolio (a folio does not support the title anymore), added
>to it (a new title is supported by the folio) or simply updated (a
>title stays in the portfolio, but has its weight changed)
>For example, if the portfolio #2 would contain :
>[ PortfolioTitle ]
>id | idFolio | idTitre | poids
>1 2 1 10
>2 2 2 20
>3 2 3 30
>and I must update the PortfolioTitle based on these values :
>idFolio | idTitre | poids
>2 2 20
>2 3 35
>2 4 40
>then I should
>1 ) remove the title #1 from the folio by deleting its entry in the
>PortfolioTitle table
>2 ) update the title #2 (weight from 30 to 35)
>3 ) add the title #4 to the folio

Hi ibiza,

Deleting all rows, then re-inserting (as you do now) is one of the two
popular ways to acheive this. The second is (assuming that the new
values are stored in the table NewFolioTitles):

-- Step 1: DELETE rows that are no longer needed
DELETE FROM FolioTitles
WHERE NOT EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)

-- Step 2: UPDATE rows that have been changed
UPDATE FolioTitles
SET poids =
(SELECT poids
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
WHERE EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre
AND n.poids <> FolioTitles.poids)
-- Or, as an alternative, use the version below;
-- this is shorter and often faster, but uses
-- proprietary code and is therefor less portable
-- UPDATE f
-- SET poids = n.poids
-- FROM FolioTitles AS f
-- INNER JOIN NewFolioTitles AS n
-- ON n.idFolio = f.idFolio
-- AND n.idTitre = f.idTitre
-- WHERE n.poids <> f.poids

-- Step 3: INSERT new rows
INSERT INTO FolioTitles (idFolio, idTitre, poids)
SELECT n.idFolio, n.idTitre, i.poids
FROM NewFolioTitles AS n
LEFT JOIN FolioTitles AS f
ON f.idFolio = n.idFolio
AND f.idTitre = n.idTitre
WHERE f.idFolio IS NULL

This is just the basic outline - you should enclose it in a transaction
and add proper error handling.

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,

thank you very much for your excellent reply.
Is there a big performance difference between the two methods? I guess
the only difference is that the identity numbers increment a lot
quicker with the first method of deleting a batch then reinserting a
batch, even if only a few records differ.

If I have to run this kind of update quite frequently (once/twice a
day, with thousands of records), what method would you suggest me?

Thanks again! :)

ibiza|||On 16 Feb 2006 12:54:19 -0800, ibiza wrote:

>Hi Hugo,
>thank you very much for your excellent reply.
>Is there a big performance difference between the two methods? I guess
>the only difference is that the identity numbers increment a lot
>quicker with the first method of deleting a batch then reinserting a
>batch, even if only a few records differ.

Hi ibiza,

Why would you even want to have an identity on the linking table
FolioTitles? Are there many other tables that refer to rows in this
table? I don't think so, since that would make deleting and re-inserting
rows a very bad idea!

If not, then stick to using only the natural key (FolioID + TitreID) and
leave the identity column out.

>If I have to run this kind of update quite frequently (once/twice a
>day, with thousands of records), what method would you suggest me?

If performance matters, than you should test both versions on your
hardware, with your data. That's the only way to get relevant data.

(My recommended test procedure is: clear cache; run one method a few
times, then clear cache again and run second method the same number of
times. Do thin on a test system that is as much as possible like your
production system. Compare average execution times).

--
Hugo Kornelis, SQL Server MVP|||ibiza (lambertb@.gmail.com) writes:
> thank you very much for your excellent reply.
> Is there a big performance difference between the two methods? I guess
> the only difference is that the identity numbers increment a lot
> quicker with the first method of deleting a batch then reinserting a
> batch, even if only a few records differ.

Why is there an IDENTITY columns at all? Surely a pair of
(foliotitle_idfolio, foliotitle_idtable) can only appear once in table?
Then this should be the primary key, and not any identity column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, you are both right, I will remove it then.

I thought it was always a good idea to have an identity primary key on
any table.

Thanks for the tip!|||ibiza (lambertb@.gmail.com) writes:
> Yes, you are both right, I will remove it then.
> I thought it was always a good idea to have an identity primary key on
> any table.

If you ask some people, they will tell you that it is never a good idea!

Personally, I say it is a good idea when there is no useful natural key,
which often is the case for base entities: customers, financial
instruments, addresses etc. However for connection table, or tables
describing composed entities, articifical keys are usually not needed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to copy or restore an entire database(everything) from one server to another server no

I am in a situation where I have to copy an entire database(not only
data but alos storedprocedures etc like restoring a database) from one
server to another server from time to time. I could archive by
creating a backup file from one server, copy that to the another
server and then restore a database from the backup file. but the thing
is the backfile is too big to copy it over. Are there any scripts or
whatever I run to archive this?
Thank a lot in advance,See my reply in .DTS
--
Andrew J. Kelly SQL MVP
"Leon" <yootaeho@.yahoo.com> wrote in message
news:8a817d39.0409121712.7942afab@.posting.google.com...
> I am in a situation where I have to copy an entire database(not only
> data but alos storedprocedures etc like restoring a database) from one
> server to another server from time to time. I could archive by
> creating a backup file from one server, copy that to the another
> server and then restore a database from the backup file. but the thing
> is the backfile is too big to copy it over. Are there any scripts or
> whatever I run to archive this?
> Thank a lot in advance,|||"Leon" <yootaeho@.yahoo.com> wrote in message
news:8a817d39.0409121712.7942afab@.posting.google.com...
> I am in a situation where I have to copy an entire database(not only
> data but alos storedprocedures etc like restoring a database) from one
> server to another server from time to time. I could archive by
> creating a backup file from one server, copy that to the another
> server and then restore a database from the backup file. but the thing
> is the backfile is too big to copy it over. Are there any scripts or
> whatever I run to archive this?
Is Logshipping an option? That'll be smaller files usually.
> Thank a lot in advance,|||What about snapshot replication.
HTH
Saleem@.sqlnt.com
"Leon" wrote:
> I am in a situation where I have to copy an entire database(not only
> data but alos storedprocedures etc like restoring a database) from one
> server to another server from time to time. I could archive by
> creating a backup file from one server, copy that to the another
> server and then restore a database from the backup file. but the thing
> is the backfile is too big to copy it over. Are there any scripts or
> whatever I run to archive this?
> Thank a lot in advance,
>

How to copy db from test server to local machine running MSSQL2000 Per.

I am in a situation where I need to get a copy of test database that is
on production server running MSSQL 2000 Standard to my local machine
running MSSQL 2000 personel. I tried to use the copy wizard where it
appears I get connected to the source server OK but when I try to
indicate the destination server which is my local machine I get errors
poping up about cannot connect to (local) etc.

I am NOT a DBA just a programmer trying to get a local test environment
up to be more productive.

LsumnlerHi,

go to the sql client network utility on sql 2000 standard and add the
IP address of the sql 2000 personal.
Try connecting first thru query Analyzer if it connects, then go try
sql wizard.

Easy way is to backup the DB on sql 2000 standard and restore on sql
2000 personal.

Let me know what happens

RA.|||"len" <lsumnler@.gmail.com> wrote in message
news:1113927630.057592.236140@.o13g2000cwo.googlegr oups.com...
>I am in a situation where I need to get a copy of test database that is
> on production server running MSSQL 2000 Standard to my local machine
> running MSSQL 2000 personel. I tried to use the copy wizard where it
> appears I get connected to the source server OK but when I try to
> indicate the destination server which is my local machine I get errors
> poping up about cannot connect to (local) etc.
> I am NOT a DBA just a programmer trying to get a local test environment
> up to be more productive.
> Lsumnler

See this KB article - there are a number of requirements for the wizard to
work correctly:

http://support.microsoft.com/defaul...kb;en-us;274463

If possible, you might find that backup/restore is an easier option:

http://support.microsoft.com/defaul...kb;en-us;314546

Simon