Copy tables from one database to another in SQL Server

You can  use the Generate SQL Server Scripts Wizard to help guide the creation of SQL script's that can do the following:

• copy the table schema
• any constraints (identity, default values, etc)
• data within the table
• and many other options if needed

Good example workflow for SQL Server 2008 with screen shots shown here.

Right Click on Database >> Tasks >> Generate Scripts >>

This will pop up Generate SQL Server Scripts Wizards >> Click on Next >> Select Database >> This will bring up a screen that will suggest to Choose Script Option.

On Choose Script Option Screen of Script Wizard under section Table/View Options Look at Script Data row and Turn the Option to True.

The Next Screen will ask about object types. Select all the objects that are required in the generated script. Depending on the previous screen it will show few more screen requesting details about the objects required in script generation.

On the very last screen it will request output options. Select the desired output options of Script to file, Script to Clipboard or Script New Query Window.3

Clicking on Finish button will generate a review screen containing the required objects along with script generating data.

Clicking on Finish button one more time will generate the requested output.

Similarly, if you want to generate data for only one table, you can right click on the table and follow almost a similar wizard. I am sure this neat feature will help everybody who has been requesting for it for a long time.

Ref:-http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/

How to implement SQL Bulk Copy in SQL Server

SqlBulkCopy for insert multiple rows of data into table in SQL Server

Sometimes we need to insert bulk amount of data into a table in SQL. We can insert bulk data to SQL in different methods. Here we are going to demonstrate how to insert large amount of data using SQL Bulk Copy method.

Example of SQL BulkCopy method

Here we are going to insert multiple rows of sample data into Employee table. First of all create a table in SQL named Employee. After that  create sample data for employee table and finally this multiple rows of employee data insert into table using SQL bulk copy method.

 

Create a Table in SQL 

CREATE TABLE Employee
(
ID INT,
EmpName varchar(200),
Department varchar(200),
DOB DateTime
);

SQLMulkCopy Example in SQL Server

 

C# Code to generate sample data and insertion using SQL Bulk Copy

private void loadDataBySQLBulkCopy()
        {
            DataTable dtTblEmployee = new DataTable();
            dtTblEmployee.Columns.Add("ID", typeof(Int32));
            dtTblEmployee.Columns.Add("EmpName", typeof(String));
            dtTblEmployee.Columns.Add("Department", typeof(String));
            dtTblEmployee.Columns.Add("DOB", typeof(DateTime));

            DataRow dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 1; //Bind Data to Columns
            dtrow["EmpName"] = "Wazeem";
            dtrow["Department"] = "Admin";
            dtrow["DOB"] = "1990-10-16 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 2; //Bind Data to Columns
            dtrow["EmpName"] = "Aslam";
            dtrow["Department"] = "HR";
            dtrow["DOB"] = "1987-05-16 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 3; //Bind Data to Columns
            dtrow["EmpName"] = "John";
            dtrow["Department"] = "Finance";
            dtrow["DOB"] = "1992-12-11 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 4; //Bind Data to Columns
            dtrow["EmpName"] = "Mishal";
            dtrow["Department"] = "Infra structure";
            dtrow["DOB"] = "1989-04-01 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            SqlBulkCopy bulkCopy = new SqlBulkCopy(
                "server=TEST;database=TEST;uid=test;password=test",
                SqlBulkCopyOptions.TableLock);
            bulkCopy.DestinationTableName = "dbo.Employee";
            bulkCopy.WriteToServer(dtTblEmployee);

        }

About these ads

How many classes can a single .NET DLL contain?

There’s a common question in a programming interview that sounds like this:

“How many classes can a single .NET DLL contain?”

I did a little research on the web and I saw that lots of people think that the number is unlimited. Well, in my opinion, this is wrong!

Let me clarify this! Inside MSDN there’s a section on Overview on .NET Framework that contains a section on Metadata and Self-Describing Components. In this section you will find out how metadata is stored inside a Portable Executable and what is the structure of Metadata. When you are looking inside “Metadata and the PE File Structure” , you will find out that each element of your program is stored in a “Metadata table”(one for classes, one for members, a.s.o.). Each element is identified by a “Metadata token” and it’s definition is: ”Each row of each metadata table is uniquely identified in the MSIL portion of the PE file by a metadata token.Metadata tokens are conceptually similar to pointers, persisted in MSIL, that reference a particular metadata table.”(see MSDN). After this section, there is an explanation of these tokens. “A metadata token is a four-byte number. The top byte denotes the metadata table to which a particular token refers (method, type, and so on). The remaining three bytes specify the row in the metadata table that corresponds to the programming element being described.”

Let me stop here. If a class is identified in the metadata table using a metadata token and the metadata token is afour-byte number, this means that the number of classes that an assembly can contain is smaller than the biggest number on 3 bytes (the first byte is a connection to the metadata table type). The biggest number on 3 bytes is 2^24– 1 or 16777215.

Therefore, the maximum number of classes (and classes members) that a .NET assembly can contain is 16777215. Am I right? (Wait for comments!)

That’s nice, right? :)

If I’ll have the time, I’ll try to write some code that will generate an assembly with more than 16777215 classes and see if I can use it… :)