Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

How to Import SQL Server Database

In this article we will discuss about how we can import an external data into SQL server database.

OPENROWSET command may be used in the FROM clause of an inquiry as though it is a tuple name. The OPENROWSET command may be used as the mark tuple of a statement, subject to the capacity of the OLEDB provider. Even while the question mark may give so many outputs, OPENROWSET gives the earliest one. It does not recognize variables for its urging.

In context of SQL Server, OPENDATASOURCE command may be worn in the same TSQL grammar area as a connected server name. OPENDATASOURCE may be employed as the earliest point of a four-part name which refers to a tuple or view name in a SELECT, INSERT, UPDATE, or DELETE syntax, or to a distant SP in a carry out statement. When executing distant SP, OPENDATASOURCE may refer to SQL Server. OPENDATASOURCE does not believe variables for its parameters.

On one occasion defined, it can be worn in recurring disseminated queries, or execute SP in the connected attendant.

Listing 1: Following Examples may define the Status

1.
Excel file
Use OPENROWSET
SELECT * FROM OPENROWSET(
‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;DATABASE=C:\Prac.xls’, 
    ‘Select * from [sheet3$]‘)
Though OPENROWSET does not use variable in arguments, if the file name is dynamically defined in execution time, use self-motivated query be fond of -  
DECLARE @fName varchar(255)
SET @ fName =N’C:\ Prac.xls’
DECLARE @SL varchar(2000)
SET @SL=’SELECT * FROM OPENROWSET(
”Microsoft.Jet.OLEDB.5.0”,
”Excel 10.0;DATABASE=’+@fName+”’, 
    ”Select * from [sheet3$]”)’
PRINT @SL
EXEC(@SL)
 
2.
EXEC sp_adlnkdsrvr ‘ExclSrc’,
   ‘Jet 4.0′,
   ‘Microsoft.Jet.(version)
   ‘C:\Prac.xls’,
   NULL,
   ‘Excel (version)′
GO
SELECT * FROM ExclSrc[sheet address]
SELECT * FROM OPENQUERY(ExclSrc, ‘SELECT * FROM [sheet3$]‘) 
 
sp_ adlnkdsrvr ropes variables in the constraint data source, for examples:
DECLARE @fName varchar(255)
SET @fName=’C:\Prac.xls’
EXEC sp_ adlnkdsrvr ‘Prac,
   ‘Jet 4.0′,
   ‘Microsoft.Jet(version)
   @fName,
   NULL,
   ‘Excel(version)
GO
SELECT * FROM Prac[sheet address]
 
3.
 Use OPENDATASOURCE:
SELECT * FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0′,
  ‘Data Source="C:\Prac.xls";
    User ID=SA;Password=A1d4m1N;Extended properties=Excel (version)′) [sheet address]
For dynamic file names:
DECLARE @fName varchar(255)
DECLARE @SL varchar(2000)
SET @fName=’C:\Prac.xls’
SET @SL=N’
SELECT * FROM OpenDataSource( ”Microsoft.Jet.OLEDB.4.0”,
  ”Data Source="<mailto:%27+@FileName+%27>Name’";
    User ID=SA;Password=A1d4m1N;Extended properties=Excel (version)”)[sheet address]‘
PRINT @SL
EXEC(@SL)

Mass INSERT is a TSQL command used in SQL Server to fill an outer file into a database tuple for using a precise plan. How to introduce facts using the control, and locate how can altering a few of its menus can build it more competent to put in data.

When you toil with database, at a few points, we'll pact with inputting data into SQL Server from external data archive. In this article, we discuss how to bring in data with the BULK INSERT command and explain how to shifting some of its options can make it linear and more competent to input data.

Bulk Insert:

BULK INSERT is a Transact SQL command used in SQL Server to fill an outdoor file into a database tuple using a specific plan. It gives the developer the skill to insert straight into database tuples without with an external plan, such as incorporation Services. Even though BULK INSERT doesn't permit any compound sense or transformation, it does tender useful options in terms of scheming the data and how the insert is proficient. One restriction to BULK INSERT is that it only puts data into SQL Server.

Data Input:

Listing 2: The following sample shows the data input

Using SQl command we can insert Bulk Data like following manner:
CREATE TABLE [dbo].[SHist]
(
[SID] [int],
[Prod] [varchar](10) NULL,
[SDate] [datetime] NULL,
[SPrice] [money] NULL
)

At the time of Bulk Insert is used to input data into a stage table, triggers on that tuple will not be ablaze by evaded. This is suitable because triggers sluggish downward the import process.

In following trigger I want to show SHist tuple that prints out the number of records inserted into the table.

Listing 3: The following sample shows trigger

CREATE TRIGGER tr_ SHist
ON SHist
FOR INSERT
AS
BEGIN
PRINT CAST(@@ROWCOUNT AS VARCHAR(5)) + ' rows Inserted.'
END

Following script helps to insert data in StudentData Table; I need to give for the data to be inserted is the FIELDTERMINATOR. If I create following code, output will not provide:

BULK INSERT SHist FROM 'c:\ SHist.txt' WITH (FIELDTERMINATOR = ',')

BULK INSERT is not inadequate to nearby mapped drives in SQL Server 2005. The below said statement displays that how you can import data from the SHistTxt file from the E drive on a server named FlServer.

BULK INSERT SlHist FROM 'FileServerD$SlHistText.txt' WITH (FIELDTERMINATOR = ',')

Sometimes it is good to sight the data you are inserting before committing it to a database tuple. The following declaration uses the OPENROWSET command, along with the BULK command, to read data from the SlHistText text file. This statement also uses a file that identifies how the data in the text file is structured.

Listing 4: The following sample checking the data

 
SELECT * FROM  OPENROWSET(BULK  'c:\SlHistText.txt' ,
FORMATFILE='C:\ SlHistTextFormat.Xml'
) AS tstTable;
GO
Process Web = new Process();
            Web.StartInfo.UseShellExecute = false;
            Web.StartInfo.CreateNoWindow = false;
            Web.StartInfo.RedirectStandardOutput = true;
            Web.StartInfo.FlNm = "H:\Test.exe";
            Web.StartInfo.Arguments = "-i -d -ext ";
            Web.Start();
            string outp = Web.StandardOutput.ReadToEnd();
            Web.WaitForExit();

Requirement:

Poignant information into SQL Server is a little that nearly all DBAs or Developers are acknowledged with most likely on a daily foundation. One easy way of liability this is by using the Import / Export procedure, but beside with this selection there are quite a lot of other ways of initializing data into SQL Server tuples. Another common procedure would be to use SSIS.

Solution:

In addition to using the Import / Export process or SSIS to switch information into SQL Server there are also a little other feature for doing this that are default into SQL Server. Some of these other features take in bcp, BULK INSERT, OPENROWSET plus others.

BCP:

This is one of the features that is frequently generally used. One grounds for this is that it has been approximately for a meanwhile, so Administrators have come fairly well-known with this domination. This domination allows you to both input and export data, but is mainly used for txt data formats.

Here is an easier command for importing data from file C:\ImpDt.txt into table dbo.PracTest.

bcp dbo.PracTest in 'C:\ImpDt.txt' -T -serverName\instanceName

Bulk Insert:

This statement is a TSQL statement that allows us to insert data straight from within SQL Server by using TSQL. This command inserts data from file C:\ImpDt.txt into table dbo.PracTest.

Listing 5: The following sample is showing insert

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

OpenRowSet:

Openrowset process will drag in all data from excel worksheet [Sheet3$]. Using the INSERT INTO syntax you can input the doubt results into tuple dbo.ImpTst.

Listing 6: The following sample is showing insert from excel sheet

INSERT INTO dbo. ImpTst
SELECT * FROM OPENROWSET('Microsoft.Jet(version)', 
'Excel (version);Database=C:\InsertDt.xls', [sheet address])

Here is another instance where information is pulled from excel file [Sheet3$] by using a SELECT * FROM command. Once more, by using the INSERT INTO command you can input the query results into tuple dbo.ImpTst.

INSERT INTO dbo.ImpTst
SELECT * FROM OPENROWSET('Microsoft.Jet.(version)
'Excel(version);Database=C:\InsertDt.xls', 'SELECT * FROM [sheet address]')

OpenDataSource:

OpenDataSource is a TQL command that allows you to question data from other data sources unswervingly from within SQL Server.

Listing 7: The following sample is showing insert from excel sheet

INSERT INTO dbo.ImpTst
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.(version) 
'Data Source=C:\ InsertDt.xls;Extended Properties=Excel (version)')[sheet address]

OpenQuery:

One more option is OPENQUERY. Here is one more command that can be used us to problem a TSQL code to fetch data and once more with the INSERT INTO choice we can fill data into our tuple. Here are two steps with these processes, first an associated system is arrangement and then second the query is issued using the OPENQUERY command. It allows you to pass through a filter the columns and rows by the question that is issued not in favor of your associated data source.

Listing 8: The following sample is showing Open query

EXEC sp_adlnkdsrvr 'ImportData', 
   'Jet(version)', 'Microsoft.Jet(version)
   'C:\ InsertDt.xls', 
   NULL, 
   'Excel(version)' 
GO 

INSERT INTO dbo. ImpTst
SELECT * 
FROM OPENQUERY(InsertDt, 'SELECT * FROM [sheet address]') 

Associated Servers:

Here is yet another way with configure an Associated server and then issuing a straight SQL statement against the Associated server.

Listing 9: The following sample is showing associated server

EXEC sp_addlnkdsrvr 'ImpDt', 
   'Jet (version)‘, 'Microsoft.Jet(version) 
   'C:\ImpDt.xls', 
   NULL, 
   'Excel(version)' 
GO 

INSERT INTO dbo.PracTest 
SELECT * FROM ImpDt [sheet address] 
CREATE TABLE #Temprep
(
    Result  VARCHAR(MAX)
)
 
DECLARE @sqlC VARCHAR(1000)
DECLARE @rCd INT
SET @sqlC = 'TYPE C:\first.txt'
 
INSERT INTO # Temprep
EXEC @rCd = master.dbo.xp_cmdshell @sqlC
 SELECT  *
FROM    # Temprep
GO
DROP TABLE # Temprep

Conclusion:

External data import using TSQL is a very commanding mode of scheming the ending output, and the promise of what we be able to do with the inserted information retransformation are continuous if we have a high-quality control of SQL.



Website: www.techalpine.com Have 16 years of experience as a technical architect and software consultant in enterprise application and product development. Have interest in new technology and innovation area along with technical...

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login