a few basic questions about mssql

sfullman

hi,

migrating over from mysql experience, setting up stuff now in mssql.

How to copy a table using SQL commands -- and where do I go, if necessary, to find the field properties for that table.
How to list fields in a table?

Thanks! and any online tutorials would be great.

tomhath

A of lot support software comes with mssql. Tools to create, copy, backup, restore, and trace, etc. Get a good book on SQL Server Administration so you have a reference in one place, a couple of tutorials just won't cut it. The user interface is kind of goofy and it's hard to know which options to select to do what you want (e.g. to watch a trace of the SQL the server is running you start something called the "Profiler", huh?).

sfullman

Hi Tom,

THANKS FOR THE SUGGESTION! I know you're a moderator on the forum, would you suggest any book yourself? (this is version 7.0)

I'm doing this project remotely and only have access to mssql through php remotely, not the desktop interface. So whatever is done, needs to be done through SQL commands. The biggest issue is copying a table and its contents to another table which is easy in phpmyadmin of course.

By the way, that SQLBOL (SQL Books Online) resource has some great stuff, but there's so much in it it's an effort to find what I want sometimes.

Sincerely,
Sam Fullman

Sphynx

SELECT * INTO <new_table_name> FROM <old_table_name> [WHERE ... ]

<new_table_name> should not exist before executing the query. You'll end up with a newly named table containing exactly the same data as the old table. Same fields, same data types. WHERE clause is optional, of course.

sfullman

now I have to admit, that's the bomb when it comes to table duplication. mySQL should do likewise.

What book would you recommend for getting started in mssql? I already know SQL fairly well (atleast as applied to mySQL) and basic set theory and relational database theory.

Thanks,
Sam Fullman

tomhath

What Sphynx posted works. There are also some system tables (sysobjects, syscolumns) that you can query:

select sysobjects.name as tablename, syscolumns.name as columnname
from sysobjects,syscolumns
where sysobjects.id=syscolumns.id
and sysobjects.type='U'
order by 1,2

I often create temporary tables for testing or short term backup with:

select * into #junk from someTable

I'd go with a book from Microsoft or O'Reilly. SQLServer has the usual Microsoft non-standard extensions so an ANSI SQL book won't be enough. SQLServer 7.0 and 2000 are almost the same.

http://www.microsoft.com/mspress/books/4713.asp
http://www.oreilly.com/catalog/wintrnssql/