Code To Find The Latest File In A Folder And Export It To A Sql Server Table

Feb 15, 2008

Hi Experts,

I have to find the latest file in a folder and export data to a table in sql server.
The code should be something that has to be incorporated into a t-sql stored procedure.

The file name would for example abc_defYYYYMMDD.xls.
would i be able to find the latest file in the folder using the the datestamp (YYYYMMDD) in the filename.

Please note i would have files in other format and names with datestamp attached to it, so the code has to pick specific file for which the file name starts with 'abc_def'

and export data to a table.

Any help would be highly appreciated

View 10 Replies


ADVERTISEMENT

How To Find Oldest File In A Folder

Jan 29, 2007

I may have one or several files in a folder that act as source files.

How do I loop through the folder, picking the oldest dated file according to its 'Date Modifield' attribute.

Do I have to use a Script Task or similar as I can't seem to find a way to do it with normal tasks.

If so, does anyone have example code.

Thanks

P R W.

View 1 Replies View Related

VB Code To Export And Import SQL Server Table To Oracle

Jan 29, 2008

Hi,

Please do anyone know a VB 2005 code to export and import SQL server table to Oracle. Thanks.

View 3 Replies View Related

Transact SQL :: Find Missing Months In A Table For The Earliest And Latest Start Dates Per ID Number?

Aug 27, 2015

I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:

create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth) 
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'

For the above table, the answer should be:

ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01

1) don't include an ID column,

2) don't use the start date/end dates in the data or

3) use cursors, which are forbidden in my environment.

View 9 Replies View Related

SQL Server 2008 :: Query To Group And Find Latest

Aug 25, 2015

I have a scenario as below for one ID -

+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 10.00 | 2015-08-25 12:05:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
| 5689 | 130.00 | 2015-08-25 12:07:57.107 | 3 |
+------+--------+-----------------------------+-----+

I want to fetch below 3 records from the above scenario i.e. latest record of each amount (Latest is determined using "descr" column i.e. 4 is greater then 3 -

+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
+------+--------+-----------------------------+-----+

But in case of same amounts I am unable to fetch the latest status as even using partitioning will treat them as one.

CREATE TABLE #TMP
(
ID INT,
AMOUNT DECIMAL,
[DATE] DATETIME,
DESCR VARCHAR(10)
)

INSERT INTO #TMP VALUES
(5689,10.00,'2015-08-25 12:10:57.107','4')
,(5689,10.00,'2015-08-24 12:07:57.107','3')
,(5689,10.00,'2015-08-25 12:05:57.107','3')
,(5689,130.00,'2015-08-24 12:07:57.107','4')
,(5689,130.00,'2015-08-25 12:07:57.107','3')

View 8 Replies View Related

Export Data From Sql Server 2005 Table To Text File

Feb 27, 2008



Hii
I want to transfer data from table to a text file.I m trying to use bcp utility and xp_cmdshell.but the export is not successful.
My query is:

EXEC master..xp_cmdshell'bcp "Select * from test..emp" queryout "c:dept.txt" -c -T -x'

and its output is:

NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 16 Average : (187.50 rows per sec.)
NULL


but there is no row copied into c:dept.txt

where is the problem??

Thanx
-Supriya

View 23 Replies View Related

SQL Server 2008 :: Export Images From Server To A Folder Using SSIS

May 26, 2015

I have table which consists of images. I am trying to export those images to a folder.

declare @path varchar(100)= 'c:images'
SELECT [PICTURE]
,@path+[PICTURE] AS Path
FROM [A].[dbo].[PICTURE]

I am getting this error: The data types varchar and varbinary(max) are incompatible in the add operator.

View 1 Replies View Related

T-SQL (SS2K8) :: Find Alt Code Characters In Table

Feb 12, 2015

I have a table with code and description as below

create table isin_code
(
code varchar(5),
code_desc varchar(255)
)
go
insert into isin_code values ('aaa','aäsas')
go
insert into isin_code values ('aaa','asâ•šas')
go
insert into isin_code values ('aaa','aâsas')
go
insert into isin_code values ('aaa','asas')
go

I want to identify the list of alt codes available in the table.

View 6 Replies View Related

Add Existing Shared Folder To File Table

Jan 29, 2015

I need to add an existing shared folder to a SQL FileTable.So this is the path and I created a SQL Filetable and now I need to add it to the filetable.

View 0 Replies View Related

Find All Chars In Table That Are ASCII Code 128 And Greater

Dec 12, 2007

Does anyone know how to query a field in a table where it contains anASCII code >= 128 - without looping through every field for everyrecord in table (using charindex)?Ex of char I would like to find: ü which is char(252)

View 1 Replies View Related

SQL 2012 :: Add Existing Shared Folder To File Table

Jan 29, 2015

I need to add an existing shared folder to a SQL FileTable. So this is the path and I created a SQL Filetable and now I need to add it to the filetable.

View 0 Replies View Related

Integration Services :: Exporting A Table To Flat File In Folder Location

Jun 10, 2015

I am using the below code in my command prompt and it is copying all the records from a particular table and dropping in Flat file format in particular folder location. The below code is working if I am pointing to my local database but if I need to point to different database outside my environment how should I set it here also including the case where User ID and password are required to access the db.bcp AdventureWorks.HumanResources.Department out C:myDepartment_c_t.txt -c -t, -r -T -S.

View 12 Replies View Related

Integration Services :: Exporting A Table From Flat File In Folder Location

Jun 8, 2015

I have a requirement where I have to take all the data available from a sql table and write it out as a flat file in folder location.Its a simple table have 8-10 coloumns, have to take this data on daily basis from sql table and deliver out as flat file in a folder.

View 19 Replies View Related

SQL Server 2014 :: Restore The Latest Transactional Log File Job?

Sep 23, 2015

I'm trying find a way of crating an sql server job which would restore only transactional log file just once a day. The trouble I'm having is trying to get only the latest transactional log file it it possible to achieve through e.g powershell etc as I can't find a way through sql query?

View 9 Replies View Related

Find Latest Records

Dec 16, 2007

Hi all,

I have a question regarding SQL Server Performance and would be grateful for a tip. Let's say I have a DB with 50.000 records. These records belong to 1.000 different datasets, so there is 1 actual and 49 historical data records. For example a company with 1000 employees has a database where each year a new record is created for each employee so after 50 years they have 50.000 records (50 years x 1000 employees). 1 record is actual, and 49 are historical. What is the best way to store this? Main target is performance for the enduser, so when an employee clicks "See all my records" it should be fast. But on the other hand the application mainly works only with the current year. Additionally it should also be fast for the boss of business unit who wants to see the latest records of his e.g. 100 employees. I have some ideas and would like to get your opinion:

1. Retrieve by latest date
Just store the records. To get the current year just select the record with the latest year. Disadvantage might be with larger databases: If the company switches to store the requests per month, each user would have 600 records (12 months x 50 years). Each time the latest record should be retrieved, 600 recards have to be compared regarding the latest date (or sorted by date descending using Top1, but this might be a problem for the boss then? Or could this be combined for a group if the boss wants to see all the latest records of his employees?).

2. Add a 'IsCurrent'-Flag
Each time a new record is stored it should be compared to the latest record. If it is newer, the 'IsCurrent'-Flag should be removed and then checked on the new record. This should be fast processed (because on saving a new record it only needs to be checked against the currently 'IsCurrent'-flagged record), and for retrieving the current record no further comparison is necessary. But how could I do this? I need to update the "AddRecord"-SP with this comparison, but I don't know how to do this.

Currently number 2 is my favorite, I just don't know how to do it ;-) What is your opinion about it, and could you include an example?

Thanks

View 20 Replies View Related

SQL Server 2008 :: Load Latest CSV Files From File Server Automatically

Feb 4, 2015

I need to load the latest csv files from file server , The files are placed in a folder called -

Posted 02022015- --> csv files .

I am able to copy the csv files from filserver using bulk insert (manually) , giving the file location

I am having difficulty picking up the latest folder which is posted on the server and import it into database using a stored proc .

View 2 Replies View Related

Moving Csv File From One Folder To Another Folder

Mar 28, 2008

I am having problem with moving a file from one folder to another folder. Here is the detailed scenario:

I want to move a input.csv file from shared input folder to shared archive folder. i am using the below code to do this.
declare @inpath varchar(100)
SET @inpath = 'move "\abcdefINPUTinput*.csv" "\abcdefARCHIVEarchive.csv"'
EXEC @filestatus = master..xp_cmdshell @inpath
but the problem was it was cutting the input.csv file from INPUT folder but not pasting it in the ARCHIVE folder.
I really appreciate if anyone can help me to solve this or anyone can tell some workarounds.

Thanks,

View 20 Replies View Related

SQL Server 2008 :: How To Get Latest Records From Table

Mar 17, 2015

I have a table where i am inserting into temp table, I mean selecting the records from existing table. From this how can i get latest records.

create table studentmarks
(
id int,
name varchar(20),
marks int
)
Insert into dbo.studentmarks values(1,'sha',20);

[Code] ....

How to write a sql query to get the below output

studentname totalmarks

sha 90
hu 120

View 1 Replies View Related

Export Table To Dbf File

Jul 20, 2005

I need to export certain fields of a table from sql 2000 into a dbffile, but can't find the proper query command to make it work. I alsowant to set this up to run each night automatically.Any help will be greatly appreciated.Randy

View 1 Replies View Related

Can Not Find Reports Folder

Nov 5, 2007

Hi

I donn't see reportserver folder in my local host is there any wrong at instalation time ?

Thank you

View 3 Replies View Related

SQL Server 2014 :: Linked Server To File Folder Security

Dec 8, 2013

Here is my Problem:

1. I have sql 2008 R2 running on my LocalHost.
2. Created Data Base [Customer].
3. Created Linked Server [CUSTOMERLINK] USING Microsoft Jet 4.0 to link to Drive F:Data which has DBF files in it.
4. Create dbo.Customer_Upload Table.
5. INSERT INTO [Customer].[dbo].[Customer_UpLoad]
([Name],[Email])
SELECT
NAME,EMAIL
FROM [CUSTOMERLINK]...[CUS]

All this works fine. I can even put it in to an After Insert Trigger on another table and it works.

My problem is that I need this to work in a scheduled job.

F:Data is just a folder with files in it.

This info is from a Restaurant POS system and I need to update it every night.

I have tried every which way to to setup the security issue as there isn't any login security on the folder and SqlServerAgent wants security.

View 4 Replies View Related

Table Data Export To .sql File.

Nov 16, 2006

Rookie qustion here quys,
Using SQL Sever 2005 Express and Visual Web Devoloper Express.
I'm trying to get data from my local SQL sever database to my shared hosting database. SImple enough but...
1.) GoDaddy does not allow remote connections to the DB server
2.) With GoDaddy you can't just upload an .MDF file to your remote directory and script a connection string. You have to reference the DB server. Can't upload DB file to DB server.
As a work around, I've seen several .sql files that have all the T-SQL that will create a database and tables then insert the data. I've google'd extensively and searched several forums with no luck as to how to export my local data to file. I could then copy and paste into an online utility GoDaddys has to insert the data.
Is there a relatively easy way to export table data to a .sql file??? When I try using SQL Server Management Studio Express and "Scipt Table As> Insert To> File", It just gives me something like the following...
INSERT INTO [pubs].[dbo].[authors]           ([au_id]           ,[au_lname]           ,[au_fname]           ,[phone]           ,[address]           ,[city]           ,[state]           ,[zip]           ,[contract])     VALUES           (<au_id, id,>           ,<au_lname, varchar(40),>           ,<au_fname, varchar(20),>           ,<phone, char(12),>           ,<address, varchar(40),>           ,<city, varchar(20),>           ,<state, char(2),>           ,<zip, char(5),>           ,<contract, bit,>)
Plenty of data in this table, no data in script   ???
Can SQL Server Management Studio Express do what I wanting to do? If not, any suggestions? Know of a better hosing company?

View 3 Replies View Related

Export Text File To A Table

Dec 5, 2000

Anbody please help
I am trying to export a text file to a table using enterprise manager
and all tasks
But the process keeps adding strange charater like squares at the end
of each line and also replaces each empty line in the text file with a record in the table with that square type character. I used the following code to delete all rows with that character (as a work around) but no joy. I am losing hope.

Code is
DELETE FROM table1
WHERE column1 = ' '

View 1 Replies View Related

Table Data Export Into An XML File

Sep 16, 2002

Hi,

I need to export data from some tables into an XML file format.

Any help please?

Thanks
John Jayaseelan

View 4 Replies View Related

How Do I Export A Table To Dbf (dbase) File ?

Dec 29, 2006

besides using the costly EMS Data export application?

I'm using the SQL 2005 Express version......I heard the enterprise manager can do the job but I can't afford a standard edition yet....

View 11 Replies View Related

How To Export Certain Columns From A Table Out To CSV File

Aug 24, 2015

I have a requirement to export data from a SQL Server 2012 table, to a CSV file. This needs to be done either every hour, or ideally if it is possible, whenever a new record is created or an existing record is updated/deleted.

The data to be extracted from SQL is:

Mxmservsite.siteid as Marker_ID, mxmservsite.name as Name, 'SITE' as Group, '3' as Status, '' as Notes, mxmservsite.zipcode as Post_Code, 'GB' as Country, '' as Latitude, '' as Longitude, '' as Delete
Where dataareaid='ansa'

How I can go about doing this?

View 3 Replies View Related

Query A Table For A Value In The Code File.

Feb 7, 2008

Ok, so im pretty much finished writing my forum web page.  However to display things like how many replies each thread has and who replied last, i need to perform a query in the code file.  Im guessing its simple enough but i cant get the syntax for actually performing any query.  I already know the sql syntax like select * from all that stuff but how do i get do something like:
Dim x as integer = sqlQuery("Select count(*) FROM ...")
 Currently i have it all working by creating a table and making it invisible and just pulling data from the table but thats sloppy and pretty ineffecient if i databind a table for every single topic name.

View 9 Replies View Related

SQL Server 2008 :: SSIS - Package To Zip Contents Of A Folder Into One ZIP File

Aug 12, 2015

I have a requirement to create a package that takes all files in a given folder and adds them to a single archive (.zip) file. I've tried several methods using 7zip and while I can create archives for every file in the directory I can't seem to get them into a single .zip file.

View 9 Replies View Related

Cannot Find Folder Maintenance Plans.

Dec 2, 2005

I've been looking for treads with a possible similar problem but thus far nothing.   I recently upgraded SQL 2000 (STD ed.) instance to SQL 2005 (STD ed.). This includes the db's. All their compatibility levels were changed to level 90 (SQL 2005).   Everything seems to be working fine but when trying to migrate a legacy maintenance plan I keep getting the message, "Cannot find folder "Maintenance Plans"." even though the folder is clearly listed.   Not sure where to start. I have created two virtual images and both have the same problem.   Any help would be greatly appreciated. RoviWil

View 8 Replies View Related

SQL Server 2012 :: Query Pulling Latest Info Data From Table

Aug 29, 2014

Let's say I have a table of data as per the below..

I'm trying to extract only the green highlighted items..

The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.

As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).

I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.

View 7 Replies View Related

Export Data From Table To Text File

Apr 22, 2002

I need to export data from a table to a text file, where the data in the table is deleted after written to the file. It is simple using DTS, but I want to do the export in "chunks" of data, committing the delete say after every 1000 rows.

My thought was a stored procedure would be easy enough to do this (done these in Oracle many times), but I don't know the quickest way to export a row of data from a stored procedure to a text file. Isn't using a command-line shell too slow? What are my options?

View 1 Replies View Related

How To Export A Temp Table To A Text File?

Apr 24, 2006

Hello everyone:

I create a temperal table to load data in a stored procedure. At last I want to export this temp table to a text file.

Any suggestion will be great appreciated.

ZYT

View 1 Replies View Related

Export Records Of A Table In TXT File (in Pivot Way)

May 23, 2014

I have to export the records of a table in a .txt file but in a strange way (in a column). For example I have this table with 3 fields:

create table prova
(cod varchar(1),
des1 varchar(2),
des2 Varchar (2)
)
insert into prova values (1, 'A1', 'A2')
insert into prova values (2, 'B1', 'B2')

I need to have the output in this way:

1 cod 1
1 des1 A1
1 des2 A2
8 S
1 cod 2
1 des1 B1
1 des2 B2
8 S

So, I have to put '1' at the beginning of every field and '8 S' at the end of every record.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved