Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Is it possible to create tables Dynamically using Cursors from another table with csv values Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 3:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 4, 2014 5:46 PM
Points: 12, Visits: 42
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?
Post #1444177
Posted Thursday, April 18, 2013 7:56 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
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.
Post #1444206
Posted Thursday, April 18, 2013 9:00 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:54 PM
Points: 3,972, Visits: 2,983
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
Post #1444211
Posted Thursday, April 18, 2013 9:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 4, 2014 5:46 PM
Points: 12, Visits: 42
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.
Post #1444222
Posted Friday, April 19, 2013 2:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 7,127, Visits: 13,503
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
Post #1444272
Posted Friday, April 19, 2013 5:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 4, 2014 5:46 PM
Points: 12, Visits: 42
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.
Post #1444320
Posted Friday, April 19, 2013 5:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 7,127, Visits: 13,503
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
Post #1444324
Posted Friday, April 19, 2013 6:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 4, 2014 5:46 PM
Points: 12, Visits: 42
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.
Post #1444336
Posted Monday, April 22, 2013 5:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 7,127, Visits: 13,503
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
Post #1444929
Posted Monday, April 22, 2013 6:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1444944
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse