Update table via text file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update table via text file

Author

Topic

Denizen
Starting Member

7 Posts

Posted - 2010-01-23 : 13:58:49

I need to update a number of records in a single table and am not sure how to do it through a text file. I believe it's fairly straightforward, but am not sure about the syntax.

The table is: CommDetails

The fields are: CDID (autonum), CommID, MemID, IsChair, IsRapp

Any help would be appreciated. Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-23 : 16:50:46

use data import wizard to import the text file to a new table. then you can write an update statement as u mormaly would

update a
set somefield = b.somefield
from tableA a
join tableB b
on a.pk = b.pk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 09:38:38

or you can use openrowset to directly read from text file.

Denizen
Starting Member

7 Posts

Posted - 2010-01-24 : 14:25:00

quote:
Originally posted by russell

use data import wizard to import the text file to a new table. then you can write an update statement as u mormaly would

update a
set somefield = b.somefield
from tableA a
join tableB b
on a.pk = b.pk

Actually, in this case it's an Insert, but in the interest of completeness, here is what I did:

1. Used SQL Import Wizard to take my flat file (.txt file) and make a a temporary table. (When you do this, make sure that your datatypes are correct).

2. Used an INSERT statement with a nested SELECT to insert data from the temporary table to the one I wanted to populate.

Here is the syntax:

INSERT INTO [RealTable]
(Field1, Field2, Field3, Field4, Field5)
SELECT Field1, Field2, Field3, Field4, Field5
FROM [TempTable]

And there you have it.

The key is that this method involves a two-step process, but frankly it gives you a way to make sure the data is correct anyway.

Thanks!

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 15:22:07

Personally, I think using a "staging table" in the way you describe is essential. No reason not to set all the columns of the staging table to VARCHAR(MAX) so that the data imports regardless of whether it actually matches the datatype specification for the columns, or not!!

You can then do exception report of WHERE IsNumeric(MyNumericCol) = 0 to flush out any goofy data. (We actually add an ERROR_MSG column, append to that any errors from tests for datatype, and then exclude anything with WHERE ERROR_MSG IS NOT NULL in the import, and we have a nice "human readable" message for the user to sort out their data and try again

Denizen
Starting Member

7 Posts

Posted - 2010-01-25 : 00:36:35

Very clever!

Thanks for the tip.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 06:18:26

quote:
Originally posted by Kristen

You can then do exception report of WHERE IsNumeric(MyNumericCol) = 0 to flush out any goofy data. (We actually add an ERROR_MSG column, append to that any errors from tests for datatype, and then exclude anything with WHERE ERROR_MSG IS NOT NULL in the import, and we have a nice "human readable" message for the user to sort out their data and try again

beware that isnumeric can also bring some values which may not be desirable

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 06:54:18

Yeah, good point. I was just using that as a example of the sort of Goofy Data Checking that you may use, but actually IsNumeric is a poor example for that job!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 06:55:39

quote:
Originally posted by Kristen

Yeah, good point. I was just using that as a example of the sort of Goofy Data Checking that you may use, but actually IsNumeric is a poor example for that job!!

yeah may be add a couple of other conditions based on what OP is looking at

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 07:33:27

IsDate()? Also pretty goofy IME

Column NOT LIKE '%[^0-9]%' is the only test for positive Integer that I know of ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 07:58:54

quote:
Originally posted by Kristen

IsDate()? Also pretty goofy IME

Column NOT LIKE '%[^0-9]%' is the only test for positive Integer that I know of ...

yeah. it also needs additional conditions to make sure date values are really what you're looking for

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources