sql-server


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

Related Links

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

Categories

HOME
pug
project-management
performancecounter
reportportal
chaiscript
constructor
dropbear
click
android-source
cs-cart
line-api
pdo
mod-pagespeed
riak
rhapsody
grub2
ejbca
tweets
jni
rails-activerecord
apple-tv
url.action
shopping-cart
non-deterministic
codelite
maximo
metadata-extractor
weinre
gpib
body-parser
transient
remote-server
rider
xen
nlb
broadleaf-commerce
office365connectors
strstr
image-compression
active-model-serializers
alchemy.js
wcf-security
facebook-chatbot
filepath
tuleap
ti-basic
component-pascal
ticker
volume
vici
iostat
mailcatcher
webkit2
instruments
image-editing
dbscan
gmsmapview
post-processing
group-concat
handlebars.java
android-recyclerview
oai
guzzle6
client-side-validation
kallithea
pgagent
symfony-2.6
sysctl
node-imagemagick
getimagedata
alertifyjs
flash-cc
internal
winrt-httpclient
sqljdbc
logentries
driver-signing
dynamic-binding
dynamics-ax-2009
xslkey
magickwand
web-safe-fonts
gjs
signals2
point-sprites
hinstance
qtembedded
executable-format
nagle
chatroom
osx-leopard
web-statistics
weborb

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App