1

I want a query or SSIS solution to solve my problem. I have a main table. I want a query that create some tables with tha name of one of my columns values and copy the related record to that table. Example :

 name, id, address,tell
  aaa , 1 , asfsaf , 234
  bbb , 23 , afa    , 654
  aaa , 34 , kkk   , 73
  ccc , 3434 ,hfdg , 643
  ccc , 325 , uytr , 876

So create 3 tables named : "aaa" and "bbb" and "ccc". For more info I attached a pic :

http://www.uploadax.com/images/39411125324526961608.jpg

Thank you

Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
user3154311
  • 13
  • 2

1 Answers1

0

You need to be able to dynamically create tables, use EXEC('insert DDL HERE'), you would be better of calling a stored procedure to handle this than trying to do in SSIS. Look here for EXEC command.

Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)

Step #1: Get distinct list of "new tables"

SELECT DISTINCT name
INTO #TMP_newTables

Step #2:

Create a table for each row captured via a cursor (http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/)

Step #3: Inside the cursor issue the EXEC('CREATE TABLE .......') syntax.

Step #4: Store your data for the current table into a TEMP table.

SELECT col1,col2,col3
FROM MainTable
INTO #TMP_DATA
WHERE name = @CurrentName

SET @InsertSQL = 'INSERT ' + @CurrentName +'(col,col2,col3) SELECT col1,col2,col3 FROM #TMP_DATA'

EXEC(@InsertSQL)
DROP TABLE #TMP_DATA
DROP TABLE #TMP_newTables
Community
  • 1
  • 1
T McKeown
  • 12,971
  • 1
  • 25
  • 32
  • I guess I didn't answer the auto column portion of this.... let me know if you need help on that. BUT you should be able to query system tables to get all the column names. – T McKeown Jan 02 '14 at 16:46