SSCrazy - (totally off topic but...) I agree wholeheartedly about Office 2007. I can't find anything and the help doesn't even let you look up the old menu sequence...

Thank you! I'm a wiz at MSWord but never thought to use it.

(Used to teach "Introduction to Microcomputer Applications" at the local college - when I started...

No, you are NOT the only one. I thought _I_ was doing something wrong. I copy Jeff's beautiful code and have to run through reinserting the line breaks....

Curses - web site ate the spacing in previous post.

Mannie,

If you inherited the db, it is what it is and you're stuck with it.

But if I were writing that query, I'd write:

SELECT a.bankid

,a.datum

,a.instruction

,a.amount

,a.description

,a.detail

FROM bs787 a left...

I still prefer KEYWORDS in all caps. Sure the IDE colorizes them for you but that doesn't help when you print the code on your old B/W laser printer....

'...performance will be terrible compared to the "UPDATE" method I wrote about, but the group sizes are small enough where most folks won't notice...'

Do you have a link to your...

That is almost exactly what we are doing.

We set the restored database to simple before making the subset and anonymizing the data. In fact, it's the...

Thank you.  I learn so much from here. 

Michael - Very nice solution!  I've never worked with the INFORMATION_SCHEMA tables before, only sysobjects like Jeff Moden's solution to the same...

I ran Jeff's code (because I want to understand it!!) and got what I expected which is:

Shape,Length,Width,Color,Length side 1,Length side 2,Length hypotenuse

rectangle,8,10,blue,,,

square,12,,red,,,

triangle,,,yellow,3,4,5

I added another item to the data:

INSERT INTO @table1...

...UNION...

This would make a good "academic exercise" to teach yourself database design using MS Access.  Check the MS Office Templates area on the MS web site.  They have several collections templates...

Works great, Frank. 

Also see: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=119192&p=1

for similar solution.

I did it in a stand-alone VB program.  I found this very helpful:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_2yi7.asp

Set oSQLServer = CreateObject("SQLDMO.SQLServer2")

oSQLServer.LoginSecure = True

oSQLServer.Connect strSqlServerName

Set oDatabase = oSQLServer.Databases(strSqlDatabaseName)

For Each oDatabaseObject In oDatabase.Tables

    If Not oDatabaseObject.SystemObject Then

         strScript...

TRUNCATE also resets any IDENTITY column so it starts over from the seed value.  DELETE does not.  After a DELETE, new rows are numbered from where it left off. 

DATEADD(d,DATEDIFF(d,0,[DateVar]),0)

is faster than

CONVERT(DATETIME,CONVERT(varchar(11), [DatVar]), 112)

Both simply set the time component to 12:00:00 AM.

We have a database with patient appointments and the ApptDate column is the date & time...