SQL Clone
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
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16459 Visits: 10050
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16047 Visits: 19528
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16047 Visits: 19528
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16047 Visits: 19528
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 Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84889 Visits: 41069
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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