How to create the Dynamic Table...

Question

Post reply

How to create the Dynamic Table...

sarwaanmca

Right there with Babe

Points: 746
More actions
June 19, 2013 at 11:45 am

#294881

Hi Friends,
I have the sample data for the following Structure
Create Table PivotA ( IDT int,A int,B int)
insert into PivotA values (24,1,-1),(24,2,-2),(24,3,-3),(24,4,-4),(25,5,-5),(25,6,-6),(25,7,-8),(26,8,-8),(26,9,-9),(26,10,-10)
select * from PivotA
but i need to create the tables dynamically for the following structure..
based on the PivotA Table...
create table IDT_24( A int,B int)
insert into IDT_24 values (1,-1),(2,-2),(3,-3),(4,-4)
create table IDT_25( A int,B int)
insert into IDT_25 values (5,-5),(6,-6),(7,-8)
create table IDT_26( A int,B int)
insert into IDT_26 values (8,-8),(9,-9),(10,-10)
select * from IDT_24
select * from IDT_25
select * from IDT_26
How To Get it?
any one help me...
Thanks & Regards
K.D.Saravanan

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

What is the purpose of this? This is going to require some pretty complicated dynamic sql for this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

Are you looking for something like this...

--(1) THE DATA

IF OBJECT_ID('ajbtest.dbo.PivotA') IS NOT NULL

DROP TABLE PivotA;

CREATE TABLE dbo.PivotA (IDT int, A int, B int);

INSERT dbo.PivotA VALUES (24,1,-1),(24,2,-2),(24,3,-3),(24,4,-4),(25,5,-5),(25,6,-6),(25,7,-8),(26,8,-8),(26,9,-9),(26,10,-10);

--SELECT * FROM dbo.PivotA;

--(2) THE ROUTINE

DECLARE @sql1 varchar(1000);

DECLARE @col_a varchar(30)=(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA='dbo'

AND TABLE_NAME='PivotA'

AND ORDINAL_POSITION=2);

DECLARE @col_b varchar(30)=(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA='dbo'

AND TABLE_NAME='PivotA'

AND ORDINAL_POSITION=3);

SELECT @sql1='SELECT '+@col_a+', '+@col_b+' INTO newTable FROM dbo.PivotA';

EXEC(@sql1);

SELECT * FROM newTable

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

I am pretty sure you are looking for something more like this.

--first we have to create the tables

declare @Tables nvarchar(max)

select @Tables =

(

select 'create table IDT_' + cast(IDT as varchar(25)) + ' (A int, B int);'

from PivotA

group by IDT

for xml path('')

)

select @Tables

exec sp_executesql @Tables

--Now we need to generate the data

declare @Inserts nvarchar(max)

select @Inserts =

(

select 'insert into IDT_' + cast(IDT as varchar(25)) + '(A, B) select ' + CAST(A as varchar(10)) + ', ' + CAST(B as varchar(10))

+ ' from PivotA where IDT = ' + cast(IDT as varchar(25)) + ';'

from PivotA

for xml path('')

)

select @Inserts

exec sp_executesql @Inserts

This will generate each of your unique tables and populate them with the correct data.

This code produces what you said you wanted in your post but this process seems to be a very bad implementation. It suggests that are some very very bad design choices in the system.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Sean Lange (6/19/2013)
What is the purpose of this? This is going to require some pretty complicated dynamic sql for this.

It's not that complicated, but it seems like a very bad idea. This could generate lots and lots of tables. I'm sure this is against best practices for database design.

However, I'm leaving one solution.

Create Table dbo.PivotA ( IDT int,A int,B int)

insert into dbo.PivotA values (24,1,-1),(24,2,-2),(24,3,-3),(24,4,-4),(25,5,-5),(25,6,-6),(25,7,-8),(26,8,-8),(26,9,-9),(26,10,-10)

DECLARE @sql1 varchar(8000)= '';

SELECT @sql1= @sql1 + 'SELECT A, B INTO dbo.IDT_' + CAST( IDT AS varchar(10)) + ' FROM dbo.PivotA;' + CHAR(13)

FROM (SELECT DISTINCT IDT FROM dbo.PivotA) x;

--PRINT @sql1

EXEC( @sql1);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2