如何将一个临时表拆分为多个临时表? [英] How Do I Split One Temp Table Into Multiple Temp Tables?
问题描述
我有一个层次结构顺序的表。现在我想拆分成多个表,其中只存储root-id = 1数据。然后以相同的方式,root-id = 2应该存储在第二个表中。对于n个根应该重复。该表如下所示:
## gblTempTable
level clasi_clasid clasi_name clasi_parentid clasi_rootid
1 1 Main Classification1 NULL 1
1 3主要分类1 NULL 1
1 4主要分类2 NULL 1
1 5主要分类3 NULL 1
2 8子分类1 5 2
3 11下一个子分类1 8 3
2 7子分类1 4 2
3 10下一个子分类1 7 3
2 6子分类1 3 2
2 2子分类1 1 2
所需的输出:
## gblTempTable1
level clasi_clasid clasi_name clasi_parentid clasi_rootid
1 1主要分类1 NULL 1
1 3主要分类sification 1 NULL 1
1 4主要分类2 NULL 1
1 5主要分类3 NULL 1
# #gblTempTable2
level clasi_clasid clasi_name clasi_parentid clasi_rootid
2 8子分类1 5 2
2 7子分类1 4 2
2 6 Sub分类1 3 2
2 2子分类1 1 2
这样,我想要输出n个根id。 ## gblTempTable应以循环方式拆分为多个临时表。我怎样才能实现这一目标?
mai的目的是产生如1> 1.1-> 1.1.1,2-> 2.1,2-> 2.2,2.1的书籍索引。 1等
USE [DBNAME]
GO
/ * *****对象:StoredProcedure [dbo]。[sp3_loop]脚本日期: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 ))+ < span class =code-string>' ,'
SET < span class =code-sdkkeyword> @ 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 (选择 1 来自 gblTempTable 其中 clasi_rootid = @ prm_id ))
BEGIN
选择 * 来自 gblTempTable 其中 clasi_rootid = @ prm_id 订单 by clasi_clasid
END
SET @ param1 = RIGHT ( @ param1 ,LEN( @ par am1 ) - @ Pos )
SET @Pos = CHARINDEX(' ,', @ param1 , 1 )
END
END
我尝试过SQL程序,但无法创建多个表,所以我生成了vb .net代码请使用它正常工作..
公共 oCn As 新 System.Data.SqlClient.SqlConnection( 数据源=(本地);初始目录= MSTMDB; Uid = sa)
私有 Sub Button2_Cl ick( ByVal sender As System。 Object , ByVal e As System.EventArgs) Handles Button2.Click
Dim ds,ds1 As 新 DataSet
Dim root_param 作为 字符串 =
Dim i As 整数 = 0
Dim j 作为 整数 = 0
ds = sel ect_data( 通过clasi_rootid按clasi_rootid从) clasi_rootid)。ToString& (level1,clasi_clasid,clas_name,clasi_parentid,clasi_rootid)值(& ds1.Tables( 0 ) .Rows(j).Item( level1)。ToString& ,& ds1.Tables( 0 )。行( j).Item( clasi _clasid)。ToString& ,'& ds1.Tables( 0 )。行(j).Item( clas_name)。ToString& ',& Val(ds1.Tables( 0 )。行(j).Item( clasi_parentid)。ToString)& ,& ds1.Tables( 0 )。行(j).Item( clasi_rootid)。ToString& ))
如果 ds.Tables( 0 )。Rows.Count> 0 然后
( i<> ds.Tables( 0 )。Rows.Count)
Get_ValueByQuery( CREATE TABLE [dbo]。[gblTempTable& ds.Tables( 0 )。行(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( 从gblTempTable中选择level1,clasi_clasid,clas_name,clasi_parentid,clasi_rootid,其中clasi_rootid =& ds.Tables( 0 )。行(i) .Item( clasi_rootid)。ToString)
如果 ds1.Tables( 0 )。Rows.Count> 0 然后
( j<> ds1.Tables( 0 )。Rows.Count)
Get_ValueByQuery( 插入gblTempTable& ds.Tables( 0 )。行(i).Item(< span class =code-string>
j = j + 1
结束 虽然
结束 如果
ds1.Clear()
j = 0
i = i + 1
结束 while
结束 如果
结束 Sub
功能 select_data( ByVal str As String ) As DataSe t
如果 oCn.State = ConnectionState.Closed 那么
oCn.Open( )
结束 如果
Dim cmd 作为 新 SqlClient.SqlCommand(str,oCn)
Dim da As 新 SqlClient.SqlDataAdapter(cmd)
Dim ds As 新 DataSet ( bpl)
尝试
da.Fill(ds, bpl)
Catch ex As 例外
MessageBox.Show(ex.Message)
最后
oCn.Close()
结束 尝试
返回 ds
结束 功能
功能 Get_ValueByQuery( ByVal 查询作为 String ) as String
Dim temp 作为 字符串
Dim ocom 作为 新 SqlClient.SqlCommand
暗淡 oRead As SqlClient.SqlDataReader
If oCn.State = ConnectionState.Closed < span class =code-keyword>然后
oCn.Open()
结束 如果
ocom.Connection = oCn
ocom.CommandText = Query
oRead = ocom.ExecuteReader
如果 oRead.HasRows = True 那么
oRead .Read()
如果 IsDBNull(oRead( 0 ))= True 然后
temp = 0
否则
temp = oRead( 0 )
结束 如果
oRead .Close()
Else
temp = 0
oRead.Close()
结束 如果
返回 temp
结束 功能跨度>
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.
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
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..
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
这篇关于如何将一个临时表拆分为多个临时表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!