Copy Data from One Table to Another Table and insert values

There are multiple ways to do this.

1) INSERT INTO SELECT

This method is used when table is already created in the database earlier and data have to be inserted into this table from another table. If columns listed in the INSERT clause and SELECT clause are same, listing them is not required.

INSERT INTO table-name (column-names)

SELECT column-names 

FROM table-name

WHERE condition

2) SELECT INTO

This method is used when table is not created earlier and it needs to be created when data from one table must be inserted into a newly created table from another table. The new table is created using the same data types as those in selected columns.

select * into dbo.CopyTo from dbo.CopyFrom

3) INSERT BY VALUE

By using a values clause to specify the data values for one row.

          INSERT INTO MyTable (Column1, Column2)

              VALUES (‘Column1Value’, Column2 Value.')

SQL Server ACID property

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for while evaluating databases and application architectures. For a reliable database, all this four attributes should be achieved.

Atomicity: In a transaction involving two or more discrete pieces of information, either all of the pieces are committed or none are.

Consistency: A transaction either creates a new and valid state of data, or, if any failure occurs, returns all data to its state before the transaction was started.

Isolation: A transaction in process and not yet committed must remain isolated from any other transaction.

Durability: Committed data is saved by the system such that, even in the event of a failure and system restart, the data is available in its correct state.

What is the FILL FACTOR ?

A FILL FACTOR is one of the important arguments that can be used while creating an index.

FILL FACTOR specifies a value interns of the percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild. Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.

Specifying a fill-factor value of 70 would imply that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table. The fill-factor setting applies only when the index is created or rebuilt

By Default fill factor is 0 of the database, even you can set it out at SQL Server instance level using instance Properties in below screen .

The following example shows how to use sp_configure to set the fill factor server configuration option to 100. Using sp_configure, you can change fill factor only when show advanced options is set to 1. The setting takes effect after restarting the server.

Use ABC;

GO

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'fill factor', 100;

GO

RECONFIGURE;

GO

MERGE Statement in SQL Server

MERGE is a new feature introduce in SQL server 2008 version that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement, we can include the logic of such data modifications in one statement that even checks when the data is matched, then just update it, and when unmatched, insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.

·        

Manipulating data in a single query for below parameters

·         Insert the records if the record does not exists.

·         Update the records if exists.

·         Delete the record if exists.

Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.

Databases Design and Development Lifecycle

The steps in developing any application can be represented as a linear sequence where each step in the sequence is a function, which passes its output to its successor function. Adherence of a ‘waterfall development model’ ensures quality software, which is ‘complete’, ‘efficient’, ‘usable’, ‘consistent’, ‘correct’ and ‘flexible’. These traits are also some of the core underpinnings of a well-built database. The waterfall model can be applied to database design theory as effectively as it is applied to other software engineering theory. The steps can be summarised as follows:

Specify hints in SELECT-SQL Query

Although in most cases the query optimiser will pick the appropriate index for a particular table based on statistics, sometimes it is better to specify the index name in your SELECT query. For example, consider the following

Notice the additional "WITH" clause after FROM. This example is specific to MS SQL Server. Every database use different syntax for specifying this value and they are quite different from each other. Refer to your RDBMS documentation for details.

Database View in SQL Server

—Standard Views: Most of the time, we create this kind of view where we use single or more that one tables, use JOINs and other clauses and pull the data out. The tables are saved on a single physical file, most of the time on primary data file – that is .mdf file. All tables don’t have any kind of partitions

                                                                                                       

—Indexed Views: Usually views pull the data out from underlying tables and utilise indexes created on table columns. However, an indexed view allows us to create a unique clustered index on it – allowing us to physically keep a separate copy of sorted data based on which column we created a unique clustered index. This dramatically improves the view’s performance for some types of queries. An indexed view works best for queries that are giving summary data. However, this type of view is not recommended in case there is high frequency of data insert, update or delete on underlying table(s).

—Partitioned Views: This type of view joins horizontally partitioned data from a set of members table across one or more servers. We partition a table horizontally and keep data partitions either on one server or on multiple servers (federated servers) in order to gain performance. We may sub-categorize this category into two:

◦Local Partitioned Views: This joins required tables on the same instance of SQL Server i.e.. same SQL Server engine.

◦Distributed Partitioned Views: This joins required tables across servers i.e. multiple SQL Server engines.

Sequence of Execution of The SQL Queries

What actually sets SQL Server apart from other programming languages is the way SQL Server processes its code. Generally, most programming language process statement from top to bottom. By contrast, SQL Server processes them in a unique order which is known as Logical Query Processing Phase. These phases and their orders are given as follows:

1. FROM

2. ON

3. OUTER/LEFT/RIGHT/INNER/CROSS Joins

4. WHERE

5. GROUP BY

6. CUBE 

7. HAVING

8. SELECT

9. DISTINCT

10. ORDER BY

11. TOP

So always think to optimise the data and result top to bottom with this sequence for getting quick results.

Partitioning for partition elimination

With this technique, the goal is to reduce the amount of data that SQL Server needs to scan through when doing table scans. Typically, this is done by using a static window approach with many partitions created in the system and data. For example, you could put the data for each month into its own partition. This way, when users run a query, SQL Server needs to scan through only one partition instead of going through all the data.