Copy data from a flat file to a temporary table

Question

Copy data from a flat file to a temporary table

CanuckBuck

Hall of Fame

Points: 3915
More actions
April 30, 2008 at 1:39 pm

#185031

Is there any mechanism for other than bcp or bulk copy, (read; that a normal user can use) to copy data from a csv to a temporary table?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

It depends on what you're copying and what front-end you have on the database.

For example, in MS Access, which can be used as a front end for SQL Server databases, there are Import wizards that will get data into tables. I assume a .NET form could be built for the same thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 2

I'm effectively using a simple sql script with variables and hoping to pass in the name of the CSV in one of the variables.

Something like this

IF OBJECT_ID(N'TempDb..#SCOPE',N'U') IS NOT NULL DROP TABLE #SCOPE

CREATE TABLE #SCOPE (HoleId VARCHAR(13))

CREATE INDEX SCOPE_PK ON #SCOPE(HoleId)

BULK INSERT #SCOPE FROM 'D:\wells.csv' WITH(FIELDTERMINATOR = ',')

This is the message I get back

Msg 8104, Level 16, State 2, Line 4

The current user is not the database or object owner of table '#SCOPE'. Cannot perform SET operation.

GSquared SSC Guru Points: 260824 More actions · Answer 3

I just tried that script, and it seems to work for me. Maybe you need to use Execute As?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 4

Steve Jones - SSC Editor

SSC Guru

Points: 734449

May 1, 2008 at 7:47 am

#809540

You could use OPENQUERY as well.

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 5

I believe that the source of my problem is that I (and the users who will use this SQL script) do not - and would never be permitted to - have the appropriate privialges to run a Bulk Insert command. :crying:

The database that I'm working in is an enterprise database. Essentially I want to load a list of PK's into a temporary table so I can join to a table in the database and do some processing.

It is very common and natural for my users to have a list of these PK's in a csv file.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

It only takes bulk admin privs... if your DBA won't allow you to have it, (s)he can give you privs to run a job that uses a proxy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)