'not a valid Microsoft Tape Format backup set' error when trying to restore DB

Hello
I have a point in time Microsoft SQL Server 2000 .mdf data
file which was taken from a snapshot. Basically, it is the
database at a point in time, which I can attach to and
view data no problem in a sql server.
I also have hourly transaction logs which occured after
the above .mdf data file snapshot. I would like to apply
those transaction log files to the .mdf data file. I've
tried many many varieties, but seem to get farthest with
the following code:
ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf '
WITH NORECOVERY
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_7.00.00__JDE_PRODUCTION.trb'
WITH NORECOVERY
[...MORE RESTORE HOURLY LOG STATEMENTS...]
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_13.00.01__JDE_PRODUCTION.trb'
WITH RECOVERY
/****end****/
My problem seems to be the following error:
Server: Msg 3242, Level 16, State 1, Line 2
The file on device 'F:\mssql7
\jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
Microsoft Tape Format backup set.
Can't seem to get around the fact that Microsoft is
looking for the restore database file as a Tape format,
which it isn't, it's a .mdf database file.
Any clues?
Thanks in advance...
Jeff
You can't RESTORE from an 'mdf' file because a RESTORE command expects a
file created with BACKUP. The backup file will be in MTF format regardless
of the underlying media type.
The proper way to accomplish your task is to start with a full backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <jblanding@.nwpasta.com> wrote in message
news:76fd01c47672$4443f470$a401280a@.phx.gbl...
> Hello
> I have a point in time Microsoft SQL Server 2000 .mdf data
> file which was taken from a snapshot. Basically, it is the
> database at a point in time, which I can attach to and
> view data no problem in a sql server.
> I also have hourly transaction logs which occured after
> the above .mdf data file snapshot. I would like to apply
> those transaction log files to the .mdf data file. I've
> tried many many varieties, but seem to get farthest with
> the following code:
> ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
> ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf '
> WITH NORECOVERY
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_7.00.00__JDE_PRODUCTION.trb'
> WITH NORECOVERY
> [...MORE RESTORE HOURLY LOG STATEMENTS...]
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_13.00.01__JDE_PRODUCTION.trb'
> WITH RECOVERY
> /****end****/
> My problem seems to be the following error:
> Server: Msg 3242, Level 16, State 1, Line 2
> The file on device 'F:\mssql7
> \jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
> Microsoft Tape Format backup set.
> Can't seem to get around the fact that Microsoft is
> looking for the restore database file as a Tape format,
> which it isn't, it's a .mdf database file.
> Any clues?
> Thanks in advance...
> Jeff
sql

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

Hello
I have a point in time Microsoft SQL Server 2000 .mdf data
file which was taken from a snapshot. Basically, it is the
database at a point in time, which I can attach to and
view data no problem in a sql server.
I also have hourly transaction logs which occured after
the above .mdf data file snapshot. I would like to apply
those transaction log files to the .mdf data file. I've
tried many many varieties, but seem to get farthest with
the following code:
ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONda
ta_new.mdf'
WITH NORECOVERY
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_7.00.00__JDE_PRODUCTION.trb'
WITH NORECOVERY
[...MORE RESTORE HOURLY LOG STATEMENTS...]
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_13.00.01__JDE_PRODUCTION.trb'
WITH RECOVERY
/****end****/
My problem seems to be the following error:
Server: Msg 3242, Level 16, State 1, Line 2
The file on device 'F:\mssql7
\jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
Microsoft Tape Format backup set.
Can't seem to get around the fact that Microsoft is
looking for the restore database file as a Tape format,
which it isn't, it's a .mdf database file.
Any clues?
Thanks in advance...
JeffYou can't RESTORE from an 'mdf' file because a RESTORE command expects a
file created with BACKUP. The backup file will be in MTF format regardless
of the underlying media type.
The proper way to accomplish your task is to start with a full backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <jblanding@.nwpasta.com> wrote in message
news:76fd01c47672$4443f470$a401280a@.phx.gbl...
> Hello
> I have a point in time Microsoft SQL Server 2000 .mdf data
> file which was taken from a snapshot. Basically, it is the
> database at a point in time, which I can attach to and
> view data no problem in a sql server.
> I also have hourly transaction logs which occured after
> the above .mdf data file snapshot. I would like to apply
> those transaction log files to the .mdf data file. I've
> tried many many varieties, but seem to get farthest with
> the following code:
> ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
> ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONda
ta_new.mdf'
> WITH NORECOVERY
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_7.00.00__JDE_PRODUCTION.trb'
> WITH NORECOVERY
> [...MORE RESTORE HOURLY LOG STATEMENTS...]
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_13.00.01__JDE_PRODUCTION.trb'
> WITH RECOVERY
> /****end****/
> My problem seems to be the following error:
> Server: Msg 3242, Level 16, State 1, Line 2
> The file on device 'F:\mssql7
> \jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
> Microsoft Tape Format backup set.
> Can't seem to get around the fact that Microsoft is
> looking for the restore database file as a Tape format,
> which it isn't, it's a .mdf database file.
> Any clues?
> Thanks in advance...
> Jeff

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

Hello
I have a point in time Microsoft SQL Server 2000 .mdf data
file which was taken from a snapshot. Basically, it is the
database at a point in time, which I can attach to and
view data no problem in a sql server.
I also have hourly transaction logs which occured after
the above .mdf data file snapshot. I would like to apply
those transaction log files to the .mdf data file. I've
tried many many varieties, but seem to get farthest with
the following code:
ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf'
WITH NORECOVERY
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_7.00.00__JDE_PRODUCTION.trb'
WITH NORECOVERY
[...MORE RESTORE HOURLY LOG STATEMENTS...]
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_13.00.01__JDE_PRODUCTION.trb'
WITH RECOVERY
/****end****/
My problem seems to be the following error:
Server: Msg 3242, Level 16, State 1, Line 2
The file on device 'F:\mssql7
\jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
Microsoft Tape Format backup set.
Can't seem to get around the fact that Microsoft is
looking for the restore database file as a Tape format,
which it isn't, it's a .mdf database file.
Any clues?
Thanks in advance...
JeffYou can't RESTORE from an 'mdf' file because a RESTORE command expects a
file created with BACKUP. The backup file will be in MTF format regardless
of the underlying media type.
The proper way to accomplish your task is to start with a full backup.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <jblanding@.nwpasta.com> wrote in message
news:76fd01c47672$4443f470$a401280a@.phx.gbl...
> Hello
> I have a point in time Microsoft SQL Server 2000 .mdf data
> file which was taken from a snapshot. Basically, it is the
> database at a point in time, which I can attach to and
> view data no problem in a sql server.
> I also have hourly transaction logs which occured after
> the above .mdf data file snapshot. I would like to apply
> those transaction log files to the .mdf data file. I've
> tried many many varieties, but seem to get farthest with
> the following code:
> ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
> ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf'
> WITH NORECOVERY
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_7.00.00__JDE_PRODUCTION.trb'
> WITH NORECOVERY
> [...MORE RESTORE HOURLY LOG STATEMENTS...]
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_13.00.01__JDE_PRODUCTION.trb'
> WITH RECOVERY
> /****end****/
> My problem seems to be the following error:
> Server: Msg 3242, Level 16, State 1, Line 2
> The file on device 'F:\mssql7
> \jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
> Microsoft Tape Format backup set.
> Can't seem to get around the fact that Microsoft is
> looking for the restore database file as a Tape format,
> which it isn't, it's a .mdf database file.
> Any clues?
> Thanks in advance...
> Jeff

Normalizing sub-categories?

Hi,
I'm designing a database, but am having a problem with categories and sub
categories.
Basically we have documents. Each document has a category, and a sub
category. The selection of sub category is dependant on which category is
selected (so that you cannot select a sub category that does not relate to
its category).
If anyone has any ideas, I would be all ears.
Many thanks in advance.
AndyIf you have a separate table with documents on you can defin a Check
Constraint to check wheter the inserted combination exists in this table. Or
you use Foreign Key Constraint as follows:
CREATE TABLE [dbo].[Tab_Categories] (
[CategoryID] [char] (10),
[SubCategoryID] [char] (10)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Tab_Docs] (
[DocID] [char] (10) ,
[CategoryID] [char] (10) ,
[SubcategoryID] [char] (10)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tab_Categories] ADD
CONSTRAINT [PK_Tab_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID],
[SubCategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tab_Docs] ADD
CONSTRAINT [PK_Docs] PRIMARY KEY CLUSTERED
(
[DocID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tab_Docs] ADD
CONSTRAINT [FK_Docs_Tab_Categories] FOREIGN KEY
(
[CategoryID],
[SubcategoryID]
) REFERENCES [dbo].[Tab_Categories] (
[CategoryID],
[SubCategoryID]
)
GO
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Andy" <Andy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:25C8585D-3986-4DC6-9997-160B27BB49CC@.microsoft.com...
> Hi,
> I'm designing a database, but am having a problem with categories and sub
> categories.
> Basically we have documents. Each document has a category, and a sub
> category. The selection of sub category is dependant on which category is
> selected (so that you cannot select a sub category that does not relate to
> its category).
> If anyone has any ideas, I would be all ears.
> Many thanks in advance.
> Andy|||To model this properly you need 3 tables (additional columns omitted for
clarity):
CREATE TABLE Categories (
Category_name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Categories
PRIMARY KEY (Category_name)
)
CREATE TABLE Sub_categories (
Sub_category_name VARCHAR(50) NOT NULL,
Category_name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Sub_categories
PRIMARY KEY (Sub_category_name, Category_name),
CONSTRAINT FK_Sub_categories__Categories
FOREIGN KEY (Category_name)
REFERENCES Categories (Category_name)
)
CREATE TABLE Documents (
Document_Name VARCHAR(100) NOT NULL,
Sub_category_name VARCHAR(50) NOT NULL,
Category_name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Documents
PRIMARY KEY (Documents),
CONSTRAINT FK_Documents__Sub_categories
FOREIGN KEY (Sub_category_name, Category_name)
REFERENCES Sub_categories (Sub_category_name, Category_name)
)
Jacco Schalkwijk
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:25C8585D-3986-4DC6-9997-160B27BB49CC@.microsoft.com...
> Hi,
> I'm designing a database, but am having a problem with categories and sub
> categories.
> Basically we have documents. Each document has a category, and a sub
> category. The selection of sub category is dependant on which category is
> selected (so that you cannot select a sub category that does not relate to
> its category).
> If anyone has any ideas, I would be all ears.
> Many thanks in advance.
> Andy|||Jens,
Thanks for the reply.
Surely having sub-category in the documents table doesn't meet 3rd normal
form, since it relies on a non-key table i.e.category? This is my problem.
Its similar to what I have currently...
Documents
--
Doc ID (PK)
CatID (FK)
SubCatID (FK)
Categories
--
CATID (PK)
Cat Description
SubCategories
--
CatID (PK)
SubCatID (PK)
SubCat Description
...With a constraint between Categories and SubCategories
But I don't like it. Maybe Categories and sub categories is just one of
those things that isn't pretty or meets the rules?
"Jens Sü?meyer" wrote:

> If you have a separate table with documents on you can defin a Check
> Constraint to check wheter the inserted combination exists in this table.
Or
> you use Foreign Key Constraint as follows:
> CREATE TABLE [dbo].[Tab_Categories] (
> [CategoryID] [char] (10),
> [SubCategoryID] [char] (10)
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Tab_Docs] (
> [DocID] [char] (10) ,
> [CategoryID] [char] (10) ,
> [SubcategoryID] [char] (10)
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Tab_Categories] ADD
> CONSTRAINT [PK_Tab_Categories] PRIMARY KEY CLUSTERED
> (
> [CategoryID],
> [SubCategoryID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Tab_Docs] ADD
> CONSTRAINT [PK_Docs] PRIMARY KEY CLUSTERED
> (
> [DocID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Tab_Docs] ADD
> CONSTRAINT [FK_Docs_Tab_Categories] FOREIGN KEY
> (
> [CategoryID],
> [SubcategoryID]
> ) REFERENCES [dbo].[Tab_Categories] (
> [CategoryID],
> [SubCategoryID]
> )
> GO
>
> HTH, Jens Sü?meyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Andy" <Andy@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:25C8585D-3986-4DC6-9997-160B27BB49CC@.microsoft.com...
>
>|||Jacco,
Thanks for that, its basically what I currently have. Should I not worry
about it breaking 3NF? - i.e. In the Documents table, sub_category_name
relies on category_name, and not the primary key?
Many thanks
Andy
"Jacco Schalkwijk" wrote:

> To model this properly you need 3 tables (additional columns omitted for
> clarity):
> CREATE TABLE Categories (
> Category_name VARCHAR(50) NOT NULL,
> CONSTRAINT PK_Categories
> PRIMARY KEY (Category_name)
> )
> CREATE TABLE Sub_categories (
> Sub_category_name VARCHAR(50) NOT NULL,
> Category_name VARCHAR(50) NOT NULL,
> CONSTRAINT PK_Sub_categories
> PRIMARY KEY (Sub_category_name, Category_name),
> CONSTRAINT FK_Sub_categories__Categories
> FOREIGN KEY (Category_name)
> REFERENCES Categories (Category_name)
> )
> CREATE TABLE Documents (
> Document_Name VARCHAR(100) NOT NULL,
> Sub_category_name VARCHAR(50) NOT NULL,
> Category_name VARCHAR(50) NOT NULL,
> CONSTRAINT PK_Documents
> PRIMARY KEY (Documents),
> CONSTRAINT FK_Documents__Sub_categories
> FOREIGN KEY (Sub_category_name, Category_name)
> REFERENCES Sub_categories (Sub_category_name, Category_name)
> )
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:25C8585D-3986-4DC6-9997-160B27BB49CC@.microsoft.com...
>
>|||Andy,
Do not store the CategoryID in the Documents table (store only the
SubCategoryID). The Category combobox should be unbound, used only in
the form (not stored in the database).
Razvan|||create table CATEGORY
(
CODE varchar(3), --PK
DESCRIPTION varchar(30)
)
create table SUB_CATEGORY
(
CODE varchar(3), --PK
DESCRIPTION varchar(30)
)
create table CAT_DEF
(
RID uniqueidentifier, --PK
CATEGORY varchar(3), -- FK to CATEGORY
SUB_CATEGORY varchar(3) -- FK to SUB_CATEGORY
)
go
unique constraint on CATEGORY & SUB_CATEGORY
create table DOCUMENT
(
DOC_ID integer, -- PK
DOC_NAME varchar(30),
CAT_DEF uniqueidentifier -- FK to CAT_DEF
)
you don't have to use unqiueidentifiers for the link between DOCUMENT and
CAT_DEF, they could be integers etc. Some may say you don't need the
RID/PRID columns at all, but its just a bit neater than having the category
and sub category fields repeated in DOCUMENT.
CAT_DEF allows you to associate the sub categories to the categories, hence
you cannot incorrectly associate a sub category with a category
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:25C8585D-3986-4DC6-9997-160B27BB49CC@.microsoft.com...
> Hi,
> I'm designing a database, but am having a problem with categories and sub
> categories.
> Basically we have documents. Each document has a category, and a sub
> category. The selection of sub category is dependant on which category is
> selected (so that you cannot select a sub category that does not relate to
> its category).
> If anyone has any ideas, I would be all ears.
> Many thanks in advance.
> Andy|||Category_name depends on sub_category_name, not the other way around. And
even that is only the case if sub_category_name is UNIQUE. If the same
sub_category_name can appear in multiple categories, the model I presented
is in 3NF. If sub_category_names are unique, the model below is in 3NF. It
wasn't clear to me from your original post whether sub_category_names are
unique or not.
CREATE TABLE Categories (
Category_name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Categories
PRIMARY KEY (Category_name)
)
CREATE TABLE Sub_categories (
Sub_category_name VARCHAR(50) NOT NULL,
Category_name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Sub_categories
PRIMARY KEY (Sub_category_name),
CONSTRAINT FK_Sub_categories__Categories
FOREIGN KEY (Category_name)
REFERENCES Categories (Category_name)
)
CREATE TABLE Documents (
Document_Name VARCHAR(100) NOT NULL,
Sub_category_name VARCHAR(50) NOT NULL,
CONSTRAINT PK_Documents
PRIMARY KEY (Documents),
CONSTRAINT FK_Documents__Sub_categories
FOREIGN KEY (Sub_category_name)
REFERENCES Sub_categories (Sub_category_name)
)
Jacco Schalkwijk
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:B1A8D1FE-743E-43BC-B38F-0A784A542C26@.microsoft.com...
> Jacco,
> Thanks for that, its basically what I currently have. Should I not worry
> about it breaking 3NF? - i.e. In the Documents table, sub_category_name
> relies on category_name, and not the primary key?
> Many thanks
> Andy
> "Jacco Schalkwijk" wrote:
>

Noob: Why does this Query work?

I'm wondering how/why this query works. Trying to get my head wrapped
around SQL. Basically the Query deletes from the Import table all
records that are already in FooStrings so that when I do an insert from
the FooStringsImport table into the FooStrings table, then I won't get
primary key violations.

DELETE FROM FooStringsImport
WHERE EXISTS
(SELECT * FROM FooStrings
WHERE FooStringsImport.FooKey = FooStrings.FooKey)

It seems to work fine, but I'm wondering about how the EXISTS keyword
works.

(SELECT * FROM FooStrings
WHERE FooStringsImport.FooKey = FooStrings.FooKey)
This part is going to return only records from FooStrings correct? Or
does it do a cartesian product since I've specified more than one table
in the WHERE statement?

I wonder if it only returns records in FooStrings, then I don't see how
a record from FooStringsImport would "EXISTS" in the records returned
from FooStrings.

The reason I wondered about the cartesian product is because, if only
FooStrings is specified in the FROM part of the SELECT statement, then
I was thinking it is only going to return FooString records. These
records would then be returned by the select statement to the WHERE
EXISTS, which would look for FooStringImport records, but would find
none because the select statement only returned FooString records.

I'm guessing maybe because it has to do a cartesian product to evaluate
the WHERE Pkey's equal, then the "SELECT *" just goes ahead and gets
ALL the fields, and not just those in FooStrings.

FooStrings and FooStringsImport are identically structured tables,
where the FooKey is set as the primary key in each table:

CREATE TABLE [dbo].[FooStrings] (
[FooKey] [bigint] NOT NULL ,
[Name] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Thanks in advance. I'm so appreciative of the help I've gotten here,
as I've been able to write several very useful queries on my own now
after everyones help and plus lots of reading on my own.On 27 Apr 2005 10:54:43 -0700, shumaker@.cs.fsu.edu wrote:

> I'm guessing maybe because it has to do a cartesian product to evaluate
> the WHERE Pkey's equal, then the "SELECT *" just goes ahead and gets
> ALL the fields, and not just those in FooStrings.

Close. The "WHERE EXISTS" is processed such that *no* columns will be
returned by the SELECT * inside it. Instead, it just notes whether any row
was returned and assigns true or false. You could get the same behavior by
replacing SELECT * with SELECT 1, or
SQRT(LEN(FooStringsImport.FooKey))*3.1415926535, or even SELECT NULL.
All of them will do exactly the same thing: completely ignore what comes
after the word SELECT.

Conceptually, the query processor is doing a separate "SELECT *" statement
for each and every row in FooStrings. If that separate statement returns a
row, it deletes the corresponding row from FooStrings; if it doesn't, then
it doesn't.

In actuality, the query optimizer doesn't do that. It rewrites the query as
if it were a JOIN (aka cartesian product, as you stated) and uses that to
decide which rows to delete from FooStringsImport.|||It's called a Correlated Subquery. Theoretically you can assume that the
subquery is executed once for every row in the main (outer) part of the
statement. The correlation part is the reference to the outer table
(FooStringsImport.FooKey). In other words EXISTS returns true for any
particular row in FooStringsImport if there is at least one row in
FooStrings that matches FooStringsImport.FooKey.

This is a Selection operation, not a Join and a DELETE statement rather than
a query, so I don't see that it helps much to think in terms of a Cartesian
Product. In fact SQL Server may use join operations to execute statements
involving subqueries but the server is not going to join every row to every
row before deciding which rows to delete.

See also Joe Celko's narrative on how queries work:
http://www.google.co.uk/groups?selm...ooglegroups.com

By the way, NOT EXISTS is also possible. So rather than do a DELETE followed
by an INSERT why not just filter the INSERT statement in a similar way:

INSERT INTO FooStrings (...)
SELECT ...
FROM FooStringsImport
WHERE NOT EXISTS
(SELECT *
FROM FooStrings
WHERE FooStringsImport.FooKey = FooStrings.FooKey)

--
David Portas
SQL Server MVP
--|||On 27 Apr 2005 10:54:43 -0700, shumaker@.cs.fsu.edu wrote:

>I'm wondering how/why this query works. Trying to get my head wrapped
>around SQL. Basically the Query deletes from the Import table all
>records that are already in FooStrings so that when I do an insert from
>the FooStringsImport table into the FooStrings table, then I won't get
>primary key violations.
>DELETE FROM FooStringsImport
>WHERE EXISTS
>(SELECT * FROM FooStrings
>WHERE FooStringsImport.FooKey = FooStrings.FooKey)
>It seems to work fine, but I'm wondering about how the EXISTS keyword
>works.
>(SELECT * FROM FooStrings
>WHERE FooStringsImport.FooKey = FooStrings.FooKey)
>This part is going to return only records from FooStrings correct? Or
>does it do a cartesian product since I've specified more than one table
>in the WHERE statement?
(snip)

Hi Shumaker,

This is called a correlated subquery. Correlated, because the
"FooStringsImport.FooKey" correlates it to the part of the query outside
the subquery.

The way that this will be executed (in theory - the optimizer usually has
some nifty trcks to get the same result faster, but the slow version is
easier to understand):

- For the DELETE statement, start with the first row of FooStringsImport;
- Replace "FooStringsImport.FooKey" in the subquery with the FooKey value
of that first row;
- Execute the subquery (which now only references the table used in the
FROM clause, making it a stand-alone query);
- Evaluate if the EXISTS predicate is true or false;
- If it is true, delete the first row of FooStringImport; if it's false,
retain it.
- Move to the second row of FooStringsImport;
- Replace "FooStringsImport.FooKey" in the subquery with the FooKey value
of that second row;
- Execute the subquery (which now only references the table used in the
FROM clause, making it a stand-alone query);
- Evaluate if the EXISTS predicate is true or false;
- If it is true, delete the second row of FooStringImport; if it's false,
retain it.
- Move to the third row of FooStringsImport;
(etc etc etc -- until all rows of FooStringImport have been processed).

(Note - the terms "first", "second", etc are used very loosely here, as a
table has no intrinsic order)

The main difference between correlated and non-correlated subqueries, is
that a non-correlated subquery only has to be evaluated once, and the
result set can be retained and reused during the complete query
evaluation, whereas ta correlated subquery has to be recomputed for each
row in the "surrounding" layer of the query.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks everyone! This is great info.sql

Non integer counting

1
2
3
* (unscheduled visit) (should be 3.01)
* (unscheduled visit) (should be 3.02)
Basically when there is an unscheduled visit, it should take the previous visit number and add .01

I am not sure how to count using non integers

ThanksCan you please explan ur question in detail