Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is it possible to create tables Dynamically using Cursors from another table with csv values


Is it possible to create tables Dynamically using Cursors from another table with csv values

Author
Message
sravan.here
sravan.here
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 45
Have a Table with the CSV Values in the columns as below

ID Name text
1 SID,DOB 123,12/01/1990
2 City,State,Zip NewYork,NewYork,01234
3 SID,DOB 456,12/21/1990
What is need to get is 2 tables in this scenario as out put with the corresponding values

ID SID DOB
1 123 12/01/1990
3 456 12/21/1990


ID City State Zip
2 NewYork NewYork 01234
Is there any way of achieving it using a Cursor or any other method in SQL server?
Mansfield
Mansfield
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 Visits: 394
I'm not sure how exactly the source table and the data is formatted. Can you please provide a runnable example that creates the table and inserts some sample rows that will cover your test case? Something like in this article. :-)
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10215 Visits: 9549
I'm afraid I'm just not sure what you're after from the post above. If you post a create table statement and some inserts to populate it, then describe what you want the result to be, we may be able to help you out.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
sravan.here
sravan.here
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 45
Hi,
Yes please see the Create table script for source table and the insert script as well

CREATE TABLE yt
   ([ID] int, [Name] varchar(14), [text] varchar(21))
;
   
INSERT INTO yt
   ([ID], [Name], [text])
VALUES
   (1, 'SID,DOB', '123,12/01/1990'),
   (2, 'City,State,Zip', 'NewYork,NewYork,01234'),
   (3, 'SID,DOB', '456,12/21/1990')
;

Now the out put should have 2 tables as i mentione in my question. and these values in the source table are getting populated dynamically so i cannot hardcode them in my script for generating the output tables.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
sravan.here (4/18/2013)
Hi,
Yes please see the Create table script for source table and the insert script as well

CREATE TABLE yt
   ([ID] int, [Name] varchar(14), [text] varchar(21))
;
   
INSERT INTO yt
   ([ID], [Name], [text])
VALUES
   (1, 'SID,DOB', '123,12/01/1990'),
   (2, 'City,State,Zip', 'NewYork,NewYork,01234'),
   (3, 'SID,DOB', '456,12/21/1990')
;

Now the out put should have 2 tables as i mentione in my question. and these values in the source table are getting populated dynamically so i cannot hardcode them in my script for generating the output tables.


This is an unusual transport file with metadata (partial, column names only) and data as string on the same row. Where / how was it generated? How does it deal with embedded quotes?

There are two row types in the sample, one with metadata defining two columns 'SID,DOB' and the other defining three columns 'City,State,Zip'. The key question is - are they constant throughout the table? Are there any rows having 'Street,City,State,Zip' as the metadata?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
sravan.here
sravan.here
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 45
Yes there are tables with 4 or 5 or more columns as well as you doubted.
I have just provide a simple example here.

But the maximum column names (with csv) will be 26 in the meta file / table.

I am just looking for a way to create tables on the fly.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
sravan.here (4/19/2013)
Yes there are tables with 4 or 5 or more columns as well as you doubted.
I have just provide a simple example here.

But the maximum column names (with csv) will be 26 in the meta file / table.

I am just looking for a way to create tables on the fly.


How do you identify which rows go into which tables? With your sample data it's easy - two tables. A larger and more realistic sample data set would be very useful.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
sravan.here
sravan.here
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 45
All the similar rows should go into one table. In my example, row 1 and 3 will be in table and 2 will be another table. but my metatable will have thousands of rows and possibly they have 10 or 20 tables that will have to be created.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
sravan.here (4/19/2013)
All the similar rows should go into one table. In my example, row 1 and 3 will be in table and 2 will be another table. but my metatable will have thousands of rows and possibly they have 10 or 20 tables that will have to be created.


This is straightforward to do, coding against the simple sample dataset:

SET NOCOUNT ON

------------------------------------------------------------------
-- get the list of tables and their column list
------------------------------------------------------------------
IF object_id('tempdb..#TableList') IS NOT NULL
   DROP TABLE #TableList

SELECT DISTINCT
   TableCount = MAX(TableID) OVER(PARTITION BY 1),
   TableID,
   TableName = 'Staging.dbo.Table' + RIGHT('00'+CAST(TableID AS VARCHAR(3)),3),
   ColumnNumber,
   ColumnName
INTO #TableList
FROM (
   SELECT
      TableID = DENSE_RANK() OVER(ORDER BY yt.[Name]),
      ColumnNumber = x.ItemNumber,
      ColumnName = x.Item
   FROM yt
   CROSS APPLY dbo.DelimitedSplit8K(yt.[Name],',') x
) d
ORDER BY TableID, ColumnNumber


------------------------------------------------------------------
-- shape the data for convenience
------------------------------------------------------------------
IF object_id('tempdb..#ProcessedData') IS NOT NULL
   DROP TABLE #ProcessedData

;WITH ProcessedData AS (
SELECT
   TableID = DENSE_RANK() OVER(ORDER BY [Name]),
   RowID = ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY ID),
   [Name],
   [Text]
FROM yt
)
   SELECT
      TableID,
      RowID,
      x.ItemNumber,
      ColumnName = x.Item,
      ColumnValue = y.Item
   INTO #ProcessedData
   FROM ProcessedData yt
   CROSS APPLY dbo.DelimitedSplit8K([Name],',') x
   CROSS APPLY dbo.DelimitedSplit8K([Text],',') y
   WHERE y.ItemNumber = x.ItemNumber
ORDER BY TableID, RowID



------------------------------------------------------------------
-- Cycle through the list of tables, create or truncate
-- Then load with data
------------------------------------------------------------------

-- set up some variables
DECLARE @TableID INT, @ColumnList VARCHAR(1000), @TableName VARCHAR(100), @Script VARCHAR(2000)
SET @TableID = 1

WHILE @TableID > 0
BEGIN
   -- create or truncate the table
   SELECT @TableName = TableName
   FROM #TableList
   WHERE TableID = @TableID
   
   IF @@ROWCOUNT = 0 BREAK

   SET @ColumnList = NULL
   
   SELECT @ColumnList = ISNULL(@ColumnList + ', ',' ') + ColumnName + ' VARCHAR(100)'
   FROM #TableList
   WHERE TableID = @TableID
   ORDER BY ColumnNumber

   SET @Script =
      'IF object_id(''' + @TableName + ''') IS NULL' + CHAR(10) +
      'CREATE TABLE ' + @TableName + ' (' + @ColumnList + ') ' + CHAR(10) +
      'ELSE ' + CHAR(10) +
      'TRUNCATE TABLE ' + @TableName

   PRINT ''
   PRINT @Script
   EXEC(@Script)



   -- insert data into table @TableName
   SET @ColumnList = NULL

   SELECT @ColumnList = ISNULL(@ColumnList + ', ', ' ') +
      '[' + ColumnName + '] = MAX(CASE WHEN ItemNumber = ' + CAST(ColumnNumber AS VARCHAR(3)) +
      ' THEN ColumnValue END)' + CHAR(10)
   FROM #TableList
   WHERE TableID = @TableID
   ORDER BY ColumnNumber
   
   SET @Script =
      'INSERT INTO ' + @TableName + CHAR(10) +
      'SELECT ' + CHAR(10) + @ColumnList +
      'FROM #ProcessedData ' + CHAR(10) +
      'WHERE TableID = ' + CAST(@TableID AS VARCHAR(3)) + CHAR(10) +
      'GROUP BY RowID'

   PRINT ''
   PRINT @Script
   EXEC(@Script)
   PRINT ''
   PRINT '-----------------------------------------------------------------'

   SET @TableID = @TableID + 1

END   

SELECT * FROM Staging.dbo.Table001
SELECT * FROM Staging.dbo.Table002





“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44993 Visits: 39879
ChrisM@Work (4/19/2013)
This is an unusual transport file with metadata (partial, column names only) and data as string on the same row.


BWAAAA-HAAA!!!!! I say the same thing everytime I have to use XML. :-P

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search