Hi All I have situation where only 10 records need to left in the production database for testing purpose. Data of 10 records should be relevant as per referential rule between tables. It is seems to be easier to delete (or truncate tables) with all of data as I am using following script. But need help to modify it as per above requirement. Script.... SET NOCOUNT ON Declare @SQL VarChar(500) Declare @TableName VarChar(255) Declare @ConstraintName VarChar(500) Declare curAllForeignKeys SCROLL CurSor For Select Table_Name,Constraint_Name From Information_Schema.Table_Constraints Where Constraint_Type='FOREIGN KEY' Open curAllForeignKeys Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName While @@FETCH_STATUS=0 Begin Set @SQL = 'ALTER TABLE ' + @TableName + ' NOCHECK CONSTRAINT ' + @ConstraintName Print @SQL -- Execute(@SQL) Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName End Declare curAllTables Cursor For Select Table_Name From Information_Schema.Tables Where TABLE_TYPE='BASE TABLE' Open curAllTables Fetch Next From curAllTables INTO @TableName While @@FETCH_STATUS=0 Begin Set @SQL = 'DELETE FROM ' + @TableName If @ResetIdentity = 1 AND OBJECTPROPERTY (OBJECT_ID(@TableName),'TableHasIdentity')=1 Set @SQL = @SQL + '; DBCC CHECKIDENT(''' + @TableName + ''',RESEED,0)' -- Execute(@SQL) Print @SQL Fetch Next From curAllTables INTO @TableName End Fetch First From curAllForeignKeys INTO @TableName,@ConstraintName While @@FETCH_STATUS=0 Begin Set @SQL = 'ALTER TABLE ' + @TableName + ' CHECK CONSTRAINT ' + @ConstraintName -- Execute(@SQL) Print @SQL Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName End Close curAllTables Deallocate curAllTables Close curAllForeignKeys Deallocate curAllForeignKeys End SET NOCOUNT OFF Please help...
Why not simply script out these 10 records data as a file and simply truncate all the tables, then apply this 10-rows script to ensure data exists as expected.
Is there any tool(s) or method to do this as export utility of SQL Server do not give data limitation UI to export or import...
What version of SQL tools you are using? What edition of tools you are using, hope they are not SQLExpress? SSMS is pretty much can help you quickly: http://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx
We are using SQL Server 2005 and using no tools.... Generating script option do not give us option of no. of records limits in export or import.... well I am try to write a script that give us tables with and without identity column. And then generate the deletion script with where clause of no. of records limits.. i hope that this will works... and share with all here... Mean while any suggestion will be helpful... Thanks
Canyou check you are not using SSMSE (Express edition Management studio), above link is correct and using server edition's SSMS should give you the capability.
Hi, You can export records using data export import wizard and it will give ou two options either export all or write a query to select the data. here you can write your query for these 10 records. then once your exported all your data. then you can truncate your tables. I truly agree with satya as we shud always seek the easier way not the complicated one. Tell me if this helps. Rohit