I just reread your original post. For data transfer between databases on the same server, the Execute SQL task with INSERT/SELECT should be faster than an SSIS data flow...

In the data flow task, bulk insert is enabled by the properties of the destination component. An OLEDB destination should use a data access mode of "Table or view...

The check to see if the temp table exists doesn't work. The OBJECT_ID() function with a simple table name only looks in the current database. To check for...

There are applications such as SharePoint that can't deal with parallel queries at all (MAXDOP=1), which would override any magic number generated from the hardware configuration.

First let me strongly agree that you should not store data in forms you can't work with. This isn't a date, it's not really an integer, it's a year...

SELECT OptionCode, OptionDesc,

OptionLongDesc = CAST(OptionLongDesc AS NVARCHAR(MAX)) , UnitPrice

FROM tbMasterOption

WHERE ...

DECLARE @t1 DATETIME = '2014-05-02 10:30:43';

DECLARE @t2 DATETIME = '2014-05-02 14:25:13';

-- Show the time difference in HH:MM:SS (assumes timespan < 24 hours)

PRINT CONVERT(CHAR(8), @t2 - @t1, 8)

-- Shrink to HH:MM...

If your XML PATH subquery uses ,TYPE to return an XML value, you can use .value() to restore the original text.

SELECT ( SELECT ...

...

sp_executesql does promote execution plan reuse. You can also declare OUTPUT parameters.

Yes, you could import the data to a staging table. But if neither the source nor destination is SQL, the overhead of creating the table and cleaning up the...

Mea culpa, I overlooked one line in the first response. I'll just go shoot myself now.

Now you're free to criticize Jeff Moden (for whom I have a tremendous amount...

I was just making the observation that the original post did not say where the data was coming from or where it was going. However clever the T-SQL solutions...

Since "I have the input:" doesn't specify where it's coming from, your T-SQL solutions seem to assume that the data is coming from a SQL database. If not, there...

Here are two versions, column lists removed for clarity. Don't rely on "SELECT *" to work across servers.

-- V1: Copy the entire remote table, keep only what you need

INSERT...

Don't try to delete all the rows at once. Even in simple recovery mode, all the activity must be logged for the duration of the statement. Drop batches...