How to handle the basket?
Introduction:
Database and tables specification:
- Microsoft SQL Server 2008 R2
- compatible_level = 80 (don't ask why)
Table specification:
- 3189706 line.
- 48 columns all varchar NULL
- No id, no index (just fun!)
A task:
The goal of all of this is to clean up migrations to a clean table. Both tables will exist until the old program learns to use the new one.
I'll put the whole thing in a temp table to check for type and null.
Then clear all duplicates. Virtually impossible due to 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)
How slow it will be, I came to you for some advice on this process.
and how to make it as effective as possible.
Make it an insert to insert? Doing conversion and zero insert validation? Add index before insertion?
Ps: I have simplified the Sql query because of 48 columns.
source to share
SELECT
Foo = CONVERT ( u.TrashFoo , expression )
,Bar = u.trs_Something
FROM dbo.BurnMe u
Since this is going to be slow, I came to you for some advice on this process. and how to make it as effective as possible.
It won't be efficient as you are fetching all data from the table
If you want to validate the data and don't want to modify the table, I recommend Trigger
going with , something like below
create trigger trg_test
on table
for insert
as
begin
do your validation here
end
Update as per the comments:
if you want to keep both tables, I recommend not using temporary tables, use a permanent table. Pay the one-time cost of checking everyone, then pay only the delta cost. This is the only idea I could think of
source to share