How to handle the trash table?
Introduction: Database and table specification: Microsoft SQL Server 2008 R2 compatibility_level = 80 (Do not ask why) Table specification: 3189706 row. 48 columns all varchar NULL No id, no index (Only fun !) The challenge: The purpuse of all this is to clean an migrate to a clean table. Both table will existe till old program learn to use the new one. I will put all this in a temp table to check type and null. Then clean all duplicate.Actually impossible because of varchar and no index. CREATE TABLE #TempTrash ( ID INT IDENTITY(1,1), Foo INT, Bar VARCHAR(50) ) INSERT INTO #TempTrash ( Foo, Bar ) SELECT Foo = CONVERT ( u.TrashFoo , expression ) ,Bar = u.trs_Something FROM dbo.BurnMe u -- 3 Search index for duplicate clean CREATE CLUSTERED INDEX IDX_C_Trash_IdFoo ON #TempTrash(Foo) CREATE INDEX IDX_Trash_IdFoo ON #TempTrash(Foo) As this is going to be verry slow I came to you for some advice on this process. and How to make it as efficient as possible. Make it a bluck insert? Doing convertion and null check on the insert? Add index before insert? Ps: I simplified the Sql query because of the 48 columns.
SELECT Foo = CONVERT ( u.TrashFoo , expression ) ,Bar = u.trs_Something FROM dbo.BurnMe u As this is going to be verry slow I came to you for some advice on this process. and How to make it as efficient as possible. This will not be efficient,since you are selecting all data from the table If you want to check the validity of data and don't want to alter the table, i recommend going with Trigger,some thing like below create trigger trg_test on table for insert as begin do your validation here end Update as per comments: if you are looking to keep both the tables, i recommend not using temp tables,use a permanent table.Pay one time cost of checking all and then pay only delta cost.this is the only idea i could think off
Best way to handle updates on a table
Retrieving XML Data from sql server
Azure Recovery Vault Backup is failing for SQL VMs
excel via for Mac 2011 SQL Server
multiple declare statements with one creating a temporary table
SQL Query is taking infinite time when using with order by
SAS query issue on external DBMS TABLE where Column Name has space
Try-Catch with Stored Procedures in SQL server
Is there an analogy between a recursive SQL query and a recursive function?
stored procedure 'auto_pk_for_table' not found
OUTPUT is not displaying more than ch3794, col 3794, Ln 35,
Insert/update datetime from VB6 to SQL Server
When calling process from SQLCLR, must the called process be 64bit?
SQL Server 2008 - Computed Column Extract Only Numeric From Other Column [duplicate]
Simple SELECT query with calculated columns returns error
Code required to run a query on the first day of each month