Create table, remove records?

SQL 2000;
I have a table that has info from 2001 thru 2005, I want to create new
tables and move records from the original table to the new tables, based on
year.
What is the best way to do this?
1. Create empty tables for 2001 to 2005 with record structures identical
to the original and no indexes.
2. For each table, do something like the following:
insert into SALES_2001 select * from SALES where period = 2001
3. Create indexes to the new tables.
Since you are inserting into new tables, you could also temporarily set the
database recovery model to "simple". If there are millions of rows, this
will reduce transaction logging and speed things up.
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:1439F5CF-1C3E-4A28-BEF8-D7CA4423CBA3@.microsoft.com...
> SQL 2000;
> I have a table that has info from 2001 thru 2005, I want to create new
> tables and move records from the original table to the new tables, based
on
> year.
> What is the best way to do this?
>
|||Same advice JT gave you except if the table structures are the same use
select into instead of create table and insert into.
"JT" <someone@.microsoft.com> wrote in message
news:OxqloKwgFHA.3616@.TK2MSFTNGP12.phx.gbl...
> 1. Create empty tables for 2001 to 2005 with record structures
> identical
> to the original and no indexes.
> 2. For each table, do something like the following:
> insert into SALES_2001 select * from SALES where period = 2001
> 3. Create indexes to the new tables.
> Since you are inserting into new tables, you could also temporarily set
> the
> database recovery model to "simple". If there are millions of rows, this
> will reduce transaction logging and speed things up.
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:1439F5CF-1C3E-4A28-BEF8-D7CA4423CBA3@.microsoft.com...
> on
>
sql

Create table, remove records?

SQL 2000;
I have a table that has info from 2001 thru 2005, I want to create new
tables and move records from the original table to the new tables, based on
year.
What is the best way to do this?1. Create empty tables for 2001 to 2005 with record structures identical
to the original and no indexes.
2. For each table, do something like the following:
insert into SALES_2001 select * from SALES where period = 2001
3. Create indexes to the new tables.
Since you are inserting into new tables, you could also temporarily set the
database recovery model to "simple". If there are millions of rows, this
will reduce transaction logging and speed things up.
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:1439F5CF-1C3E-4A28-BEF8-D7CA4423CBA3@.microsoft.com...
> SQL 2000;
> I have a table that has info from 2001 thru 2005, I want to create new
> tables and move records from the original table to the new tables, based
on
> year.
> What is the best way to do this?
>|||Same advice JT gave you except if the table structures are the same use
select into instead of create table and insert into.
"JT" <someone@.microsoft.com> wrote in message
news:OxqloKwgFHA.3616@.TK2MSFTNGP12.phx.gbl...
> 1. Create empty tables for 2001 to 2005 with record structures
> identical
> to the original and no indexes.
> 2. For each table, do something like the following:
> insert into SALES_2001 select * from SALES where period = 2001
> 3. Create indexes to the new tables.
> Since you are inserting into new tables, you could also temporarily set
> the
> database recovery model to "simple". If there are millions of rows, this
> will reduce transaction logging and speed things up.
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:1439F5CF-1C3E-4A28-BEF8-D7CA4423CBA3@.microsoft.com...
> on
>

CREATE TABLE template, Management Studio Express

I accidentally overwrote the CREATE TABLE template in SQL Server Management Studio Express. Could someone please post the original template?

FYI

-- =========================================
-- Create table template
-- =========================================
USE <database, sysname, AdventureWorks>
GO

IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL
DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,
<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,
CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

CREATE TABLE Template

I accidentally altered the CREATE TABLE template from SQL Server Management Studio Express. Now I don't have the original. Could somebody please post CREATE TABLE template.

-- =========================================

-- Create table template

-- =========================================

USE <database, sysname, AdventureWorks>

GO

IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL

DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>

GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>

(

<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,

<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,

<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,

CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)

)

GO

|||

Or the simplified version:

Create Table MyTable(FirstField varchar(50), SecondField int)

or...

when in doubt >> Right Click in Management Studio to create a new table manually

Adamus

|||

Thanks, joeydj,

johncelmer

sql

create NorthWind Sample Database

hi
i have the instnwnd.sql with the structure of NorthWind Sample Database. How
can i import it?
The original database was deleted.
thx
Use OSQL and specify the file name using the /i parameter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"msnews.microsoft.com" <visperas1@.hotmail.com> wrote in message news:uE9kYeAOEHA.1456@.TK2MSFTNGP09.phx.gbl...
> hi
> i have the instnwnd.sql with the structure of NorthWind Sample Database. How
> can i import it?
> The original database was deleted.
> thx
>
|||Use osql or open the file in Query analyzer (isqlw) and run the .sql file
using F5.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"msnews.microsoft.com" <visperas1@.hotmail.com> wrote in message
news:uE9kYeAOEHA.1456@.TK2MSFTNGP09.phx.gbl...
> hi
> i have the instnwnd.sql with the structure of NorthWind Sample Database.
How
> can i import it?
> The original database was deleted.
> thx
>
|||Alternately, there is a free (for personal use) tool at our site (MSDE
Manager) that you can use for this and other management options. Hope you
find it useful.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:%23LMH1nBOEHA.268@.TK2MSFTNGP11.phx.gbl...
> Use osql or open the file in Query analyzer (isqlw) and run the .sql file
> using F5.
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "msnews.microsoft.com" <visperas1@.hotmail.com> wrote in message
> news:uE9kYeAOEHA.1456@.TK2MSFTNGP09.phx.gbl...
> How
>