asked
How can I insert data from one flat file to different tables in sql
Hi, Could you please suggest me how can I insert data from one flat file to different tables in sql server Database using SSIS or SQL code? I have a data like this format in flat file: TB1*AP*ENH5895*86597*20120620~ TB2*395*000001*0052959874~ TB3*0569*00*E042165578*0034603409~ TB1*AP*ENH5895*86597*00501025836~ TB2*395*000001*00529265684~ I need to insert it into different 3 tables in sql server 2008 , named TB1, TB2 and TB3. here in raw file : fieldterminator = ‘*’ and rowterminator = ‘~’ 1st and 4th rows records should be inserted in TB1 TABLE. 2nd and 5th rows records should be inserted in TB2 table and 3rd row’s records should be inserted in TB3 table. Thanks in advance
Sure. I'm assuming it is a text file. place it in a directory on the server with a filetype .TXT or .CSV . Create a linked server like this. EXEC sp_addlinkedserver @server= ,-- the name of the linked server to create. server is sysname, with no default. @srvproduct= N'Jet 4.0',--product name of the OLE DB data source to add as a linked server. @provider= N'Microsoft.Jet.OLEDB.4.0', --Is the unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to this data source. @datasrc= ,--Is the name of the data source as interpreted by the OLE DB provider. @location= null, --Is the location of the database as interpreted by the OLE DB provider. @provstr= N'Text'-- Is the OLE DB provider-specific connection string that identifies a unique data source. You should then be able to see your file as a table if you click on the 'linked servers' part of the object browser for that server. Drill down to the table for the database whose name youve chosen and there should be the table. you can access it just like any other table and the SQL to put it into the right table is very trivial. Make sure you copy the table-name exactly from the object browser. You may need to create a schema.ini if the OLE Jet driver can't figure out the file.
You could use OpenDataSource to access the file like a table, then you could use a where clause to limit which rows you are inserting into each table based on the value in the first column. To handle a format like that you will need a schema.ini file, I discuss that at: https://www.sqlservercentral.com/articles/reading-ad-hoc-text-files-with-opendatasource
SSIS is very flexible and will let you do it directly, but you could use the very user friendly import and export wizard to bring it into a staging table, specifying your unusual delimiters in the wizard, and then use normal T-SQL to split it out from the staging table.
Of course, you could always use a language like Python or Perl that is really good at text manipulation to split it out for you before you import it if you are more comfortable doing it that way.
9 People are following this question.
