insert query - 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

General SQL Server Forums

New to SQL Server Programming

insert query

Author

Topic

asm
Posting Yak Master

140 Posts

Posted - 2010-02-12 : 06:37:12

hi,

how to insert the data in sql 2008 from .txt file

example

01,2,20100211,174624,1009,A0001
01,2,20100211,174631,1020,A0001
01,2,20100211,174635,1210,A0001
01,2,20100211,174648,1011,A0001
01,2,20100211,174651,1018,A0001

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 07:32:12

Read about Bulk insert in SQL Server help file

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 07:33:06

use bcp or bulkinsert or export import wizard

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

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

asm
Posting Yak Master

140 Posts

Posted - 2010-02-12 : 07:57:39

Thanks... bulk insert is working

BULK INSERT Atten FROM 'C:\Temp2005\11.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')

pls guide what is error in this procedure--

alter Procedure spInsertFromTextFile (@TextFile varchar(200))
as
Begin
-- BULK INSERT Atten FROM 'C:\Temp2005\11.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
BULK INSERT Atten FROM @TextFile WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
--select * from Atten
End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 08:00:24

you need to use dynamic
EXEC ('BULK INSERT Atten FROM ' + @TextFile + 'WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')')

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 08:33:53

quote:
Originally posted by visakh16

you need to use dynamic
EXEC ('BULK INSERT Atten FROM ' + @TextFile + 'WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')')

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

Dont forget about single quotes around the file name

Madhivanan

Failing to plan is Planning to fail

asm
Posting Yak Master

140 Posts

Posted - 2010-02-13 : 05:00:28

Alter Procedure spInsertFromTextFile (@TextFile varchar(200))
as
Begin
Truncate Table Atten
EXEC ('BULK INSERT Atten FROM ' + @TextFile + 'WITH (FIELDTERMINATOR = ',' ,ROWTERMINATOR = '\n')')
select * from Atten
End

show an error message:
Msg 102, Level 15, State 1, Procedure spInsertFromTextFile, Line 9
Incorrect syntax near '\'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-13 : 10:21:15

quote:
Originally posted by asm

Alter Procedure spInsertFromTextFile (@TextFile varchar(200))
as
Begin
Truncate Table Atten
EXEC ('BULK INSERT Atten FROM ' + @TextFile + 'WITH (FIELDTERMINATOR = ',' ,ROWTERMINATOR = ''\n'')')
select * from Atten
End

show an error message:
Msg 102, Level 15, State 1, Procedure spInsertFromTextFile, Line 9
Incorrect syntax near '\'.

wat about the above

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

asm
Posting Yak Master

140 Posts

Posted - 2010-02-14 : 02:23:41

ALTER Procedure [dbo].[spInsertFromTextFile] (@TextFile varchar(200))
as
Begin
EXEC ('BULK INSERT Atten FROM ' + @TextFile + 'WITH (FIELDTERMINATOR = ',' ,ROWTERMINATOR = ''\n'')')
End

still error show-
Msg 102, Level 15, State 3, Procedure spInsertFromTextFile, Line 5
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-14 : 03:28:58

quote:
Originally posted by asm

ALTER Procedure [dbo].[spInsertFromTextFile] (@TextFile varchar(200))
as
Begin
EXEC ('BULK INSERT Atten FROM ' + @TextFile + 'WITH (FIELDTERMINATOR = '','' ,ROWTERMINATOR = ''\n'')')
End

still error show-
Msg 102, Level 15, State 3, Procedure spInsertFromTextFile, Line 5
Incorrect syntax near ')'.

try this too

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

asm
Posting Yak Master

140 Posts

Posted - 2010-02-14 : 03:45:06

thanks.. its working...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-14 : 03:47:58

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources