SQL Query/SQL-Server/Excel/Access

The fastest way to Copy a table with a million rows from one database to another in SQL Server.

2011-11-07T02:31:00.003+05:30

I have been working with SQL Server in most of my career, many consequences arise when we need to backup a large database, or merge two large database into a single database. The question here is not how it is done? but the question is what is the fastest way to do it?

To backup a whole database:
The slower way: you can use the 'backup database' wizard from SQL-Server
        you can use the 'Copy database' wizard

The faster way: you can use the restore wizard, restore it as a new name. It will be done within a minutes for database up to 10GB.

To copy a table with a million rows and hundreds of columns
The slower way: you can select all from the table, copy it to excel and import it.
        you can export the table as text and import it to the destination server.
        you can use the 'Copy database' wizard
However, the above techniques can take hours to complete.

The fastest way:
use the query: SELECT * INTO DESTINATION_DATABASE_NAME.DES_TABLENAME FROM SOURCE_DATABASENAME.SOURCE_TABLENAME

This query works best for SQL SERVER 2005/2008.

How to declare a SQL query and execute it in SQL Server

2011-11-07T02:30:00.003+05:30

Sometimes consequences arise in which you need to declare a query and execute it a number of times when you are working in SQL Server or any Server in SQL. There are store procedures that requires a query to be declared and execute it under some loop or even in an if else statement.

One way is to write the query everytime, but when we have multiple values in the query, say the query needs to change some of it's features everytime you call, it is necessary to declare a query as a variable and execute it whenever needed. We have the query as below:

declare @query nvarchar(max)
set @query = ('select * from Table')
exec (@query)

The above shows a very simple SQL Server query to select a table. From the above example, we can bend it to the way we like. When we learned, 1 + 1 = 2, We don't always need to explain what will be 2 + 2. :)

Just for a heads up, I would like to add one more example:

declare @Cnt int set @Cnt=10
declare @query nvarchar(max)
set @query = ('select * from Table' where Column_ID = @Cnt)
exec (@query)
set @Cnt = @Cnt + 1
exec (@Query)

and so on.
NB: Do not forget the brackets that hold @query. SQL Server will not execute the query without the brackets

How to use a 'while' loop in sql server.

2011-11-07T02:29:00.002+05:30

In sql server, 'while' loop is the most used loop and the most popular loop. Most programers also use it in stead of a 'for' loop. Suppose, you need to do a task for n number of times like, 'while' loop is successfully used:

The 'while' loop syntax in sql is:

while (boolean expression)
begin
do this; /*update/insert/delete/etc*/
increment/decrement
end

Eg.

declare @table as table(ID int identity(1,1),Name varchar(20))
declare @Cnt int set @Cnt=1
while (@Cnt < 10)
insert into @table
select Name from SomeTable where SomeTable_ID = @Cnt
set @Cnt = @Cnt + 1
end

NB: SomeTable is the table from where we are getting the name and SomeTable_ID is the ID. This query works in SQL Server.

How to put comma(,) seperations in a large numeric value of SQL Server Database using select statement?

2011-11-03T02:21:00.004+05:30

inserting commas in number SQL

Suppose you have a large numeric value that you want to make more readable in SQL select query, that is, you have 1000000000 numeric data and you want to display it as 1,000,000,000.00 or may be just 1,000,000,000 if it is not a money. It can be easily done in SQL Server by joining two/three functions: CAST and CONVERT and PARSENAME

1. First lets create a table or simply decleare it as

CREATE TABLE Table_1(COLUMN_1 NVARCHAR(20), COLUMN_2 NVARCHAR (20))
INSERT INTO Table_1 VALUES(1230000000.45,3250000000.87)

Now we have the table T1 as:

COLUMN_1 COLUMN_2

1230000000.45 3250000000.87

2. USE THE QUERY BELOW

CORRECT QUERY:

SELECT (select convert(varchar,cast(COLUMN_1 as money),1)) FROM Table_1
This will give 1,230,000,000.45

SELECT PARSENAME((select convert(varchar,cast(COLUMN_2 as money),1)), 2) FROM Table_1
This will give 3,250,000,000

It can also be done by a select statement as:

SELECT PARSENAME((select convert(varchar,cast(10000000 as money),1)), 2)
10,000,000

1. How can I CAST a string (nvarchar datatype) from a database table as integer?

2011-11-03T02:11:00.003+05:30

string into integer

If you want to string datatype into integer using SQL Server. You can CAST an nvarchar datatype from a table (any other datatype too) into integer by first using the ROUND function as long as the data is a number. You can also CAST an expression within codes in SQL Server into integer as long as they are numeric values or numbers.

Example:

1. First lets create a table or simply decleare it as

CREATE TABLE T1(COLUMN_1 NVARCHAR(20), COLUMN_2 NVARCHAR (20))
INSERT INTO T1 VALUES(123.45,325.87)

Now we have the table T1 as:

COLUMN_1 COLUMN_2
123.45 325.87

2. USE THE QUERY BELOW

CORRECT:

SELECT ROUND(COLUMN_1,1) FROM T1
This will give 123.5

SELECT CAST(ROUND(COLUMN_1,2) AS INT) FROM T1
This will give 123

ERROR

SELECT CAST(COLUMN_1 AS INT) FROM T1
This will give data conversion error as

"Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '123.45' to data type int."

LIKEWISE, using only a select statement, you can test the above example as:

CORRECT QUERY:

SELECT ROUND(123.49546,1)
This will give 123.5

SELECT CAST(ROUND(123.98635472,2) AS INT)
This will give 123

Splitting excel into 4 areas.

2011-11-02T01:36:00.002+05:30

Guys, the figure below is not any photoshop work, it is the faeture of Microsoft Excel.

Spliting a page in excel

To make your excel page into 4 parts,
1. select the cell in the page you want to split
2. Goto view pane and click Split, to unsplit, do the reverse and you are back.

Freeze and Unfreeze panes in Microsoft Excel

2011-11-02T01:22:00.002+05:30

This will make certain columns or rows freeze or fixed in Microsoft excel. Suppose you want to stroll on the large page of excel without going away from the first column(certain column). In other words, you need to view other columns or rows with respect to a fixed row, then you can use freeze and unfreeze in excel.
To freeze and unfreeze,

select the column or the row,
go to view, and click on the freeze panes.

The figure below shows Column A freeze so as to be viewed adjacent to column Z and AA, etc

Freeze panes Unfreeze panes

How to generate all the store procedures of a database in SQL Server 2005/2008.

2011-10-26T21:35:00.002+05:30

1. connect to the server
2. right click the database

3. go to tasks --> generate scripts
4. then you will get the dialog box as below and follow the steps

start page

select the database

just click next

select what ever script you need to generate

select the name of the store procedure or a table depending on your selection

How to create a hyperlink in excel work sheet

2011-10-26T21:07:00.002+05:30

1. select the cell in which you want to create a hyperlink

2. press Ctrl+K or (Insert --> Hyprlink), then you get the dialog box as shown below

dialog box to add a hyper link in excel

3. Enter the name you want to display in the excel in the upper text box
4. Enter the address of the website or the location of the document or the cell location in the sheet in the lower text box.

How to Auto-Increment/Auto-decrement Numbers in Excel in any series

2011-10-26T20:56:00.002+05:30

1. select the cell from which you want to start the series or where the incrementing should start

2. enter the number which you want to start the serien
3. select the cell by keeping the cursor at the right bottom of the rectangle (the cursor will become a plus sign)
4. hold the right button of the mouse and drag where you want to increment/decrement the number
5. as you leave the mouse, options will appear, chose series and fill the amount you want to increment or decrement as (1)-for incrementing by 1 and (-1) to decrement by 1.

Basic shortcuts in Microsoft Excel

2011-10-26T20:43:00.004+05:30

Some of the important and basic Microsoft excel Keyboard short-cuts are listed below:

Ctrl+A = this selects all the cells in the current worksheet
Ctrl+B = this bolds the selected cells
Ctrl+C = this copies the selected cell/(range of cells)
Ctrl+F = this finds a string of text in the current worksheet
Ctrl+G = this takes you to any cell you specify
Ctrl+H = this finds and replaces text or cell content
Ctrl+I = this makes the selected cell as ITALIZED
Ctrl+K = this creates hyperlink for referring a website or a worksheet
Ctrl+N = this opens a new tap or worksheet
Ctrl+O = this opens the box to browse a new file
Ctrl+P = this prints the file
Ctrl+S = this saves the current worksheet or prompts you to save the file
Ctrl+U = this underlines the selected cells
Ctrl+V = this pastes the copied/cut cells in the selected cell(s)
Ctrl+W = this closes the current worksheet
Ctrl+X = this cuts the selected cells
Ctrl+Y = this repeats the undo
Ctrl+Z = undo: it takes you back to the previous state

Left Function, Right Function, Substring in SQL: SQL Server 2005/2008 supported.

2011-10-25T01:35:00.002+05:30

If you have a word and you want the ending alphabets(right) or the beginning alphabets(left) to be selected,
you easily use the left and right function as discussed below.

select LEFT('WELCOME',2)

This will return 'WE'

select RIGHT('WELCOME',2)

This will return 'ME'

If you are creative enough you can use the above two functions to select any type of combination from a string.
Example:
select RIGHT(LEFT('WELCOME',5),2)
This will give 'CO' and so on......

You can also avoid the complexity of the above by using the function SUBSTRING as
SELECT SUBSTRING('WELCOME',1,3)
This will give 'WEL'
SELECT SUBSTRING('WELCOME',1,2)
This will give 'WE' and so on.....

Now you can use your creativity to select any parts of the string according to your requirement.

7 Tips for loading a text file in sql server

2011-10-25T01:23:00.003+05:30

1. Always check the file for a delimiter

2. It is safer to upload to access database before taking it directly to the SQL server
3. It is safer to use pipeline '|' as a delimiter rather than any other symbols
4. Always check the row count before and after upload. This can be done using 'Ultra edit'
5. Match the columns in the source and the destination
6. .dat can also be converted to a text file by opening with a notepad and saving it as .txt file for easy handling
7. Make all the data types as text format to solve data conversion issues

How to select a word from a sentence or a name.(Parsename)

2011-10-25T01:15:00.002+05:30

You have a sentence or a name with multiple words like First name, Second Name, Surname, etc and you
want to write a select statement that will give you a part of the sentence, you can write a SQL query as:

SELECT PARSENAME(REPLACE('You are welcome here', ' ', '.'), 2)

This will return 'welcome'

Or

SELECT PARSENAME(REPLACE('Hello Angelina Jolie', ' ', '.'), 1)
This will return 'Jolie'

How to replace a set of alphabets in a string in SqlServer?

2011-10-15T22:56:00.002+05:30

SELECT REPLACE('you are beautiful','beautiful','ugly')

This will give the result
'you are ugly'

How to generate ER-Diagram in Microsoft SQL Server?

2011-10-15T22:51:00.004+05:30

To generate the ER-Diagram or the pictorial representation of the database in Microsoft SQL Server
2005/2008-
1. Set all the references and keys in the database tables
2. Connect to the server
3. Select the database of which you want the ER-diagram
4. Expand the database diagrams
5. A message will come like the below diagram

6. Select Yes in the above message

7. Right Click on the database diagrams and select new database diagram

8. Select the table you want in the pop up window and click ADD.

9. Then you will get the diagram

10. If you want to copy the diagram, right click on the diagram and select 'copy to clipboard' and paste it in new paints file.

11. Save it as you like.

How to enter a single value/formula in multiple excel cells?

2011-10-15T22:28:00.002+05:30

If you want to enter the same value in many cells in excel, the easiest way is -

1. Select all the cells in which you want the value to be entered
2. Type the value
3. Press Ctrl+Enter

How to select multiple rows in one cell?

2011-09-30T00:30:00.002+05:30

Suppose we have a table T1 as

C1     C2
1         a
2         s
3         d
4         f
5         g

Write a query as
DECLARE @C1_ALL NVARCHAR(MAX)
select @C1_ALL=coalesce(@C1_ALL + ', ','') + C1 from T1
select @C1_ALL

You will get the result as

C1_ALL
[1, 2, 3, 4, 5]

How to join multiple columns in a single row?

2011-09-28T23:22:00.004+05:30

We have Table(T1) as

C1    C2
a    1
b    2
c    3
d    4

Write the following sql query

select isnull(C1,'')+' , '+isnull(C2,'') as C1C2 from T1

This will give the result as
C1C2
a,1
b,2
c,3
d,4

How to add a rank to a column that has multiple values w.r.t another column?

2011-09-28T23:21:00.002+05:30

Here, I have data(T1) like this,

C1    C2    C3
1      11      A
2      11      B
3      12 C
4 12 C

I want to display a column (Rank of C2) that will display the rank of C2 based on the column C1 and get the result as,

C1    C2    C3    Rank
1 11     A      1
2      11    B 2
3 12   C 1
4 12   C      2

select C1,C2,C3,
ROW_NUMBER() over(partition by C2 order by C1) as Rank
from T1

Export database from SQL Server to Microsoft Access

2011-08-31T13:39:00.002+05:30

This will give all the tables of the database from SQL Server to Access File (.mdb)

1. First create the empty access file .mdb as shown here
2. Right click on the database you want to export
3. Go to TASK
4. Go to EXPORT and Click it, (Import/Export) wizard will appear.
5. Click Next
6. select the source as SQL server client
7. Browse the Access file (.mdb) for the destination file
8. See if the data maping is correct in Mapping
9. (optional) click on preview to see the table to created
10. Chose execute immediately and finish it
11. Once the copying is done, you now have all the tables of your database in the access database file.

How to create .mdb file in Access 2007 or Access 2010?

2011-08-31T13:31:00.002+05:30

1. Open the Access file and click on new database

2. Yow will get this screen and click in the Left Top corner FILE

3. Select Save and publish, save database as, Access 2000 database as shown above

Paste/write multiple lines in an excel cell.

2011-08-31T13:18:00.002+05:30

To Paste:
1. Chose the cell, press F2 and Ctrl+V

To break the line while writing
1. Type a line, press Alt+Enter to break the line in the cell

How can you select select the whole data in a particular row(s) or column(s) in excel sheet that have thousands of data?

2011-08-31T13:14:00.002+05:30

1. Chose the cell in the edge of the data
2. Press Shift or Ctrl and select the rows or columns you want

3. Move towards the direction you want to select using the direction keys
4. Press Shift+End and then Shift+direction you want to select till the end.

How to select the maximum value of multiple columns in a single row?

2011-08-27T17:23:00.002+05:30

There is no direct function in SQL to do this. However one can use Pivot/Unpivot and dump it into a temporary
table and use the MAX function on it.