Often on data recovery cases, we need to extract all the user table data into a new database. There are multiple options to perform this task:
1. Export/Import Wizard
2. Custom SSIS Task
3. Copy Database Wizard
4. BCP operations
5. SELECT INTO T-SQL commands
I find that the last option is the easiest. I use the following script to get all the T-SQL SELECT…INTO commands for all user tables in the old database:
select 'select * into <new db>.'+b.name+'.['+a.name+'] from <old db>.'+b.name+'.['+a.name+']' from sys.objects a inner join sys.schemas b on a.schema_id = b.schema_id where a.type = 'U'
Note: You will have to replace the new database and old database names with the appropriate names. The above script only works for versions of SQL Server 2005 and above.
I have had multiple questions on how users wanted to remove the quotation marks while importing the data into a SQL Server database table while using BCP or BULK INSERT. Last month I worked on a similar issue where the need was to strip out the quotation marks for all the data columns present in the .DAT file. Read my post on SQLServerFAQ to find out how this can be accomplished using BCP, BULK INSERT or if you like using UI, the Export/Import Wizard.
If you have an Excel File named ExcelFile.xls, then you can perform SELECTs on that Excel file using the Excel Sheet name and the Jet Provider.
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\ExcelFile.xls;Extended Properties="Excel 8.0"')...[Data_Load$];
In case, you use the same Excel file over and over again, then you can use OPENQUERY using a Linked Server defined to your Excel File.
FROM OPENQUERY (DataLoad, 'select * from [Data_Load$]')
If you find that the data being imported for non-Text columns is NULL, then refer the following blog entry: