Function To Check The Existence Of A Temp Table

Jun 13, 2006

This function, F_TEMP_TABLE_EXISTS, checks for the existence of a temp table (## name or # name), and returns a 1 if it exists, and returns a 0 if it doesn't exist.

The script creates the function and tests it. The expected test results are also included.

This was tested with SQL 2000 only.



if objectproperty(object_id('dbo.F_TEMP_TABLE_EXISTS'),'IsScalarFunction') = 1
begin drop function dbo.F_TEMP_TABLE_EXISTS end
go
create function dbo.F_TEMP_TABLE_EXISTS
( @temp_table_name sysname )
returns int
as
/*
Function: F_TEMP_TABLE_EXISTS

Checks for the existence of a temp table
(## name or # name), and returns a 1 if
it exists, and returns a 0 if it doesn't exist.

*/
begin

if exists (
select *
from
tempdb.dbo.sysobjects o
where
o.xtype in ('U')and
o.id = object_id( N'tempdb..'+@temp_table_name )
)
begin return 1 end

return 0

end
go
print 'Create temp tables for testing'
create table #temp (x int)
go
create table ##temp2 (x int)
go
print 'Test if temp tables exist'

select
[Table Exists] = dbo.F_TEMP_TABLE_EXISTS ( NM ),
[Table Name] = NM
from
(
select nm = '#temp' union all
select nm = '##temp2' union all
select nm = '##temp' union all
select nm = '#temp2'
) a


print 'Check if table #temp exists'

if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1
print '#temp exists'
else
print '#temp does not exist'


print 'Check if table ##temp4 exists'
if dbo.F_TEMP_TABLE_EXISTS ( '##temp4' ) = 1
print '##temp4 exists'
else
print '##temp4 does not exist'
go

-- Drop temp tables used for testing,
-- after using function F_TEMP_TABLE_EXISTS
-- to check if they exist.

if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1
begin
print 'drop table #temp'
drop table #temp
end

if dbo.F_TEMP_TABLE_EXISTS ( '##temp2' ) = 1
begin
print 'drop table ##temp2'
drop table ##temp2
end


Test Results:

Create temp tables for testing
Test if temp tables exist
Table Exists Table Name
------------ ----------
1 #temp
1 ##temp2
0 ##temp
0 #temp2

(4 row(s) affected)

Check if table #temp exists
#temp exists
Check if table ##temp4 exists
##temp4 does not exist
drop table #temp
drop table ##temp2



CODO ERGO SUM

View 1 Replies


ADVERTISEMENT

Existence Of Index For Temp Table

Oct 4, 2007



Hi!
SS2005:

create table #tmp(a int)

create index idxt1 on #tmp(a)

insert into #tmp values (42)

select * from sys.indexes

where name like '%idx%'

order by name

But I can't see any rows about idxt1 :-(

If I say

create index idxt1 on #tmp(a)

againg I got error, because it exists.

How to check for existence using query?

B. D. Jensen

View 3 Replies View Related

Checking For Existence Of A Temp Table Before Droping It

Dec 31, 2003

I'm familiar with how to check for the existence of a table before dropping it using the following command:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[xxx]

How does one check for the existence of a temp table (using # syntax) before dropping it? I've tried various flavors of this command and none work. One flavor is

use tempdb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#xxx]

CREATE TABLE #xxx (
NumID INTEGER IDENTITY(1,1),
Exhibitor_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL
)

Thanks.

Nick

View 2 Replies View Related

How To Check The Existence Of A Column In A Table

Jun 23, 2007

Dear All,



I wanted to know how do I know or check that whether a column exists in a table. What function or method should I use to find out that a column has already exists in a table.



When I run a T-SQL script which i have written does not work. Here is how I have written:

IF Object_ID('ColumnA') IS NOT NULL
ALTER TABLE [dbo].[Table1] DROP COLUMN [ColumnA]
GO



I badly need some help.

View 9 Replies View Related

How To Check For Table Existence Before Dropping It?

May 8, 2006

Apologies if this has been answered before, but the "Search" function doesn't seem to be working on these forums the last couple of days.

I'd just like to check if a table already exists before dropping it so that I can avoid an error if it doesn't exist. Doing a web search, I've tried along the lines of
"If (object_id(sensor_stream) is not null) drop table sensor_stream"
and
"If exists (select * from sensor_stream) drop table sensor_stream"

In both of these cases I get the error: "There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = if ]"

Sooooo... what is the standard way to check for existence of a table before dropping it? Someone help? This seems like it should be simple, but I can't figure it out.

Thanks in advance!
Dana

View 7 Replies View Related

Check The Field Existence Of A Database Table

Jun 5, 2007

Check the field existence of a database table, if exist get the type, size, decimal ..etc attributes
I need SP
 SP
(
@Tablename varchar(30),
@Fieldname varchar(30),
@existance char(1) OUTPUT,
@field_type varchar(30) OUTPUT,
@field_size int OUTPUT,
@field_decimal int OUTPUT
)
as
/* Below check the existance of a @Fieldname in given @Tablename */
/* And set the OUTPUT variables */
 
 
 
Thanks

View 4 Replies View Related

How To Check Existance Of Temp Table

Sep 10, 2004

Within the execution of a t-sql script how do I check for the existance of a temporary table associated with the session or a global temporary table?

My understanding is that the table name doesn't get placed in the current database sysobjects table - it goes into tempdb. But the object name is cryptic (so as to be unique) and I see no way of associating it with the current session (@@SPID).

Thanks,
Fred

View 4 Replies View Related

How To Check If Employees Have Full Month - If Not Move To Temp Table

Jan 10, 2008

need help
how to check in table if all the employees have Full month


if it double days fix it

if the employees have less days ! > move to temp table

for eny problem with this employee (check continuity or error) move to temp table !
i have stored procedure that insert employees for next month "Full month"
from start of the month until end of the month
how to check continuity and if i don't give less days OR double days in month
like if the month is 29 days the employee must have 29 not more

for eny problem with this employee move to temp table !

situation 1 employees have less days !
sn empid ShiftDate day
-------------------------------------------------
1 111111 2008-02-01 Tuesday
2 111111 2008-03-02 Wednesday
3 111111 2008-04-03 Thursday
4 111111 2008-03-04 Friday
5 111111 2008-03-05 Saturday
6 111111 2008-03-06 Sunday
7 111111 2008-03-07 Monday
. ----------------------------------------------
8 111111 2008-03-09 Wednesday
9 111111 2008-03-10 Thursday
.......................................................................until end of the month
99 222222 2008-02-01 Tuesday
100 222222 2008-03-02 Wednesday
101 222222 2008-04-03 Thursday
102 222222 2008-03-04 Friday
. ----------------------------------------------
104 222222 2008-03-06 Sunday
105 222222 2008-03-07 Monday
106 22222 2008-03-09 Wednesday
108 22222 2008-03-09 Wednesday
109 22222 2008-03-10 Thursday
.......................................................................until end of the month

situation 2 employee have double days!
sn empid ShiftDate day
-------------------------------------------------
1 111111 2008-02-01 Tuesday
2 111111 2008-03-02 Wednesday
3 111111 2008-04-03 Thursday
4 111111 2008-03-04 Friday
5 111111 2008-03-05 Saturday
6 111111 2008-03-06 Sunday
7 111111 2008-03-07 Monday
8 111111 2008-03-09 Wednesday
9 111111 2008-03-09 Wednesday
10 111111 2008-03-10 Thursday
.......................................................................until end of the month
99 222222 2008-02-01 Tuesday
100 222222 2008-03-02 Wednesday
101 222222 2008-04-03 Thursday
102 222222 2008-03-04 Friday
103 222222 2008-03-04 Friday
104 222222 2008-03-05 Saturday
105 222222 2008-03-06 Sunday
106 222222 2008-03-07 Monday
107 22222 2008-03-09 Wednesday
108 22222 2008-03-09 Wednesday
109 22222 2008-03-10 Thursday
.......................................................................until end of the month

TNX

View 3 Replies View Related

Transact SQL :: Check For Existence Before Inserting

Nov 3, 2015

I have a webpage where users can connect with other users by sending them a request. Sending the request just puts a row in a connect table that contains the users id, the targetusers id, a requesteddate, an accepted date and a disconnectdate (for the original requester to end the connection and a reject bit the the target can reject the request. Hoever I need to check to assure that the connect does nt already exist either as pending (requestdate is not null and accept date is null) or both dates are not null (connection already complete.).

ALTER PROCEDURE [dbo].[requestConnect]
-- Add the parameters for the stored procedure here
@requestor int,
@requested int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

[Code] ....

View 4 Replies View Related

Query Analyser Does Not Check For Object Existence

Nov 27, 2000

we tried out the following code in query analyser -

create procedure TrialProc

as

select * from sakjdhf


when we executed this piece of TSQL in query analyser, we expected it to give an error or warning that no object by the name of sakjdhf exists ( as actually there is no such table or view in the database ). however to our surprise we got "command completed successfully " !!

does this mean the SQL server does not check for necessary objects when creating a stored procedure ? or is there some setting that we missed out whihch is causing SQL server to overlook the error in the code ?

View 3 Replies View Related

Transact SQL :: How To Check For Existence Of Environment Variable

Oct 8, 2015

I'm trying to figure out the best way to write a script to deploy environment variables to different servers. To create the variable I'm currently using  catalog. create_environment_variable. I want to wrap that in an if not exist statement.I had thought about just blowing away all the variables and recreating them but I thought that wouldn't go over well in prod. I wasn't sure if by deleting the variable, references to the variable would be lost.

View 3 Replies View Related

How To Check For A File Existence In A Folder In SSIS

Oct 26, 2007

I have a data flow task inside a foreach loop container which will take multiple excel files and load into a sql table. Once all the excel files are loaded into the table, then at the end one of the column gets updated using execute sql task. Now I am trying to check for a file existence, if the file is not present in the folder then the data flow task should not be executed. Any help is greatly appreciated, I am thinking of using file system task for this, but not exactly sure. Thanks in advance.

View 13 Replies View Related

Function Vs Temp Table Calcs Performance

Feb 11, 2004

I need to know what is the best performance for needing to do calculations for a particular column. I want to do something like:


Select IID
, ItemNo
, StdRun
, ActRun
, dbo.fnCalc(OutCount)
From myTable


The function is basically a set of Case Statements and various calculations dependant upon the Case.

Is this the best (performance wise) way to do it or should I dump the needed info in a Temp Table and do the calcs on it and then tie the select statement to the table.

I've seen both approaches done, but they both seem to be a different way of getting to the same conclusion. I'm just wondering which puts the lightest load on the server.

Thanks,
Tim

View 2 Replies View Related

Check If A Table Is Used In Any Sp Or Function

Jul 13, 2006

Hello, there,I am trying to find out if a table is used by any of the storedprocedures or functions.I can generate all the scripts and look for it. But is there an easyway?THXJohn

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

Existence Of A Value In Table

Sep 24, 2007



Hi,

I have a table with the follwing values

Table Cats
{

CatID, date
-----------------------
Cat1, D1
Cat2, D2
Cat3, D3
}

I just wanted to check whether Cat1 exists in the table. Can anyone post the query
Thanks
~MOhan

View 6 Replies View Related

Point Of Information - Checking For Existence Of A Table In Another Database...

Feb 22, 2005

Hi all,

While cleaning up some code, I ran across the following statement in a stored proc - the purpose of which is to determine if a table exists in the local database: SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[XML_PRINTDATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1of course I removed it from the IF just for testing purposes, but my quandry is this...
Why chose that select (converting table name to object ID) rather than just doing THIS:SELECT * FROM dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1

I first thought it was to gain access to the "id" column value (and that may yet be the purpose of it), but the second code seems to work just peachy (I assume because the id column is present in the sysobjects table itself).

A follow-on question is this:
When I try to do the same check from another server (i.e.SELECT * FROM APRECEIVE1.DailyProd.dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1) it of course fails because OBJECTPROPERTY only looks for the id on the local database.

So, do I CARE if it is a user table? (I am reasonably sure it is, of course) and if so, is there a way to check on the remote server for the object type?

Bottom line is I Think I can just simplify things and check for the object name on the remote server, but just don't want to take away any "warm fuzzy feeling" generated by the original stored proc, if such a warm fuzzy is of any benefit (though don't get me started on the relativity of warm fuzzies, I wrote my Thesis on that ;) )

View 9 Replies View Related

Integration Services :: Create A Table Based On Existence Dynamically

Aug 10, 2015

I am using the following script to check existence of table in the Database and create it dynamically...

This is working when table not existed, it error-ed when the table existed...

This script i am using in the Exec Sql Task.....

[Execute SQL Task] Error: Executing the query "declare @ODSDB varchar(50)
declare @SQLSTMT varcha..." failed with the following error: "There is already an object named 'addressTable' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not
set correctly, or connection not established correctly.

declare @ODSDB varchar(50)
declare @SQLSTMT varchar(max)
set @ODSDB = 'SampleDB'
begin
set @SQLSTMT = '
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + @ODSDB + '.dbo.addressTable'') and Type=''U'')

[Code] ...........

View 8 Replies View Related

Add Temp Parameters To A Function

May 4, 2006

I am writing a set of functions and then a stored procedure to allow me to view some data in Reporting Services. I have written the first part of the function as shown ;

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[fnWTRTerrierData]

(@vch_site_ref nvarchar (3), @dt_src_date datetime)

RETURNS @WeeklyTerrierRSPI TABLE



(Areacode varchar(2),siteref nvarchar(3),

estatename nvarchar(100), Securitised nvarchar(255),

unitref nvarchar(15), unittype nvarchar(30),

unittype_count int, tenantname nvarchar(100),

tenantstatus nvarchar(25), tenantstatus_count int,

unitstatus nvarchar(15), unitstatus_count int,

floortotal float, floortotocc float,

initialvacarea float, initialvacnet float,

TotalRent float, NetRent float,

FinalRtLsincSC float, DiscEndDate datetime,

ErvTot float, Leaseterm int,

leasestart datetime, rentreview nvarchar(255),

leaseend datetime, breakclause datetime,

tenancyterm datetime, landact nvarchar(255),

datadate datetime)

AS

BEGIN

INSERT @WeeklyTerrierRSPI



SELECT Areacode, siteref, estatename, Securitised, unitref, unittype, unittype_count, tenantname,

tenantstatus, tenantstatus_count, unitstatus, unitstatus_count, floortotal, floortotocc,

initialvacarea, initialvacnet, TotalRent, NetRent, FinalRtLsincSC, DiscEndDate, ErvTot,

Leaseterm, leasestart, rentreview, leaseend, breakclause, tenancyterm, landact, datadate

FROM dbo.src_terrier

WHERE (datadate = @dt_src_date) AND (siteref = @vch_site_ref)



RETURN

END

I have then written a stored procedure which picks up the result set from the function and ultimately will deliver it to Reporting Services. The problem I have is that as soon as I run the CREATE PROCEDURE script, I get an error saying ;

Msg 216, Level 16, State 1, Procedure spWTRWeeklyTerrierData, Line 16

Parameters were not supplied for the function 'fnWTRTerrierData'.

How can I add parameters to the function sufficently so that I can run the Create procedure element of my code?

Regards

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

Is There A Sql Database Function To Check Connection String?

Nov 1, 2005

Im working on a db library and I have everything working, what Im wanting to do is though is add a method that can check a connection string to make sure it is actually working.  Right now, I have it doing a simple select * query to a particular table and returning true or false if an exception is caused.  But I want to make the library as generic as possible, so is there another way  to test teh connection string and tell if its working or not?Thanks,Cedric

View 1 Replies View Related

SQL Server 2008 :: Function To Check Name Format (Last / First / Middle)

May 27, 2015

I have to figure out the items that Legal Name implies individual but Legal Entity Structure indicates a incorporation type. In this sample, you can see Alexander, Justin N. is my target. But my problem is how should I use a query to figure out which one is a individual's name? How should I write a function to check the name format (Last, First Middle)?

Legal Name ////////////////////////////////////// Legal_Entity_Struct

S & H Farm Supply, Ltd.////////////////////////////Company
F.M.Abbott Power Equipment,Co.///////////////Company
Ray's Dixie Chopper, Inc.////////////////////////// Company
Alexander, Justin N. ///////////////////////////////// Company
Alameda Power Equipment, Inc.//////////////// Company

[Code] .....

View 0 Replies View Related

Insert Stored Procedure With Error Check And Transaction Function

Jul 21, 2004

Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?

thanks

My contract table DDL:
************************************************** ***

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);


My insert stored procedure is:
************************************************** *****

create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char](4),
@contactId[int])
as

if not exists (select studentid
from student
where studentid = @studentId)
begin
print 'studentid is not a valid id'
return -1
end

if not exists (select contactId
from contact
where contactId = @contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction

insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)

/*Error Check */
if @@error !=0 or @@rowcount !=1
begin
rollback transaction
print ‘Insert is failed’
return -1
end
print ’New contract has been added’

commit transaction
return 0
go

View 1 Replies View Related

A T-SQL Function To Check Column Level Data For Special Charaters

Nov 30, 2007

I need to create a function that will check data inputted by a user into a column anc check for special charaters. If any of these exist then block the insert.

Any help will be appreciated!

Paul

View 10 Replies View Related

Delete Record Based On Existence Of Another Record In Same Table?

Jul 20, 2005

Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray

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







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