Drop A Temp Table

Sep 26, 2007

Using SQL Server 2005. I have a stored prod that uses a temp table. I need to test at the start of the prod to see if the temp table is there. Using the following code at the start of my prod, but does not run.

IF exists(select * from ##TO_STATUS_TBL)
DROP TABLE ##TO_STATUS_TBL

What is the best way to check and/or drop the temp table.
Thank you, David

View 10 Replies


ADVERTISEMENT

Drop Existing Temp Table

Nov 7, 2007

hello!! i am using a couple of temp tables for a select statement.
i need to drop the tables only if they exist before using them, because if i don't drop, then i will get this error:

There is already an object named '#Tmp01' in the database.

and if i try to drop the tables for the first time i run the query, then i will get an error saying that i cannot drop a table that doesn't exist.

i have tried using this sentence:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = '#Tmp01') DROP TABLE #Tmp01
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = '#Tmp02') DROP TABLE #Tmp02
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = '#Tmp03') DROP TABLE #Tmp03

but it only seems to work with normal tables, since temp tables are not found in sysobjects.

any suggestions??


thnx

View 7 Replies View Related

DROP Temp Table Or Any Scripts

May 17, 2007

Hi,



What control can I use to put any scripts like that?



ExecuteSQLTask lets me do just sql query.



I need to have something like this.



USE [DB1]

GO



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Lookup]') AND type in (N'U'))

DROP TABLE [dbo].[Lookup]





or Create



CREATE TABLE [Lookup] (
[MD_ID_Old] INTEGER,
[MD_ID_New] uniqueidentifier
)



I need to find the way to use scripts in SSIS.



Thanks.

View 3 Replies View Related

Transact SQL :: Drop A Temp Table If It Exists?

Jan 21, 2010

What is the best way to drop a temp table if it exists? I am looking something similar to this: if exists (select top 1 * from #TableName) then drop #TableName else end

View 5 Replies View Related

Drop ##temp

Nov 8, 2006

due to unavoidable reasons i had to use a ## temp table in a SP,

ie i had to dynamically create a table whose (number of)columns i come to know at runtime..

if i do thi ::set @sql = 'create #table....select some columns _ append varchar(10)'

then insert into #temp....temp is not valid here..so i used ##temp

now i need to explicitly drop it...also in catch block , i need to make a provision for droping it incase of an error in runnin proc...some kind os IF EXISTS drop ##temp.... as i dont know if it'll be created by that time or not..how do i do it..there is ofcource no entry in sysobjects....where is the entry for temp tables...tempdb dosent have system tables..!!

View 12 Replies View Related

Looping To Drop Temp Tables

Sep 10, 2007

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @n as nvarchar(3), @table as nvarchar(10)

set @n = 1
while @n <= 5 begin

set @table = '#temp'+@n

if object_id('tempdb..#temp'+@n) is not null drop table @table

set @n = @n + 1
end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@table'.


I have also tried:
declare @n as nvarchar(3), @dropstmt as nvarchar(25)

set @n = 1
while @n <= 5 begin

set @dropstmt = 'drop table #temp'+@n

if object_id('tempdb..#temp'+@n) is not null @dropstmt

set @n = @n + 1
end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@dropstmt'.

Does anyone know how to get this to work?

Thanks.

View 8 Replies View Related

T-SQL (SS2K8) :: Moving Values From Temp Table To Another Temp Table?

Apr 9, 2014

Below are my temp tables

--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL

[Code] ....

I want to loop through the data from #Base_Resource and do the follwing logic.

1. get the Resourcekey from #Base_Resource and insert into #Resource table

2. Get the SCOPE_IDENTITY(),value and insert into to

#Resource_Trans table's column(StringId,value)

I am able to do this using while loop. Is there any way to avoid the while loop to make this work?

View 2 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

Temp Table Vs Global Temp Table

Jun 24, 1999

I think this is a very simple question, however, I don't know the
answer. What is the difference between a regular Temp table
and a Global Temp table? I need to create a temp table within
an sp that all users will use. I want the table recreated each
time someone accesses the sp, though, because some of the
same info may need to be inserted and I don't want any PK errors.

thanks!!
Toni Eibner

View 2 Replies View Related

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

SQL Server 2012 :: Stored Procedures Compiles Even When There Is No CREATE TABLE For A Temp Table

Feb 11, 2015

i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?

--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END

only on calling the proc does this give an execution error

View 3 Replies View Related

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

Jul 19, 2012

I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).

Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32

Simple example:
    declare @tTable(col1 int)
    insert into @tTable(col1) values (1)
    select * from @tTable

Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.

But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.

Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

View 11 Replies View Related

INSERT INTO - Data Is Not Inserted - Using #temp Table To Populate Actual Table

Jul 20, 2005

Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO

View 2 Replies View Related

SQL Tools :: Adding Column To A Table Causes Copying Data Into Temp Table

Sep 23, 2015

If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.

Example of the script is below: in the source project I added columns [MyColumn_LINE_1]  and [MyColumn_LINE_5].

Is there any way I can make it generating an alter statement instead?

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_MyTable] (
[MyColumn_TYPE_CODE] CHAR (3) NOT NULL,

[Code] ....

The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.

View 7 Replies View Related

Transact SQL :: Update Table With Its Value And Data From Row In Temp Table For Matching Record?

Oct 25, 2015

I have a temp table like this

CREATE TABLE #Temp
 (
  ID int,
  Source varchar(50),
  Date datetime,
  CID varchar(50),
  Segments int,
  Air_Date datetime,

[code]....

Getting Error

Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.

View 4 Replies View Related

Column Name Or Number Of Supplied Values Does Not Match Table Definition When Trying To Populate Temp Table

Jun 6, 2005

Hello,

I am receiving the following error:

Column name or number of supplied values does not match table definition

I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:

SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp

INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'

The SP UserPersist_GetByCriteria does a
"SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture",
so why am I receiving this error when both tables have the same
structure?

The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.

Thanks,
Greg.

View 2 Replies View Related

Transact SQL :: Table Structure - Inserting Data From Other Temp Table

Aug 14, 2015

Below is my table structure. And I am inserting data from other temp table.

CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,

[Code] ....

Now in a next step I am deleting the records from #revf table. Please see the delete code below

DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2

[Code] ...

I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:

SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],

[Code] ....

If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.

View 5 Replies View Related

Update Temp Table With Stored Procedure Joined With Table

Sep 8, 2006

Hello

Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?

insert #MyTempTable

exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3

JOIN dbo.OtherTable...

I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.

I just can't figure it out

Many Thanks!

Worf

View 2 Replies View Related

Is A Temp Table Or A Table Variable Used In UDF's Returning A Table?

Sep 17, 2007

In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
 

View 1 Replies View Related

Copying Temp Table Data To Permanent Table

Nov 23, 2007

Hello guys..

Can u plz help me by giving me an idea how i can copy the temp table data to permanent table

Thanks,
sohails

View 1 Replies View Related

Difference In Performance Between Temp-table And Local-table?

Jan 23, 2008

Hi!

What is the difference in performance if I use a Temp-table or a local-table variable in a storedprocedure?

Why?


//Daniel

View 5 Replies View Related

Transact SQL :: Insert Data From Temp Table To Other Table

Oct 5, 2015

I want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary  (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.

Actual Data
Expected Result

1000
1-Aug
1000
1-Feb
1000
1-Jul
1000

[code]....

View 3 Replies View Related

Rename Table After Loading Data Into Temp Table

Dec 19, 2007

WE have a job that loads data from an Oralce DB into our SQL Server 2000 DB twice a day. The schedule has just changed so that now there is a possibility of having my west coast users impacted when it runs at 5 PM PST and my east coast users impacted when it runs at 7 AM EST. As a workaround, I have developed a DTS package that loads the data into temp tables instead of the real tables. IE. Oracle -> XTable_temp instead of Oracle -> XTable. The load sometimes takes about an hour to an hour and a half to load, so this solution works great, but I want to then lock the table, delete it and rename the temp table to table X. The pseudo code would be:

Begin Transaction


Lock Table XTable

Drop XTable

Alter Table XTable_temp rename to XTable

Release Lock XTable

End Transaction

Create XTable_temp

I see two issues with this solution. 1) I think if I can lock XTable that the lock would be released when the table is dropped and the XTable_temp was being renamed. 2) I can't find a command to rename a table.

Any ideas on a process that might help?


TIA,

A

View 5 Replies View Related

Trigger- Dump 'inserted' Table To Temp Table

Jul 11, 2006

I want to pass the 'inserted' table from a trigger into an SP, I think I need to do this by dumping inserted table into a temporary table and passing the temp table. However, I need to do this for many tables, and don't want to list all the column names for each table/trigger (maintenance nightmare).

Can I dump the 'inserted' table to a temp table WITHOUT specifying the column names?

View 16 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

Global Temp Table Vs. Permanent Table Use

Dec 17, 2004

I need to decide what is better to use: global temp table ( I can't use local one) or permanent table in SQL 2000 stored procedures. I extract data from linked server table and update several tables on our server.
Those procedures scheduled to run every 3 hours.

Another question: for some reasons when I used global temp table, I wasn't able to schedule multi steps with every step executing one of the stored procedures.I think global temp tables should be visible to other stored procedures, right?

Your suggestions?

View 1 Replies View Related

Data From Temp Table Into Regular Table.

Feb 21, 2007

Hi everyone, I'm fairly new to sql and right now I am struggling with a script. I am trying to extract data from a normal table into a temporary table, update it in the temporary table, then put it back into the normal table. I'll display my code, let me know what you think, any suggestions are appreciated. Thanks a lot.


Create table scripts (
UserID int,
UserName char(50),
ScrRan char(50),
StartTime datetime default getdate(),
EndTime datetime);

Create table errors (
ID int,
UserName char(50),
UserLogin char(50),
ErrorNumber int,
Message char(100),
TimeOfError datetime default getdate());

declare @error int
declare @msg varchar(100)
declare @startTime datetime
declare @endTime datetime

select @startTime = getDate()

SELECT *
INTO #Temp
FROM Publisher
WHERE pub_Name = 'Scene Publishing'

UPDATE #Temp
SET pub_Name = UPPER(pub_Name)


SELECT *
INTO Publisher
FROM #Temp


--Begins Error Checking Routine
select @error = @@error

IF @error <> 0
BEGIN
select @msg ='error: ' + convert(varchar(7), @error) +
''
insert into errors values (@@SPID, USER, USER_NAME(), @error,
@msg, getDate())
END
ELSE
BEGIN
select @endTime = getDate()

insert into scripts values (@@SPID, SYSTEM_USER, @startTime, @endTime)
END
select * from errors
select * from scripts




lost and loaded.

View 2 Replies View Related

Advantage Of Derived Table Over Temp Table

Jan 22, 2008

Hi, I wanna know is there any advantage of perf gain when using Derived Tables over Temp Tables, advice me which one is better to use. Can I create Indexes and Insert/Update records into Derived Tables.


-Senthil

View 8 Replies View Related

The Age Old Argument Of Temp Table Vs Table Variable

Jan 26, 2007

Hi All,Hope someone can help me...Im trying to highlight the advantages of using table variables asapposed to temp tables within single scope.My manager seems to believe that table variables are not advantageousbecause they reside in memory.He also seems to believe that temp tables do not use memory...Does anyone know how SQL server could read data from a temp tablewithout passing the data contained therein through memory???Is this a valid advantage/disadvantage of table variables VS temptables?

View 2 Replies View Related

Table Variable Versus #Temp Table

Jan 17, 2008

In a previous post "Could #TempTable within SP cause lock on tempdb?" http://forums.microsoft.com/msdn/showpost.aspx?postid=2691763&siteid=1

It was obvious that we have to limit the use of #Temp table to a minimum. Let assume that some of the temp tables are really difficult to replace and we have to live with them.

Would it be easier on tempdb if the #TempTable is replaced by a table variable? Or do they all end up in tempdb?

Thanks in advance for any help.

View 4 Replies View Related

Updating A Table With Data From A Temp Table

Oct 19, 2007

I am trying to update a table in one database with data from a temporary table which i created in the tempdb.

I want to update field1 in the table with the tempfield1 from the #temp_table

The code looks something like this:

Use master
UPDATE [dbname].dbo.table
SET [dbname].dbo.table.field1 = [tempdb].dbo.#temp_table.tempfield1
WHERE ( [dbname].dbo.table.field2= [tempdb].dbo.#temp_table.tempfield2
AND [dbname].dbo.table.field3= [tempdb].dbo.#temp_table.tempfield3
AND [dbname].dbo.table.field4= [tempdb].dbo.#temp_table.tempfield4)

I get the following error:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield2" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield3" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield4" could not be bound.

What is wrong?

View 1 Replies View Related

Query Oracle Table In SQL &&amp; Put In Temp Table

Nov 6, 2006

Hi all,

I am querying a table in oracle, the server connection to the Oracle database is determined by a criteria. Though how can I put the results from the oracle query into a temp table ?

This is the code i'm using for the query:

DECLARE @cmd VARCHAR(500)
declare @Year varchar(25)
set @Year = '2006'

DECLARE @Link VARCHAR(100)
DECLARE @Table VARCHAR(100)

select @Link = Server from tbl_Conn where Area='Floor'
select @Table = Target_Table from tbl_Conn where Area='Floor'

SET @cmd =
'
select * from OPENQUERY
(
' + @Link + ',
''
UPDATE '+ @Table +'
SET TARGET_VALUE = '+@Value+'
WHERE Date = '+@Year'
''
)
'
EXEC (@cmd)

How do I put the executed results into a TEMP table ?

Rgds,

View 2 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







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