insert a delimited text file into an sql databse

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

General SQL Server Forums

New to SQL Server Programming

insert a delimited text file into an sql databse

Author

Topic

zukipower
Starting Member

10 Posts

Posted - 2010-10-01 : 09:53:08

Hello everyone,

As the subject title says:
We will create a file with 2 columns, 1st column is spare part number 2nd column is location. The table i want to implement the file into is looking like this:

spare part number, location
123456789, 2nd floor
32145697, 1st floor
and so on.

I want the file i'm inserting to update the spare parts already in my table with information. based on the spare part number, because on many of the numbers the location is blank in the table, and i want to update the locations on these.

Earlier i have used a script like this:
BULK
INSERT TBL_SPR_REPLACEMENT
FROM 'c:\test.txt'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
GO

But now i guess i need to have a update instead, but is there any correct way to do this?

Thank you very much for helping me out in this issue! :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-01 : 09:58:21

Import your flatfile data into a staging table.
Then do your updates against the destination table.
Then do your inserts using WHERE NOT EXISTS().

You are welcome if you have more questions on this

No, you're never too old to Yak'n'Roll if you're too young to die.

zukipower
Starting Member

10 Posts

Posted - 2010-10-01 : 10:13:28

The issue here is that we want the update of the location on all spare parts to happen directly if possible.

There fore we need to have one file that have alle spare parts we want to update the location of, and then run the sql command.

Is it possible to use the script i have used earlier but only like example:

BULK
update TBL_SPR_REPLACEMENT set location from file where spare part number column 1 = from file.
FROM 'c:\test.txt'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
GO

or something like this?
im not sure of the syntax on this, but it must search up the spare part number in the table based on the lines with numbers in the file in some way and then update the lines lying in the text file.

I hope you understand what i say?! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-02 : 03:18:18

you can try using OPENROWSET for that

http://msdn.microsoft.com/en-us/library/ms190312.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources