Drop Table Select Into

Jan 28, 2008

I'm confused as to how this sp is working? Can you drop a table and then select into it without recreating it? I thought when you dropped a table it was gone.

sp:
drop table tmp_claimeligiblebalance

SELECT tsd_Claim.clpid,
Sum(tsd_Claim.cloutstandingamt) as sumoutstanding
INTO tmp_ClaimEligibleBalance

FROM tsd_Claim

GROUP BY tsd_Claim.clpid
HAVING sum(tsd_Claim.cloutstandingamt) <>0 AND sum(tsd_Claim.cloutstandingamt) IS NOT NULL
And max(tsd_Claim.clfromdos) < (cast(getdate() as smalldatetime) - 120) And max(tsd_Claim.clins) Is Not Null

View 2 Replies


ADVERTISEMENT

Default Table Owner Using CREATE TABLE, INSERT, SELECT && DROP TABLE

Nov 21, 2006

For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.

View 6 Replies View Related

DROP TABLE, SELECT INTO, Performance

Jul 11, 2007

Hello -- thank you for taking the time to read this.

I have a very large table that is used both for archives and new information. To get the current information, the table is queried by many different users at various polling periods. The SELECT required includes about fifteen JOINS, and only returns about 200 rows at any given time.

So I got to thinking if it might be faster to periodically run the big query as a SELECT INTO into a smaller table and letting the polling clients query the smaller table with SELECT *. Periodically, the smaller table would be DROPPED and refereshed with another SELECT INTO.

Trouble is, the data would have to be updated once every 30 seconds, and there are inbound polls coming at the rate of about 200 per minute. It got me to thinking what might happen if a client attemtped to query the smaller table when it was in the process of being dropped and refilled.

So my question is three-part:

1) assuming a larger table of about 500,000 records and only 500 pertinent at any given time, is there any real potential of performance enhancement by switching to a SELECT INTO table?

2) if so, is there a chance of a client failing a query if the inbound query somehow collides with the DROP/SELECT INTO procedure?

3) if so, is there any way to prevent it or a better way of doing this?

Thanks again for reading, and in advance for any help you can provide. I apologize if I sound like a dummy - it's hard to fake intelligence!

View 3 Replies View Related

Drop All Indexes In A Table, How To Drop All For User Tables In Database

Oct 9, 2006

Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob

View 2 Replies View Related

(Select All) In Multi-select Enabled Drop Down Parameters Doesn't Work

Apr 29, 2008

Hello all,
I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.

select distinct ProductType
from Product
order by ProductType

Any suggestion? thx


View 12 Replies View Related

(Select All) In Multi-select Enabled Drop Down Parameters

Feb 27, 2008

There are several parameters on a report. One of the parameter is a multi-select enabled parameter and I suppressed the value "All" showing as one of the item in the drop down list, simply by filter out the [bha].[bha].CURRENTMEMBER.LEVEL.ORDINAL to 1, as "(Select All)" is pre-assigned to the drop list when multi-select is enabled and it is confusing to show "(Select All)" and "All" in the drop list. However I have another report which is linked to this report and the value which is required to pass to this report for this parameter is "All". Can I pass the "Select All" as a parameter from the other report? If so, how? Thanks.

View 1 Replies View Related

Multi-select Drop Down Width

Feb 25, 2008



Hi,
Has the inability to change the width of the multi-select drop down for parameters been fixed yet? Even if I could at least make it default to be wider would be great.
Thanks.

View 1 Replies View Related

How To Form SQL Select Queries Using Drop Down Lists??

Nov 8, 2006

Hi
 Will somebody please explain how  to combine asp.net dropdown lists to write
a  SQL database select query. I am using VWdeveloper and C Sharp.
For example, say I have  3 dropdownlists on my  webpage  as below,
List 1, Cities, London, Rome,  Barcelona etc
List 2, Restaurants by  Type, Italian, chinese, Indian etc
List 3, Number of tables/ seats 10-20,  20- 40, 50  -100
I want someone to be able to  search for a restaurant by selecting  an  item from  each dropdownlist
such as, "Barcelona" "Italian" "50-100"
This search query would return all the Italian restaurants in Barcelona with  50-100  tables/seats.
I  would also like the select query to work even if one of the dropdownlists items  is not selected.
Hope  somebody can clear this up?
Also would sql injection attacks be a threat by doing it this way?
Thanks all
 
 
 

View 9 Replies View Related

SSRS Drop Down - Allow User To Select Value To Appear In A Report

Jul 30, 2013

I am really new to SSRS (This is my first report) and I am looking to create a drop down that will allow a user to select the value to appear in a report. Meaning that is my values are:

a
b
c
d

If the user types a B it would "jump to that letter in the selection.

View 1 Replies View Related

Delete Or Drop Table Then Create Table

Mar 14, 2007

which one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.

View 7 Replies View Related

Is There A Method To Convert Select * From Table To Select Field1,field2,...fieldn From Table ?

Nov 29, 2007

Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
 
Thanks

View 1 Replies View Related

SQL Server 2012 :: Select Statement That Take Upper Table And Select Lower Table

Jul 31, 2014

I need to write a select statement that take the upper table and select the lower table.

View 3 Replies View Related

Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query

Oct 15, 2007

Hello,

I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.

I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.

This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.

So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?

Thanks in advance,
Andy



Select * from

(

declare @years table (years int);

insert into @years

select

CASE

WHEN month(getdate()) in (1) THEN year(getdate())-1

WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())

END

select

u.fullname

, sum(tx.Dm_Time) LastMonthBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-1

and

year(dm_date) = (select years from @years)

and tx.dm_billable = 1

group by u.fullname

) as A

left outer join

(select

u.FullName

, sum(tx.Dm_Time) Billhours

, ((sum(tx.Dm_Time))

/

((day(getdate()) * ((5.0)/(7.0))) * 8)) perc

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

tx.Dm_Billable = '1'

and

month(tx.Dm_Date) = month(GetDate())

and

year(tx.Dm_Date) = year(GetDate())

group by u.fullname) as B

on

A.Fullname = B.Fullname

Left Outer Join

(

select

u.fullname

, sum(tx.Dm_Time) TwomosagoBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-2

group by u.fullname

) as C

on

A.Fullname = C.Fullname

View 1 Replies View Related

Could Not Drop Table!

Jun 23, 2006

Hello !


I try to drop a table, but I get this message :

Could not drop object 'dbo.cs_Users' because it is referenced by a FOREIGN KEY constraint

This message does not tell me which is the Foreign key constraint so that I may disable it.

Any solution to force the dropping?

Thank you very much!

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

View 3 Replies View Related

Drop Table

Dec 29, 2006

hi, is it possible to recover the dropped table?
if means please tell me how.

View 3 Replies View Related

Drop Table Into??

Jan 14, 2008

hi everyone,

I have a couple questions. I'm very new to SQL and I have this problem:
I need to be able to drop the contents of Existingtable_B into Newtable_A - I found this command (below) that will make a 'copy' but I don't want to keep the contents of Existingtable_B. Is it possible to drop them into Newtable_A instead of copy? Also, I want to do this for 5 tables on Sundays at midnight.. how could I schedule that? Finally, what happens if there is not enough space or some other critical error happens during this procedure? I don't want to lose the data.

Any help is greatly appreciated.

View 4 Replies View Related

Why I Can't Drop A Table

Dec 12, 2005

Hi,When I drop a table in Sqlserver 2000 database, The following error occurs:Server: Msg 1204, Level 19, State 1, Line 1The SQL Server cannot obtain a LOCK resource at this time. Rerun yourstatement when there are fewer active users or ask the system administratorto check the SQL Server lock and memory configuration.What's wrong? Any help is greatly appreciated, thanks.

View 4 Replies View Related

Drop All Table

Jul 20, 2005

Hi everybody,I need some help in SQL Server. I am looking for a command that will "Dropall user table" in auser database.Can anyone help me?Thank you very muchSabrina

View 1 Replies View Related

Table Won't DROP

Jan 20, 2006

I am unable to get the table in the following VB code to actually DROP. A straight SQL version with literals (no variables) runs "successfully" as a query but the table also fails to DROP. Can anybody explain what I'm doing wrong?

  sSQL = ""
  sSQLExists = ""
  sSQLExists = "IF OBJECT_ID (N'ImEx.dbo." & TableName & "', N'U') IS NOT NULL"
  sSQLExists = sSQLExists & " DROP TABLE ImEx.dbo." & TableName & ";"
 
  sSQL = "CREATE TABLE [" & TableName & "] (" & sFieldInfo & ");"
  
  ConnectSQLExpress "ImEx.mdf"
  DbConn.Execute sSQLExists
  DbConn.Execute sSQL
  DbConn.Close

View 3 Replies View Related

Drop And Re-create Table

Jul 27, 2006

 
What kind of problems may I see if a process drop and re-create a set of tables every night?

View 1 Replies View Related

DROP TABLE If It Exists

Feb 9, 2008

 i am using vb.net and ms sql server 2005 express.....what is the syntax for dropping a table if existsi have used this but it says incorrect syntax near if Dim cmda As New SqlCommand("drop table " + test + " if exists", New SqlConnection(strdb)) cmda.Connection.Open()        cmda.ExecuteNonQuery()       cmda.Connection.Close()any solutions???? plz only answer in vb.net and sql server express

View 8 Replies View Related

Cannot Drop Temporary Table

Apr 30, 2008

I'm trying to drop a temporary table.  I keep getting this error:
Cannot drop the table '#temp_table', because it does not exist in the system catalog.
I tried the following but it did not work, so help would be appreciated.
if object_id('tempdb..#temp_table') is not nulldrop table #temp_table

View 1 Replies View Related

Replication - Cannot Drop Table

Mar 15, 2001

I need some help with Merge Replication. After successfully defining a publisher, distributor and subscriber to perform merge replication, we decided to test Merge Replication to see what we can and can't do.

What we found is that you can't add new fields or change the nullable/null attributes of tables whilst the replication settings are still defined to the databases. ie you get the following error

'tblProducts' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tblProducts' because it is published for replication.

So we thought, fair enough. We decided to uninstall replication using the wizards provided to see whether we can make schema changes. What we found is that we can make schema changes to the publisher database(on Server1), BUT not to the subscriber (Server2).

Does anyone know why? We are running SQL7 SP2.
Email me on ftowicz@icontact.com.au

View 4 Replies View Related

Table And View Will Not Drop

Jun 13, 2001

We have a table created by an application, and a view that joins the table with other tables.

For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.

However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.

Anyone know what could be the problem here?

View 2 Replies View Related

Drop A Column In A Table!!

Aug 2, 2001

Hi all,
how can I drop a column in a table.

View 1 Replies View Related

Drop And Create FK On A Table - 6.5

Oct 19, 2001

Hello,

What's the command for dropping an existing Foreign key on a table and reacreating it in SQL Server 6.5

Thanks
Sri

View 1 Replies View Related

Publication Cannot Drop Table.

Nov 13, 2000

We have a publication from database a to database b. Database a containts table1 to be published to databasae b. Database b contains a publication of table1 to database c. When we go to rerun the publication from database a it errors saying cannot drop table1 since it is part of database b's publication. how do you do this.

Thanks in Advance,
Phillip M. Triocli

View 1 Replies View Related

Drop-create Table

Mar 17, 2000

Hi,

I had a question and did not obtain an answer. So I am trying to rephrase and ask again, in case I was not clear the first time.

When we drop and recreate a table, do we always have to recompile the stored procedures that reference the table? Or is only under certain scenarios that we need to do so- like if an index on the table is changed
Please let me know
Thanks in advance
Kiran

View 1 Replies View Related

Drop Table Permissions

Jul 12, 2000

I have a group that has select, insert, update, delete permissions and I have a user in that group that
needs permissions also to drop tables.

does that login need to be aliased to dbo - which is in public -- do I then to give puboic all the other permissions.

Is there a way to just give one login all permissions including dropping tables??

Help!!

View 1 Replies View Related

DELETE FROM Vs. DROP TABLE

Jan 30, 2008

I want to get rid of a huge table. DELETE FROM would take for-freaking-ever, so I was thinking of doing DROP TABLE. Would this be any faster?

If not, would doing a TRUNCATE TABLE and then DROP TABLE be fast? What's the best way to nuke a big table?

Thanks.

View 4 Replies View Related

Drop A Table If I Exists

Feb 18, 2004

in mysql i can drop a table or db if it currently exists using

drop table if exists [table1] or
drop database if exists [db1]

is there an equalivant in ms sql


thanks

View 4 Replies View Related

Drop #Temporary Table

Jul 21, 2004

I created the #Temporary table in MS SQL. Now i want to drop this #Temporary table, but i want to check first before i drop the this table. How to check the Temporary table exist or not in MS SQL?

View 4 Replies View Related

How To Drop Db And Create New Table

Feb 20, 2005

Hi all,

I have 2 questions:
1. How can I drop /remove a complete db from MSDE desktop engine?
2. After exporting my db onto MSDE server and get connected those db tables in FE (adp), how can I create a new table in the same back end db?

I know these are basic questions but since I am new to MSDE I hope the forum would bear me.

With kind regards,
Ashfaque

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved