How Do I Split One Temp Table Into Multiple Temp Tables?

Question

1.00/5 (1 vote)

See more:

I have a table in a hierarchy order. Now i want to split into multiple tables where only root-id=1 data are stored. Then in same manner, root-id=2 should be stored in 2nd table.It should be repeated for for n number of roots. The table looks like this:
##gblTempTable

level	clasi_clasid	clasi_name	clasi_parentid	clasi_rootid	
1	1	Main Classification1	   NULL	             1	              
1	3	Main Classification1	   NULL	             1	              
1	4	Main Classification2	   NULL	             1	              
1	5	Main Classification3	   NULL	             1	              
2	8	Sub Classification1	    5	             2	              
3	11	Next Sub Classification1    8	             3	              
2	7	Sub Classification1	    4	             2	              
3	10	Next Sub Classification1    7	             3	              
2	6	Sub Classification1	    3	             2	              
2	2	Sub Classification1	    1	             2

The desired output:
##gblTempTable1

level	clasi_clasid	clasi_name	clasi_parentid	clasi_rootid	
1	1	Main Classification 1	NULL	1	
1	3	Main Classification 1	NULL	1	
1	4	Main Classification 2	NULL	1	
1	5	Main Classification 3	NULL	1

##gblTempTable2

level	clasi_clasid	clasi_name	clasi_parentid	clasi_rootid
2	8	Sub Classification 1	5	2	
2	7	Sub Classification 1	4	2	
2	6	Sub Classification 1	3	2	
2	2	Sub Classification 1	1	2

In this way, i want output for n number of root id. The ##gblTempTable should be split into multiple temp tables in a loop manner. How can i achieve this?
The mai intention is to produce book oreder indexing like 1->1.1->1.1.1, 2->2.1,2->2.2,2.1.1 etc.

Posted 16-Feb-14 20:46pm

C135

Updated 16-Feb-14 20:47pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sohail awr · Answer 1 · 2014-02-17T03:34:00

SQL

USE [DBNAME]
GO
/****** Object:  StoredProcedure [dbo].[sp3_loop]    Script Date: 02/17/2014 20:03:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CRWEATE PROCEDURE [dbo].[sp3_loop]
	@param1 varchar(1000)='1,2,3,4'
	AS

SET NOCOUNT ON

DECLARE @Prm_id varchar(10), @Pos int
DECLARE @Str1 varchar(1000)
	SET @param1  = LTRIM(RTRIM(@param1)) + ','
	SET @Pos = CHARINDEX(',', @param1, 1)

IF REPLACE(@param1, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @Prm_id = LTRIM(RTRIM(LEFT(@param1, @Pos - 1)))
			IF @prm_id <> ''
			IF(EXISTS(select 1 from gblTempTable where clasi_rootid = @prm_id))

			BEGIN
			select * from gblTempTable where clasi_rootid = @prm_id order by clasi_clasid
			END

			SET @param1 = RIGHT(@param1, LEN(@param1) - @Pos)
			SET @Pos = CHARINDEX(',', @param1, 1)
			END
			END

sohail awr · Answer 2 · 2014-02-19T05:59:00

I have tried through SQL procedures but unable to create multiple tables so I have generate vb.net code please use this it is working properly..

VB

Public oCn As New System.Data.SqlClient.SqlConnection("Data Source=(local);Initial Catalog=MSTMDB;Uid=sa")

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim ds, ds1 As New DataSet
        Dim root_param As String = ""
        Dim i As Integer = 0
        Dim j As Integer = 0
        ds = select_data("select clasi_rootid from gblTempTable group by clasi_rootid order by clasi_rootid")
        If ds.Tables(0).Rows.Count > 0 Then
            While (i <> ds.Tables(0).Rows.Count)
                Get_ValueByQuery("CREATE TABLE [dbo].[gblTempTable" & ds.Tables(0).Rows(i).Item("clasi_rootid").ToString & "]([level1] [int] NULL,	[clasi_clasid] [int] NULL,	[clas_name] [varchar](50) NULL,	[clasi_parentid] [int] NULL,[clasi_rootid] [int] NULL) ON [PRIMARY]")
                ds1 = select_data("select level1,clasi_clasid,clas_name,clasi_parentid,clasi_rootid from gblTempTable where clasi_rootid =" & ds.Tables(0).Rows(i).Item("clasi_rootid").ToString)
                If ds1.Tables(0).Rows.Count > 0 Then
                    While (j <> ds1.Tables(0).Rows.Count)
                        Get_ValueByQuery("Insert into gblTempTable" & ds.Tables(0).Rows(i).Item("clasi_rootid").ToString & "(level1,clasi_clasid,clas_name,clasi_parentid,clasi_rootid) values(" & ds1.Tables(0).Rows(j).Item("level1").ToString & "," & ds1.Tables(0).Rows(j).Item("clasi_clasid").ToString & ",'" & ds1.Tables(0).Rows(j).Item("clas_name").ToString & "'," & Val(ds1.Tables(0).Rows(j).Item("clasi_parentid").ToString) & "," & ds1.Tables(0).Rows(j).Item("clasi_rootid").ToString & ")")
                        j = j + 1
                    End While
                End If
                ds1.Clear()
                j = 0
                i = i + 1
            End While
        End If
    End Sub

    Function select_data(ByVal str As String) As DataSet
        If oCn.State = ConnectionState.Closed Then
            oCn.Open()
        End If
        Dim cmd As New SqlClient.SqlCommand(str, oCn)
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim ds As New DataSet("bpl")
        Try
            da.Fill(ds, "bpl")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            oCn.Close()
        End Try
        Return ds
    End Function

    Function Get_ValueByQuery(ByVal Query As String) As String
        Dim temp As String
        Dim ocom As New SqlClient.SqlCommand
        Dim oRead As SqlClient.SqlDataReader
        If oCn.State = ConnectionState.Closed Then
            oCn.Open()
        End If

        ocom.Connection = oCn
        ocom.CommandText = Query

        oRead = ocom.ExecuteReader
        If oRead.HasRows = True Then
            oRead.Read()
            If IsDBNull(oRead(0)) = True Then
                temp = "0"
            Else
                temp = oRead(0)
            End If
            oRead.Close()

        Else
            temp = "0"
            oRead.Close()
        End If
        Return temp
    End Function

How Do I Split One Temp Table Into Multiple Temp Tables?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0