Export table data to flat file

• simon phoenix-479217

  SSCommitted

  Points: 1573

  More actions

  January 29, 2009 at 7:08 pm

  #130495

  Hi

  Guys

  I have a table like this

  CREATE TABLE [dbo].[Test](

  [col1] [int] NOT NULL,

  [col2] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

  )

  Truncate table dbo.Test

  insert into dbo.Test

  select '2','a' union all

  select '2','b' union all

  select '2','c' union all

  select '3','d' union all

  select '3','e' union all

  select '4','f' union all

  select '5','g' union all

  select '5','h' union all

  select '5','i' union all

  select '5','j' union all

  select * from dbo.Test

  I would like to popluate into .txt file in the following format

  2 a b c

  3 d e

  4 f

  5 g h i j

  Is there a VB script that can be imbeded into Script task tool of inside SSIS

  Thanks

  Simon

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 29, 2009 at 8:12 pm

  #935239

  the number one tool to export SQL queries and tables to disk would be BCP; you can decide what the delimiters would be, ie commas or tab delimited, or even unusual characters;

  unless you need something exotic, like appending to existing files, I'd suggest you use that utility. It is also blazingly fast.

  it is a command line utility, and has dozens of flags...too many to even try to explain here.

  you'll want to read Books online for it, as well as search fro "BCP queryout" here on SSC to find other users examples.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• simon phoenix-479217

  SSCommitted

  Points: 1573

  More actions

  January 30, 2009 at 4:26 pm

  #935899

  Hey

  Lowell

  Thanks for the input , but i was able to do it with the help of user defined function and if you would like to explore it is posted on the development forum and a guy name Mahesh helped me out

  Thanks

  Simon