1

So I have database with two tables. and they have foreign key relation between them. I want to do some testing but the tables are so big. I want to delete most of the data in these tables and just keep around 500 rows in the first table and the related rows in the second table so testing will be faster. How can I do that?

Database structure: enter image description here

  • You don't even tell us what the criteria is for the 500 rows or how many rows these "big tables". We don't have a query. – Evan Carroll Jul 10 at 21:55
  • @EvanCarroll So there are some WHERE conditions ( OrderNumber <> '' AND OrderNumber IS NOT NULL etc .. ) Which is I want to apply on the first table and I want to copy top 500 rows which have satisfy these conditions and copy the correlated rows in the second table. These are the 500 rows that I want to keep. Big tables I mean like 15 millions rows. – Nano Jul 10 at 22:41
  • 1
    @Nano, well the testing should be done with a good amount of data, instead of deleting the records, do a test with the same dataset so you can evaluate your performance of your queries also. Just a thought! – Biju jose Jul 11 at 5:28
  • @Bijujose I wish that I can use it but once I use any query which has inner join .. it takes like 3 minutes to get the answer. I don't know why. On the production database its 3 seconds process but on my local machine it takes forever. Do you have any thought? – Nano Jul 11 at 9:18
6

One simple way to do this is to extract the rows from those tables into temporary tables then truncate the source tables.

Example:

-- Load the top 500 UUT_RESULT records into a temp table
SELECT TOP (500) *
INTO #UUT_RESULT_TEMP
FROM UUT_RESULT
ORDER BY ID

-- Load the related STEP_RESULT records into a separate temp table
SELECT *
INTO #STEP_RESULT_TEMP
FROM STEP_RESULT
WHERE UUT_RESULT IN (SELECT TOP (500) FROM UUT_RESULT ORDER BY ID)

-- Remove the record
TRUNCATE TABLE STEP_RESULT
TRUNCATE TABLE UUT_RESULT

-- Load the records back in
INSERT INTO UUT_RESULT
SELECT * FROM #UUT_RESULT_TEMP

INSERT INTO STEP_RESULT
SELECT * FROM #STEP_RESULT_TEMP

Using TRUNCATE will help speed things up as this is a minimally logged operation.

  • I get error in the line (WHERE UUT_RESULT IN (SELECT TOP (500) FROM UUT_RESULT ORDER BY ID)) – Nano Jul 10 at 23:31
  • After I added * after top (500) * I got this error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. – Nano Jul 10 at 23:39
  • Sorry my mistake, use SELECT TOP (500) ID FROM UUT_RESULT ORDER BY ID – HandyD Jul 11 at 0:02
  • 1
    another error: Cannot truncate table 'UUT_RESULT' because it is being referenced by a FOREIGN KEY constraint. – Nano Jul 11 at 0:32
  • You will need to disable the constraints or use DELETE FROM instead of TRUNCATE TABLE – HandyD Jul 11 at 0:49
0

Another solution is using a SELECT * INTO option in SQL Server and renaming the tables.

--Taking HandyD's code,Thanks HandyD

-- Load the top 500 UUT_RESULT records into a temp table
SELECT TOP (500) *
INTO UUT_RESULT_TEMP
FROM UUT_RESULT
ORDER BY ID

-- Load the related STEP_RESULT records into a separate temp table
SELECT *
INTO STEP_RESULT_TEMP
FROM STEP_RESULT
WHERE UUT_RESULT IN (SELECT TOP (500) FROM UUT_RESULT ORDER BY ID)

--Rename tables.
sp_rename 'UUT_RESULT', UUT_RESULT_OLD'
sp_rename 'STEP_RESULT', STEP_RESULT_OLD'

sp_rename 'UUT_RESULT_TEMP', UUT_RESULT'
sp_rename 'STEP_RESULT_TEMP', STEP_RESULT'
--Create your constraints after this.
--PK,FK,TRIGGER ETC

--DELETE the old tables.
DROP TABLE UUT_RESULT_OLD
DROP TABLE STEP_RESULT_OLD
  • I get this error: Incorrect syntax near 'sp_rename'. – Nano Jul 11 at 9:44
  • After fixing some syntax error in your answer, I got this: (500 rows affected) (0 rows affected) Caution: Changing any part of an object name could break scripts and stored procedures. Caution: Changing any part of an object name could break scripts and stored procedures. Caution: Changing any part of an object name could break scripts and stored procedures. Caution: Changing any part of an object name could break scripts and stored procedures. Msg 3726, Level 16, State 1, Line 25 Could not drop object 'UUT_RESULT_OLD' because it is referenced by a FOREIGN KEY constraint. – Nano Jul 11 at 9:55
  • I don't see STEP_RESULT_OLD has been created! I am afraid that I missed up things using your answer – Nano Jul 11 at 9:57
  • I lost all the data in STEP_RESULT Table .. thank you so much. I don't believe that there is a fix from that – Nano Jul 11 at 10:02
  • @Nano, DROP tables should be done in order so it won't have any referencing issues. – Biju jose Jul 11 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.