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

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

Categories

HOME
makefile
sql-server
dotnetrdf
cil
paramiko
agile
heap-memory
wampserver
jrebel
filter
i2c
google-spreadsheet-api
slim-3
metronic
uiscrollview
dlib
lenskit
ssms-2016
bootstrap-popover
arm-template
cruisecontrol.net
thingsboard
servicemix
remove-method
python-textprocessing
excel-2013
metadata-extractor
mayavi
partial-application
fileinfo
mangodb
jackson-dataformat-csv
crt
receipt
ocs
modulo
pox
.net-assembly
vmd
elfinder
silverlight-3.0
installshield-2012
equivalence
parent
socketscan
sendgrid-templates
spring-bean
lftp
amd
context-free-language
lumen-5.3
number-theory
workflow-foundation-4.5
windows-95
openh264
photon-controller
gce
httplib2
qos
nsfetchedresultscontrolle
spring-ioc
picasa
debugdiag
vst
bacnet
gherkin
soda
spinlock
asp.net-mvc-partialview
vips
python-winshell
sevenzipsharp
httpie
es2015
line-numbers
log4cplus
boost-test
resource-files
sanitization
opendata
cilk-plus
nservicebus4
.net-cf-3.5
robospice
onselect
image-zoom
installshield-2009
vim-powerline
redirectstandardoutput
macruby
die
entity-framework-ctp5
msgbox
tcxgrid
gtk2hs
post-redirect-get
drawtext
plinq
data-entry
interface-design

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