SQL 2005 bulk insert when file name changes

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 2005 Forums

SSIS and Import/Export (2005)

SQL 2005 bulk insert when file name changes

Author

Topic

Raptor
Starting Member

8 Posts

Posted - 2009-07-06 : 02:00:17

I am new here so I hope I have not ask something that has not been posted. I look but did not find my solution.

I have a txt file that is stored on a windows file share by date. 070509newacct.txt. This file changes everyday, A new file get uploaded to this folder with a new name so I would need to insert this new file everyday. I can change the SQL script to reflect the name manually to import thins but I was hoping I could right a script or a stored procedure to handle the name change and bulk insert.

This is what I looking at
bulk insert dbo.FGHDATA from '\\server\path\"SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYYYY]" & "newacct.txt' with (Batchsize = 10000, codepage='raw', rowterminator='\n').

Well this did not work.

Any ideas how I could resolve this?

Thank You

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 02:17:16

BULK INSERT does not accept such operation. Use a variable to form the file name and then pass to BULK INSERT


declare @fullpath varchar(1000)

select @fullpath = '\\server\path\' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') + 'newacct.txt'

bulk insert dbo.FGHDATA from @fullpath with (Batchsize = 10000, codepage='raw', rowterminator='\n')

KH
[spoiler]Time is always against us[/spoiler]

Raptor
Starting Member

8 Posts

Posted - 2009-07-06 : 02:40:56

Hey KH Tanks for the reply

This is what I tried

declare @fullpath varchar(1000)

select @fullpath = SELECT '\\mp-server2\docs\Forrest General\FTP Uploads\Import_Files\' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '')) + 'newacct.txt'

bulk insert dbo.FGHDATA from @fullpath with (Batchsize = 10000, codepage='raw', rowterminator='\n')

This is the error I get

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '\'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@fullpath'.
Msg 319, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 02:42:21

edited the post

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 02:44:20

also


declare @cmd nvarchar(1000)
select @cmd = 'bulk insert dbo.FGHDATA from ' + @fullpath + 'with (Batchsize = 10000, codepage=''raw'', rowterminator=''\n'')'

exec (@cmd)

KH
[spoiler]Time is always against us[/spoiler]

Raptor
Starting Member

8 Posts

Posted - 2009-07-06 : 02:45:07

I must be a dummy.

This is the new error message

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@fullpath'.
Msg 319, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 02:50:41

see my last post. If not mistaken, bulk insert does not support passing of variables. Use dynamic sql exec() to achieve this

KH
[spoiler]Time is always against us[/spoiler]

Raptor
Starting Member

8 Posts

Posted - 2009-07-06 : 03:00:05

Well I am closer with your help. This is the new script

declare @fullpath varchar(1000)
select @fullpath = '\\mp-server2\docs\Forrest General\FTP Uploads\Import_Files\' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') + 'newacct.txt'
declare @cmd nvarchar(1000)
select @cmd = 'bulk insert dbo.FGHDATA from ' + @fullpath + 'with (Batchsize = 10000, codepage=''raw'', rowterminator=''\n'')'
exec (@cmd)

Error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 03:02:43

do a print @cmd before exec (@cmd) and check the syntax

KH
[spoiler]Time is always against us[/spoiler]

Raptor
Starting Member

8 Posts

Posted - 2009-07-06 : 03:06:22

bulk insert dbo.FGHDATA from \\mp-server2\docs\Forrest General\FTP Uploads\Import_Files\070609newacct.txt with (Batchsize = 10000, codepage='raw', rowterminator='\n')
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 03:08:35

oh forgot to enclose the full path in single quote


select @fullpath = '''\\mp-server2\docs\Forrest General\FTP Uploads\Import_Files\' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') + 'newacct.txt'''

KH
[spoiler]Time is always against us[/spoiler]

Raptor
Starting Member

8 Posts

Posted - 2009-07-06 : 03:14:22

Thank You KH.

Here is the entire script and it looks like you have a home run.

declare @fullpath varchar(1000) select @fullpath = '''\\mp-server2\docs\Forrest General\FTP Uploads\Import_Files\' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') + 'newacct.txt'''
declare @cmd nvarchar(1000)
delete from fghDATA
select @cmd = 'bulk insert dbo.FGHDATA from ' + @fullpath + ' with (Batchsize = 10000, codepage=''raw'', rowterminator=''\n'')'
Insert FGHnew(PatNo,[Med-REC-NO],[SERVICE-DATE],[Guar-ssno],[GUAR-FIRST-NAME],[GUAR-MI],[GUAR-LAST-NAME],[GUAR-LNAME-SFX],[GUAR-TITLE],[PAT-SSNO],[PAT-FIRST-NAME],[PAT-MI],[PAT-LAST-NAME],[PAT-LNAME-SFX],[PAT-TITLE],[PAT-BIRTHDATE],[PAT-ADD1],[PAT-ADD2],[PAT-CITY],[PAT-STATE],[PAT-ZIP],[PAT-PHONE],[TOTAL-CHARGES],[CURRENT-BALANCE],[PAT-TYPE],INS1,INS2,INS3,INS4)
select substring(data,1,12), Substring(data,18,10), substring(data,28,9), substring(data,36,9), substring(data,45,14), substring(data,60,1),substring(data,61,25),substring(data,86,5),substring(data,91,5),substring(data,96,9),substring(data,105,15),substring(data,120,1),substring(data,121,25),substring(data,146,5),substring(data,151,5),substring(data,156,8),substring(data,164,25),substring(data,189,25),substring(data,214,15),substring(data,229,2),substring(data,231,5),substring(data,236,10),substring(data,246,11),substring(data,257,11),substring(data,308,1),substring(data,309,3),substring(data,312,3),substring(data,315,3),substring(data,318,3) from fghdata
print @cmd
exec (@cmd)

Thank You

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources