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 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.
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();
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.
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.
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
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 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 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]
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]')
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
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.