Update a table - 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

Update a table

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-19 : 07:45:20

Hi,

I have a folder (G:\Test) which has 3 csv files (Test1.csv, Test2.csv, Test3.csv) on the server. On my database (GTech) I have a table called Tbl.Filename, it has two fields FilenameID and Filename. FilenameID is the primary key and also auto increment.

Now I want to be able to read the contents of the 3 csv files in the folder Test and copy the filenames to Tbl.Filaname. So that my table opens us

FilenameID Filename
1 Test1
2 Test2
3 Test3

Any ideas? Thanks

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-19 : 08:27:31

Ok, this worked for me...

DECLARE @cmd nvarchar(200)
SET @cmd = 'dir /b G:\Test\*.csv'

INSERT INTO dbo.TblFilenameList (Filename)
EXEC xp_cmdshell @cmd

SELECT *
FROM dbo.TblFilenameList

Results

ID Filename
1 Test1.csv
2 Test2.csv
3 Test3.csv
4 NULL

Now, I want to update the code to check if the filename exists in Tblfilenamelist if so,then ignore it and copy the next filename...
My G:\Test has an extra file Test4.csv...

Running the same script gives the result..

ID Filename
1 Test1.csv
2 Test2.csv
3 Test3.csv
4 Test4.csv
5 NULL
6 Test1.csv
7 Test2.csv
8 Test3.csv
9 Test4.csv
10 NULL

I wanted the result to be;

ID Filename
1 Test1.csv
2 Test2.csv
3 Test3.csv
4 Test4.csv
5 NULL

N/B - TblFilenamelist has duplicates too

Any ideas .. many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-19 : 08:57:24

DECLARE @cmd nvarchar(200)
SET @cmd = 'dir /b G:\Test\*.csv'

INSERT INTO dbo.TblFilenameList (Filename)
EXEC xp_cmdshell @cmd

SELECT distinct row_number() over(order by FileName) as ID, FileName
FROM dbo.TblFilenameList

Madhivanan

Failing to plan is Planning to fail

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-19 : 09:07:07

It didn't work!!

Results, similar

ID Filename
1 Test1.csv
2 Test2.csv
3 Test3.csv
4 Test4.csv
5 NULL
6 Test1.csv
7 Test2.csv
8 Test3.csv
9 Test4.csv
10 NULL

I want to append tblFilename, so for the above example the table should ONLY have 4 records

ID Filename
1 Test1.csv
2 Test2.csv
3 Test3.csv
4 Test4.csv

No matter how many times I run the script, the table should have the same records until when a new file is added in G:\Test, then it will be appended. For example, I add another file Test5.csv..

Result will change to;

ID Filename
1 Test1.csv
2 Test2.csv
3 Test3.csv
4 Test4.csv
5 Test5.csv

Please ignore the select statement it is NOT necessary, my objective is to append tblFilenamelist...

Any help please

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-19 : 10:07:59

Tried this code and the output result is fine displayed on the screen, however, Tblfilenamelist is not appended with new files.. Why

DECLARE @cmd nvarchar(200)
SET @cmd = 'dir /b G:\Test\*.csv'

INSERT INTO dbo.TblFilenameList (Filename)
SELECT Filename from dbo.TblFilenameList WHERE not exists (select filename from dbo.TblFilenameList)
EXEC xp_cmdshell @cmd

Many thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources