SQL Database Attach and Detach Script

             DATABASE ATTACH SCRIPT

1   .In this example Adventureworks database data and log file exists in “C:\Adventureworks2008R2_Database”  folder.

2.  Run the below command 

  USE Master

  Go

  EXEC sp_attach_db @dbname = 'AdventureWorks',

    

 @filename1 = 'C:\AdventureWorks2008R2_Database\AdventureWorks2008R2_Data.mdf',

    

 @filename2 = 'C:\AdventureWorks2008R2_Database\AdventureWorks2008R2_log.ldf'

               DATABASE DeTACH SCRIPT

   

  USE Master

    Go

    EXEC sp_detach_db @dbname = 'AdventureWorks'

   

Import data from Excel to SQLServer

                                      Import data from Excel to SQLServer             ===========================================================
The following are the queries to retrieve data from Excel files
1)
--Excel 2007-2010
SELECT * --INTO #Cars
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\cars.xlsx','SELECT * FROM [cars$]');
2)
--Excel 97-2003
SELECT * --INTO #Cars
FROM OPENROWSET('Microsoft.ACE.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=C:\cars.xls','SELECT * FROM [cars$]');
These queries return excel data in the results window when executing with SQL Server Management Studio. To insert the data into a table, just uncomment the INTO clause. That will create a local temporary table with the retrieved data in the tempdb database.
Here, HDR=YES means the 1st row in the excel sheet is a column name. If that's not the case set HDR = No instead YES.
If you've executed uncommented INTO clause query then you can query the temporary table (here, it's Cars table) just like any other normal sql table.
SELECT * FROM #Cars

CHECK DATA TYPES OF TEMP TABLE (#Cars)
Let's have a look how such temporary tables are structured when created using OPENROWSET(). Execute the following command in query window.
USE tempdb;
GO
sp_help '#Cars'

***********QUERY TO IMPORT DATA DIRECTLY INTO SQL TABLES**************
1)
--Excel 2007-2010
INSERT INTO DATABASE.SCHEMA.TABLENAME
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\cars.xlsx','SELECT * FROM [cars$]');

2)
--Excel 97-2003
INSERT INTO DATABASE.SCHEMA.TABLENAME
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=C:\cars.xls','SELECT * FROM [cars$]');

********************** Enable 'AD HOC DISTRIBUTED QUERIES'******************
You can enable this option through SQL Server Surface Area Configuration tool (Start > All Programs > Microsoft SQL Server > Configuration Tools > SQL Server Surface Area Configuration > Surface Area Configuration for Features).