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 2 RepliesDoes 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.
hello all.., i want to make procedure can decrease totalcost from order table(database:games.dbo) with balance in bill table(database:bank.dbo). my 2 database in same server is name "boy"
i have 2 database like: bank.dbo and games.dbo
in games.dbo, have a table name is order(user_id,no_order,date,totalcost)
in bank.dbo, have a table name like is bill(no_bill,balance)
this is a list of bill table
no_bill balance
111222 200$
222444 10$
this is a list of order table
user_id no_order date totalcost
a 1 1/1/07 50$
when customer insert no_bill(111222) in page and click a button, then bill table became
no_bill balance
111222 150$
222444 10$
when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough"
is procedure can take data from 2 database?mystore procedure like:ALTER PROCEDURE [dbo].[pay]( @no_bill AS INT, @no_order AS int, @totalcost AS money)ASBEGIN BEGIN TRANSACTION DECLARE @balanc AS money SET @balanc= (SELECT [balance] FROM [boysqlexpress.Bank.dbo.bill] WHERE [no_bill] = @no_bill) UPDATE [bill] SET [balance] = @balanc - @totalcost WHERE [no_bill] = @no_bill COMMIT TRANSACTIONEND it's output message "Invalid object name '<boysqlexpress>.Bank.dbo.bill'.Transaction
count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION
statement is missing. Previous count = 0, current count = 1.No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[pay]." plss.. help...
Hello there,Now I'm really down, how do I simply create a Table in a database?It must be something likeCreate Table TableName
(
column_name data_type
)
But first how do I execute that string, so it create the table..And if we get that far, how do you then set a table to primarykey?
Hope really for help, because this is a importen thing, and I cant find the answer? :S
I'm writing a search engine and I want to make a search and then, after
I've recieved the results, put them into some sort of temporary(in
mememory) table so that I can do another query on that temporary table.
I saw something about temp tables (in T-SQL) in the
help guide, but I still want to know how to do a query on a regular
table and then store those results in a temporary table to perform a
query on that. Could anyone show me some example or something?
Following sp gives wrong result whats wrong with following cursor?
ALTER PROCEDURE [dbo].[spPMPT_GetProjectBenefitDetailsForAssess]
@ProjectBenefitID INT
AS
SET NOCOUNT ON
DECLARE @ErrorMsgID INT
DECLARE@ErrorMsg VARCHAR(200)
DECLARE @TEMP_BENEFIT
TABLE (ActualQuantity INT,
ExpectedQuantity INT,
ActualQulity VARCHAR(2000),
ExpectedQulity VARCHAR(2000) )
DECLARE @AssessBenefitID INT
DECLARE @ActualQuantity INT
DECLARE @ExpectedQuantity INT
DECLARE @ActualQuality VARCHAR(2000)
DECLARE @ExpectedQuality VARCHAR(2000)
DECLARE @AssessFlag CHAR
DECLARE CUR_BENEFIT CURSOR FOR
SELECT AssessBenefitID,ProjectBenefitID,AssessFlag FROM PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID
OPEN CUR_BENEFIT
FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ActualQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ExpectedQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ActualQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
SELECT @ExpectedQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
INSERT INTO @TEMP_BENEFIT (ActualQuantity ,
ExpectedQuantity ,
ActualQulity ,
ExpectedQulity )VALUES(@ActualQuantity,@ExpectedQuantity,@ActualQuality,@ExpectedQuality)
FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag
END
CLOSE CUR_BENEFIT
DEALLOCATE CUR_BENEFIT
SELECT * FROM @TEMP_BENEFIT
Can any one please tell me where i am going wrong..
Code Snippet
create proc SP_PercentageRMU
as
SELECT cast (sum([Usage Qty]) as [decimal] (28,8))as 'TUsageQty'
,RAW_MATERIAL
into #TZMelt_Pound
FROM [LatrobeOCT].[dbo].[ZMelt_Pound]
group by RAW_MATERIAL
GO
select [Usage Qty],(case when r.raw_material = z.raw_material
then cast ((r.[usage QTY] / z.TUsageQty) as decimal (28,8))
else 0
end) as '%UsageQty'
,r.[PRODL]
,r.RAW_MATERIAL
,r.[GRADECODE]
into #PZMelt_Pound
FROM [LatrobeOCT].[dbo].[ZMelt_Pound]r
inner join #TZMelt_Pound z on r.raw_material = z.raw_material
drop table #TZMelt_Pound
go
error
Msg 208, Level 16, State 0, Line 2
Invalid object name '#TZMelt_Pound'.
How can you loop over a data reader and save the current row to a temp array of some sort and return this array back to the caller?
SqlDecimal total = 0;
SqlConnection conn = new SqlConnection("Context Connection=true");
SqlCommand cmd = new SqlCommand(@"SELECT column1, column2, column3
FROM table ", conn);
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (rdr.GetSqlString(1) == "01")
{
// ADD the current record to a temp list
total = total + rdr.GetSqlDecimal(2);
}
}
SqlContext.Pipe.Send(total.ToString());
// RETURN the temp list
rdr.Close();
conn.Close();
Hi all,
I've a requirement to store the output of the stored procedure into temp. tables/ table varibles.
I've 4 select statements as my output of the stored procedure. How do I store the results of all the 4 select stmnts into 4 different temp tables.
Simplified SP is as...
Create procedure usp_test
as
begin
select c1,c2 from table1
select c3,4 from table2
select c9,c8 from table3
select c5,c7 from Table4
end
I'm expecting something like this...
declare @table1 table (c1, c2)
insert into @table1
Exec <Sp_Name>
select * from @table1
I know the above stmnt works, if my SP has only 1 select stmnt as output.
Please help me to acheive this for multiple select statements.
Thanks,
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp
Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
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
Hi all!I have a problem with a temp table.I start creating my table:bdsqlado.execute ("CREATE TABLE #MyTable ...")There is no error. The sql string has been tested and when it'sexecuted in the sql query analyzer it really creates the table.After creating the table, I execute an insert statement:bdsqlado.execute ("INSERT INTO #MyTable VALUES(...) "It returns an error like this: "Invalid Object Name #MyTable"I don't understand what's wrong. If I execute both sql sentences inthe SQL Query Analyzer it works perfectly.I use the same connection to execute both statements and I don't closeit before the INSERT is executed.I think it may be something related to dynamic properties of theconnection, but I'm not sure. It's just an idea.Please I need help.Thanks a lot,
View 1 Replies View RelatedCan someone send me an example of creating a variable to use instead of a temp table? I cannot find an example on books on line, but know it is possible in SQL2000.
Thanks,
Dianne
I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:--------------------------------------------------------------------------------------- Get all SubquestionIDs for this concept-------------------------------------------------------------------------------------DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR SELECT QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesFROM RotationMaster AS RM INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumberWHERE RM.Study = @StudyGROUP BY QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesHAVING QGDM.SubquestionID <> 0--------------------------------------------------------------------------------------- Dynamically create a Temp Table to store the data, simulating a pivot table-------------------------------------------------------------------------------------SET @Count = 2SET @SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'OPEN curStudySubquestionsFETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValuesWHILE @@FETCH_STATUS = 0BEGIN SET @SQL = @SQL + ', Col' + CAST(@Count AS VARCHAR(5)) + ' VARCHAR(10)' SET @Count = @Count + 1 FETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValues ENDSET @SQL = @SQL + ', ShowOrder SMALLINT)'CLOSE curStudySubquestionsPRINT 'Create Table SQL:'PRINT @SQLEXEC (@SQL)SET @ErrNum = @@ERROR IF (@ErrNum <> 0) BEGIN PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!' RETURN ENDPRINT '*** Table Created ***'-- Test that the table was createdSELECT *, 'TEST' AS AnyField FROM #AllSubquestions The line PRINT @SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):CREATE TABLE #AllSubquestions (Col1 VARCHAR(100), Col2 VARCHAR(10), Col3 VARCHAR(10), Col4 VARCHAR(10), Col5 VARCHAR(10), Col6 VARCHAR(10), Col7 VARCHAR(10), ShowOrder SMALLINT) However, the SELECT statement to test the creation of the table produces this error:*** Table Created ***Server: Msg 208, Level 16, State 1, Procedure sp_SLIDE_CONCEPT_AllSubquestions, Line 73Invalid object name '#AllSubquestions'. It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?
View 4 Replies View RelatedI have a view which works fine but I cannot display the data in the Report tool because its
CCSID is HEX. If I could create it to temp table
I think then there would be an easy way to get around this problem.
This is the code:
CREATE VIEW astlib.acbalmpk AS (
(SELECT LMLTPC, COALESCE(IRLOC1,'') as IRLOC1,
COALESCE(IRLOC2,'')
as IRLOC2, COALESCE(IRLOC3,'') as IRLOC3, IRPRT#, IRQOH#, IRWHS#,
'' as IEPRT#, '.00' as IEQOH#, '' as IELOC1, '' as IELOC2, '' as
IELOC3, '' as IERIDC, '' as IEWHS#
[Code] ....
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
Hello
I have a local SQL2005 server with a linked SQL2000 server. I would like to know how to create a temporary table in the remote server in such a way that I can make an inner join as follows; my idea is to optimized a distributed query by doing so:
create table #myRemoteTempTable
insert into #myRemoteTempTable
select * from myLocalTable
update myRemoteTable
set
Value=#myRemoteTempTable.Value
from myRemoteTable
inner join #myRemoteTempTable on #myRemoteTempTable.ID=myRemoteTable.ID
Is there a way to find out what the datatypes of a temp table are?
Example:
select cust_code, cust_name, cust_state
into #customers
where cust_state = 'TX'
I would like to know what datatypes SQL used when creating #customers.
Thank you for all the help.
Hi all
I'm new to sql and could do with some help resolving this issue.
My problem is as follows,
I have two tables a BomHeaders table and a BomComponents table which consists of all the components of the boms in the BomHeaders table.
The structure of BOMs means that BOMs reference BOMs within themselves and can potentially go down many levels:
In a simple form it would look like this:
LevelRef: BomA
1component A
1component B
1Bom D
1component C
What i would like to do is potentially create a temporary table which uses the BomReference as a parameter and will loop through the records and bring me back every component from every level
Which would in its simplest form look something like this
LevelRef: BomA
1......component A
1......component B
1......Bom D
2.........Component A
2.........Component C
2.........Bom C
3............Component F
3............Component Z
1......component C
I would like to report against this table on a regular basis for specific BomReferences and although I know some basic SQL this is a little more than at this point in time i'm capable of so any help or advice on the best method of tackling this problem would be greatly appreciated.
also i've created a bit of a diagram just in case my ideas weren't conveyed accurately.
Bill Shankley
I have 2 tables:
Customer Table: ID, OrderID (composite key)
100, 1
100, 2
200, 3
200, 1
Order Table: OrderID, Detail
1, Orange
2, Apple
3, Pineaple
Assuming each customer always orders 2 items. I need to create a SQL query that shows as following (a view or a temp table is OK). How do I do that?
CustomerID, Order Detail1, Order Detail2
100, Orange, Apple
200, Pineaple, Orange
Hi
Folx,
I
am new to SQL Server and I am struggling.
Versions:
Microsoft
SQL Server Integration Services Designer Version
9.00.1399.00
Microsoft
SQL Server Management Studio 9.00.1399.00
I
would like to
01.
create a temp table
02.
load the temp table from a flat file
03.
insert into a destination table the rows from the temp table where NOT EXIST the
primary key of the destination table.
ISSUES:
Flat
File Source will not accept that a resource will be available that does not yet
exist (the temp table)
I
set the Flat File Source to €śIgnore Failure€? and ran the package. It ran with
warnings but did not insert the new rows.
The
€śIgnore Duplicates€? radio button is €śgrayed out€? because the index is clustered
Now
I could work around this thing by keeping a table just for purposes of this
process flow. I am opposed to that philosophically and would prefer to do this
in the way that I consider appropriate€¦is there a solution?
Thanks,
Bill
Hi anyone can tell me how to create a table from an existing table in MSSQL.
In oracle we issue " create table <tablea> as select * from <tableb>; " ?
Thanks in Advance.
Hi,
I am using a SQL back end to dynamically populate an asp.net report/page.
As the data I'm interrogating is created from a tree control, I'm having to use a recursive function to retrieve the data into a series of ID values. This all happens at the moment in a DataTable manipulated with c# code. So my ID values end up in this datatable.
My problem is that I am then performing a crosstab query in SQL Server 2000 and these ID are required as part of that query.
Should I create a temp table and join this into the query or should i feed in a series of ID values into a where clause?
Any help gratefully appreciated.
Thanks.
John
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". Â The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". Â I will post the definitions from another effort below.
CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,
[code]....
SQL Server 2008 R2.
I have an Access app. that I am migrating the DB portion (queries, tables) to SQL server. I need to create a temp table that lasts as long as the user has the Access FE app. open. Idea is that the temp table stores the user's parameters (used for filtering data entry forms and report). The parameters allow the app. to only show the user his data (cannot view other users data). The SP shown below works OK, it creates a ##Temp table and updates it with the parameters sent by Access FE app. The issue I am having is that as soon as the SP finishes the ##Temp table is removed. I thought of using a regular table, but, I am currently testing this migration in my local SQL server instance, as soon as I move the database to production environment, then users will not be able to create tables as permissions are only read/write.
USE [Work_Allocation]
GO
/****** Object: StoredProcedure [dbo].[spUser_Parameters_update] Script Date: 9/30/2015 12:27:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUser_Parameters_update]
[code]...
Hey friends,I want create a database on a usb pendrive and acess it my server isMSSQL 7.0 when ever i plugin the pend drive.Please let me know the the procedure .. this is a chellenging job..Thank Youregards*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedI want to generate dynamic temp table so, from one strored procedure am getting an some feilds as shown below
CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Invoice raised date],''[Payment Received date],''[Payout date],''[Payroll lock date]
for i want to generate table for the above feilds with datatype
Hello all,
Please help....
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
Any help would be highly appreciated.
Thanks in advance.
Dear All,
I have the table creation script and insret record script.
This is MySQl Format.
What changes I have to do so can I run this scripts into SQL Server 2000.
If any body has successfully done it then please tell me the procedure.
CREATE TABLE `activity` (
`id` bigint(20) NOT NULL auto_increment,
`object_type` varchar(60) default NULL,
`object_id` varchar(20) default NULL,
`person_id` bigint(20) NOT NULL default '0',
`activity_dtm` datetime NOT NULL default '0000-00-00 00:00:00',
`activity_type_cd` varchar(25) NOT NULL default '',
`description_code` varchar(200) default NULL,
PRIMARY KEY (`id`),
KEY `FK9D4BF30FB4715636` (`activity_type_cd`),
KEY `FK9D4BF30F270CDEE0` (`person_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
--
-- Dumping data for table `activity`
--
-- --------------------------------------------------------
--
-- Table structure for table `actv_type`
--
CREATE TABLE `actv_type` (
`code` varchar(25) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`void_ind` char(1) NOT NULL default '',
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `actv_type`
--
INSERT INTO `actv_type` VALUES ('job_create', 'Created job', 'F');
INSERT INTO `actv_type` VALUES ('job_update', 'Changed job', 'F');
INSERT INTO `actv_type` VALUES ('job_void', 'Voided job', 'F');
INSERT INTO `actv_type` VALUES ('job_activate', 'Activated job', 'F');
INSERT INTO `actv_type` VALUES ('job_deactivate', 'Changed job to deactive', 'F');
INSERT INTO `actv_type` VALUES ('job_appl_create', 'Created application', 'F');
INSERT INTO `actv_type` VALUES ('job_appl_update', 'Updated application', 'F');
INSERT INTO `actv_type` VALUES ('intrv_create', 'Created interview', 'F');
INSERT INTO `actv_type` VALUES ('intrv_update', 'Updated interview', 'F');
INSERT INTO `actv_type` VALUES ('person_update', 'Update person', 'F');
INSERT INTO `actv_type` VALUES ('person_create', 'Create person', 'F');
INSERT INTO `actv_type` VALUES ('company_void', 'Voided company', 'F');
INSERT INTO `actv_type` VALUES ('company_create', 'Created company', 'F');
INSERT INTO `actv_type` VALUES ('company_update', 'Updated Company', 'F');
Thanks in Advance.
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.Â
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC,Â
[Code] ....
How to purge data in transaction table or we can delete some data and store in separate table in data warehouse?
View 7 Replies View RelatedI have a code that is working, and want to create a store procedure in order to put the information of the running code into a new table.
View 7 Replies View RelatedWhen Create Mirror Database Server, Where need to store the Transaction Log backup file?I took FULL Backup of my Primary Database, and I restored at my Mirror Server also. When I try to create a Mirror Database."The remote copy of database "<db_name>" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error:1412)".I am misplacing the Transaction Log backup file. Where I need to store that file?
View 10 Replies View Related