Yes, it can be done without a cursor or loop. Whether you should use dynamic SQL will depend on what will consume the results. Is this a one-off request or...

TVFs may be a bit more flexible from a programming perspective especially if you're interested in capturing the results in a temp table (re: limitations on INSERT...EXEC).

If you choose TVFs,...

Sounds like a fun project...and that you're approaching it carefully and in your own time. That is a breath of fresh air. It sounds like SSMA has helped you get...

Disclaimer: my approach is shaped by my lack of awareness of an SSIS-specific tool/component that can do what you're asking so read with that in mind.

Initially I thought of writing...

chetanr.jain (3/30/2011)

---

I have apperaed for the same exam in last oct and got 980.

i have gone through the Self study guide of Microsoft for 70-432. Read out that book fully.

There...

I meant a motivation to use synonyms, specifically. Have you considered using views to accomplish the same end result?

Something like this:

CREATE VIEW dbo.account_data

AS

SELECT *

FROM dbo.Bi001 ;

GO

I am happy you were able to find a workable solution.

In the spirit of completely avoiding temp tables, hopefully improving performance, would something like this work?

SELECT a.ContactID AS...

Your connection string was slightly flawed. Try this:

IF OBJECT_ID(N'tempdb..#MyTable') > 0

DROP TABLE #MyTable ;

/* notice:

1. use SQLNCLI10 as the provider if you're accessing SQL 2008

2....

Do you have multiple CPUs in your server? And if so, what is your server's MAXDOP setting?

If SQL Server decided to employ parallel worker threads to execute your UPDATE then...

Is there a specific motivation in your organization towards using synonyms over views containing SELECT * FROM SCHEMA_NAME.TABLE_NAME in this scenario?

What are you trying...

Here is a free tool that may help get you started on the scripting part of your task:

http://www.idera.com/Products/Free-Tools/SQL-permissions/%5B/url%5D

emilybrank (3/28/2011)

---

Another Question: Is there a way, if I have both of the columns in one table, that I could set the PO Item Category field = Segment3 when the...

For your first question, you do not need to qualify the column you are updating. Try this:

UPDATE a

SET PO_ITEM_CATEGORY = b.[segment3]

FROM dbo.trialInvoice...

I like the Transcender products. I have used them for years going back to the VB6/SQL7 days.