If the databases are on same server then do it like this, select * into DB_2.T1 from DB_1.[dbo].[T1] if you have databases on different servers than you have to create a linked server. On second thought you can generate “create tables scripts” and run them on second database
See the Introducing SQL Server Express Local DB Runtime presentation – gives a great overview. The huge benefit of LocalDB is that it’s real SQL Server – it’s a special version of SQL Server Express, but it basically supports everything that “real” SQL Server has – spatial data types, stored procedures – you name it. … Read more
CREATE TRIGGER sampleTrigger ON database1.dbo.table1 FOR DELETE AS DELETE FROM database2.dbo.table2 WHERE bar = 4 AND ID IN(SELECT deleted.id FROM deleted) GO
Returning a Table Variable will make it a multi-statement table valued function and can be bad for performance due to the fact that it’s treated like a table except there are no statistics available for SQL Server to base a good execution plan on – so it will estimate the function as returning a very … Read more
The two highest voted answers use a lot of deprecated tables that should be avoided. Here’s a much cleaner way to do it. Get all the tables on which a stored procedure depends: SELECT DISTINCT p.name AS proc_name, t.name AS table_name FROM sys.sql_dependencies d INNER JOIN sys.procedures p ON p.object_id = d.object_id INNER JOIN sys.tables … Read more
Try using REPLACE UPDATE Your_Table SET Your_Column = REPLACE(Your_Column, NCHAR(0x00A0), ”) WHERE Id = x
Put your delete statement in an Execute SQL Task. Then make it the first component of your flow. The component looks something like this:
Granted, the article is for SQL Server 2000, but one would hope the scope doesn’t change between versions. According to the article How triggers affect ROWCOUNT and IDENTITY in SQL Server 2000, @@ROWCOUNT will not be affected by triggers. Specifically: It’s safe to use @@ROWCOUNT in SQL Server 2000 even when there is a trigger … Read more
This should work: SELECT ID FROM [TableName] WHERE Calling_ID IN ( SELECT Called_ID FROM [TableName] )
You can’t declare variables in a view. Could you make it into a function or stored procedure? Edit – you might also be able to put something into a CTE (Common Table Expression) and keep it as a view. e.g. WITH conditions as ( … do the STUFF here ) SELECT blah FROM blah INNER … Read more