Prev: Querying XML data
Next: Question on OVER Clause Useage
From: hbjimmy on 28 Jul 2010 13:17
I have a flat file I am importing into SQL server db. One of the fields is a
long string field, and in that string field, there are 3 pieces of
information I need to report on. Example "Fees Criminal Tar Office"

I need to be able to report by grouping "Criminal", "Tar" & "office".

How do you suggest I do that in MS SQL? via sub-queries or should I import
them into seperate tables?
From: Eric Isaacs on 28 Jul 2010 13:45
Import them into TempDB as they are...then import that data from
tempdb and parse that string field to separate the possible values
into their own fields in the target table. I would not recommend
leaving them in that string field in the target table, which is what I
think you were asking.

-Eric Isaacs
From: Erland Sommarskog on 28 Jul 2010 18:23
hbjimmy (hbjimmy(a)discussions.microsoft.com) writes:
> I have a flat file I am importing into SQL server db. One of the fields
> is a long string field, and in that string field, there are 3 pieces of
> information I need to report on. Example "Fees Criminal Tar Office"
>
> I need to be able to report by grouping "Criminal", "Tar" & "office".
>
> How do you suggest I do that in MS SQL? via sub-queries or should I import
> them into seperate tables?

Well, either import into staging table, and then crack the fields. Or import
trough SSIS and use some tranformation operator.

Personally, I would use the first approach, as I'm completely out of touch
with SSIS!



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Querying XML data
Next: Question on OVER Clause Useage