Different Options/Methods for Importing Data into SQL Server

In this Article We are going to see the Different methods for Importing Excel data to SQL server or  Data to SQL Server.Moving data into SQL Server is something that most DBAs or Developers are faced with probably on a daily basis.  One simple way of doing this is by using the Import / Export wizard, but along with this option there are several other ways of loading data into SQL Server tables. Another common technique would be to use SSIS.  In this tip we take a look at some of these other options for importing data into SQL Server.

In addition to using the Import / Export wizards or SSIS to move data into SQL Server there are also a few other options for doing this that are built into SQL Server.  Some these other options include bcp, BULK INSERT, OPENROWSET as well as others.  The following examples show you some of these different options for importing data and how you can use some of these inline with your T-SQL code as well as others that can be run from the command line.

---

BCP

This is one of the options that is mostly widely used.  One reason for this is that it has been around for awhile, so DBAs have come quite familiar with this command.  This command allows you to both import and export data, but is primarily used for text data formats.  In addition, this command is generally run from a Windows command prompt, but could also be called from a stored procedure by using xp_cmdshell or called from a SSIS package.

Here is a simple command for importing data from file C:\ImportData.txt into table dbo.ImportTest.

bcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName

For more information about bcp click here.

---

BULK INSERT

This command is a T-SQL command that allows you to import data directly from within SQL Server by using T-SQL.  This command imports data from file C:\ImportData.txt into table dbo.ImportTest.

BULK INSERT dbo.ImportTest FROM 'C:\ImportData.txt' WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

For more information about BULK INSERT click here.

---

OPENROWSET

This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server.  By using this command along with an INSERT INTO command we can load data from the specified data source into a SQL Server table.

This command will pull in all data from worksheet [Sheet1$]. By using the INSERT INTO command you can insert the query results into table dbo.ImportTest.

INSERT INTO dbo.ImportTest SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\ImportData.xls', [Sheet1$])

Here is another example where data is pulled from worksheet [Sheet1$] by using a SELECT *FROM command. Again, by using the INSERT INTO command you can insert the query results into table dbo.ImportTest.   The query can be any valid SQL query, so you can filter the columns and rows by using this option.

INSERT INTO dbo.ImportTest SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\ImportData.xls', 'SELECT * FROM [Sheet1$]')

For more information about OPENROWSET click here.

---

OPENDATASOURCE

This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. This is similar to the OPENROWSET command.

INSERT INTO dbo.ImportTest SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]

For more information about OPENDATASOURCE click here.

---

OPENQUERY

Another option is OPENQUERY.  This is another command that allows you to issue a T-SQL command to select data and again with the INSERT INTO option we can load data into our table.  There are two steps with this process, first a linked server is setup and then second the query is issued using the OPENQUERY command.  This option allow you to filter the columns and rows by the query that is issued against your linked data source.

EXEC sp_addlinkedserver 'ImportData',    'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',    'C:\ImportData.xls',    NULL,    'Excel 8.0' GO INSERT INTO dbo.ImportTest SELECT * FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')

For more information about OPENQUERY click here.

---

Linked Servers

Here is yet another option with setting up a linked server and then issuing a straight SQL statement against the linked server.  This again has two steps, first the linked server is setup and secondly a SQL command is issued against the linked data source.

EXEC sp_addlinkedserver 'ImportData',    'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',    'C:\ImportData.xls',    NULL,    'Excel 8.0' GO INSERT INTO dbo.ImportTest SELECT * FROM ImportData...Sheet1$

For more information about Linked Servers click here.

As you can see right out of the box SQL Server offers many ways of importing data into SQL Server.  Take a look at these different options to see what satisfies your database requirements.

SQL Tutor

SQL Tutor

In this SQL Tutor Session I will explain by practically as following topics see below:

Ø  Filtered index

Ø  Like operator in  SQL Server

Ø  Except command & Intersect Command in SQL Server 2005

Ø  Status of SQL Server Services in SQL 2012

Ø  Map Login with users

Filtered index

Filtered index

Filtered index is a feature added in SQL Server 2008 and serves a great purpose for some of the requirements
As a general we all know that index improves search performance if INDEX is created properly
But some cases where we font requires data from entire column but only some filtered values
Even for this we have to create index for data entire column.
Whenever we query this column it has to read index for all values  column.



But if we want only certain value from column based on some logic then is there any other option available?
In this case it would be better if we create index only filtered value of column so it will put less overhead on the
server in terms of maintenance as well as server will have to check less amount of data while reading from index

Lets create first normal index than we will create filtered index and check this.

We want to check for orderqty > 10 for all out requirements.

CREATE INDEX idx_SalesOrderDetail_OrderQty

    ON Sales.SalesOrderDetail(OrderQty)

CREATE INDEX idx_SalesOrderDetail_OrderQty_Filtered

    ON Sales.SalesOrderDetail(OrderQty)

WHERE OrderQty > 10

Lets see space used by this 2 indexes

We can see that space used by filtered index much lesser compare to non filtered index

EXEC Sp_msindexspace 'Sales.SalesOrderDetail'

 

Ok , how to find index is filtered ?

SELECT *

FROM   sys.indexes

WHERE  object_id = Object_id('Sales.SalesOrderDetail')

       AND has_filter = 1

For unique index




One more use of filtered index is for creating unique index by filtering not unique values

Let see an example

CREATE TABLE test

    (

         id    INT IDENTITY(1, 1),

         data  VARCHAR(100),

         value INT

    )

INSERT INTO test

VALUES      ('a',

             1),

            (NULL,

             1),

            (NULL,

             2),

            ('b',

             2),

            ('c',

             3)

SELECT *

FROM   test 

Now I want to create unique index on data column but it has multiple null values  

It will fail 

CREATE UNIQUE INDEX idx_test_data_nonfiltered

    ON test(data)

 

We will use filtered index here to filter null values

CREATE UNIQUE INDEX idx_test_data_filtered

    ON test(data) 

where data is not null

Like- SQL Server

Recently I got a query on how to use like clause. As we all know we can use to find word with pattern matching.
Here we will look a sample for it.


CREATE TABLE user_id_list (

user_id VARCHAR(256))


/*If you are using SQL Server 2008*/

INSERT INTO user_id_list

VALUES ('amish'),

('amish123'),

('amish.shah'),

('123456'),

('123amish')


/*If you are using SQL Server 2005*/

INSERT INTO user_id_list

SELECT 'amish'

UNION ALL

SELECT 'amish123'

UNION ALL

SELECT 'amish.shah'

UNION ALL

SELECT '123456'

UNION ALL

SELECT '123amish'


-- To get ID which has only alphabet in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z]%'


-- To get ID which has only alphabet and '.' in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z,.]%'


-- To get ID which has only alphabet and numbers in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z,0-9]%'

Except command -SQL Server 2005

Except command can be used when we want rows from table1 which are not in table2.
This command is supported in SQL 2005 and later versions.
Let see an example for this


CREATE TABLE #temp (

id INT,

data VARCHAR(100),

code INT)


INSERT INTO #temp

VALUES (1,'a',1)


INSERT INTO #temp

VALUES (1,'b',2)


INSERT INTO #temp

VALUES (2,'a',1)


INSERT INTO #temp

VALUES (2,'b',2)


CREATE TABLE #temp1 (

id INT,

data VARCHAR(100),

code INT)


INSERT INTO #temp1

VALUES (1,'a',1)


INSERT INTO #temp1

VALUES (1,'b',2)


INSERT INTO #temp1

VALUES (3,'a',1)


INSERT INTO #temp1

VALUES (3,'b',2)


Now we want rows from #temp which are not in #temp1

In SQL 2000 we can do this by matching all columns



SELECT *

FROM #temp t

WHERE NOT EXISTS (SELECT *

FROM #temp1 t1

WHERE t.id = t1.id

AND t.data = t1.data

AND t.code = t1.code)


In SQL 2005 we can do it easily by except


SELECT *

FROM #temp

EXCEPT

SELECT *

FROM #temp1

INTERSECT COMMAND in SQL (As Same as above EXCEPT Command)

Now we want rows from #temp which are not in #temp1

In SQL 2000 we can do this by matching all columns



SELECT *

FROM #temp t

WHERE EXISTS (SELECT *

FROM #temp1 t1

WHERE t.id = t1.id

AND t.data = t1.data

AND t.code = t1.code)


In SQL 2005 we can do it easily by except


SELECT *

FROM #temp

INTERSECT

SELECT *

FROM #temp1

Easy way to get status of SQL Server Services in SQL 2012

SQL 2012 has some new DMVs.
One of them is sys.dm_server_services
It gives us details about the SQL Server services (SQL Serve,SQL Server Agent, Full Text)

SELECT *

FROM   sys.dm_server_services

 

Map Login with users

When we restore backups to new server then users in the database are not mapped with their login in the new server
Its because sid is not matching.In this case we will are used to with sp_change_users_login
But this procedure will be deprectated in future version . So instead of this we should use alter user


alter user username
with
= new username



So for mapping a user test with testlogi  we will use


ALTER USER test WITH login = testlogin