Click here to Skip to main content
15,881,600 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have a program where I send to SQL all lines of a file, for this, I use the BULK, and after that the BULK saved all lines of the file in the database, I would like to perform some processes these lines, for example, pick up some information that contains these lines and save into another table, how can I do this and what would be the fastest way to accomplish this, in all the records saved in the table that contains the rows?

In this database, which were sent the lines of this file; may contain 10 lines to 100,000 lines, is very fickle the number of lines

In that sended line I have:
in the position 1-1 : one ID
in the position 2-10 : name
in the position 11-20 : one message

I need to substring the information in that line, and save other table, and that table have the columns:
ID | NAME | MESSAGE

I really don't know how I can create that procedure to do this :(
Could someone help me?
Posted
Comments
[no name] 10-Dec-13 12:33pm    
Put your request as reply to this comment in Spanish and I will try to translate that you can improve your question(I know your native is most probably Portuguese...but with Portuguese I can't help, sorry).
Alexandre Bencz 10-Dec-13 12:37pm    
Sorry, but, I really do not understand why to translate spanish?
[no name] 10-Dec-13 12:58pm    
ok then not do so. I just tried to help
CHill60 10-Dec-13 14:02pm    
He meant that your question isn't very clear. What do you mean by "in the position" - are you populating the rows of your first table with lines from a file - so the first table only has one column? And you want to populate the 2nd table with substrings from your first table?
[no name] 10-Dec-13 14:14pm    
So it is, thanks for clarification

1 solution

Creating a stored procedure is straight-forward. Have a look in the on-line help for SQL Server (T-SQL). As for what you want to do I think the simplest thing to do is to load your data (as you already have) and then move it around. Something like this...

SQL
CREATE PROCEDURE [LoadFile] 
  @SourceFilePath varchar(512),
  @LogFilePath    varchar(512)
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

  -- Set up a single column temporary table to hold the data whilst we play with it.
  -- Vary the column size to suit your data.
  SELECT CAST(NULL AS VARCHAR(255)) AS [LineText] INTO #IMPORTBUFFER WHERE 1 = 0

  -- The dynamic SQL lets us use variable names for our files.
  -- The ERRORFILE parameter is especially handy if you're importing data
  -- with column separators, such as csv, and you can't guarantee the 
  -- inbound data is well formatted.
  exec('
  BULK INSERT #ImportBuffer
  FROM ''' + @SourceFilePath + '''
   WITH (
    ROWTERMINATOR = ''\n'',
    ERRORFILE =  ''' + @LogFilePath + '''
  );
  ')

  -- Now take a subset of the data and copy it to another table.
  -- Start position and field lengths are for just for the example,  
  -- you may have to change them for your data.
  INSERT INTO MyIndexTable([ID], [NAME], [MESSAGE])
  SELECT 
    SUBSTRING([LineText],  1,1),
    SUBSTRING([LineText],  2,8),
    SUBSTRING([LineText], 11,9)
  FROM [#ImportBuffer]
  WHERE {any where clauses that might be necessary}

  -- Repeat for as many other modifications/extractions as are necessary.
  -- Finally copy the content of the buffer to its final resting place.

  Insert into [FinalTable] ([ImportedText])
  SELECT 
    [LineText]
  FROM [#ImportBuffer]
  WHERE {any where clauses that might be necessary}

END
GO


And to test it...

SQL
declare @filePath     varchar(512)
declare @errorLogPath varchar(512)
set @filePath =  'C:\@scratch\somedata.txt'
set @errorLogPath  = 'C:\scratch\error.log' 

-- Empty the target table(s)
truncate table MyIndexTable
select * from MyIndexTable

-- Fill the target table(s)
exec LoadFile @filePath, @errorLogPath

-- Prove that the data is loaded.
select * from MyOtherTable
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900