Different ways to accomplish this are described in Microsoft KB article #321686. If importing is not recurring, or persistent connection is not possible, it makes sense to use Distributed Query. Running such query in SQL Server Management Studio of SQL Server 2005 will, however, produce the following error:
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
It can be resolved by enabling “advanced options” and “Ad Hoc distributed Queries” option, executing query to import data, and (optionally) reverting the configuration to its original state:
|# First, enable advanced options
sp_configure ‘show advanced options’, 1
|# Second, enable execution of Ad Hoc queries
sp_configure ‘Ad Hoc Distributed Queries’, 1
|# Import data from Excel to SQL table
SELECT * INTO TableName FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=Location\Name.xls’, [SheetName$])
|# Revert both options back: first disable Ad Hoc distributed queries
sp_configure ‘Ad Hoc Distributed Queries’, 0
|# … And hide advanced options
sp_configure ‘show advanced options’, 0
In SQL 2005 and above, a statement can be executed in context of another user:
EXECUTE AS User = ‘username‘
Requires IMPERSONATE permissions on the login or username impersonated. This permission is implied for sysadmin for all databases, and db_owner role members in databases that they own.
SQL Server Management Studio has nice ability to generate SQL Script for database schema, but has nothing for data. So if I want to preserve the entire table (schema and data) as SQL Script, in addition to generating “CREATE…” script, I’d need a set of INSERT statements to generate the static data in a table.
The best solution I’ve seen to date can be found as a comment to some other solution. The script goes like this:
declare @table_name nvarchar(100)
declare @columns nvarchar(max)
declare @values nvarchar(max)
declare @identity bit
declare @sql nvarchar(max)
set @table_name = 'MyTable'
set @columns =''
set @values =''
set @identity = 0
@identity = @identity | columnproperty(object_id(@table_name), column_name, 'IsIdentity'),
@columns = @columns + ',' + '['+column_name+']',
@values = @values + '+'',''+isnull(master.dbo.fn_varbintohexstr(cast(['+column_name+']
table_name = @table_name and data_type != 'timestamp'
set @sql = 'select ''insert into [' + @table_name + '] (' + substring(@columns,2,len(@columns)) + ')
values (''+' + substring(@values,6,len(@values)) + '+'')'' from ' + @table_name
if @identity=1 print 'set identity_insert [' + @table_name + '] on'
exec sp_executesql @sql
if @identity=1 print 'set identity_insert [' + @table_name + '] off'
Thanks to unknown hero who created it!
A little useful trick, lifted from somewhere:
sp_MSforeachdb 'use [?]
[additional statements come here]'
Any single quotes within the “additional statements” must be escaped with second single quote (not double quotes, as it may seem), e.g.:
sp_MSforeachdb 'use [?]
declare @tbl nvarchar(500)
select @tbl=name from sysobjects where type=''U'' and name=''MYTABLE''
if @res = ''MYTABLE''
begin exec sp_spaceused
This procedure will retrieve size of the database if it finds a specific table (called MYTABLE) in it.
Basically the most valuable point here is: to use the name of the current database, question mark is used, e.g.:
sp_MSforeachdb 'DBCC CHECKDB(''?'')'
Another similar procedure is sp_MSforeachtable.