Ignore my last post. It's utterly wrong.

What kind of indexes do you have on the table?

It is a security issue. You have to make that determination for yourself. It's off by default. Run sp_configure. It's the setting 'Remote Proc Trans' and setting it to 1...

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.