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.
| Author |
Topic |
|
servmgr2004
Starting Member
10 Posts |
 Posted - 2013-10-21 : 10:54:13
|
| I have a .txt file with the data fields below. I am trying to create a SQL query or CF statement that will convert the data into a a SQL INSERT statement. The plan would be to run the query, which would create a TEMP table to pull in the created INSERT statement, then place the TEMP table data into an already created table named FinalTable.data1,data2,1,1 of 1,"data5",data6data1,data2,1,1 of 1,"data5",data6data1,data2,1,1 of 1,"data5",data6 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-21 : 11:10:47
|
If you have this data in a text file and you want to get it into the database, the easiest way would be to use Import/Export wizard. Right click on the database name in SSMS object explorer and select Tasks -> Import data and follow through the wizard.If you do want to generate the insert statements, another option would be to use a regex editor such as TextPad or Notepad++ to edit the text file.However, using the import export wizard has the advantage that it can handle embedded column separators in the data. For example, if your data happened to have an embedded comma such as:data1,data2,1,1 of 1,"data5, with an embedded comma",data6 |
 |
|
|
servmgr2004
Starting Member
10 Posts |
Posted - 2013-10-21 : 11:17:49
|
| I want this to be an automatic process that would run daily, so I would need an actual SQL "INSERT" Query that I could set up to run daily and do the insert creation, then merge into the FinalTable database. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-21 : 11:26:12
|
quote:
Originally posted by servmgr2004
I want this to be an automatic process that would run daily, so I would need an actual SQL "INSERT" Query that I could set up to run daily and do the insert creation, then merge into the FinalTable database.
Import/Export wizard (or the more expanded SSIS) is ideally suited for this type of repeated task that you want to run daily.When you use SSIS wizard to import the file, it also gives you an option to save the "SSIS Package". This package contains all the information you provided, and it is configurable. This package can then be scheduled to run daily (or at any interval you choose) using any scheduling mechanism, including Windows Scheduler, SQL Server Agent, or even third party schedulers. |
|
|
|
servmgr2004
Starting Member
10 Posts |
Posted - 2013-10-21 : 11:53:25
|
| Understood. Is there a way to do it without using Import/Export Wizard or maybe using a CF staement. I am ultimately wanting to run this from a HTML page using a CF statement. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-21 : 13:29:46
|
| I don't know what CF statement is; so I will defer someone to who is familiar with that.I don't know of a way to do a regularly scheduled job via a static HTML page. If you are using something like ASP.Net then you could write the (C# or VB) code to read the text file, parse it, generate appropriate insert statements and send to the SQL Server. If you google for examples of connecting to SQL Server from C#, you will find a lot of tutorials and examples. |
|
|
|
|
|
|
|
|
INSERT statement created