Insert INTO - 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 INTO

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-21 : 06:37:27

Hi, this query runs fine, but the problem is it inserts NULL values on the filename field.

DECLARE @cmd nvarchar(200)
DECLARE @Filename nvarchar (200)
SET @cmd = 'dir /b C:\Collections\*.*'

if NOT EXISTS (SELECT Filename FROM TblFilenameList WHERE Filename = @Filename)
INSERT INTO dbo.TblFilenameList (Filename)
Values (@Filename)

EXEC xp_cmdshell @cmd

For example,

I have two folders test1.zip and test2.zip

Results

ID Filename
1 NULL
2 NULL

I am expecting it to be

Results

ID Filename
1 Test1.zip
2 Test2.zip

any help please!!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-07-21 : 06:49:14

Where are you setting @Filename to be a value as you certainly aren't doing it in this query?

I think you want your results from xp_cmdshell instead of @Filename..

something like this?

DECLARE @cmd nvarchar(200)
DECLARE @Filename nvarchar (200)
SET @cmd = 'dir /b C:\Collections\*.*'

create table #TblFilenameList (Filename nvarchar(200))

INSERT INTO #TblFilenameList (Filename)
EXEC xp_cmdshell @cmd

select * from TblFilenameList

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-21 : 06:57:38

Ok, thats fine .. but the folder in C:\Collections will always be populated with new files and i would like to update TblFilenameList with the new files.

For example, when I have already Test1.zip and Test2.zip in TblFilenameList. Then, a new file was added to the folder - Collections e.g., Test3.zip.
I want to re-run the script and populate Test3.zip to TblFilenameList. The suggested query won't do that...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 07:24:26

you mean you need to run script periodically and check for newly added files?

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

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-21 : 07:43:55

exactly.. Visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 07:47:03

then you may be better off adding this to sql agent job and scheduling it to be executed in period as per your convienience.
You can even create simple SSIS package for this rather than using xp_cmdshell

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

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-21 : 08:00:26

I will set to SQL agent job and schedule it., however I need to get the function do it right ..
How can I do it SSIS?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 10:05:32

you can do it in SSIS by adding a for each file enumerator and using a execute sql task inside it

see below
http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources