Need to update a table column.

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)

Need to update a table column.

Author

Topic

daytymer
Starting Member

2 Posts

Posted - 2014-01-10 : 09:37:44

I have a customer who needs to update records in a table. The table has a field 'response_Code. If a pan in the table is also in a text file then response_Code needs to be set to '41' if the response_Code is anything other than '41'. The text file will contain between 60000-100000 records.Can a script be written to look at the text file and then do a compare of the table?

Thanks,

Joe Teicheira

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 09:43:50

yep..using OPENROWSET
the logic would be like


UPDATE t
SET response_Code = '41'
FROM Table t
INNER JOIN OPENROWSET('MSDASQL', 

'Driver={Microsoft Text Driver (*.txt; *.csv)};

DefaultDir=<your directory path>;',

'SELECT * FROM <filename.txt>')f
ON f.columnname = t.pan

Also see

http://www.sqlusa.com/bestpractices2008/import-csv/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

daytymer
Starting Member

2 Posts

Posted - 2014-01-10 : 12:37:11

Visakh,

Thank you for the quick response. I think this will do what I want.

Joe Teicheira

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 23:14:44

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources