Creating a unique temp table.

Set Quoted_Identifier On
Go
Set Ansi_Nulls On
Go

Alter Procedure spReport_SomeFooReport
@.SearchFromThisDate datetime = null, @.SearchToThisDate datetime = null

As
Declare @.TableUniqueIdentifier varchar(80), @.SQLString varchar(5000)

set @.TableUniqueIdentifier = newid()
set @.TableUniqueIdentifier = 'Report_SomeFooReport' + @.TableUniqueIdentifier
set @.TableUniqueIdentifier = replace(@.TableUniqueIdentifier, '-', '7')
set @.SQLString = 'Create Table ' + @.TableUniqueIdentifier + ' (xxx varchar(40))'
exec @.SQLString

Return
Go
Set Quoted_Identifier Off
Go
Set Ansi_Nulls On
Go

--------------
the error is:
Server: Msg 2812, Level 16, State 62, Line 12
Could not find stored procedure 'Create Table Report_SomeFooReport06EEEC8D7EA6A74D0178EDD79E999B (xxx varchar(40))'.

So may'be a format issue or something,
im trying to create "temp" tables for sql 2005 report services in my Stored procedures which would have a sql job to get deleted at 23:00This looks like Sql Server. If that's the case, try using

EXEC (@.SQLString)|||RedNeckGeek is right, the EXEC without the parentheses means you're calling a stored procedure.|||also, it's bad form to create permanent tables on the fly from sprocs.

If your database schema is well designed, there would be no need for this. Perhaps use a temp table instead?

Or use a single permanent table for all your reports since they all have exactly the same structure, with just a single column, xxx varchar(40). You could add another column to identify the report instance.|||I would like to know howyou plan to reference that table in the future|||I would like to know howyou plan to reference that table in the future

the only possible way I know of would be to return the table name as an out param from the sproc.

Creating a unique index on a table

Hi,
I'm trying to create an index on a newly created table. I want the index to
be on the field ref. I tried running:
CREATE INDEX ref ON U_segment (ref)
but it's just sitting there! The ref is unique anyway, and there are about
700,000 records.
Any help, or advice would be appreaciated.
Regards
Rob
Does it run and stop, or what?
I wonder if having the index name and the column name the same is the
issue...try
CREATE INDEX IX_ref ON U_segment (ref)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:9AF46DBF-1693-4DD3-A324-BB378FCEBB79@.microsoft.com...
> Hi,
> I'm trying to create an index on a newly created table. I want the index
> to
> be on the field ref. I tried running:
>
> CREATE INDEX ref ON U_segment (ref)
> but it's just sitting there! The ref is unique anyway, and there are about
> 700,000 records.
> Any help, or advice would be appreaciated.
> Regards
> Rob
|||Did you look to see if you are being blocked?
Andrew J. Kelly SQL MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:9AF46DBF-1693-4DD3-A324-BB378FCEBB79@.microsoft.com...
> Hi,
> I'm trying to create an index on a newly created table. I want the index
> to
> be on the field ref. I tried running:
>
> CREATE INDEX ref ON U_segment (ref)
> but it's just sitting there! The ref is unique anyway, and there are about
> 700,000 records.
> Any help, or advice would be appreaciated.
> Regards
> Rob
sql

Creating a Unique Index

Hi

I tried the following from the help file...

When you create or modify a unique index, you can set an option to
ignore duplicate keys. If this option is set and you attempt to create
duplicate keys by adding or updating data that affects multiple rows
(with the INSERT or UPDATE statement), the row that causes the
duplicates is not added or, in the case of an update, discarded.

For example, if you try to update "Smith" to "Jones" in a table where
"Jones" already exists, you end up with one "Jones" and no "Smith" in
the resulting table. The original "Smith" row is lost because an
UPDATE statement is actually a DELETE followed by an INSERT. "Smith"
was deleted and the attempt to insert an additional "Jones" failed.
The whole transaction cannot be rolled back because the purpose of
this option is to allow a transaction in spite of the presence of
duplicates.

But when I did it the original "Smith" row was not lost.

I am doing something wrong or is the help file incorrect.

DanThose paragraphs are referring to the IGNORE_DUP_KEYS option which is not
the default when creating an index. Did you specify the IGNORE_DUP_KEYS
option on your CREATE INDEX statement?

Why do you want to ignore duplicate keys in this way? Typically, it would be
better to put the code to ignore duplicates in your INSERT or UPDATE
statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
IGNORE_DUP_KEYS option is a little strange and very non-standard and
non-relational as this article explains.

--
David Portas
----
Please reply only to the newsgroup
--|||Hi

"Did you specify the IGNORE_DUP_KEYS"

Yes I did.

The issue I have is using a update statement with a table that has
IGNORE_DUP_KEYS index as the help file says --

"if you try to update "Smith" to "Jones" in a table where "Jones"
already exists, you end up with one "Jones" and no "Smith" in the
resulting table. The original "Smith" row is lost because an UPDATE
statement is actually a DELETE followed by an INSERT"

But when I try this, the original "Smith" row is not lost...

So am I doing something wrong or is the help file wrong.

Could you give it a try?

Thanks

Dan

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<lI-dnYK39q4-Y1yi4p2dnA@.giganews.com>...
> Those paragraphs are referring to the IGNORE_DUP_KEYS option which is not
> the default when creating an index. Did you specify the IGNORE_DUP_KEYS
> option on your CREATE INDEX statement?
> Why do you want to ignore duplicate keys in this way? Typically, it would be
> better to put the code to ignore duplicates in your INSERT or UPDATE
> statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
> IGNORE_DUP_KEYS option is a little strange and very non-standard and
> non-relational as this article explains.|||You're right. The UPDATE statement described should produce an error
("Cannot insert duplicate key"). The RTM version of Books Online is wrong
and that page has been changed in the latest version:

http://msdn.microsoft.com/library/e...uniqueindex.asp

--
David Portas
----
Please reply only to the newsgroup
--

Creating a unique constarint on a multiple null column

HI,

To create a unique constraint on a multiple nullable column, we need to create a view with not null column and and then create a unique index on that view.

Is this is the only way of doing ?

Thank you.

Yes.

Since a Primary Key CONSTRAINT requires NOT NULL values, a UNIQUE index is the best alternative method to force a constraint on columns that can contain NULL values.

Creating a sustaining counter as in work orders - unique

Hi Guys,

Im still new to this SQL stuff and have a question about creating a counter that does not reset if I drop the temp table. Is this possible? I need to add new work order number (counter) to a daily orders table/view/procedure and I can make it work for one day but then when I drop the temp table, it resets the counter back. How can I keep the Max(orderno) going forward to the next day?

I am a little versed in stored procedures too. We are using SQL 2000 at the moment in the office.

Any ideas for this simple minded gal?

Thanks!
Jo

Quote:

Originally Posted by Joell

Hi Guys,

Im still new to this SQL stuff and have a question about creating a counter that does not reset if I drop the temp table. Is this possible? I need to add new work order number (counter) to a daily orders table/view/procedure and I can make it work for one day but then when I drop the temp table, it resets the counter back. How can I keep the Max(orderno) going forward to the next day?

I am a little versed in stored procedures too. We are using SQL 2000 at the moment in the office.

Any ideas for this simple minded gal?

Thanks!
Jo

try the IDENTITY column. it may not be sequential, but it's unique and it will not reset|||

Quote:

Originally Posted by ck9663

try the IDENTITY column. it may not be sequential, but it's unique and it will not reset

Thank you so much for responding! I did try the Identity column but I dont know how to make it not reset when pulling the stored procedure tomorrow. Should I send you my code? Maybe I shouldnt create table? How can you put an identity column in a SELECT stmt?|||

Quote:

Originally Posted by Joell

Thank you so much for responding! I did try the Identity column but I dont know how to make it not reset when pulling the stored procedure tomorrow. Should I send you my code? Maybe I shouldnt create table? How can you put an identity column in a SELECT stmt?

try sending your code... just those that will be needed|||Hello Again,

Here is my cutdown code. There is a lot more to the Select stmt but I cut it down just to show you around it. I would like to put the end result into a procedure so that I can push it to crystal reports and my end users can then export to Excel. Maybe even schedule the proc to run as a DTS package?

create procedure @.businessunit varchar(30), @.datepulled datetime

as

create table #UPSDaily(
Location_no varchar(20) not null,
OrderNo int Identity(100000,1) not null
)

Insert into #UPSDaily

Select c_id_alpha Location_No,
-- Dont I need a placeholder here somehow for orderno from the creation of the table above?
from cust inner join rxrf on cust.c_id = rxrf.c_id
where rxrf.next_date = getdate()

-- then I need to keep the last value of the OrderNo for the next day's pull of data.

declare @.intCounter int
select @.intCounter = coalesce(max(orderno), 1) from #UPSDaily
declare @.when datetime
set @.when = getDate()

update #UPSDaily
set @.intCounter = OrderNO = @.intCounter + 1

drop table #UPSDaily -- but this removes my orderno value for the beginning of the next day.
--I need the next sequential number to start off the next day's pull of data.

Thanks so much for your help again! - JOELL|||

Quote:

Originally Posted by Joell

Hello Again,

Here is my cutdown code. There is a lot more to the Select stmt but I cut it down just to show you around it. I would like to put the end result into a procedure so that I can push it to crystal reports and my end users can then export to Excel. Maybe even schedule the proc to run as a DTS package?

create procedure @.businessunit varchar(30), @.datepulled datetime

as

create table #UPSDaily(
Location_no varchar(20) not null,
OrderNo int Identity(100000,1) not null
)

Insert into #UPSDaily

Select c_id_alpha Location_No,
-- Dont I need a placeholder here somehow for orderno from the creation of the table above?
from cust inner join rxrf on cust.c_id = rxrf.c_id
where rxrf.next_date = getdate()

-- then I need to keep the last value of the OrderNo for the next day's pull of data.

declare @.intCounter int
select @.intCounter = coalesce(max(orderno), 1) from #UPSDaily
declare @.when datetime
set @.when = getDate()

update #UPSDaily
set @.intCounter = OrderNO = @.intCounter + 1

drop table #UPSDaily -- but this removes my orderno value for the beginning of the next day.
--I need the next sequential number to start off the next day's pull of data.

Thanks so much for your help again! - JOELL

by the looks of this, you're just getting the last OrderNo? coz you're dropping the table anyway...|||

Quote:

Originally Posted by ck9663

by the looks of this, you're just getting the last OrderNo? coz you're dropping the table anyway...

Is there a better way to structure (maybe some kind of while loop) to run my select while grabbing the next incremental value? If so, how do I code that? Its just not working as is. I need a sequential number to restart over each day that the query runs. If I use a table, then doesnt that make the database larger when it is not necessary? All I am trying to do is create a seq number for the work orders each day and not duplicate any number. The seq number needs to be in a column of the select statement that I am running. Does that make better sense than before?

HELP!|||Hi Jo,

do you have a field in the table with todays date in it ?

If you do, select the max date and compare with today, if the date part of today is bigger, reset your counter..

If not then post the table structure for the table and the temp table..

Regards Purple|||

Quote:

Originally Posted by Purple

Hi Jo,

do you have a field in the table with todays date in it ?

If you do, select the max date and compare with today, if the date part of today is bigger, reset your counter..

If not then post the table structure for the table and the temp table..

Regards Purple

I think I had a typo in my question. What I need to have is a counter that does NOT reset each day. So for my last order on 8/27 is 833230, then the first order on 8/28 should be 833231. Does that make better sense? sorry for all of my confusion.

I do not know how to write the code for it.

Help please.

Jo|||Hi Jo,

am I missing something, cant you just use an auto increment field ?

Purple|||

Quote:

Originally Posted by Purple

Hi Jo,

am I missing something, cant you just use an auto increment field ?

Purple

That sounds logical but I dont know how to do that. I have tried the Identity counter but then if you use a temp table, it resets the next time you run the query. I dont want it to reset. I need unique values every time I run the query and to never reset. I also do not want to add tables to my database. I just need to pull existing data and add an sequential number that will not reset each day I run the query.

Isnt there another way? Maybe could you tell me about the auto increment?|||Jo,

Why are you using a temp table ?

Purple|||

Quote:

Originally Posted by Purple

Jo,

Why are you using a temp table ?

Purple

I thought creating a temp table would be better than to have a table created for 11 separate business divisions of work orders that need to be pulled every day and imported into another system. Wouldnt it make the database very large in a small amount of time?

how else could I pull data from one system, attach a sequential number and then import it into another system? system = database|||Hi Jo,

It is often difficult to analyse the problem when somewhat distant from the basic requirements. From what you have described, I think I would look again at the database structure.

Maybe add an int column with a foreign key to a business unit table and have all of the 11 business units work orders in one table,

I have no idea of what you perceive large is, MSSQL will be fine with multiple million rows in a table if it is appropriately indexed and running on a server with enough grunt.

If you use an autoincrement field for the work order number you will automatically get unique incremental work order numbers.

Does this help or have I missed the point ?

Regards Purple|||

Quote:

Originally Posted by Purple

Hi Jo,

It is often difficult to analyse the problem when somewhat distant from the basic requirements. From what you have described, I think I would look again at the database structure.

Maybe add an int column with a foreign key to a business unit table and have all of the 11 business units work orders in one table,

I have no idea of what you perceive large is, MSSQL will be fine with multiple million rows in a table if it is appropriately indexed and running on a server with enough grunt.

If you use an autoincrement field for the work order number you will automatically get unique incremental work order numbers.

Does this help or have I missed the point ?

Regards Purple

You are awesome and I appreciate your help. I am not very versed in SQL lingo and so defining the problem is a bit of a challenge for me.

I am using proprietary software and trying to pull from it into another package without incrementing the values in the proprietary software.

If I create a new table and add a int column, then on day 2, how do I get the counter to NOT reset? My logic is not such that I can remove the previous day's work orders yet so I will be building and building this table adding new work orders every day. Maybe a better question would be if I create a new table on day 1, how do I add to it on day 2, day 3, keeping the counter int going?

Jo|||

Quote:

Originally Posted by Joell

You are awesome and I appreciate your help. I am not very versed in SQL lingo and so defining the problem is a bit of a challenge for me.

I am using proprietary software and trying to pull from it into another package without incrementing the values in the proprietary software.

If I create a new table and add a int column, then on day 2, how do I get the counter to NOT reset? My logic is not such that I can remove the previous day's work orders yet so I will be building and building this table adding new work orders every day. Maybe a better question would be if I create a new table on day 1, how do I add to it on day 2, day 3, keeping the counter int going?

Jo

Jo...

In order to keep this value, you have to store it somewhere. What do you with the daily tables? Do you have a one master table that contain it all? if you do, you can take the max(counter)+1 on that master table as the starting counter on your daily table. for this, you might need a trigger to handle the counter on your daily table. make sure that your counter is the PK on both table to ensure uniqueness.|||Hi Jo,

I suggest you have a master workorder table which holds all of the work orders created for all of the business areas and this a permanent table not a temp table..

When you create the table workOrder add a field as the primary key and set it as an auto increment field, this will be the work order id.

Now when you insert rows into the table the work order id field is automatically incremented by one for every new row. (dont try to set a value for this field on the insert, if you want the numbers to start from a specific value, ie other than 1 specify a seed value)

Also create a field to represent the business unit as an int and use a join to the business unit table where you may have columns like

buId buName buContact etc...

I would also reiterate my suggestion to take some time out of the coding work to reconsider the database structure - Mary (one of the site administrators) has written this article which you may find helpful..

Regards Purple

Createing tables

I am trying to make a customer management database and need some help. I have two tables, customer and equipment. I want to have a unique value that is in both tables such as custid. I feel I need this to be able to search for all equipment belonging to a specific customer. Help please.Not sure what exactly you are asking, your approach of having a key that identified a customer and of using that the identify the equipment sounds like a fine one.|||

If an equipment can only belong to one customer this would be something like this:

Customers
=======

CustomeriD
(OtherColumns)

Equipment
=========
EquipmentD
(OtherColumns)
CustomerID

If one Equipment can belong to more than one customer you have a N-M relation:

Customers
=======
CustomeriD
(OtherColumns)

Equipment
=========
EquipmentD
(OtherColumns)

CustomerEquipment
================
EquipmentD
CustomeriD

HTH, jens Suessmeyer.

http://www.sqlserver2005.de