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

Question

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

sravan.here

SSC Veteran

Points: 272
More actions
April 18, 2013 at 3:42 pm

#271298

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?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Mansfield SSCrazy Points: 2396 More actions · Answer 1

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[/url]. 🙂

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

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 SSC Veteran Points: 272 More actions · Answer 3

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 SSC Guru Points: 186127 More actions · Answer 4

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

sravan.here SSC Veteran Points: 272 More actions · Answer 5

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 SSC Guru Points: 186127 More actions · Answer 6

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

sravan.here SSC Veteran Points: 272 More actions · Answer 7

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 SSC Guru Points: 186127 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 9

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. 😛

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Jeff Moden (4/22/2013)
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. 😛

XML is worse ! This thing only agonisingly repeats the column name once per row 😛

^{“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