带插入的SQL过程 [英] SQL Procedure with insert

问题描述

我有一个程序，我向SQL发送文件的所有行，为此，我使用BULK，然后BULK保存了数据库中文件的所有行，我想执行这些行的一些过程例如，获取包含这些行的一些信息并保存到另一个表中，如何在包含行的表中保存的所有记录中执行此操作以及实现此目的的最快方法是什么？



在这个数据库中，发送了这个文件的行;可能包含10行到100,000行，行数非常多变



在该发送行中我有：

在1位-1：位置2-10的一个ID

：姓名
11-20位的
：一条消息



我需要对该行中的信息进行子串，并保存其他表，该表包含以下列：

ID | NAME |消息



我真的不知道如何创建这个程序:(

有人能帮帮我吗？

解决方案

创建存储过程非常简单。查看SQL Server（T-SQL）的在线帮助。至于你想做什么，我认为最简单的事情就是加载你的数据（就像你已经拥有的那样）然后移动它。这样的东西......

  CREATE   PROCEDURE  [LoadFile] 
  @ SourceFilePath   varchar （ 512 ），
  @LogFilePath   varchar （ 512 ）
  AS  
  BEGIN  
   -    SET NOCOUNT ON以防止额外的结果集 
   -   干扰SELECT语句。 
  SET   NOCOUNT   ON ; 
 
   -   设置单列临时表以便在我们玩它时保存数据。 
   -   改变列大小以适合您的数据。 
  SELECT  CAST（ NULL   AS  < span class =code-keyword> VARCHAR （ 255 ）） AS  [LineText]  INTO  #IMPORTBUFFER  WHERE   1  =  0  
 
   -   动态SQL让我们为我们的文件使用变量名。 
   -    ERRORFILE参数特别方便您正在导入数据 
   -   带有列分隔符，例如csv，你不能保证 
   -   入站数据格式正确。 
  exec （'  
 BULK INSERT #ImportBuffer 
 FROM''' +  @ SourceFilePath  + ' ''
 WITH（ 
 ROWTERMINATOR =''\ n''，
 ERRORFILE =''' +  @ LogFilePath  + ' ''
）; 
'）
 
   -   现在取一个子集数据并将其复制到另一个表格。 
   -   起始位置和字段长度仅适用于例如， 
   -   您可能需要为数据更改它们。 
  INSERT   INTO  MyIndexTable（[ID]，[NAME]，[MESSAGE]） 
  SELECT  
 SUBSTRING（[LineText]， 1 ， 1 ），
 SUBSTRING（[LineText]， 2 ， 8 ），
 SUBSTRING（[LineText]， 11 ， 9 ）
  FROM  [#ImportBuffer] 
  WHERE  {任何 其中条款可能是必需的} 
 
   -   重复所需的其他修改/提取。 
   -   最后将缓冲区的内容复制到最终的休息位置。 
 
 将 插入 [FinalTable]（[ImportedText]）
  SELECT  
 [LineText] 
  FROM  [#ImportBuffer] 
  WHERE  {任何 其中条款可能是必需的} 
 
  END  
  GO  

并测试它......

声明 @ filePath varchar （ 512 ）
声明 @ errorLogPath varchar （ 512 ）
set @ filePath = ' C： \ @scratch\somedata.txt'
set @ errorLogPath = ' C：\ scratch \ error.log'

- 清空目标表
truncate table MyIndexTable
sel等 * 来自 MyIndexTable

- 填写目标表
exec LoadFile @ filePath ， @ errorLogPath

- 证明数据已加载。
选择 * 来自 MyOtherTable

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?

解决方案

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...

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...

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

这篇关于带插入的SQL过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！