0

As a newcomer to SQL-server topics, here is my first question on Stackoverflow.

I am importing one .txt flat file into local SQL server 2012 by SQL Data import and export wizard for further data process. This datasource .txt file is kept with new data in, no changes on the data columns only with new data set generated on every day. I would like to make these new coming data sync/imported to SQL datatable then.

After bulk importing to SQL server, how to make the data continously updating from the .txt file? Could it realized by SQL server software itself?

.flat data strcutrure like this format:
1;25.12.17;09:21:10; 8034; 1;IBC; 850.0; 1; 1; 57.0; 850.000; ;
1;25.12.17;09:25:17; 8035; 1;IBC; 850.0; 1; 2; 57.0; 850.000; ;
1;25.12.17;09:29:39; 8036; 1;IBC; 850.0; 1; 3; 56.8; 850.000;;
1;25.12.17;09:33:40; 8037; 1;IBC; 850.0; 1; 4; 57.2; 850.000; ;
...
...
...

Xing.Shan
  • 13
  • 4
  • 1
    Your question is too broad, there are many ways to achieve this. Try to google, make something up and eventually come with more specific problem. [How to ask](https://stackoverflow.com/help/how-to-ask) – Vojtěch Dohnal Mar 15 '18 at 06:57
  • Does the file get extra data added, or is there a new file supplied with new data? Is the data guaranteed to not repeat other data? You might want to look into SSIS as there are many guides for this – Nick.Mc Mar 15 '18 at 09:34
  • Please explain exactly how you are importing it now (with code). You can probably just use SQL Agent to schedule the import, but it really depends on whats in the file - is it always new data? – Nick.Mc Mar 15 '18 at 09:35
  • Thanks for the comments. I will add more details to the original question. – Xing.Shan Mar 16 '18 at 04:51

1 Answers1

0

The question is too broad but basically you should follow these steps:

  1. Write a Stored Procedure that read the file and import the data inside a table. You can follow this tutorial but the code will contain something like BULK INSERT TmpStList FROM 'c:\TxtFile1.txt' WITH (FIELDTERMINATOR = '","')
  2. Schedule the execution of the SP in a resonable interval (once a day, once every hour, ...) with SQL Server Agent.

A different approach to the problem would be using MS SSIS to write a DTSX package that check more frequently if the file is being modified since the last time but this approach would take you more time (a get a better result).

Nicolaesse
  • 2,554
  • 12
  • 46
  • 71
  • Nicolaesse,thanks. I will try the first menthod at first, I didn't use the SSIS before and I will test the solution later. – Xing.Shan Mar 16 '18 at 05:18
  • You are wellcome. Please consider upvote and/or acept my question (you can use the icon) in the left of my answer. – Nicolaesse Mar 16 '18 at 07:22