You are using the right syntax:
SET IDENTITY_INSERT dbo.sometable ON;
However, a schema in SQL Server is not the same as a database.
The default schema for objects is dbo
as in my example. You do not need to specify the database name if you are connected to it, but you could like this:
SET IDENTITY_INSERT DbName.dbo.sometable ON;
There seems to be no way to get the current setting as it is session-scoped and not persisted.
If you run this query in your database it returns the fully qualified names for all tables in there:
SELECT
QUOTENAME(DB_NAME())+'.'+
QUOTENAME(OBJECT_SCHEMA_NAME(object_id))+'.'+
QUOTENAME(OBJECT_NAME(object_id)) AS FullTableName
FROM sys.tables;
SET IDENTITY_INSERT ON...
must be run in the same session as the insert.
Back in 2007, I asked for an easy way to generate a CREATE TABLE
script via T-SQL rather than using the UI or SMO. I was summarily rejected.
However, SQL Server 2012 makes this very easy. Let's pretend we have a table with the same schema across multiple databases, e.g. dbo.whatcha
:
CREATE TABLE dbo.whatcha
(
id INT IDENTITY(1,1),
x VARCHAR(MAX),
b DECIMAL(10,2),
y SYSNAME
);
The following script uses the new sys.dm_exec_describe_first_results_set
dynamic management function to retrieve the proper data types for each of the columns (and ignoring the IDENTITY
property). It builds the #tmp table you need, inserts from each of the databases in your list, and then selects from #tmp, all within a single dynamic SQL batch and without using a WHILE
loop (that doesn't make it better, just simpler to look at and allows you to ignore Database_Ref_No
entirely :-)).
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N'';
SELECT @cols += N',' + name + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.whatcha', NULL, 1);
SET @cols = STUFF(@cols, 1, 1, N'');
SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'
DECLARE @dbs TABLE(db SYSNAME);
INSERT @dbs VALUES(N'db1'),(N'db2');
-- SELECT whatever FROM dbo.databases
SELECT @sql += N'
INSERT #tmp SELECT ' + @cols + ' FROM ' + QUOTENAME(db) + '.dbo.tablename;'
FROM @dbs;
SET @sql += N'
SELECT ' + @cols + ' FROM #tmp;';
PRINT @sql;
-- EXEC sp_executesql @sql;
The resulting PRINT
output:
CREATE TABLE #tmp(id int,x varchar(max),b decimal(10,2),y nvarchar(128));
INSERT #tmp SELECT id,x,b,y FROM [db1].dbo.tablename;
INSERT #tmp SELECT id,x,b,y FROM [db2].dbo.tablename;
SELECT id,x,b,y FROM #tmp;
When you are confident it's doing what you expect, just uncomment the EXEC
.
(This trusts you that the schema is the same; it does not validate that one or more of the tables has since been changed, and may fail as a result.)
Best Answer
The error isn't coming from the trigger. It's coming from your insert statement.
It's getting parsed and returning an error before you ever get to the trigger. Change it to this and it works.