January 28, 2008 at 2:39 am
Hi Guys. I would like to delete all values from a single table within a database + reset the seed to zero.
Database / Scema / Table =
[Test].[dbo].[Products].
I used a wizard from our FE Application to import data from excel. This has not added the correct values to a field 'UnitsOfMeasure'. I would like to delete and try again (with a seed of zero).
Have tried:
DELETE FROM [Test].[dbo].[Products]
sp_reseed Products
(Pretty sure above is incorrect syntax!!)
DELETE FROM [Test].[dbo].[Products]
WHERE UnitOfMeasure = 1
SQL Returns Error:
The DELETE statement conflicted with the REFERENCE constraint "FK_StructureVersions_Products".....
Can any of you gurus advise the correct syntax?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 28, 2008 at 2:48 am
You have one (or more) tables with a foreign keys on them that reference the table you would like to delete from. You could either delete the values from those tables, or, if you are sure that the key values in your [Products] will remain the same after you populate it again, then you can either drop those foreign keys, and create them again, or disable the foreign keys
ALTER TABLE sometable NOCHECK CONSTRAINT FK_StructureVersions_Products
and then reenable it
ALTER TABLE sometable WITH CHECK CHECK CONSTRAINT FK_StructureVersions_Products
Regards,
Andras
January 28, 2008 at 3:17 am
Thanks for the reply. Tried to run that command against Products, sql returned error:
Cannot find the object "Products" because it does not exist or you do not have permissions.
Connected to SQL as 'sa'.
Obviously something I am doing wrong!!
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 28, 2008 at 3:20 am
Philip Horan (1/28/2008)
Thanks for the reply. Tried to run that command against Products, sql returned error:Cannot find the object "Products" because it does not exist or you do not have permissions.
Connected to SQL as 'sa'.
Obviously something I am doing wrong!!
Thanks,
Phil.
Are you connected to the "Test" database? What is the command exactly you have tried to run? Have you found the foreign keys that reference the Products table?
Andras
January 28, 2008 at 3:48 am
Hi Andras.
I launch Management Studio then connect to the Server (Server Name: My Server) using the sa account. I then expand 'Databases' then select 'Test'
The Summary window displays:MY-SERVER\Databases\Test
If I select connection options then specify 'Connect to Database' browse then select 'Test' the Summary window displays: MY-SERVER
I used the command you posted, substituting sometable with my table name Products:
ALTER TABLE Products NOCHECK CONSTRAINT FK_StructureVersions_Products
Also tried:
ALTER TABLE [Test].[dbo.].[Products] NOCHECK CONSTRAINT FK_StructureVersions_Products
Error:
The DELETE statement conflicted with the REFERENCE constraint "FK_StructureVersions_Products". The conflict occurred in database "Test", table "dbo.StructureVersions", column 'Product'.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 28, 2008 at 3:59 am
Philip Horan (1/28/2008)
Hi Andras.I launch Management Studio then connect to the Server (Server Name: My Server) using the sa account.
How would I ensure I am connected to a specific database?
I used the command you posted, substituting sometable with my table name Products:
ALTER TABLE Products NOCHECK CONSTRAINT FK_StructureVersions_Products
Also tried:
ALTER TABLE [Test].[dbo.].[Products] NOCHECK CONSTRAINT FK_StructureVersions_Products
Error:
The DELETE statement conflicted with the REFERENCE constraint "FK_StructureVersions_Products". The conflict occurred in database "Test", table "dbo.StructureVersions", column 'Product'.
Many Thanks,
Phil.
In Management studio the database you are using is shown in a dropdown box in the top left corner. You can change it there, or you could execute
use Test
The foreign key that you would need to disable is not on the Products table, it is on another table, and it is referencing the Products table. To find out which table it is on execute:
SELECT OBJECT_NAME(parent_object_id)
FROM sys.foreign_keys
WHERE name LIKE 'FK_StructureVersions_Products'
(also make sure you do refer to dbo as [dbo] only, and not [dbo.])
Regards,
Andras
January 28, 2008 at 4:08 am
Hi Andras. The [dbo.] was a typo!!
I will have a play with your commands.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 28, 2008 at 4:28 am
Andras just to note, if I use command Use Test then I am able to see the 'Database' drop down selector. However this is not usually visible? Can I make this visible? Can not see a toolbar that does this?
Thanks,
Phil.
Update: I see that option is available if you have the query window open!! Silly me!!
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 28, 2008 at 4:35 am
DBCC CHECKIDENT can be used to reseed the identity value.
N 56°04'39.16"
E 12°55'05.25"
January 28, 2008 at 4:46 am
Andras I have now managed to execute the delete statement after removing the FK Constraint.
I take it I reverse the NOCHECK before re-importing the data?
Phil.
PS: Thanks for the Reseed command.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 28, 2008 at 5:06 am
Quick update. I ran:
ALTER TABLE [Test].[dbo].[StructureVersions] WITH CHECK CHECK CONSTRAINT FK_StructureVersions_Products
to re-add the FK Constraint. SQL returned error:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_StructureVersions_Products". The conflict occurred in database "Test", table "dbo.Products", column 'Product'.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 28, 2008 at 6:21 am
Philip Horan (1/28/2008)
Andras I have now managed to execute the delete statement after removing the FK Constraint.I take it I reverse the NOCHECK before re-importing the data?
Phil.
PS: Thanks for the Reseed command.
Yes, you should do the reenabling of the foreign key after the re-import. It does assume that the key values will be the same. What the statement will do is it will enable the foreign key, check that the other table does not refer to Products that do not exist, and set the foreign key to be trusted. (more info on http://www.simple-talk.com/sql/database-administration/foreign-keys-and-their-states/)
Regards,
Andras
January 28, 2008 at 7:53 am
A few smallish things:
1.Keep in mind that the RESEED will not automatically reseed to a SMALLER number, but it will find the appropriate next HIGHER identity value to put it in line with the highest of the existing rows. So if you want to "adjust downward", you usually need to run something like this:
DBCC Checkident('Mytable', RESEED,0) --set to something too low
DBCC Checkident('Mytable', RESEED) --let it figure out what the "real" next identity value should be
2. Like Andras pointed out, re-enable the constraint AFTER the re-import.
3. you need to be VERY careful that you don't mess up relational integrity, so make sure you update the foreign keys so that they continue to relate to the SAME parent records they used to point to (if need be of course).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 28, 2008 at 10:09 am
Thanks guys. I manually re-mapped the FK Constraints as there were only 2 of them.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply