How to split a long string with tab delimited

Old Pedant · July 16th, 2008, 06:55 PM

And why do you assume that BULK INSERT will only put data in to a temporary table???

It *CAN* do that, but only if you choose to.

Read the docs:
http://msdn.microsoft.com/en-us/library/aa225968(SQL.80).aspx

In fact, your file is ideal for use with BULK INSERT, because it uses the default delimiters! (TAB and NEWLINE, that is.)

You should be able to simply do
BULK INSERT INTO ...name of your table...
FROM '...absolute path to the data file...'

If you don't want to insert *all* fields from the data file, then you'd need to use a FORMAT FILE. Or if the fields in the data file aren't in the same order, then ditto. Again, read the docs on that page I pointed you to.

Oh, heck, read especially this part:
FORMATFILE [ = 'format_file_path' ]
Specifies the full path of a format file. A format file describes the data file that contains stored responses created using the bcp utility on the same table or view. The format file should be used in cases in which:
-- The data file contains greater or fewer columns than the table or view.
-- The columns are in a different order.
... more ...

Don't make this harder than it is!

Andraw · July 16th, 2008, 08:28 PM

Jeff and Old Pedant, thank you both for your help. I will study BULK INERT.

Old Pedant · July 16th, 2008, 08:37 PM

One thing I noticed in the docs for BULK INSERT that I hadn't seen before: It says you *CAN* use a VIEW of your actual data table for the BULK INSERT! The restriction is that the VIEW can *only* use fields from a single table.

Why do I mention this? Because if the order of your fields in the data table in the DB is *NOT* the same as the order in the text file, you could simply create a VIEW the changed the order to match and it should work!

Example:

dbo.MyTable
    id int
    name varchar(50)
    rating real

Text file:
   name [tab] rating [tab] id

So you would just do

CREATE VIEW pseudoTable AS
    SELECT name, rating, id FROM dbo.MyTable

and presto!

*** COMPLETELY UNTESTED BY ME! Just going on what is written on that page! ***

Andraw · July 16th, 2008, 09:52 PM

My text data file order is fixed, my actural table order is exactly same as the order of data in text file, and I will import all data all rows to my actural table if these rows are not in my actural table (in case there is data error and failed to insert in previous day, maybe client will apply new data for that row in the next day).

1: there are 65 fields in each row. Do I need to specify definition for all 65 fields while create the temporary table?

2: While insert data to my actural table from the temporary table, do I have to specify clearly all these 65 fields?

3: how to conditionally insert data to my actural table from the temporary table(if the row already exist in my actural table, ignor the row in temporary table)

Thanks!

Jeff Moden · July 16th, 2008, 10:23 PM

For 1, Yes. But, since it's the same as a table that already exists, you could create it by using SELECT/INTO with a WHERE 1=0 to create an identical but empty new table.

For 2, No. But, it's a good practice. Again, you can write code that will write the code for you.

For 3, Outer Join with an ISNULL detector in the WHERE clause.

--Jeff Moden

Andraw · July 16th, 2008, 10:36 PM

Jeff, thanks for your reply!

Could you please apply more detail informatons for me about the three questions? can you show me some sample codes to let me know how to do it?

Thanks!

Jeff Moden · July 16th, 2008, 11:10 PM

Sure... but you also need to look them up in Books Online so you learn how to use it... it's a real life saver...

1.

Code:

 SELECT *
   INTO #newtablename
   FROM oldtablename
  WHERE 1=0

2. (Note: Run this in the text mode)

Code:

USE AdventureWorks
GO

DECLARE @TableName SYSNAME
SET @TableName = 'HumanResources.Shift'
DECLARE @Output VARCHAR(MAX)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--===== Makes the INSERT portion of the code
 SELECT @Output = ISNULL(@Output+','+CHAR(10),'') + SPACE(8)+sc.Name
   FROM dbo.SYSCOLUMNS sc,
        dbo.SYSOBJECTS so
  WHERE sc.ID = so.ID
    AND so.ID = OBJECT_ID(@TableName)
  ORDER BY sc.ColID
 SELECT ' INSERT INTO '+@TableName+CHAR(10)+SPACE(8)+'('+CHAR(10)+@Output+CHAR(10)+SPACE(8)+')'

--===== Clear the guns
 SELECT @Output = NULL

--===== Makes the SELECT portion of the code
 SELECT @Output = ISNULL(@Output+','+CHAR(10),'') + SPACE(8)+sc.Name+CHAR(9)+' = xxxx.'+sc.Name
   FROM dbo.SYSCOLUMNS sc,
        dbo.SYSOBJECTS so
  WHERE sc.ID = so.ID
    AND so.ID = OBJECT_ID(@TableName)
  ORDER BY sc.ColID

--===== Display the whole statement
 SELECT ' SELECT' + CHAR(10) + @Output

3.

Code:

--===== Create and populate some demonstration tables
     -- THIS IS NOT PART OF THE SOLUTION
 CREATE TABLE #StagingTable
(
        SomeID   INT,
        SomeDesc VARCHAR(50)
)
 CREATE TABLE #PermanentTable
(
        SomeID   INT PRIMARY KEY CLUSTERED,
        SomeDesc VARCHAR(50)
)

 INSERT INTO #StagingTable
        (SomeID,SomeDesc)
 SELECT 3, 'Item3' UNION ALL
 SELECT 5, 'Item5' 

 INSERT INTO #PermanentTable
        (SomeID,SomeDesc)
 SELECT 1, 'Item1' UNION ALL
 SELECT 2, 'Item2' UNION ALL
 SELECT 3, 'Item3' UNION ALL
 SELECT 4, 'Item4' UNION ALL
 SELECT 6, 'Item6'

--===== Show the table before the selective insert
 SELECT * FROM #PermanentTable

--===== Demo the selective input (the solution)
     -- Won't insert Item3 because it already exists in permanent table
     -- Will insert Item5 because it doesn't
 INSERT INTO #PermanentTable
        (SomeID,SomeDesc)
 SELECT s.SomeID,
        s.SomeDesc
   FROM #StagingTable s
   LEFT OUTER JOIN #PermanentTable p
     ON s.SomeID = p.SomeID
  WHERE p.SomeID IS NULL

--===== Show the table after the selective insert
 SELECT * FROM #PermanentTable

--===== Cleanup so we can run again, if we want
   DROP TABLE #PermanentTable,#StagingTable

--Jeff Moden

Andraw · July 17th, 2008, 10:00 AM

Also every day there are about 100 rows of data in my text file, while use BULK INSERT, do I need to set BATCHSIZE = 1 or not?
Thanks!

Andraw · July 17th, 2008, 10:05 AM

I have 100 rows for each text file, while I use BULK INSERT, do I need to set BATCHSIZE=1?
can I get the number of rows that failed to insert while BULK INSERT?

Thanks!

Jeff Moden · July 17th, 2008, 12:51 PM

Quote:

quote:Originally posted by Andraw
I have 100 rows for each text file, while I use BULK INSERT, do I need to set BATCHSIZE=1?
can I get the number of rows that failed to insert while BULK INSERT?

Thanks!

No, no.... if anything, you should set the batch size to at least 50,000. Better yet, don't include the batch size... let it default to the whole file.

And, it'll tell you if any rows fail.