Devide a main table to sub tables dynamically

Question

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 :

Thank you

score 0 · Accepted Answer · edited May 23 '17 at 12:11

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

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

Devide a main table to sub tables dynamically

1 Answers1