IMPORT DATA FROM EXCEL TO SQL SERVER DATA BASE

Hi every one,this is my first assignment in my ms sql server journey.

i got an excel sheet with two columns i need to import into an sql server data base  2000

i tried with bulk insert but it throws error message as lack of privileges,and i tried with BCP command it is also throws same error message ,then finally i got an simple advise from my colleague.

Step1:

The excel columns as shown below is an different one, it’s only  for explanation purpose.

	A	B
1	Ename	Sal
2	Benzmen Nevarez	500000
3	Guru	600000
4	Holger Schmeling	700000

  Step2:

The Target table should be there in the database otherwise,we need to create an temporary table in the MS Sql Server data base.

 

Create table #temp_details

(Ename  varchar(10),

Sal int) 

Step3:

Now we need to frame Insert statements with the data present in the Excel sheet.

The formula my colleague suggest is

=" UNION ALL SELECT " & "'"&A2&"'" &" ,"&B2

A2(Excel Column(A) and row(2) combination) is the first column data in the table we are going to insert

Simillarly  B2(Excel Column(B) and row(2) combination) is the Second column data in the table we are going to insert.

We need to select next column (Column C) in the excel sheet to generate the required script

And type the formula in the FORMULA bar of EXCEL sheet, and then press enter

Below excel (Screen shot no1:) is just entering the formula in the formula bar.

while we are entering the formula in the formula bar the same text will reflect in the column c(what we select earlier)

Once If we press enter then the A2 and B2 values will replace with actual values.we can see that in the next screenshot(Screen shot no 2).

I tried to paste screenshots but screenshots are not pasted here,so I tried to show similar view by drawing tables.

Screen shot no1:

sum	fx	=" UNION ALL SELECT " & "'"&A2&"'" &" ,"&B2
A	B	C
Ename	Sal	=" UNION ALL SELECT " & "'"&A2&"'" &" ,"&B2

Screen shot no 2

sum	fx
A	B	C
Ename	Sal	UNION ALL SELECT 'Benzmen Nevarez' ,500000

Step 4:

Now we need to generate similar statements for the remaining rows,just place your

Select that first row of new column C then that column data highlighted,and place your mouse at the right bottom corner of that cell then mouse appear as thick plus symbal then double click there it will  automatically populate remaining rows.

Sum	fx
A	B	C
Ename	Sal
Benzmen Nevarez	500000	UNION ALL SELECT 'Benzmen Nevarez' ,500000
Guru	600000	UNION ALL SELECT 'Guru' ,600000
Holger Schmeling	700000	UNION ALL SELECT 'Holger Schmeling' ,700000

Step5:

Copy The new column C and append with Insert statement

Insert Into #temp_Details (Ename,sal)

 UNION ALL SELECT 'Benzmen Nevarez' ,500000

 UNION ALL SELECT 'Guru' ,600000

 UNION ALL SELECT 'Holger Schmeling' ,700000

Remove starting UNION ALL

Insert Into #temp_Details (Ename,sal)

 SELECT 'Benzmen Nevarez' ,500000

 UNION ALL SELECT 'Guru' ,600000

 UNION ALL SELECT 'Holger Schmeling' ,700000

This is our final Script.

Simply execute this script in the sql server database.

Formula:

=" UNION ALL SELECT " & "'"&A2&"'" &" ,"&B2

UNION ALL is the simple text to accept the duplicate values also,if you want to remove duplicates use UNION instead of UNION ALL

the A2 value enclosed by single quotes because it is an character value

,the B2 value is an numeric data

and & sign is used for concatenation.

If we have some more column to import, we need to concatenate in the above formula,we have to append with single quote for char,varchar or date data type.
Thank you.