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 with Unknown Table Name?

Hi, question:

I want to create a cursor that will loop through a table and find all the distinct county names for some address records. Then, it will create a new table with each of these county names as it loops through the cursor pulling each of the records associated with these records.

My question: How do you use the INTO syntax in Microsoft Access to create a new table when you don't know the name of the table you're creating until it finds it in the database?

My code thus far: (untested, so there might be some minor syntax errors)

DECLARE myCursor CURSOR FOR
SELECT DISTINCT CountyName FROM [ALL_RECORDS]

DECLARE @.UniqueCounty

OPEN myCursor

FETCH NEXT FROM myCursor INTO @.UniqueCounty

WHILE (@.@.FETCH_STATUS=0)
BEGIN
SELECT * FROM [ALL_RECORDS] INTO @.UniqueCounty /* <-- HERE IS THE PROBLEM!!! */
FETCH NEXT FROM myCursor INTO @.UniqueCounty
ENDAside from any questions of if or why you want to do this, you will need to use dynamic sql (aka string concatenation) to accomplish your goal.

Example:

create table #ALL_RECORDS (pk int primary key, CountyName varchar(128))
insert into #ALL_RECORDS
values (1,'del_1')
insert into #ALL_RECORDS
values (2,'del_2')
insert into #ALL_RECORDS
values (3,'insertion_attack] from (select ''Gotcha'' as val ) as tab_alias; select * from master.dbo.sysxlogins -- ')
Declare @.sql nvarchar(4000)
DECLARE @.UniqueCounty sysname
DECLARE myCursor CURSOR FOR
SELECT DISTINCT CountyName FROM #ALL_RECORDS

OPEN myCursor
FETCH NEXT FROM myCursor INTO @.UniqueCounty
WHILE (@.@.FETCH_STATUS=0)
BEGIN
set @.sql = '
SELECT * INTO [' + @.UniqueCounty + ']FROM #ALL_RECORDS '
exec (@.sql)
FETCH NEXT FROM myCursor INTO @.UniqueCounty
END

/*
Note that entry 3 in #all_records demonstrates one of the perils of this method, namely that your are executing a string the exact contents of which you do not know, leaving your system vulnerable to an insertion attack.
*/|||Thanks, I will try it.

The WHY is because I need smaller source tables refreshed every night from a new gigantic database that gets refreshed every night.

At least I have a starting point, thanks!sql

Create table with Encrypted passwords

Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regardsThis link will give you an overview of column level encryption:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regards|||Hi,
The PWDENCRYPT and PWDCOMPARE functions are used to encrypt and compare DATA
passwords are not visible in anywhere in the database.
FYI, PWDENCRYPT and PWDCOMPARE are undocumented functions , so it can change
in future versions.
Sample code to create table and encypt the password column and usage:-
Create table users ( userid int identity (1,1) not null,
pswd varbinary (128))
-- INSERTING ENCRYPED value
-- hard coded string should be replace
-- by a text box value from screen
Insert into users values (PWDENCRYPT ('hari prasad'))
declare @.pwd varbinary(128) , @.chk tinyint
-- the dencryption phase
select @.pwd=pswd from users where userid = 1
-- comparing : 1 is success, 0 is not
select @.chk=PWDCOMPARE ('hari prasad',@.pwd)
if @.chk ! = 1
Print 'Wrong Password Entered! Try Again'
else
Print 'Login Successfully'
Do a check inside application , if the value returned is "1" allow to
login.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
> Hello,
> I need to create one table where i want to add records
> with users and corresponding passwords but these passwords
> must appear encrypted.
> How can i do this? and if it is possible how can i decrypt
> these passwords.
> I need to do it but i cant put in risk the security of my
> databases. Is it possible?
> Best regards|||Thanks Narayana
Best regards
>--Original Message--
>This link will give you an overview of column level
encryption:
>http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
>Hello,
>I need to create one table where i want to add records
>with users and corresponding passwords but these passwords
>must appear encrypted.
>How can i do this? and if it is possible how can i decrypt
>these passwords.
>I need to do it but i cant put in risk the security of my
>databases. Is it possible?
>Best regards
>
>.
>

Create table as I need it, how to reference in query?

I'll try to use the customer/orders example for my situation.
Let's say I have 10 customers and each customer has n order records. Most
of the time I will just accept the default order of the order records. In
other words, when I SELECT a customers orders I get them back in the order
they are in the table.
In some cases (remember, I'm not really dealing with customers/orders) I
want to explicitly re-order a customer's order records. Not by any
particular field. There is no logical pattern.
I was thinking that I could maintain a Customer_Orders_Order table. It
would look something like this:
[ CustomerID (FK to the customer tbl) ] [ OrderIndex ] [ OrderID (FK to
Order tbl) ]
the values might look like:
1 1 2
1 2 5
1 3 8
1 4 9
2 1 2
2 2 4
2 3 3
2 4 1
2 5 5
I would use this table to display the orders in the desired order in the UI.
OrderIndex would be the index in a ListView or DataGrid, etc.
My question is, would you store all the information in a single table or
would you create a table per customer to store this information and only if
the Customer's orders have actually been reordered from their default
presentation order (row order in the table)?
If your answer is create a table for each customer, how do you work with a
dynamically created table in a sproc or query? If my client app detects
that a user dragged an order above another order thus changing the
presentation order, I would create a table with a unique name, maybe
Tbl<customerName>_OrderPresentationOrdering. Then I could insert the
ordering information. Now, later when I want to display this customer's
orders and want to check if there is explicit presentation ordering that I
should use, how do I query the table I just created? Assuming I know the
naming convention used, can you concatenate a string in a sproc to build a
table name to be used in a Query?
Man, I hope this makes sense to someone, it's hard to explain.. :)
Thanks for any help,
SteveI was just thinking about this and realized that updates and inserts would
be involved due to the quantity of item in the update/insert. This got me
thinking... If I store this data per customer, inside the customer record,
then I don't need to have anything other than an array of OrderIDs.
So I can just have a delimited string stored in a field called
"OrderOrdering" (stupid name, my actual name would be "ProtocolOrdering" but
sticking with the example....)
something like:
4;2;7;1;3;5
Insert and updates are easy (fast) and there really is no need to normalize
data like this, is there? I won't use it for searches or anything.
If anyone has a compelling reason why I shouldn't do this, please share,
please, please.
"Steve" <sss@.sss.com> wrote in message
news:uThe317QGHA.6084@.TK2MSFTNGP10.phx.gbl...
> I'll try to use the customer/orders example for my situation.
> Let's say I have 10 customers and each customer has n order records. Most
> of the time I will just accept the default order of the order records. In
> other words, when I SELECT a customers orders I get them back in the order
> they are in the table.
> In some cases (remember, I'm not really dealing with customers/orders) I
> want to explicitly re-order a customer's order records. Not by any
> particular field. There is no logical pattern.
> I was thinking that I could maintain a Customer_Orders_Order table. It
> would look something like this:
> [ CustomerID (FK to the customer tbl) ] [ OrderIndex ] [ OrderID (FK
> to Order tbl) ]
>
> the values might look like:
> 1 1 2
> 1 2 5
> 1 3 8
> 1 4 9
> 2 1 2
> 2 2 4
> 2 3 3
> 2 4 1
> 2 5 5
> I would use this table to display the orders in the desired order in the
> UI. OrderIndex would be the index in a ListView or DataGrid, etc.
> My question is, would you store all the information in a single table or
> would you create a table per customer to store this information and only
> if the Customer's orders have actually been reordered from their default
> presentation order (row order in the table)?
> If your answer is create a table for each customer, how do you work with a
> dynamically created table in a sproc or query? If my client app detects
> that a user dragged an order above another order thus changing the
> presentation order, I would create a table with a unique name, maybe
> Tbl<customerName>_OrderPresentationOrdering. Then I could insert the
> ordering information. Now, later when I want to display this customer's
> orders and want to check if there is explicit presentation ordering that I
> should use, how do I query the table I just created? Assuming I know the
> naming convention used, can you concatenate a string in a sproc to build a
> table name to be used in a Query?
> Man, I hope this makes sense to someone, it's hard to explain.. :)
> Thanks for any help,
> Steve
>|||I have a feeling you're about to feel the wrath of JC.
But to answer your question, you would create a single table for this.
I see what you mean by your sorting information. In that case, I
consider that type of data to not have anything to do with the database
-- while it's human readable and client readable, I consider the data
to be arbitrary to anything accessing it within the database. It might
as well be random binary data for all your database is concerned. Now,
the question is, does this data belong in your database? You're
breaking encapsulation by storing this in your database if it's not
specific to the data. I make two exceptions to putting
application-specific data within a database. The first is when the
database is used by one and only one application, and will be used by
one and only one application for the purpose of storing data and logic
specific to that application. The second is when the performance or
implementation advantages of storing such application-specific logic
and data are exceptionally significant.
-Alan|||Steve wrote:
> I was just thinking about this and realized that updates and inserts would
> be involved due to the quantity of item in the update/insert. This got me
> thinking... If I store this data per customer, inside the customer record
,
> then I don't need to have anything other than an array of OrderIDs.
> So I can just have a delimited string stored in a field called
> "OrderOrdering" (stupid name, my actual name would be "ProtocolOrdering" b
ut
> sticking with the example....)
> something like:
> 4;2;7;1;3;5
> Insert and updates are easy (fast) and there really is no need to normaliz
e
> data like this, is there? I won't use it for searches or anything.
> If anyone has a compelling reason why I shouldn't do this, please share,
> please, please.
>
What reason would you have for NOT normalizing in this case?
You seem to be making something very simple into something very
complex. No question in my mind: One table in Normal Form. The keys
would be I assume (customer, order_index) and (customer, order_id).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I've upset JC?
(who is JC? Like Jesus Christ JC or is there a NG regular named JC?)
Yes, the data does belong to the database. I'll tell you why; given my
example, it sounds like an app preference or user preference, but this
ordering is actually used to generate source code. If a user logs in at
location A and initiates a build without knowing the ordering of the
records, that would result in invalid firmware and an irritated customer.
This is a feature I should have added from the get go, but I overlooked it.
So moving forward and agreeing (I assume we agree?) that the data does
belong in the DB, is this a valid case for a delimited string?
If not, make your case. I want to learn the right way.
Thanks,
Steve
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141943305.488882.121840@.u72g2000cwu.googlegroups.com...
>I have a feeling you're about to feel the wrath of JC.
> But to answer your question, you would create a single table for this.
> I see what you mean by your sorting information. In that case, I
> consider that type of data to not have anything to do with the database
> -- while it's human readable and client readable, I consider the data
> to be arbitrary to anything accessing it within the database. It might
> as well be random binary data for all your database is concerned. Now,
> the question is, does this data belong in your database? You're
> breaking encapsulation by storing this in your database if it's not
> specific to the data. I make two exceptions to putting
> application-specific data within a database. The first is when the
> database is used by one and only one application, and will be used by
> one and only one application for the purpose of storing data and logic
> specific to that application. The second is when the performance or
> implementation advantages of storing such application-specific logic
> and data are exceptionally significant.
> -Alan
>|||>> So I can just have a delimited string stored in a field [sic] called"OrderOrde
ring" (stupid name, my actual name would be "ProtocolOrdering" but sticking
with the example....) something like:4;2;7;1;3;5 <<
Once more, you missed the most basic concepts again! Look up "First
Normal Form" (1NF), and learn why a column is not a field, a row is not
a record and a table is not a file. You might want to read at least
one book on RDBMS before you code in SQL.|||>I've upset JC?
>(who is JC? Like Jesus Christ JC or is there a NG regular named JC?)
I think you've probably figured this out by now.
So, you've decided to keep this in your database. Be aware of the
possible future implications of this. If you store multiple values a
single row, those values will be utterly useless in your database and
you'll be forced to do your sorting on the client. Next, if you dump
your front-end and it's rewritten, you'll have this artifact in your
database that those inheriting your work won't know what to do with
(or, given enough time, you won't either). In this situation, where
you've decided to store application-specific data in your database, at
least make it friendly to whoever may be administering thing. Store
your application-specific data in an application-specific location --
either a "helper" database that is specific to your application, or in
tables that are clearly indicated as being specific to your application
(e.g. App_MyApplication_InvoiceSorting) so that it's known they can be
dropped without implications once your application is no longer used.
-Alan|||>> 've upset JC? (who is JC? Like Jesus Christ JC or is there a NG regular
named JC?) <<
This si why I go by "--CELKO--" in the Newsgroup; peopel keep getting
us all the time.
hout knowing the ordering of the records [sic], that would result in invalid firm
ware and an irritated customer. <<
So the ordering has logical meanng in the data model!
So moving forward and agreeing (I assume we agree?) that the data does
belong in the DB, is this a valid case for a delimited string? <<
NO, it is a valid reason for a sequencing column in First Normal Form.
Since you will not post any DDL or even helpful narrative with
meaningful names, here is a guess:
CREATE TABLE FirmwareTemplates
(customer_id INTEGER NOT NULL
REFERENCES Custromers (customer_id),
template_seq INTEGER NOT NULL,
template_txt VARCHAR (255) NOT NULL,
PRIMARY KEY (customer_id, template_seq ));|||LOL.. let the NG lashings begin!
My entire database is application specific to a single app. It's entire
reason for existence is my application. It would seem in that case that ALL
my data is application specific. Is that bad? I'm getting the distinct
feeling that is somehow a bad thing?
Either way, yes, I am doing the sorting on the client. This is intentional.
My application is a high level firmware editor for our sales people. We
sell a product that can be customized (a bit) per customer. However, ALL
device's firmware must share some data that is constant.
One of the things that can be customized is the ordering of menu items in a
UI menu. All devices have the same menu items, but some customers want them
in a different order. I have approached this by maintaing a base shared
collection of menu items that all customers use. When they want to reorder
the items I store "meta data" (I'm not even sure if I understand that term,
seems like "settings" to me, but whatever) in their record.
Point is, I don't return a separate set of ordered menu item records for
each customer, I get the menu items for all customers, then the ordering
data for each customer, then on the client each customer has a reference to
the shared items and I order them the way they want in the UI.
Hope that makes a bit of sense.
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141944627.875280.107100@.i40g2000cwc.googlegroups.com...
> I think you've probably figured this out by now.
> So, you've decided to keep this in your database. Be aware of the
> possible future implications of this. If you store multiple values a
> single row, those values will be utterly useless in your database and
> you'll be forced to do your sorting on the client. Next, if you dump
> your front-end and it's rewritten, you'll have this artifact in your
> database that those inheriting your work won't know what to do with
> (or, given enough time, you won't either). In this situation, where
> you've decided to store application-specific data in your database, at
> least make it friendly to whoever may be administering thing. Store
> your application-specific data in an application-specific location --
> either a "helper" database that is specific to your application, or in
> tables that are clearly indicated as being specific to your application
> (e.g. App_MyApplication_InvoiceSorting) so that it's known they can be
> dropped without implications once your application is no longer used.
> -Alan
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141944759.875765.197440@.e56g2000cwe.googlegroups.com...
> This si why I go by "--CELKO--" in the Newsgroup; peopel keep getting
> us all the time.

>
> So the ordering has logical meanng in the data model!
yes, yes it does.

>
> So moving forward and agreeing (I assume we agree?) that the data does
> belong in the DB, is this a valid case for a delimited string? <<
> NO, it is a valid reason for a sequencing column in First Normal Form.
> Since you will not post any DDL or even helpful narrative with
> meaningful names, here is a guess:

> CREATE TABLE FirmwareTemplates
> (customer_id INTEGER NOT NULL
> REFERENCES Custromers (customer_id),
> template_seq INTEGER NOT NULL,
> template_txt VARCHAR (255) NOT NULL,
> PRIMARY KEY (customer_id, template_seq ));
>
Gotcha, I will investigate this, thanks for the suggestion!

create table as

I want to create table from
selecting records from another table In sql server 7.0
e.g
create table tablename
as
select * from another table/view.
How is it possible ??

Thanx in Advance.Hi To create a new table use

SELECT *
INTO fred
FROM bill
IT will fail if fred already exists

To empty and reuse then use

Truncate fred
INSERT INTO fred
SELECT *
FROM bill

Did I get the question right ?

Gerry|||I want to create table from
selecting records from another table In sql server 7.0
e.g
create table tablename
as
select * from another table/view.
How is it possible ??

Thanx in Advance.

WOW, you sound like a DB2 person...

Just don't use SELECT * if it's for Production.....|||Quite right about the "*" of course... I'm just a lazy typer :)

As for the DB2 guess:- not even close !|||Gerry, not you...dbtechzala

The syntax is close to a DB2 syntax....

Be careful of the TRUNCATE though...it'll mark every page as being deleted...

no where clause there....

Create Status Table

I have >200 tables and I want to create a table that lists the name of
each table, the number of records, and the number of locations within
the table.

I've created a cursor to do this but it doesn't like it. I get the
following error.

Invalid column name '<tablename>'.

Here's my script

DECLARE @.tbl varchar(100)
DECLARE @.sql varchar(1000)
-- Insert statements for procedure here
declare c_table cursor for
select table_name from INFORMATION_SCHEMA.TABLES where table_type =
'base table' order by table_name

open c_table
fetch next from c_table into @.tbl

while (@.@.fetch_status = 0)
begin

set @.SQL = 'INSERT INTO [zzTable_Status]
SELECT ('+ @.tbl +') as tblname, count(distinct station__no),
count(station__no)
FROM [bronze_views].'+@.tbl+''

exec (@.SQL)

Print @.tbl + ' Updated'

fetch next from c_table into @.tbl
end
close c_table
deallocate c_table

Any help is appreciated...try changing this
select table_name from INFORMATION_SCHEMA.TABLES where table_type =
'base table' order by table_name

to this

select quotename(table_name)
from INFORMATION_SCHEMA.TABLES where table_type =
'base table' order by table_name

you probably have a table name with a space in the name

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Unfortunately, that didn't work. I still got the same error the table
just appear with brackets.

Any other ideas?|||change exec to print and look at the code generated
If I change exec to print the cript runs without a problem
Do all table have this same column name station__no ?

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||To answer your question - Yes every table has that column. I think
what the problem is with the script is that I need to insert the name
of the table into the zzTable_Status table. This needs to be marked as
a string. So, '<tablename>' needs to be in the insert script.

Basically, I want the name of the table, number of station__no's in the
same table, and the number or records in the same table.

or another example

select 'cityname', count(distinct streets), count(streets) from
cityname.

What am I missing?

I hate it when you know what you want but you can't think of it.

I appreciate your help!

I have your blog as one of my rss feeds. I'll try to help your adsense
account. :-)|||I see, you need triple quotes to store the table name
This should do it

set @.SQL = 'INSERT INTO [zzTable_Status]
SELECT '''+ @.tbl +''' , count(distinct station__no),
count(station__no)
FROM [bronze_views].'+@.tbl+''

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||No dice! <darn it!
I still get invalid object name '<table_name>'.

Let's try skinning this cat a different way. Do you have any
suggestions.

I want to create a table (or view) with the name of each of the tables
in the db, the number of times a location appears, and the total number
of records. I'm sure someone has done this before.

Any suggestions?|||I have no problems running this in the pubs DB

use pubs

go
create table zzTable_Status (tblname varchar(600),DistinctCount
int,RegularCount int)
Go

DECLARE @.tbl varchar(100)
DECLARE @.sql varchar(1000)
-- Insert statements for procedure here
declare c_table cursor for
select table_name from INFORMATION_SCHEMA.TABLES where table_type =
'base table' order by table_name

open c_table
fetch next from c_table into @.tbl

while (@.@.fetch_status = 0)
begin

set @.SQL = 'INSERT INTO [zzTable_Status]
SELECT ('''+ @.tbl +''') as tblname, count(*),
count(*)
FROM .'+@.tbl+''

exec (@.SQL)

Print @.tbl + ' Updated'

fetch next from c_table into @.tbl
end
close c_table
deallocate c_table

select * from zzTable_Status
drop table zzTable_Status

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I still get invalid object name errors.

I'm trying to do this in 2005. Maybe there is something I haven't set
right or something.

This is frustrating...|||I know what the problem is it's the schema

This will run I just tried it on 2005
this is the change
select table_schema +'.' + table_name from INFORMATION_SCHEMA.TABLES
where table_type =
'base table' order by table_name

use adventureworks

go
create table zzTable_Status (tblname varchar(600),DistinctCount
int,RegularCount int)
Go

DECLARE @.tbl varchar(100)
DECLARE @.sql varchar(1000)
-- Insert statements for procedure here
declare c_table cursor for
select table_schema +'.' + table_name from INFORMATION_SCHEMA.TABLES
where table_type =
'base table' order by table_name

open c_table
fetch next from c_table into @.tbl

while (@.@.fetch_status = 0)
begin

set @.SQL = 'INSERT INTO [zzTable_Status]
SELECT ('''+ @.tbl +''') as tblname, count(*),
count(*)
FROM .'+@.tbl+''

exec (@.SQL)

Print @.tbl + ' Updated'

fetch next from c_table into @.tbl
end
close c_table
deallocate c_table

select * from zzTable_Status
drop table zzTable_Status

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||also take out the dot here
The code works but the dot shouldn't be there anyway
instead of this
FROM .'+@.tbl+''
use this
FROM '+@.tbl+''

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"db55" <chfran@.gmail.com> wrote in message
news:1147798134.372688.254340@.v46g2000cwv.googlegr oups.com...
> I have >200 tables and I want to create a table that lists the name of
> each table, the number of records, and the number of locations within
> the table.

Out of curiousity, WHY?|||Hi db55,

I think this will help you

Create Table TableNameRow
(
TableName varchar(100) Not Null,
recs int
)
Go
Exec sp_msforeachtable 'Insert into TableNameRow Select ''?'',count(1)
from ?'
Go
Select * from TableNameRow

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com|||I want to track the status of each table. I'm importing millions of
records into these tables and I want to track them. I also create
reports for my mgmt with the data pulls.

I always make sure I ask that question so I'm not wasting my time.

Thanks for asking...

Create record each day from time frame

I have a table that has cost records for a specific item for a specific star
t
date.
Start, cost, item, PromoCode, end date
01/02/2006, 2.45, 1234, R,
01/05/2006, 2.00, 1234, P, 01/08/2006
01/10/2006, 2.55, 1234, R,
If a record has a start date then that new cost begains. And if it doesn't
have an end date it will go indefinitely. Also when a promo (P) ends then
the cost goes back to the Regular (R) cost record.
So the cost records that I would create from the above records is
01/02/2006, 2.45, 1234, R
01/03/2006, 2.45, 1234, R
01/04/2006, 2.45, 1234, R
01/05/2006, 2.00, 1234, P
01/06/2006, 2.00, 1234, P
01/07/2006, 2.00, 1234, P
01/08/2006, 2.45, 1234, R
01/09/2006, 2.45, 1234, R
01/10/2006, 2.55, 1234, R
01/10/2006, 2.55, 1234, R
.....
Looking for any help with how to start a stored procedure or query to come
up with these records.
Thanks!Suggest joining to an auxiliary calendar table
See http://www.aspfaq.com/2519

Create procedure to insert records for a project

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

Quote:

Originally Posted by PRAW

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

if this is one time and you have many records to insert and happens to be in a file (txt or xls), try DTS|||

Quote:

Originally Posted by ck9663

if this is one time and you have many records to insert and happens to be in a file (txt or xls), try DTS

------
No this is not one time and i have to insert this set of 11 records for each of the 40 projects i.e. 40 times.. so i need to be able to create a procedure where i can increment the value of task_taskid for each record and insert the corresponding field values.|||

Quote:

Originally Posted by PRAW

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

Try below Logic to create a procedure:

1. Have a Cursor that will hold task_name, task_id, task_budjetdollar, task_budjethours
2. have a counter variable initilized to 1
3. LOOP through 1181..1221 becuase u said u need to add for 40 projectids from 1181,1182 and so on
4. With a FOR LOOP, loop through CURSOR data, and for each record (task_name), insert into table with task_name,task_id and projectid(FOR Loop value) and task_outline_num = counter variable that you have declared before in the procedure
5. COMMIT
6. Increment the counter variable by 1
7. End the Cursor LOOP
8. Reset the counter variable to 1
9. End Outer FOR LOOP
10. End Procedure