INSERT INTO - Data Is Not Inserted - Using #temp Table To Populate Actual Table
Hi there
Application : Access v2K/SQL 2K
Jest : Using sproc to append records into SQL table
Jest sproc :
1.Can have more than 1 record - so using ';' to separate each line
from 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 line
BEGIN TRAN <---------- skips
rest
INSERT INTO timesheet.dbo.table1
4.Checked permissions for table + sproc - ok
What 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 RECOMPILE
AS
SET NOCOUNT ON
DECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)
DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)
DECLARE @TimesheetCount int, @TimesheetCount1 int
DECLARE @TS_LastEdit smalldatetime
DECLARE @Last_Editby smalldatetime
DECLARE @User_Confirm bit
DECLARE @User_Confirm_Date smalldatetime
DECLARE @DetailCount int
DECLARE @Error int
/* Validate input parameters. Assume success. */
SELECT @RetCode = 1, @RetMsg = ''
IF @TimesheetDetails IS NULL
SELECT @RetCode = 0,
@RetMsg = @RetMsg +
'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)
/* Create a temp table parse out each Timesheet detail from input
parameter string,
count number of detail records and create SQL statement to
insert 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=0
WHILE LEN( @TimesheetDetails) > 1
BEGIN
SELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,
Charindex(';', @TimesheetDetails) -1) + ')'
EXEC(@SQLComplete)
SELECT @TimesheetCount = @TimesheetCount + 1
SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(
@TimesheetDetails)-Charindex(';', @TimesheetDetails))
END
IF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCount
SELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Details
couldn''t be saved.' + CHAR(13) + CHAR(10)
-- If validation failed, exit proc
IF @RetCode = 0
RETURN
-- If validation ok, continue
SELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +
CHAR(10)
/* RETURN*/
-- Start transaction by inserting into Timesheet table
BEGIN TRAN
INSERT INTO timesheet.dbo.table1
select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mber
FROM #tmpTimesheetDetails
-- Check if insert succeeded. If so, get ID.
IF @@ROWCOUNT = 1
SELECT @TimesheetID = @@IDENTITY
ELSE
SELECT @TimesheetID = 0,
@RetCode = 0,
@RetMsg = 'Insertion of new Timesheet failed.'
-- If order is not inserted, rollback and exit
IF @RetCode = 0
BEGIN
ROLLBACK TRAN
-- RETURN
END
--RETURN
SELECT @Error =@@error
print ''
print "The value of @error is " + convert (varchar, @error)
return
GO
View Complete Forum Thread with Replies
Related Forum Messages:
Trigger- Dump 'inserted' Table To Temp Table
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 Replies !
&"Column Name Or Number Of Supplied Values Does Not Match Table Definition&" When Trying To Populate Temp Table
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 Replies !
Newbie How To Create Temp Table And Populate
Sorry guys I know this is easy but I've been looking for about an hour for a straight forward explanation. I want to store a user's wish list while they browse the site, then they can send me an enquiry populated with their choices. Basically, a shopping cart! I thought of using session variables and string manipulations but I am more comfortable with DB queries. a simple 4 column table would cover everything. SQL server and VBScript Thanks M
View Replies !
Loop Through A Recordset To Populate Columns In A Temp Table
I want to take the contents from a table of appointments and insert theappointments for any given month into a temp table where all theappointments for each day are inserted into a single row with a columnfor each day of the month.Is there a simple way to do this?I have a recordset that looks like:SELECTa.Date,a.Client --contents: Joe, Frank, Fred, Pete, OscarFROMdbo.tblAppointments aWHEREa.date between ...(first and last day of the selected month)What I want to do is to create a temp table that has 31 columnsto hold appointments and insert into each column any appointments forthe date...CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2nvarchar(500), Day3 nvarchar(500), etc...)Then loop through the recordset above to insert into Day1, Day 2, Day3,etc. all the appointments for that day, with multiple appointmentsseparated by a comma.INSERT INTO#Appointments(Day1)SELECTa.ClientFROMdbo.tblAppointments aWHEREa.date = (...first day of the month)(LOOP to Day31)The results would look likeDay1 Day2 Day3 ...Row1 Joe, PeteFrank,FredMaybe there's an even better way to handle this sort of situation?Thanks,lq
View Replies !
How To Insert Derived Table To Temp Table
HI, I am executing some statment as below in this. exec('with cart as (SELECT Row_number() over(order by '+@SortCondition +') as rowindex, c.ContentID,convert(varchar(100),c.ModifiedOn,101) as ModifiedOn, c.Name, UnitCost as SalePrice,c.Currency,isnull(DiscountedPrice,0) as DiscountedPrice, isnull(SubscriptionOnly,0) as SubscriptionOnly from tbl_Content c ' + @JoinCondition + ')' + ' select * from cart where rowindex between cast('+ @StartIndex + ' as int) and cast('+ @StartIndex + ' as int) + cast(' + @NoOfRecords + ' as int) ' + '-1 order by '+@SortCondition) --------------------------------------------------------------- in the above statment(which is part of SP) passing @SortCondition ,which may come as title,SalePrice,DatePublish from dotnet code. In the sp there are multiple statment depending upon some condition we are creating @JoinCondition.So in One case the Joining conidition will not have SalePrice as Origionl colum but derived column.I have to use the above statment and handle in Sale Price condition becuase in saleprice case it is alias column name. How can i do that. or the last statment I but in one more table if @SortCondition='salePrice asc/Sale price desc' with ordering. Plz help me out
View Replies !
Rename Table After Loading Data Into Temp Table
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 Replies !
Updating A Table With Data From A Temp Table
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 Replies !
Data From Temp Table Into Regular Table.
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 Replies !
Insert Into Temp Table
Hi, I am trying to insert into temp table multiple times and then pull everything out. How would I do that? I get records back, but everything is 0. Why? Here is my stored procedure. CREATE PROCEDURE sp_SummaryReport ( @startdate datetime, @enddate datetime ) AS BEGIN SET NOCOUNT ON CREATE TABLE #CalcTemp (DataID bigint IDENTITY(1,1) NOT FOR REPLICATION, ReportType varchar(2), Volume int, NetEffect decimal(10,1), GrossEffect decimal(10,1), WeekEndDate datetime) DECLARE @OnTime decimal(10,1) DECLARE @UnControlled decimal(10,1) DECLARE @Volume int DECLARE @GrossEffect decimal(10,1) DECLARE @NetEffect decimal(10,1) DECLARE @WeekEndDate datetime --ARS AA SET @OnTime = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AA') SET @UnControlled = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AA') SET @Volume = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ARSScanType = 'D' AND ARSType='AA') SET @GrossEffect = ((@OnTime/@Volume) * 100) SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100) SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblARSData) INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate) VALUES ('AA', @Volume, @NetEffect, @GrossEffect, @WeekEndDate) --ARS AN SET @OnTime = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AN') SET @UnControlled = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AN') SET @Volume = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ARSScanType = 'D' AND ARSType='AN') SET @GrossEffect = ((@OnTime/@Volume) * 100) SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100) SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblARSData) INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate) VALUES ('AN', @Volume, @NetEffect, @GrossEffect, @WeekEndDate) --ARS AC SET @OnTime = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AC') SET @UnControlled = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AC') SET @Volume = (SELECT COUNT(DataID) FROM tblARSData WHERE (tblARSData.WeekEndDate BETWEEN @startdate AND @enddate) AND ARSScanType = 'D' AND ARSType='AC') SET @GrossEffect = ((@OnTime/@Volume) * 100) SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100) SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblARSData) INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate) VALUES ('AC', @Volume, @NetEffect, @GrossEffect, @WeekEndDate) --General SET @OnTime = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ReportType = 'GN' AND ScanType='D') SET @UnControlled = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ReportType = 'GN' AND ScanType='D') SET @Volume = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND ReportType = 'GN' AND ScanType='D') SET @GrossEffect = ((@OnTime/@Volume) * 100) SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100) SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblShipData) INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate) VALUES ('GN', @Volume, @NetEffect, @GrossEffect, @WeekEndDate) --Odessey SET @OnTime = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ReportType = 'OD' AND ScanType='D') SET @UnControlled = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ReportType = 'OD' AND ScanType='D') SET @Volume = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND ReportType = 'OD' AND ScanType='D') SET @GrossEffect = ((@OnTime/@Volume) * 100) SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100) SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblShipData) INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate) VALUES ('OD', @Volume, @NetEffect, @GrossEffect, @WeekEndDate) --General SET @OnTime = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND OnTimeFlag = 1 AND ReportType = 'HU' AND ScanType='D') SET @UnControlled = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND ControlFlag = 'U' AND ReportType = 'HU' AND ScanType='D') SET @Volume = (SELECT COUNT(DataID) FROM tblShipData WHERE (tblShipData.WeekEndDate BETWEEN @startdate AND @enddate) AND ReportType = 'HU' AND ScanType='D') SET @GrossEffect = ((@OnTime/@Volume) * 100) SET @NetEffect = (((@OnTime + @UnControlled)/@Volume) * 100) SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblShipData) INSERT INTO #CalcTemp(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate) VALUES ('HU', @Volume, @NetEffect, @GrossEffect, @WeekEndDate) SELECTtblListReportType.ReportType AS 'Report Type', tblListReportType.ReportID AS ReportID, SUM(#CalcTemp.Volume) AS Volume, CAST(SUM(#CalcTemp.NetEffect)/COUNT(#CalcTemp.DataID) as decimal(10,1)) AS 'Net % Effective', CAST(SUM(#CalcTemp.GrossEffect)/COUNT(#CalcTemp.DataID) as decimal(10,1)) AS 'Gross % Effective' FROM#CalcTemp INNER JOIN tblListReportType ON LTRIM(RTRIM(LOWER(#CalcTemp.ReportType))) = LTRIM(RTRIM(LOWER(tblListReportType.ReportAbv))) GROUP BYtblListReportType.ReportType, tblListReportType.ReportID END GO
View Replies !
How To Insert Into Temp Table
i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table. MemberID + Month + Year should ne unique in Temp table
View Replies !
How Do I Insert Into Existing Temp Table?
Hi, How do I insert data into an existing temporary table? Note: I’m primarily a .NET programmer who has to do T-SQL to grab data from time to time. What I am trying to do is this: 1) Put the scores for all the people who have completed a questionnaire into a temporary table called #GroupConfidence. 2) Add on a row at the end that gives an average for each score (ie the last row is an average of the column above). I need my SP to give me a DataSet that I can throw straight to my .NET reporting engine (I don’t want to do any number crunching inside .NET) - that's why I want to add on the 'average' row at the end. If I do this (below) the temporary table (#GroupConfidence) gets created and the values inserted. -- Insert the results into the #GroupConfidence table SELECTRTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName', RP.SubmitID, RP.GL_Score, RP.GP_Score, RP.GPH_Score, RP.DL_Score, RP.MP_Score, RP.Role_MI_Score, RP.Role_ASXRE_Score, RP.Role_APRA_Score, RP.Overall_Score AS 'AllCategories' INTO#GroupConfidence FROMRodResultPercentages RP JOIN#UsersCompleted UC ON UC.SubmitID = RP.SubmitID My problem is that #GroupConfidence already exists so in fact I have this code below: CREATE TABLE #GroupConfidence ([FullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [SubmitID] [int] NOT NULL, [GL_Score] [decimal](19, 10) NOT NULL, [GP_Score] [decimal](19, 10) NOT NULL, [GPH_Score] [decimal](19, 10) NOT NULL, [DL_Score] [decimal](19, 10) NOT NULL, [MP_Score] [decimal](19, 10) NOT NULL, [Role_MI_Score] [decimal](19, 10) NOT NULL, [Role_ASXRE_Score] [decimal](19, 10) NOT NULL, [Role_APRA_Score] [decimal](19, 10) NOT NULL, [AllCategories] [decimal](19, 10) NOT NULL ) -- Insert the results into the #GroupConfidence table SELECTRTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName', RP.SubmitID, RP.GL_Score, RP.GP_Score, RP.GPH_Score, RP.DL_Score, RP.MP_Score, RP.Role_MI_Score, RP.Role_ASXRE_Score, RP.Role_APRA_Score, RP.Overall_Score AS 'AllCategories' INTO#GroupConfidence FROMRodResultPercentages RP JOIN#UsersCompleted UC ON UC.SubmitID = RP.SubmitID So I get this error: Server: Msg 2714, Level 16, State 1, Line 109 There is already an object named '#GroupConfidence' in the database. Thanks in advance, Ian.
View Replies !
Insert Temp Table Problem
Hi Everyone, I insert data to #temp table by using 'select into'. Then I insert one extra row. when I select data, why it does not follow the order? (the last insert should be in the last row but it becomes the first row) Here is the simple script Select name,code,dates into #temp From member Order By Dates Insert #temp (name,code,dates) select 'dave', '0', getdate() select * from #temp
View Replies !
How Do I Populate A Table With Data On Setup
Hi Guys, My ASP.Net app is multilingual and all my translations are stored in seperate MSDE tables, for example, tblEN for English, tblES for Spanish and tblTH for Thai. When I send the installation files to my clients, I get them to double click on 4 MS DOS batch files that use OSQL to run 4 scripts..... 1. DataBase&Tables.sql - creates the database and tables 2. Logins&Users.sql 3. StoredProcedures.sql 4. Permissions.sql This worked great before my language table came along to spoil the party and I now need some way of getting the data stored in the language on my server into the table on the client. If I export the data to a text file and then send it out with the rest of the installation files - what are my options for transferring the data into the table ? Any suggestions appreciated. Steve.
View Replies !
Insert Unique Rows In Temp Table
i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table. MemberID + Month + Year should ne unique in Temp table
View Replies !
Insert Into Temp Table Based On If Condition
hello all,this might be simple:I populate a temp table based on a condition from another table:select @condition = condition from table1 where id=1 [this will giveme either 0 or 1]in my stored procedure I want to do this:if @condition = 0beginselect * into #tmp_tablefrom products pinner joinsales s on p.p_data = s.p_dataendelsebeginselect * into #tmp_tablefrom products pleft joinsales s on p.p_data = s.p_dataendTha above query would not work since SQL thinks I am trying to use thesame temp table twice.As you can see the major thing that gets effected with the condictionbeing 0/1 is the join (inner or outer). The actual SQL is much biggerwith other joins but the only thing changing in the 2 sql's is the joinbetween products and sales tables.any ideas gurus on how to use different sql's into temp table based onthe condition?thanksadi
View Replies !
INSERT INTO TEMP TABLE NOT WORKING IN SQL SERVER 7.
Hi I have the following Stored Proc which works in SQL Server 6.5 but not in SQL Server 7.0. All this Stored Proc does is Create a temp table, execute the DBCC ShowContig on a table and insert the results of the DBCC into a temp table. What am I missing. Thanks. The code of the Stored Proc is: /* This Stored Procedure Creates a temp table. (Step 1) */ /* Initializes a local variable @StirngToBeExecuted with */ /* a DBCC command. (Step 2) */ /* Step 3. The Command is Executed and the results of the */ /* DBCC command is inserted into Temp Table. */ /* Step 4. The results of the Temp table are shown on the */ /* Screen. */ /* This SQL Works Fine in SQL Server Version 6.5 */ /* In SQL Server 7.0 the results of the DBCC command is */ /* NOT getting inserted into the Temp table. WHY??? */ IF EXISTS (SELECT * from sysobjects where id = object_id('dbo.Test_sp') and sysstat & 0xf = 4) drop procedure dbo.Test_sp GO CREATE PROCEDURE Test_sp AS DECLARE @StirngToBeExecuted Varchar(100) CREATE TABLE #temp( -- Step 1 OutputOfExecute Varchar(255) ) -- Step 2 SELECT @StirngToBeExecuted = 'DBCC SHOWCONTIG (123456789)' INSERT INTO #temp exec (@StirngToBeExecuted) -- Step 3 SELECT * FROM #temp -- Step 4 DROP TABLE #temp --Drop the Temp Table
View Replies !
Help With Data Binding (passing Parameters To SQL Query And Retrieving Data To Populate Table)
I am working on building a data access website. The database has 12 tables (2001exp, 2001imp, 2002exp, 2002imp, 2003exp, 2003imp, 2004exp, 2004imp, 2005exp, 2005imp, 2006exp, 2006imp). All the tables have same no. of columns and names. (Commodity, Country, Month, Quantity, Value)The webpage has 4 dropdown menus (as shown in the image)Type [Imp, Exp]Year [2001, 2002, 2003, 2004, 2005, 2006]Month [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]Country [ list of 228]The inputs Type and Year combined tell me which table the data will be retrevied from i.e., if I select imp and 2003, the data will be retrevied from 2003imp. And the inputs month and year will be parameters for Where condition in the Query.I don't know how to combine (concatinate) the first 2 inputs and pass on to the SQL query.The output of the table should be just a table with top 10 values.I am rookie to programming and I would appreciate if anyone can help me. (I am using Visual Studio 2005 and SQL Server 2005) The SQL query is (I am passing 3 parameters: tablename, country, month)Declare @SQuery nvarchar(4000)DECLARE @TblName varchar(100)Declare @Country Int, @Month IntSet @Country=5310Set @Month=12SET @TblName = '[2005exp]'SELECT @SQuery = 'select top 10 a.commodity as Commodity, c.descrip_1 as Description,c.quantity_1 as Unit, sum(a.all_qy1_mo) as Quantity, sum(a.all_val_mo) as [Value],CASE WHEN (select sum(b.all_val_mo) from ' + @TblName + ' b where a.commodity=b.commodity)<>0 THEN (sum(a.all_val_mo)/(select sum(b.all_val_mo) from ' + @TblName + ' b where a.commodity=b.commodity))*100.00 ELSE NULL END as [U.S.Share(Value) %],CASE WHEN sum(a.all_qy1_mo)<> 0 THEN sum(a.all_val_mo)/sum(a.all_qy1_mo) ELSE NULL END as [Average Price]from ' + @TblName + ' a inner join concord c on a.commodity=c.commodityWHERE a.cty_code= ' + convert(varchar(10), @Country) +' and a.stat_month= ' + convert(varchar(10), @Month) +'GROUP BY a.commodity, c.descrip_1, c.quantity_1order by [Value] desc'EXEC sp_executesql @SQuery
View Replies !
Select From Multiple Tables, Insert In Temp Table
What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data? Any ideas? Thanks in advance.
View Replies !
SQL 2005 Select Into Temp Table Then Insert Causes Null Issue
Here is the scenario that I cannot resolve CREATE TABLE [dbo].[tEvents]( [EventID] [int] IDENTITY(1,1) NOT NULL, [EventName] [varchar](1000) NOT NULL, CONSTRAINT [PK_tEvent] PRIMARY KEY CLUSTERED ( [EventID] ASC )WITH FILLFACTOR = 90 ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[tEventSelections]( [EventSelectionID] [int] IDENTITY(1,1) NOT NULL, [EventID] [int] NOT NULL, [StatusPID] [int] NOT NULL, CONSTRAINT [PK_tEventSelections] PRIMARY KEY CLUSTERED ( [EventSelectionID] ASC )WITH FILLFACTOR = 90 ON [PRIMARY] ) ON [PRIMARY] then try this SELECT e.eventName, es.statuspid INTO #tmpTable FROM tEventSelections ES INNER JOIN tEvents E ON E.EVentID = ES.EventID INSERT INTO #tmpTable (eventName) values ('Another One') DROP TABLE #tmpTable this causes a null insert issue (0 row(s) affected) Msg 515, Level 16, State 2, Line 7 Cannot insert the value NULL into column 'statuspid', table 'tempdb.dbo.#tmpTable___________________________________________________________________________________________________________000000000130'; column does not allow nulls. INSERT fails. The statement has been terminated. So how do I allow the null, as the not null is coming from the ES table. But I want to allow the insert to happen without having to create the table first, this code works in SQL 2000 but fails in 2005, inserting all fileds into the insert also has it's own issues as some of the fields are delibertly left blank so in some circumstances the data returned to a grid displays correctly. This method has been used in quite a lot of stored procedures and will be a nightmare to correct if each has to be edited. One example of the use of is to return a dataset and then add a row at the bottom which is a sum of all the rows. Regards
View Replies !
Incorrect Data Is Inserted Into The SQL Table From OLEDB Command
I have an OLEDB command in a package that inserts the data into two tables. When I run the package, the data is getting inserted as divided by 100 of original data for derived columns. For example: Say col1 is my input column from flat file with value 1000. I am dividing it by 100 in Derived column and then inserting into the table. So the value 100 should be inserted into the table. But it is not so, the value 1 is getting inserted. Two Tables involved here have referential integrity constraints between them. SQL Script for the operation I am doing looks like the one just below INSERT INTO PrimaryKeyTable(ID, FirstName) VALUES (?,?) If @@rowcount=1 BEGIN DECLARE @MaxID as int SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable --AutoIncremented column INSERT INTO [ForeignKeyTable] ( [MaxID] ,[Cost] ,[MarkDownDollars] ,[VersionCode] ) VALUES(@MaxID,?,?,'act') END But this script did not work in OLEDB commandL So I wrote the below script for which I am facing the problem mentioned INSERT INTO PrimaryKeyTable(ID, FirstName) VALUES (?,?) If @@rowcount=1 BEGIN DECLARE @Cost AS money SET @Cost=? DECLARE @MarkDownDollars AS money SET @MarkDownDollars=? DECLARE @MaxID as int SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable INSERT INTO [ForeignKeyTable] ( [MaxID] ,[Cost] ,[MarkDownDollars] ,[VersionCode] ) VALUES(@MaxID,@Cost,@MarkDownDollars,'act') END
View Replies !
SSMS Express: Create TABLE && INSERT Data Into Table - Error Msgs 102 && 156
Hi all, I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise. Thanks in advance, Scott Chang ///////////--SQLQueryCroomLabData.sql--/////////////////////////// USE MyDatabase GO CREATE TABLE dbo.LabResults (SampleID int PRIMARY KEY NOT NULL, SampleName varchar(25) NOT NULL, AnalyteName varchar(25) NOT NULL, Concentration decimal(6.2) NULL) GO --Inserting data into a table INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration) VALUES (1, 'MW2', 'Acetone', 1.00) VALUES (2, 'MW2', 'Dichloroethene', 1.00) VALUES (3, 'MW2', 'Trichloroethene', 20.00) VALUES (4, 'MW2', 'Chloroform', 1.00) VALUES (5, 'MW2', 'Methylene Chloride', 1.00) VALUES (6, 'MW6S', 'Acetone', 1.00) VALUES (7, 'MW6S', 'Dichloroethene', 1.00) VALUES (8, 'MW6S', 'Trichloroethene', 1.00) VALUES (9, 'MW6S', 'Chloroform', 1.00) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00 VALUES (11, 'MW7', 'Acetone', 1.00) VALUES (12, 'MW7', 'Dichloroethene', 1.00) VALUES (13, 'MW7', 'Trichloroethene', 1.00) VALUES (14, 'MW7', 'Chloroform', 1.00) VALUES (15, 'MW7', 'Methylene Chloride', 1.00 VALUES (16, 'TripBlank', 'Acetone', 1.00) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00) VALUES (19, 'TripBlank', 'Chloroform', 0.76) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51) GO //////////Parse/////////// Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '6.2'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'VALUES'. ////////////////Execute//////////////////// Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '6.2'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'VALUES'.
View Replies !
URI Key Held In Seperate Table To Automatically Populate PK In Another Table.
Hi, I have a table for which I wish to keep the index€™s off in a separate table. The trouble being that the database has to keep legacy support for a bunch of Access Interfaces that use linked tables to the database. CREATE TABLE [dbo].[Tb_Students]( [student_uri] [int] NOT NULL, [student_ref] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL, [fname] [nvarchar](18) COLLATE Latin1_General_CI_AS NOT NULL, [sname] [nvarchar](25) COLLATE Latin1_General_CI_AS NOT NULL ) GO CREATE TABLE [dbo].[Ti_Student_Uri]( [next_uri] [int] NOT NULL ) ON [PRIMARY] GO I also have a stored procedure for getting the next URI due: CREATE PROCEDURE [dbo].[sp_get_next_Student_uri] -- Add the parameters for the stored procedure here (@uri integer output, @jump integer= 1) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRANSACTION -- Insert statements for procedure here SELECT @uri = next_uri FROM Ti_Student_Uri UPDATE Ti_Student_Uri set next_uri=next_uri+@jump WHERE next_uri = @uri if @@rowcount <> 1 begin exec sp_get_next_Student_uri @uri output, @jump end commit transaction END Now as you can guess this is fine from my C# interface as this automatically grabs the next key from the URI table and populates the new tuple in Tb_Students. The access interface could do the same but I would rather not edit this client so I was wondering if what would be the best way to automatically insert the next key on a new record? If it helps the access interface uses a view to access the Tb_Students and other tables.
View Replies !
Translating Data Into Temp Table
Hi all I have this snippet of data hierId nodeId nodeName parentId childId ----------- ----------- -------------------------------------- ----------- ----------- 437 1582275 Accountants 1582270 1582275 437 1582276 Asset financiers 1582270 1582276 437 1582270 Banking & financial institutions NULL NULL 437 1582286 Private banks 1582277 1582286 437 1582277 Banks 1582270 1582277 I want to loop through this data and put the results into a temp table so it looks like this: hierId nodeName ids ------------------------------------------------------------------------------------------------------ 437 Banking & financial institutions -> Accountants1582270 | 1582275 437Banking & financial institutions -> Asset financiers1582270 | 1582276 437Banking & financial institutions -> Banks1582270 | 1582277 437Banking & financial institutions -> Banks -> Private banks1582270 | 1582277 | 1582286 437Banking & financial institutions1582270 Can anyone help me with achieving this Thanks in advance
View Replies !
How To Use Temp Table With Vb Data Control
I have a temp table created via ADO in vb6, & want to set the record source of an ADO Data control to point to this file. I have set the record string for the data control, at design time, to point to tempdb & at run time set the record source to the temp table: Me.Adodc1.CommandType = adCmdText strSql = "select * from #GridData " Me.Adodc1.RecordSource = strSql when I define this as the source for a data grid: Set Me.TDBGrid1.DataSource = Me.Adodc1 I get the error: Invalid object name '#GridData' Any advice out there on how I should be doing this? (I have had this working for permanent files) Colin
View Replies !
Select Data From #temp Table In SQL
I am building a dynamic query stored procedure. I am first filling a temp table with data: Declare @Counter int drop table #tempmerge create table #tempmerge(IDIndex int IDENTITY, CitationNum char(9),Exp1 int) insert into #tempmerge Select E_Cit_For_Merge, Count(*) as Exp1 from dbo.E_Citation_XML_Data group by E_Cit_For_Merge having Count(*)>1 select * from #tempmerge Results returned from #tempmerge table: IDIndex CitationNum Exp1 ----------- ----------- ----------- 1 4AA020621 2 2 4AA022361 2 3 4AA022391 2 4 4AA022423 2 5 4AA022532 3 6 4AA027761 2 7 4AA030513 2 Then, I want to use a while loop, looping thru the #tempmerge table and retrieving the CitationNum value of each row: set @RowCount = (Select Count(*) from #tempmerge) set @Counter = 1 While @Counter <= @RowCount Begin Set @WhereStatement2 = ' where E_Cit_For_Merge= (Select CitationNum from #tempmerge where IDIndex = @Counter)' E_Cit_For_Merge is a field in a SQL table. I Declare @Counter as int. I get the Error message that: FROM E_Citation_XML_Data where E_Cit_For_Merge= (Select CitationNum from #tempmerge where IDIndex = @Counter) Server: Msg 137, Level 15, State 2, Line 24 Must declare the variable '@Counter'. Any Suggestions? Thanks JEB
View Replies !
Temp Table In Data Flow
is it possible to retrieve data from a #temp table in flow control task? or create a temp table perhaps? or what if i create a table in the control flow using sql execute task and inside the data flow access that table, is that possible?
View Replies !
Temp Table Loses Data
Not sure if you can help on this but Ive got a stored procedure in sql server and it creates a temp table. I then call another stored procedure from this one. When it returns to the 1st stored procedure I want the temp table to keep the information entered into the table, but the data is lost. Is there a flag that can be turned on and off do this? Or can you suggest anything else Regards Steve Steve Fouracre
View Replies !
Stored Procedure For Insert Data From One Table To Another Table
Hi, I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table. Overall it is like storing the history of the table updation. How do i write a stored procedure for this? Anybody has done this before? Pls help me.
View Replies !
Is Having A Trigger That Inserts A Row In Table 'A', When A Row In Same Table Is Inserted By ADo.Net Code?
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
View Replies !
Selecting Data From A Temp Table In A SP -- Very Urgent
Hello. I have a SP. I am passing two variables to it that are comma seperated lists (ids) so that I can use an IN clause. But since I cannot use these CSV in an IN clause directly I wrote a temp table that first parses the CS list and creates a temp table with a single column of IDS.Then in a second temp table I am selecting the data using enrollment_id IN (Select enrollment_id From #temptable)Then the SP selects data from the second temp table.This works exactly the way it is supposed to work. But when I call the SP from ASP.NET it doesn't return any data. I just cant seem to return data by selecting from a temp table. And my ASP.NET code is correct because I commented out everything and just returned the parameters I was passing and that worked. So does anyone know how to select data from a temp table in a SP when executed from ASP.NET?I even tried Table Variables and same thing. It works great in SQL but when called from ASP.NET it doesnt return any data. I am guessing it's probably losing its scope. Any quick help would be appreciated. Thank you.Here is a snippet of the SP (even this fails):CREATE PROCEDURE [dbo].[returnEnrollments](@organization_id int, @test_item_list ntext, @enrollment_list ntext)As--select @test_item_list as tilist, @enrollment_list as elistDeclare @report_start_date datetime;Declare @report_end_date datetime;Declare @test_item_id_list varchar(8000);Declare @enrollment_id_list varchar(8000);DECLARE @TestItemID varchar(10), @Pos intDECLARE @EnrollmentID varchar(10)--Set @IDList = @test_item_id_list;--Set @EnrollmentIDList = @enrollment_id_list;Set @test_item_id_list = Cast(@test_item_list As varchar(8000))Set @enrollment_id_list = Cast(@enrollment_list As varchar(8000))Select @report_start_date = report_start_date, @report_end_date = report_end_date From organization Where organization_id = @organization_id;-- Create a temp table to store all the enrollment idsIF OBJECT_ID('tempdb..#EnrollTemp') IS NOT NULLDROP TABLE #EnrollTempCREATE TABLE #EnrollTemp (enrollment_id int NOT NULL)SET @enrollment_id_list = LTRIM(RTRIM(@enrollment_id_list))+ ','SET @Pos = CHARINDEX(',', @enrollment_id_list, 1)IF REPLACE(@enrollment_id_list, ',', '') <> ''BEGIN WHILE @Pos > 0 BEGIN SET @EnrollmentID = LTRIM(RTRIM(LEFT(@enrollment_id_list, @Pos - 1))) IF @EnrollmentID <> '' And @EnrollmentID > 0 BEGIN INSERT INTO #EnrollTemp (enrollment_id) VALUES (CAST(@EnrollmentID AS int)) --Use Appropriate conversion END SET @enrollment_id_list = RIGHT(@enrollment_id_list, LEN(@enrollment_id_list) - @Pos) SET @Pos = CHARINDEX(',', @enrollment_id_list, 1) ENDENDSelect top 20 * From #EnrollTemp Edit: Just used a DataReader instead of a datatable and it works. Dont know why it would fail with a datatable.
View Replies !
How Can Data Flow Destination Be A Temp Table?
I have a series of data flow tasks that I want to output to a temp table. I've set the data source for RetainSameConnection and the Data Flows are DelayValidation. The OLE DB data source inside the Data Flow works fine, but the data destinations don't offer a # or ## as a target. I've tried every destination that sounds logical, without success. Any pointers? ... Thanks!
View Replies !
Storing Data In Temp Table During Runtime
hi all i am displaying some information in a datagrid as part of shopping cart.i want to store the informations which i displayed and some informations which the user enters in a temporary table, so that i can perform manipulations based on that. how can i do that? how to load those informations into a temporary table during runtime can anyone explain me in detail? please thanks in advance i am using sqlserver2000 vb.net2003
View Replies !
Insert And Reformat Data - Table A To Table B
Hello, I have a table called #table1 which is populated as in the example below. I would like to write a selectinsert statement based on #table1 that populates #table2 like in the #table2 example. Note #table2 is a fixed table that follows the structure below. Can any of you T-SQL gurus help me with my problem? Any help will be most appreciated. Thanks --------------------------------------------- /* Please paste T-SQL into query window */ --------------------------------------------- CREATE TABLE #table1 --max of 5 orders ( custID nvarchar(6), dateorder [datetime] NULL, order1 nvarchar(2), order2 nvarchar(2), order3 nvarchar(2), order4 nvarchar(2), order5 nvarchar(2) ) GO SET ANSI_PADDING OFF Insert into #table1 select '012345','2008-04-19 00:00:00.000' , '01', '06', '05', null, null UNION all select '012345','2008-04-20 00:00:00.000' , '01', '07', '05', '07', '03' UNION all select '012345','2008-04-21 00:00:00.000' , '01', '06', null, null, null UNION all select '012345','2008-04-22 00:00:00.000' , '01', '02', '05', '07', null UNION all select '012345','2008-04-23 00:00:00.000' , '03', '06', null, null, null UNION all select '987654','2008-04-21 00:00:00.000' , '19', '21', null, null, null UNION all select '987654','2008-04-22 00:00:00.000' , '01', '02', '05', '16', null UNION all select '987654','2008-04-23 00:00:00.000' , '03', '06', null, null, null select * from #table1 --This is the table i would like to insert my data into CREATE TABLE #table2 --max of 5 orders in 1 day --it does not matter what date the date order was made the 1st date would appear in dateorder1 and so on... ( custID nvarchar(6), dateorder1 [datetime] NULL, order1_1 nvarchar(2), order1_2 nvarchar(2), order1_3 nvarchar(2), order1_4 nvarchar(2), order1_5 nvarchar(2), dateorder2 [datetime] NULL, order2_1 nvarchar(2), order2_2 nvarchar(2), order2_3 nvarchar(2), order2_4 nvarchar(2), order2_5 nvarchar(2), dateorder3 [datetime] NULL, order3_1 nvarchar(2), order3_2 nvarchar(2), order3_3 nvarchar(2), order3_4 nvarchar(2), order3_5 nvarchar(2), dateorder4 [datetime] NULL, order4_1 nvarchar(2), order4_2 nvarchar(2), order4_3 nvarchar(2), order4_4 nvarchar(2), order4_5 nvarchar(2), dateorder5 [datetime] NULL, order5_1 nvarchar(2), order5_2 nvarchar(2), order5_3 nvarchar(2), order5_4 nvarchar(2), order5_5 nvarchar(2) ) Insert into #table2 select '012345','2008-04-19 00:00:00.000' , '01', '06', '05', null, null, '2008-04-20 00:00:00.000' , '01', '07', '05', '07', '03','2008-04-21 00:00:00.000' , '01', '06', null, null, null,'2008-04-22 00:00:00.000' , '01', '02', '05', '07', null,'2008-04-23 00:00:00.000' , '03', '06', null, null, null UNION all select '987654','2008-04-21 00:00:00.000' , '19', '21', null, null, null ,'2008-04-22 00:00:00.000' , '01', '02', '05', '16', null,'2008-04-23 00:00:00.000' , '03', '06', null, null, null , null, null, null, null, null, null, null, null, null, null, null, null select * from #table2 drop table #table1 drop table #table2 ----------------------------------------
View Replies !
Create Temp Table To Store Data From Database(mssql)
I am a starter of vb.net and trying to build a web application. Do anyone know how to create a temp table to store data from database? I need to extract data from 3 different tables (Profile,Family,Quali). Therefore, i need to use 3 different queries to extract from the 3 tables and then store it in the temp table. Then, i need to output the data from temp table to the screen. Do anyone can help me?
View Replies !
HELP - Can't Seem To Use Stored Proc And Temp Table As Transformation Data Source
Greetings follow SQL developers: I've recently encountered an interesting problem / limitation. I've created a SQL stored procedure that creates a temp table, populates it, then selects out of the temp table. This procedure works fine through Query Analyzer. Now I'm trying to use this stored procedure as my source data for a transformation task between a SQL Server 7 OLEDB connection and a text output file. Whenever I try to parse the query or define the transformation, I get the following error: Error Source: Microsoft OLE DB Provider for SQL Server Error Description: Invalid object name '#TempTableName' I know that when a temp table goes out of scope, this error can occur-- However, I don't understand why the table would be out of scope when it is selected from within the stored procedure itself. I've also tried a global temp table (##TempTableName) to no avail. Any input would be greatly appreciated. C. Dwight Austin, Texas USA
View Replies !
Temp Table Usage In Stored Proc Data Sets
Hello, In a number of stored procedures we employ in our reports and within other applications we use temp tables in order to compile results into a single dataset before using a final result set query. We have been receiving errors on a number of occasions where the designer rejects the temp tables internal to the stored procs as invalid objects. Does SRS have problems with temp tables and stored procedures in general? All the stored procs that use temp tables contain internal checks for the prior existence of the temp table then drops them, so it's not like there is no means of clearing them out.
View Replies !
Update Temp Table With Stored Procedure Joined With Table
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 Replies !
Compare Data-type Xml In A Temp/variable/physical Table In MSSQL 2000
Does abyone know how to compare data-type xml in a temp/variable/physical table in MSSQL 2000? I tried this works in MSSQL 2005, Code Snippet create Table #t1 ([c1] int identity(1,1) not null, [c2] text) create Table #t2 ([c1] int identity(1,1) not null, [c2] text) Insert into #t1 Values('This is a test') Insert into #t2 Values('This is a test') Select * from #t1 Select * from #t2 Select * from #t1 where [c2] LIKE (Select [c2] from #t2) drop table #t1 drop table #t2 but not MSSQL 2000. Server: Msg 279, Level 16, State 3, Line 12 The text, ntext, and image data types are invalid in this subquery or aggregate expression. Is this true (from BOL)? Code SnippetIn comparing these column values, if any of the columns to be compared are of type text, ntext, or image, FOR XML assumes that values are different (although they may be the same because Microsoft® SQL Server„¢ 2000 does not support comparing large objects); and elements are added to the result for each row selected.
View Replies !
|
TRACKER
