Create a New Table with Data from Existing Tables

6,7 tạo một bàn mới với dữ liệu từ hiện tại Bàn tôi thường cần để tạo bảng mới từ dữ liệu hiện có. Nếu bảng đã tồn tại, tôi cần phải xóa bảng cũ đầu tiên.
Nội dung trích xuất từ tài liệu:
Create a New Table with Data from Existing Tables6.7 Create a New Table with Data from Existing TablesI often need to create new tables from existing data. If the table already exists, I need todelete the old table first. How do I do this using T-SQL?TechniqueTo perform these tasks, you will create two T-SQL statements and use them in oneCommand object. Here are the two statements that will be used:IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N[Northwind].[dbo].[MyProdAndCat])) DROP TABLE [Northwind].[dbo].[MyProdAndCat]SELECT Categories.CategoryName, Products.ProductName INTO MyProdAndCat FROM Categories INNER JOIN ProductsON Categories.CategoryID = Products.CategoryIDThe first statement checks for the existence of the particular table you will be creating, inthis case MyProdAndCat. This statement demonstrates a couple of techniques that youcan use in T-SQL: • Using the EXIST statement with a SELECT statement, querying the system table called sysobjects • Using an IF statement to conditionally execute another command, in this case the DROP TABLE statementTip Now that you have learned this technique, you will want to use it repeatedly. Make sure you mark this page!The last statement uses an inner join to join two tables displaying the CategoryName andProductName. The clause that creates the new table is this:INTO MyProdAndCatThis tells SQL Server to create a new table called MyProdAndCat from the SELECTstatement that is specified.StepsOpen and run the Visual Basic .NET-Chapter 6 solution. From the main form, click onthe button with the caption How-To 6.7. You will see the SQL string specified in theTechnique section displayed in a label. If you click the Execute button, the new table isgenerated a SELECT statement is executed, and the results are displayed in the DataGridobject (see Figure 6.8). 1. Create a Windows Form. Then place the controls listed in Table 6.7 with the following properties set, as displayed in Figure 6.8. Table 6.7. Control Property Settings for This How-To Object Property Setting Label Text SQL Statement Label Name lblSQLString Label Text Results Button Name btnExecute Text Execute DataGrid Name dgResults 2. Add the code in Listing 6.16 to the Load event of the form. (Double-click on the form to bring up the code.) Listing 6.16 frmHowTo6_7.vb: Storing the SQL Statement in the lblSQLString Label to Display and Use Later Private Sub frmHowTo6_7_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load -- Build the SQL String that returns cities that have more than one customer in them. Dim strSQL As String strSQL = IF EXISTS (SELECT * from sysobjects & vbCrLf strSQL &= WHERE id = object_id(N[Northwind].[dbo].[MyProdAndCat])) &_ vbCrLf strSQL &= DROP Table [Northwind].[dbo].[MyProdAndCat] & _ vbCrLf & vbCrLf strSQL &= SELECT Categories.CategoryName, Products.ProductName & vbCrLf strSQL &= INTO MyProdAndCat & vbCrLf strSQL &= FROM Categories INNER JOIN Products & vbCrLf strSQL &= ON Categories.CategoryID = Products.CategoryID Me.lblSQLString.Text = strSQL End Sub3. Add the following code in Listing 6.17 to the Click event of btnExecute. This code creates Connection and Command objects by using the T-SQL routine discussed in the Technique section. Then the code executes the query. Next, a select query is run against the new table, and the DataSource property is set to the data table that was filled. Listing 6.17 frmHowTo6_7.vb: Loading the Form Private Sub btnExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecute.Click Dim dtResults As New DataTable() Try Dim ocnn As New OleDb.OleDbConnection(BuildCnnStr((local), _ Northwind)) Dim ocmd As New OleDb.OleDbCommand(Me.lblSQLString.Text) ocmd.Connection = ocnn ocnn.Open() ocmd.ExecuteNonQuery() ocnn.Close() -- Use the SQL String to build the data adapter and fill the data table. Dim odaResults As _ New OleDb.OleDbDataAdapter(Select * From MyProdAndCat, BuildCnnStr((local), Northwind)) odaResults.Fill(dtResults) Catch excp As Exception MessageBox.Show(excp.Message) Exit Sub End Try -- Assign the data table to the data grids ...