stored procedure - 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)

stored procedure

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-04-12 : 20:23:54

I am trying to import information into a table that I created in sql2008. The file is a csv and is stored on my computer. I understand that I can import this using a stored procedure. Can any one help me with this? I have never used a stored procedure before and would like to learn about it.

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2009-04-12 : 21:50:05

you can use Bulk INSERT to import CSV file into sql table..

create proc importcsvfile
(
@filename varchar(100) ---full path of the file and its name
)
AS
begin

BULK INSERT csvimport
FROM @filename WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’

)

end

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-04-12 : 21:55:29

quote:
Originally posted by cshah1

you can use Bulk INSERT to import CSV file into sql table..

create proc importcsvfile
(
@filename varchar(100) ---full path of the file and its name
)
AS
begin

BULK INSERT csvimport
FROM @filename WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’

)

end

Does this require that you have a table in advance? Sorry for the dumb questions, I have never done this.

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2009-04-12 : 21:59:49

Yes,

more information and syntax can be found at
http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-04-12 : 22:06:42

quote:
Originally posted by cshah1

Yes,

more information and syntax can be found at
http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

OK, Thanks for the help.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-04-12 : 22:35:13

quote:
Originally posted by jcb267

quote:
Originally posted by cshah1

Yes,

more information and syntax can be found at
http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

OK, Thanks for the help.

This is what I have tried:
create proc importcsvfile
(
C:\Users\John & Tanya\Documents\John\Hospital Compare\Hospital_flatfiles\dbo_vwHQI_FTNT ---full path of the file and its name
)
AS
begin

BULK INSERT dbo.HQI_FTNT
FROM dbo_vwHQI_FTNT WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’

but am getting this error:

Msg 102, Level 15, State 1, Procedure importcsvfile, Line 3
Incorrect syntax near 'C:'.
Msg 102, Level 15, State 1, Procedure importcsvfile, Line 11
Incorrect syntax near '‘'.

Can you please help me with this?

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-04-13 : 05:55:40

Hi,

Try this,

--STEP 1

CREATE PROCEDURE ImportCSVFile
AS
BEGIN
DECLARE @FileName NVARCHAR (100)
,@FilePath NVARCHAR (250)
,@Command NVARCHAR(500)

SET @FileName = 'FileName.csv'
SET @FilePath = 'C:\Users\John & Tanya\Documents\John\Hospital Compare\Hospital_flatfiles\dbo_vwHQI_FTNT\'

SET @Command = 'BULK INSERT TableName FROM "'+@FilePath + @FileName + '" WITH(ROWTERMINATOR =''\n'', FIELDTERMINATOR='','')'
EXEC (@Command)
END

--STEP 2

EXEC ImportCSVFile

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources