bulk upload - 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 2008 Forums

Transact-SQL (2008)

bulk upload

Author

Topic

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-06-03 : 06:09:53

I have one table with 5 columns

example:

id fname lname dob address

1 aaa bbb 01/02/2001 hhhh
2 bbb ccc 02/05/2001 kkkk

like that

now i have one txt file which cosists

fname,lname
ddd fff
rrr ttt
ttt ssss

like that

can i able to bulk upload this txt file into my table in sqlserver 2008?

using

BULK
INSERT tblname
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

the above syntax?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-03 : 06:12:42

Yes. Use one of the methods
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx

Madhivanan

Failing to plan is Planning to fail

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-06-03 : 07:01:35

thanks man, it works fine

but on extension to this, i haveone more doubt

sample table:

id fname lname ratingid remarks
1 aaa bb 2 good
2 bbb vv 1 vgood
3 cccc cc 4 ok

like that

The ratingid is coming from Master table

ratingid rating
1 A +
2 A
3 B +
4 B

like that

if u buld load from txt file means

the .txt file will contain:

1, aaa,bb,A,good
2, bbb,vv,A+,vgood
3, cccc,cc,B,ok

but i want to store ratingid instead of Rating in my Table

is it possible?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-03 : 09:28:46

Import the file to staging table. Update staging table value by matching with master table. Copy the staging table to your working table

Madhivanan

Failing to plan is Planning to fail

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-06-04 : 01:54:43

i didnt get u properly

can u able to expalin with sample records?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-04 : 03:24:01

1 Create new table with five columns
2 Bulk insert to that table
3
Update n
set col4=m.ratingid
from new_table as n inner join Master_table as m
on n.col4=m.rating

4
Insert into your_working_table(col_list)
select col_list from new_table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources