INSERT/UPDATE question

Question

Post reply

INSERT/UPDATE question

Greg Snidow

SSCoach

Points: 16108
More actions
August 11, 2008 at 6:55 am

#77068

Greetings everyone. I have been asked to import a report from another database, which comes with double quotes around all the values. I have set up a staging table to dump the report before I insert the data into a production table, which I do with bulk insert and a format file. I will use REPLACE(field_name,'"','') to get rid of the quotes, so my question is should I do this upon insert into the production table, or should I run an update statement to alter the data in the staging table first, then insert the data to production once it is properly formatted. My initial thought is that if I mess with the data upon insert into the production table, I will be introducing room for error. Any thoughts?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Can't you get rid of double quote characters when using BULK INSERT

by using a format file?

N 56°04'39.16"
E 12°55'05.25"

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I would think there is less room for error by having fewer steps so I would do it in the dump from staging to production. Unless you have instances where you want the double-quotes kept, the REPLACE function will be prett bullet-proof.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Unless double quote is part of the column information, such as

12" Wrench Stainless steel

N 56°04'39.16"
E 12°55'05.25"

Greg Snidow SSCoach Points: 16108 More actions · Answer 4

Thanks Jack. I tried it both ways on a file with around 30,000 rows, and no errors either way that I can see, so I will will stick with your suggestion of doing the formatting on the insert. Also, there are no instances where I want the quotes around or in the data.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.