What problems specifically are you worried about or experiencing?

54 million rows that are composed of one integer column just isn't going to be an issue. Are you experiecing fragementation of...

The issue you're running into is the logical processing order. The SELECT list is processed after the FROM, ON, JOIN, WHERE, GROUP BY, WITH, HAVING clauses. Any reference to items...

The variant doesn't scare me (much), but everything into one table? How many users are expected to access this? What does the indexing look like? Has anyone vetted the design...

Yeah, I mean, we had every single possible good technical, security and performance reason not to give them what they asked for. However, they trumped all those with a phone...

You're trying to combine the function of EXEC where it executes an ad hoc sql string with the function of exec where it captures the return status of the execution...

DBA's & Developers, yeah, they should get it because it does have the tools they need to get the job done.

End-users... Why on earth are the end-users generating ad hoc...

Or you could use COALESCE

INSERT INTO [dbo].[TableA]

([Col1],

[Col2],

[Col3])

SELECT

COALESCE([Col1],'unknown'),

COALESCE([Col2],'unknown'),

COALESCE([Col3],'unkown'

FROM [dbo].[TableB];

If you're using the VALUES statement on insert, you can use the default value:

VALUES

(DEFAULT,

'Something',

'Something)

But if you really wanted the defaults to...

insane_professional (10/22/2007)

---

hi, i think its a pretty simple task but i just cant seem to get it right as I am pretty new to SQL.

What i want to do is...

You can do this with client side code in VB. It works, but it's not the best choice since you're sacrificing the power of SQL Server in order to put...

Sorry, I'm stumped. I couldn't do both. I got to learn a bit about the query and value methods of xml data types that I wasn't that aware of, but...

ecreese (10/19/2007)

---

Grant when I run your query I get this error

Msg 421, Level 16, State 1, Line 1

The xml data type cannot be selected as DISTINCT because it is not...

I tested using the original query, not the DISTINCT. Hang on.

Oops.

Got it. Add this to the inner join:

FOR XML PATH (''), type

so it looks like this:

SELECT DISTINCT LocalID as "@ID"

, (SELECT ItemUrl

FROM foo X

WHERE f.LocalID=x.LocalID

ORDER BY x.photoseq

FOR XML PATH(''),...

Just so I had some data to mess with, I modified your query to use something form AdventureWorks:

SELECT A.ProductId "@ID"

,a.Name "Col1"

, (SELECT x.StandardCost

FROM [Production].[ProductCostHistory] X

WHERE A.ProductId=X.[ProductID]

ORDER BY X.StartDate

FOR XML PATH('')

)...

Until we get the 2008 MERGE statement, you need to do three passes through the data, joining on the incoming data such that you do a batch of updates where...