Hello,
I have to filegroups like
database_data1 stored on d:
database_data2 stored on e:
How can I delete the database_data2 and tell SQL 2005 to copy all
data to database_data1?
Thanks for any help in advance!
A. KlemtOne way...
Assuming you have clustered indexes on all you tables is to drop and
recreate the index on the database_data1 filegroup this will also move
all the data over to database_data1
http://sqlservercode.blogspot.com/|||Hi
It is pretty much described in the BOL under "Moving database file"
"Andreas Klemt" <aklemt68@.hotmail.com> wrote in message
news:Oxkia5$JGHA.3200@.tk2msftngp13.phx.gbl...
> Hello,
> I have to filegroups like
> database_data1 stored on d:
> database_data2 stored on e:
> How can I delete the database_data2 and tell SQL 2005 to copy all
> data to database_data1?
> Thanks for any help in advance!
> A. Klemt
>
Showing posts with label delete. Show all posts
Showing posts with label delete. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
How to make proper foreign keys in EM? And how to create tables using SQL statem
Hi!
I wonder how to make right foreign keys in Enterprise Manager. I use diagram but in this case I get 'rigid' keys, so I can't delete records. But I want it to set NULL on removing records.
And is it possible to create tables by writing SQl statements in EM? I tried but the new table didn't appear :(.Use Query Analyzer and you will know what are you doing (GUI does not show what it does).sql
I wonder how to make right foreign keys in Enterprise Manager. I use diagram but in this case I get 'rigid' keys, so I can't delete records. But I want it to set NULL on removing records.
And is it possible to create tables by writing SQl statements in EM? I tried but the new table didn't appear :(.Use Query Analyzer and you will know what are you doing (GUI does not show what it does).sql
Monday, March 26, 2012
How to make indentity column continually?
Hello, everyone:
I have a table with an indentity column as first column. At beginning it is continue such as 0-50. I delete last 20 columns by hand. The 0-31 is left. When the new data is inserted, I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.
Thanks a lot
ZYTThis works for me. I'm using EM. After you delete the rows from the table, remove the IDENTITY property and save the change to the table. Then add the IDENTITY property back using the defaults (seed and increment of 1). The next row that will be added should have the next available number as it's IDENTITY value. In your case, 32.|||1. Add a new column
2. Copy data from identity column to new column
3. drop identity column
4. change name of new column to old identity column
5. make new column an identity
BUT!
The fact that you want to do this tells me the column should not be an identity column.
You will forever more be worry about gaps in sequences, ect.
The identity column shouldn't be used for "ordering" data.|||Wow, Brett makes laws...
DBCC CHECKIDENT('table_name', RESEED|NORESEED, <new_seed_value>)
And why can't you order by identity column?|||And why can't you order by identity column?
I guess that didn't come out right...
They are artificially leaning on an IDENTITY Column where gaps in the sequence is a problem...if they are building a process that is dependant on the fact that there has to be a "next row", then I would consider that a bad design.|||A surrogate key, whether INT or GUID, should not be relied upon for ordering data. By definition it has no inherent relationship to the data it represents.
It's not a law. It's a principle, and a good one.|||It's not the relationship to data that defines the ordering, it's the characteristics of the field. In this case if the key is clustered then the order is dictated by the value of the field, not by what data type it is or whether it has a relationship to data or not. READ THE POSTS!|||Dude, I can see your veins popping from here. That can't be healthy.
Yes, a clustered index is physically ordered. Duh.
But it is a bad idea to depend on a surrogate key not having gaps, or even being an indication of the order the data was created. That's what the datetime datatype is for.|||That's not what the topic was about, READ THE POSTS! And leave my veins alone. I am not saying anything about what's popping on your face, right? ;)|||I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.
Read the post...ok...
Dude...they run out of tequila in Texas?sql
I have a table with an indentity column as first column. At beginning it is continue such as 0-50. I delete last 20 columns by hand. The 0-31 is left. When the new data is inserted, I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.
Thanks a lot
ZYTThis works for me. I'm using EM. After you delete the rows from the table, remove the IDENTITY property and save the change to the table. Then add the IDENTITY property back using the defaults (seed and increment of 1). The next row that will be added should have the next available number as it's IDENTITY value. In your case, 32.|||1. Add a new column
2. Copy data from identity column to new column
3. drop identity column
4. change name of new column to old identity column
5. make new column an identity
BUT!
The fact that you want to do this tells me the column should not be an identity column.
You will forever more be worry about gaps in sequences, ect.
The identity column shouldn't be used for "ordering" data.|||Wow, Brett makes laws...
DBCC CHECKIDENT('table_name', RESEED|NORESEED, <new_seed_value>)
And why can't you order by identity column?|||And why can't you order by identity column?
I guess that didn't come out right...
They are artificially leaning on an IDENTITY Column where gaps in the sequence is a problem...if they are building a process that is dependant on the fact that there has to be a "next row", then I would consider that a bad design.|||A surrogate key, whether INT or GUID, should not be relied upon for ordering data. By definition it has no inherent relationship to the data it represents.
It's not a law. It's a principle, and a good one.|||It's not the relationship to data that defines the ordering, it's the characteristics of the field. In this case if the key is clustered then the order is dictated by the value of the field, not by what data type it is or whether it has a relationship to data or not. READ THE POSTS!|||Dude, I can see your veins popping from here. That can't be healthy.
Yes, a clustered index is physically ordered. Duh.
But it is a bad idea to depend on a surrogate key not having gaps, or even being an indication of the order the data was created. That's what the datetime datatype is for.|||That's not what the topic was about, READ THE POSTS! And leave my veins alone. I am not saying anything about what's popping on your face, right? ;)|||I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.
Read the post...ok...
Dude...they run out of tequila in Texas?sql
Monday, March 19, 2012
How to lock only one row
Hello everyone,
I need to:
1. Get data from a table row
2. Lock a row for delete
3. Delete it
The problem is that the code that I use locks the whole table, not just one
row. Does anyone know how to limit the lock to one row for one table?
Thank you,
Helen
P.S.That is what I do:
EXEC SQL
DECLARE ACURS CURSOR FOR
SELECT
"MDC7_PROG_ID",
"MDC7_REV_DATE"
FROM
"MDC7"
WHERE
"MDC7_PROG_ID" = :FMDC7-PROG-ID
END-EXEC
EXEC SQL
OPEN ACURS
END-EXEC
IF SQLCODE <> 0
GO TO END-OF-PROGRAM.
EXEC SQL
FETCH ACURS
INTO
:FMDC7-PROG-ID
, :FMDC7-REV-DATE
END-EXEC
EXEC SQL
DELETE MDC7
WHERE CURRENT OF ACURS
END-EXEC.
IF SQLCODE <> 0
EXEC SQL
CLOSE ACURS
END-EXEC.Helen Stein wrote:
> Hello everyone,
> I need to:
> 1. Get data from a table row
> 2. Lock a row for delete
> 3. Delete it
> The problem is that the code that I use locks the whole table, not
> just one row. Does anyone know how to limit the lock to one row for
> one table?
>
Do you need to be using cursors for this? If not, you can just issue the
delete directly. Unless there are no indexes to help SQL Server locate
the row, you should not see any table locking for the operation.
For example:
Declare @.ID INT
Declare @.OtherKey INT
Set @.OtherKey = 1000
Select @.ID = MyID
From TableA
Where OtherKey = @.OtherKey
If @.ID IS NOT NULL
Delete From TableB Where ID = @.ID
David Gugick
Imceda Software
www.imceda.com|||Why do you need to use a cursor to do a DELETE like this? You are deleting
all rows that are in the current cursor. Wouldn't it be much easier and
faster to write
DELETE MDC7
WHERE MDC7_PROG_ID = <what ever value FMDC7-PROG-ID represents>
How many rows of how many is this operation deleting?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:e4aLcRQFFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello everyone,
> I need to:
> 1. Get data from a table row
> 2. Lock a row for delete
> 3. Delete it
> The problem is that the code that I use locks the whole table, not just
> one
> row. Does anyone know how to limit the lock to one row for one table?
> Thank you,
> Helen
> P.S.That is what I do:
>
> EXEC SQL
> DECLARE ACURS CURSOR FOR
> SELECT
> "MDC7_PROG_ID",
> "MDC7_REV_DATE"
> FROM
> "MDC7"
> WHERE
> "MDC7_PROG_ID" = :FMDC7-PROG-ID
> END-EXEC
>
> EXEC SQL
> OPEN ACURS
> END-EXEC
> IF SQLCODE <> 0
> GO TO END-OF-PROGRAM.
>
> EXEC SQL
> FETCH ACURS
> INTO
> :FMDC7-PROG-ID
> , :FMDC7-REV-DATE
> END-EXEC
>
> EXEC SQL
> DELETE MDC7
> WHERE CURRENT OF ACURS
> END-EXEC.
>
> IF SQLCODE <> 0
> EXEC SQL
> CLOSE ACURS
> END-EXEC.
>|||The problem is your use of a cursor. It looks like you want something
like this (in TSQL):
SET @.mdc7_rev_date =
(SELECT mdc7_rev_date
FROM MDC7
WHERE mdc7_prog_id = @.mdc7_prog_id)
DELETE FROM MDC7
WHERE mdc7_prog_id = @.mdc7_prog_id
AND mdc7_rev_date = @.mdc7_rev_date
David Portas
SQL Server MVP
--|||The reason why I am using a cursor is because I need to make sure that in
between my select and delete the other user cannot access the row I just
read. Is there any other way of doing this?
Thanks for everybodys reponses.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OQdynVQFFHA.2676@.TK2MSFTNGP12.phx.gbl...
> Helen Stein wrote:
> Do you need to be using cursors for this? If not, you can just issue the
> delete directly. Unless there are no indexes to help SQL Server locate
> the row, you should not see any table locking for the operation.
> For example:
> Declare @.ID INT
> Declare @.OtherKey INT
> Set @.OtherKey = 1000
> Select @.ID = MyID
> From TableA
> Where OtherKey = @.OtherKey
> If @.ID IS NOT NULL
> Delete From TableB Where ID = @.ID
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Isn't handled through transaction isolation?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:Oub2T3QFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> The reason why I am using a cursor is because I need to make sure that in
> between my select and delete the other user cannot access the row I just
> read. Is there any other way of doing this?
>
> Thanks for everybody's reponses.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OQdynVQFFHA.2676@.TK2MSFTNGP12.phx.gbl...
>
I need to:
1. Get data from a table row
2. Lock a row for delete
3. Delete it
The problem is that the code that I use locks the whole table, not just one
row. Does anyone know how to limit the lock to one row for one table?
Thank you,
Helen
P.S.That is what I do:
EXEC SQL
DECLARE ACURS CURSOR FOR
SELECT
"MDC7_PROG_ID",
"MDC7_REV_DATE"
FROM
"MDC7"
WHERE
"MDC7_PROG_ID" = :FMDC7-PROG-ID
END-EXEC
EXEC SQL
OPEN ACURS
END-EXEC
IF SQLCODE <> 0
GO TO END-OF-PROGRAM.
EXEC SQL
FETCH ACURS
INTO
:FMDC7-PROG-ID
, :FMDC7-REV-DATE
END-EXEC
EXEC SQL
DELETE MDC7
WHERE CURRENT OF ACURS
END-EXEC.
IF SQLCODE <> 0
EXEC SQL
CLOSE ACURS
END-EXEC.Helen Stein wrote:
> Hello everyone,
> I need to:
> 1. Get data from a table row
> 2. Lock a row for delete
> 3. Delete it
> The problem is that the code that I use locks the whole table, not
> just one row. Does anyone know how to limit the lock to one row for
> one table?
>
Do you need to be using cursors for this? If not, you can just issue the
delete directly. Unless there are no indexes to help SQL Server locate
the row, you should not see any table locking for the operation.
For example:
Declare @.ID INT
Declare @.OtherKey INT
Set @.OtherKey = 1000
Select @.ID = MyID
From TableA
Where OtherKey = @.OtherKey
If @.ID IS NOT NULL
Delete From TableB Where ID = @.ID
David Gugick
Imceda Software
www.imceda.com|||Why do you need to use a cursor to do a DELETE like this? You are deleting
all rows that are in the current cursor. Wouldn't it be much easier and
faster to write
DELETE MDC7
WHERE MDC7_PROG_ID = <what ever value FMDC7-PROG-ID represents>
How many rows of how many is this operation deleting?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:e4aLcRQFFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello everyone,
> I need to:
> 1. Get data from a table row
> 2. Lock a row for delete
> 3. Delete it
> The problem is that the code that I use locks the whole table, not just
> one
> row. Does anyone know how to limit the lock to one row for one table?
> Thank you,
> Helen
> P.S.That is what I do:
>
> EXEC SQL
> DECLARE ACURS CURSOR FOR
> SELECT
> "MDC7_PROG_ID",
> "MDC7_REV_DATE"
> FROM
> "MDC7"
> WHERE
> "MDC7_PROG_ID" = :FMDC7-PROG-ID
> END-EXEC
>
> EXEC SQL
> OPEN ACURS
> END-EXEC
> IF SQLCODE <> 0
> GO TO END-OF-PROGRAM.
>
> EXEC SQL
> FETCH ACURS
> INTO
> :FMDC7-PROG-ID
> , :FMDC7-REV-DATE
> END-EXEC
>
> EXEC SQL
> DELETE MDC7
> WHERE CURRENT OF ACURS
> END-EXEC.
>
> IF SQLCODE <> 0
> EXEC SQL
> CLOSE ACURS
> END-EXEC.
>|||The problem is your use of a cursor. It looks like you want something
like this (in TSQL):
SET @.mdc7_rev_date =
(SELECT mdc7_rev_date
FROM MDC7
WHERE mdc7_prog_id = @.mdc7_prog_id)
DELETE FROM MDC7
WHERE mdc7_prog_id = @.mdc7_prog_id
AND mdc7_rev_date = @.mdc7_rev_date
David Portas
SQL Server MVP
--|||The reason why I am using a cursor is because I need to make sure that in
between my select and delete the other user cannot access the row I just
read. Is there any other way of doing this?
Thanks for everybodys reponses.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OQdynVQFFHA.2676@.TK2MSFTNGP12.phx.gbl...
> Helen Stein wrote:
> Do you need to be using cursors for this? If not, you can just issue the
> delete directly. Unless there are no indexes to help SQL Server locate
> the row, you should not see any table locking for the operation.
> For example:
> Declare @.ID INT
> Declare @.OtherKey INT
> Set @.OtherKey = 1000
> Select @.ID = MyID
> From TableA
> Where OtherKey = @.OtherKey
> If @.ID IS NOT NULL
> Delete From TableB Where ID = @.ID
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Isn't handled through transaction isolation?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:Oub2T3QFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> The reason why I am using a cursor is because I need to make sure that in
> between my select and delete the other user cannot access the row I just
> read. Is there any other way of doing this?
>
> Thanks for everybody's reponses.
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OQdynVQFFHA.2676@.TK2MSFTNGP12.phx.gbl...
>
Subscribe to:
Posts (Atom)