Updating a table with a file...

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 2000 Forums

Transact-SQL (2000)

Updating a table with a file...

Author

Topic

kchandra
Starting Member

3 Posts

Posted - 2005-01-22 : 12:03:54

Hi gang

I am a business analyst by trade, so I am not very savvy with SQL-Server.

I have a file that has an IDNum, score1 and score2. I can save it as CSV, etc etc, don't matter, just tell me how

I need to replace score1 and score2 in a sql-server table with the same IDNum.

What's the easiest way of doing this.

I am not very enthusiastic about writing 300 replace statements!

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-22 : 14:40:17

see http://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.html
for the import

create table #a(idnum int, score int, score2 int)
bulk insert #a from 'c:\myfile.txt' with (fieldterminator=',')
update mytbl
set score1 = t2.score1 , score2 = t2.score2
from mytbl t1
join #a t2
on t2.idnum = t1.idnum
drop table #a

did you need the export as well?
exec master..xp_cmdshell 'bcp mydb..mytbl out c:\myfile.txt -c -t, -T'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-22 : 14:45:58

If you just want to update as a once off you can open the file in excel.
In the next free column (D) type
="update mytbl set score1 = " & b1 & ", score2 = " & c1 & " where idnum = " & a1
copy that formula down and copy the result into query analyser and you should have a series of update statements that will update the table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kchandra
Starting Member

3 Posts

Posted - 2005-01-23 : 13:30:23

Thanks a lot guys. I searched the threads and went into work and started messing with DTS yesterday. I created a temp table and used DTS to populate the table and then I updated the final table joining where temp.dealercode=final.dealercode and temp.id1 <> final.id1

Did the same for id2 and it worked great! I guess I shouldnt be afraid to try things.

kchandra
Starting Member

3 Posts

Posted - 2005-01-23 : 13:37:27

[quote]Originally posted by nr

see http://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.html
for the import

create table #a(idnum int, score int, score2 int)
bulk insert #a from 'c:\myfile.txt' with (fieldterminator=',')
update mytbl
set score1 = t2.score1 , score2 = t2.score2
from mytbl t1
join #a t2
on t2.idnum = t1.idnum
drop table #a

NR - I have some questions for you on the stuff above coz I am curious and want to improve my skills:

1) What is bcp?

2)exec master..xp_cmdshell 'echo 1,a,b,c > c:\bcp.txt'
exec master..xp_cmdshell 'echo 2,d,e,f >> c:\bcp.txt'
exec master..xp_cmdshell 'echo 3,g,h,i >> c:\bcp.txt'

Is the above just SQL commands. Seem like unix line commands. I wasnt sure I could use them as is.

3) create table #a(idnum int, score int, score2 int)
bulk insert #a from 'c:\myfile.txt' with (fieldterminator=',')
update mytbl
set score1 = t2.score1 , score2 = t2.score2
from mytbl t1
join #a t2
on t2.idnum = t1.idnum
drop table #a

Is that the exact syntax, I need to go try it on a test database.

Thats so cool not having to use DTS at all. I am so glad I found this site! Seems like there are some geniuses in here!

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-23 : 14:52:41

1) bcp is used for file import / export. For text files it is usually simpler and faster than dts. Bulk insert is usually preferable for imports but you have to use the bcp exe for exports. Have a look in bol.

2) xp_cmdshell just executes a windows os command.
echo 1,a,b,c > c:\bcp.txt creates a file and inserts the echoed data
echo 2,d,e,f >> c:\bcp.txt appends the data to the file.
Try it.

3) Should be - no guarantees though (you'll learn more if you have to fix it is always my excuse).
The examples at
http://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.html
have been tested though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources