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

4
  • 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. Jul 10 '19 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 '19 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 '19 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 '19 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.

6
  • I get error in the line (WHERE UUT_RESULT IN (SELECT TOP (500) FROM UUT_RESULT ORDER BY ID))
    – Nano
    Jul 10 '19 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 '19 at 23:39
  • Sorry my mistake, use SELECT TOP (500) ID FROM UUT_RESULT ORDER BY ID
    – HandyD
    Jul 11 '19 at 0:02
  • 1
    another error: Cannot truncate table 'UUT_RESULT' because it is being referenced by a FOREIGN KEY constraint.
    – Nano
    Jul 11 '19 at 0:32
  • You will need to disable the constraints or use DELETE FROM instead of TRUNCATE TABLE
    – HandyD
    Jul 11 '19 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
11
  • I get this error: Incorrect syntax near 'sp_rename'.
    – Nano
    Jul 11 '19 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 '19 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 '19 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 '19 at 10:02
  • @Nano, DROP tables should be done in order so it won't have any referencing issues.
    – Biju jose
    Jul 11 '19 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.