SQL Server Performance

Create Table + Bulk Insert

Discussion in 'ALL SQL SERVER QUESTIONS' started by andreandre, Dec 16, 2012.

  1. andreandre New Member

    Hi guys,

    I have a .csv file wich has the following information (exactly like this):

    List Name: User
    Format Version:1.2.5.0
    Date:12/11/2012 12:00:34
    Equipment.:L6867000214
    IP:172.21.12.62

    User,Name,Total,B&W
    [1600],[technical],76,76
    [6611],[Daise],559,559
    [900301],[Richard],0,0

    I want to execute a create table and a bulk insert transaction but I don't know how to insert the information of the top lines of the .csv file like "Equipment.:L6867000214" and "IP:172.21.12.62". I need to create a collumn called "Equipment",other collumn called "IP" and insert this information (repeat "L6867000214" and "172.21.12.62" in all records bulk inserted). The result must be like this:

    IP,Equipment.,User,Name,Total,B&W, Color
    172.21.12.62,L6867000214,[1600],[technical],76,76
    172.21.12.62,L6867000214,[6611],[Daise],559,559
    172.21.12.62,L6867000214,[900301],[Richard],0,0

    I have no idea how to do that. Could anyone here help me out on this issue?

    Thanks a lot!
  2. Shehap MVP, MCTS, MCITP SQL Server

    You can try multiple of options such as :

    1-Either you create a simple DTS job to import the data entity of your excel sheet to your table then you can update the 2 additional columns separately using the DML statement below:

    Update Table set IP = '172.21.12.62',User='L686700021'

    2-Or to Insert the needed data to your table once a time using OPENROWSET Query as below

    declare @v_path nvarchar(1000), @vc_filename nvarchar(1000), @v_sql nvarchar(1000)
    set @v_path = 'C:\Directory'
    set @vc_filename = 'Test.xlsx'

    SET @v_sql = ' insert into table
    SELECT * , '172.21.12.62', 'L686700021' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
    ''Excel 12.0;Database=' + @v_path + '\' + @vc_filename + ';HDR=No;IMEX=1'',
    ''SELECT * FROM [test$]'')'
    print @v_sql
    EXEC sp_executesql@v_sql

    Also you create a specific SSIS Packageto do that job based fundamentally on Data flow Task

    Please let me know if any further help is needed
  3. andreandre New Member

    Hi Shehap,

    Thank you for you response.

    Actually, I have several files within this folder wich I want to read the header and create a collumn to them, after that, I want to insert all the data inside the "regular" collumns. The results for the example above should be like this:

    IP, Equipment., User, Name, Total, B&W, Color
    172.21.12.62, L6867000214, [1600], [technical], 76, 76 (NULL)
    172.21.12.62, L6867000214, [6611], [Daise], 559, 559 (NULL)
    172.21.12.62, L6867000214, [900301], [Richard], 0, 0 (NULL)

    This is for each file inserted wich has different IPs, Equipments etc.
    So I need to do a BULK INSERT that reads the files names, avoid copy them twice validating the their path, parse the header information into pivots and save them in a final table.

    Have you ever tried something like this?

    Thanks!

Share This Page