Michael, are you a local administrator on the machine you installed MSDE (desktop engine) on?  By default, MSDE should allow administrators to connect with the "-E" option because of a SQL...

This may be moot because using a temp table for a large count set is desirable, but what if the record count exceeded 2,147,483,647 (the count function returns and integer), do you...

This has been a really fun topic!  Just for fun I populated a huge CHECK_DATA table to see the effects of the queries for a long history.  I am getting the...

I think 2GB is it.  Although, with SQL Standard licensed per processor, maybe a machine with 4GB memory, and 2 instances of SQL standard could each use their own 2GB??

I'm...

Andreas, you can avoid a temp table - that is what Steve's solution was for.  Post your table name and properties if need be and we can post the exact...

Andreas, Steve's solution is correct (and clever):

Based on your example:

Create TABLE temptable (col1 varchar(10), col2 varchar(10), col3 varchar(10))

GO

INSERT INTO temptable values('Val 1a', 'Val 2a', 'Val 3a')

INSERT INTO temptable values('Val 1b',...

Can you live with a table variable???

DECLARE @tempTableVariable TABLE (IDENT int IDENTITY(1,1), col1 varchar(10), col2 varchar(10), col3 varchar(10))

INSERT INTO @tempTableVariable

SELECT * FROM SourceTable

select * from @tempTableVariable

Yields:

1 Val 1a Val 2a Val 3a

2 Val 1b Val...

Matt, I'm sure there is a much more creative way to do this without a temp table, but here is an untested solution with a temp table.

CREATE TABLE #UnionTable (BusinessDate...

Simon, you may get a lot of differring opinions on this - and in many cases it will depend on the specific situation.  However, as a GENERAL guideline, I always...

That's an interesting problem.  This may not be the best way to do this...but, a quick way I would do it would be to:

Create a temp table and change the...

Senthil,

As a final note.  See Remi's use of the table variable.  If you are using this for a one time query, you really will want to do it that way. ...

Wayne, maybe my Friday afternoon reading is getting a bit lazy - if so please forgive...

Anyway, BOL states: "If the values in the VALUES list are not in the same...

From books on line:

"TRUNCATE TABLE permissions default to the table owner and are not transferable."

Ryan

Senthil, is this an example of what you are looking for?

use tempdb

create table sp_who (ID INT IDENTITY (1,1) NOT NULL

 ,spid varchar (10)

 ,status varchar (20)

 ,loginname varchar(20)

 ,hostname varchar(20)

 ,blk varchar(20)

 ,dbname varchar(20)

 ,cmd varchar(20))

GO

use master

INSERT...

Wayne,

For example one you have to explicitly state your insert fields since you are not inserting into the identity column.  Simply adding the insert fields makes it work.

DECLARE @tmp1 TABLE...