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