SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Need No.of Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

EaswarDhana
Starting Member

India
1 Posts

Posted - 10/29/2010 :  05:09:54  Show Profile  Reply with Quote
In my table no.of fields like
field1 field2 field3 field4 field5 field6 ......

i need stored procedure
i need 5 fileds each table like

table1
field1 field2 field3 field4 field5
data data data data data

table2
field6 field7 field8 field9 field10
data data data data data

table3
field11 field12 field13 field14 field15
data data data data data

table4
field16 field17 field18 field19 field20
data data data data data

....
....
....

Dhans

NeilG
Aged Yak Warrior

United Kingdom
530 Posts

Posted - 10/29/2010 :  05:17:37  Show Profile  Reply with Quote
For every table in your database are you meaning
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 10/29/2010 :  06:26:10  Show Profile  Visit nr's Homepage  Reply with Quote
You have a wide table and you want to split it into smaller tables with 5 columns each?
Bit odd. Do you have an id or some sort of pk to associate the smaller tables?
Do you really want to change the database structure (odd to do that in an SP) or to return resultsets to a client.

I'll give the code to split up the table shortly but you might get round to answering that question first.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 10/29/2010 06:26:36
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 10/29/2010 :  06:38:06  Show Profile  Visit nr's Homepage  Reply with Quote
Should be something like this. I haven't tried running or compiling it so there are probably errors but you should get the idea.
@tablename is the source table and id the pk on it.

declare @t table (colname varchar(200), seq int identity)
insert @t (colname) select '[' + column_name + ']' from information.schema.columns where table_name = @tablename

declare @sql varchar(4000)

declare @i int
select @i = 0


while @i < (select max(seq) from @t
begin
select @t = null
select @t = coalesce(@t + ',','') + colname from @t where seq > @i and seq <= @i + 5
select @t = 'select id, ' + @t + ' into table'+ convert(varchar(20),(@i + 6)/5) + ' from ' + @tablename
select @t
--exec (@t)
select @i = @i + 5
end



==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

India
840 Posts

Posted - 10/29/2010 :  06:49:33  Show Profile  Reply with Quote
Try this -

CREATE PROCEDURE SplitTable ( @TableName AS VARCHAR(128) )
AS
BEGIN
SET NOCOUNT ON
	DECLARE @i AS INT, @start AS INT, @cnt AS INT, @Loop AS INT
	DECLARE @SQL AS VARCHAR(MAX)
	DECLARE @ColumnNames AS VARCHAR(MAX)

	SELECT @SQL = '', @i = 5, @Start = 1, @Loop = 1

	SET @cnt = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName)

	WHILE @i <= ( @cnt )
	BEGIN
		SET @ColumnNames = ''
		SELECT @ColumnNames = @ColumnNames + COLUMN_NAME + ',' 
		FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE ORDINAL_POSITION BETWEEN @start AND @i AND TABLE_NAME = @TableName

		SET @ColumnNames = LEFT(@ColumnNames, LEN(@ColumnNames)-1)

		SET @SQL = @SQL + CHAR(13) + 'SELECT ' + @ColumnNames + ' INTO ' + @TableName + CONVERT(VARCHAR(10), @Loop) +  ' FROM ' + @TableName 

		SELECT @Loop = @Loop + 1

		IF @i = @cnt 
		GOTO Exit_Proc

		SET @start = @i+1

		SET @i = @i +	CASE WHEN @cnt - @i >=5 THEN 5 
						ELSE @cnt - @i END
	END
	Exit_Proc:
	--PRINT ( @SQL )
	EXEC ( @SQL )
SET NOCOUNT ON
END
GO
EXEC SplitTable '<Your Table>'


Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER
Go to Top of Page

tiffanys
Starting Member

1 Posts

Posted - 11/01/2010 :  22:29:07  Show Profile  Reply with Quote
i can not understand it

Tomorrow like the chocolate in a box full of imagination.
[url=http://www.pandorasale.org/]pandora beads[/url]
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000