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
SQL Server : executing table-valued functions strangely
Supporting RESTful UPSERTS in Microsoft SQL
insert large amount of AD data into SQL server using PowerShell
Do READ ONLY file groups reduce locking
importing SQL Server .bacpac file
Sql Server pivot command based on text giving null cells
Insert data in another server via linked query
Counting special characters in SQL
How to solve below scenario in ssis?
“Back Up” not appearing in SQL Server Management Studio 2016 or 17
Visual Studio 2015 Schema compare not displaying changes
Where does a Remote SSIS package actually run
Order By A Value In Another Field
How to check 'AAA' vs 'AAa' in TSQL using SQL_Latin1_General_CP1_CI_AS collation
Pentaho Kettle - CPU Utilization 100% for Table Input & Text File Output
Auto assign column value when row is created