Create Temp Table/loop Through Records

Jun 19, 2008

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

View 4 Replies


ADVERTISEMENT

Concatenate A String Within A Loop From A Temp Table

May 11, 2006

I need help.

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Emad

View 9 Replies View Related

SQL 2012 :: Inserting Data Into Temp Table Using 2 While Loop

Apr 21, 2015

I want to insert data (month&year) from 2014 till now - into temp table using 2 while loop.

drop table #loop
create table #loop
(
seq int identity(1,1),
[month] smallint,
[Year] smallint

For some reason I cant not get 2015 data .

View 4 Replies View Related

Loop Through Temp Table / Call Sproc / Do Updates

Mar 5, 2015

I'm trying to do something like this:

Loop through #Temp_1
-Execute Sproc_ABC passing in #Temp_1.Field_TUV as parameter
-Store result set of Sproc_ABC into #Temp_2
-Update #Temp_1 SET #Temp_1.Field_XYZ= #Temp_2.Field_XYZ
End Loop

It appears scary from a performance standpoint, but I'm not sure there's a way around it. I have little experience with loops and cursors in SQL. What would such code look like? And is there a preferable way (assuming I have to call Sproc_ABC using Field_TUV to get the new value for Field_XYZ?

View 2 Replies View Related

Loop Through A Recordset To Populate Columns In A Temp Table

Jul 23, 2005

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 11 Replies View Related

How To Automatically Create New Records In A Foreign Table When Inserting Records In A Primary Table.

Sep 13, 2006

Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables.  I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key.  Is there a way to automatically create new records in the foreign tables that will have the new id?  Would this be a job for a trigger, stored procedure?  I admit I haven't studied up on those yet--I am learning things as I need them. Thanks. 

View 4 Replies View Related

Unable To Get Records From Temp Table Using ASP

Nov 23, 2004

PLease help me with a intruiging problem with Stored Procedure, which is driving me mad.......

I wrote a Stored Procedure as listed below.
I've included the options
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF

the results are stored in a #Temp table

The SP executes fine on Query Analyzer, and shows the contents of the temp table. BUt when I run this SP using ASP, I got the following error
-----------
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/test_site/test.asp, line 59
------------

I've tried using Recordset.MoveNextRecordset, if at all there are more than one recordset, obviously I got the same error







CREATE PROCEDURE atul_med.[sp_rep_sale2]
(
@rep_in varchar(100)

)
AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF

DECLARE
@REP int,
@temp_pack varchar(20),
@temp_ntp int,
@temp_party_code varchar(20),
@temp_party_name varchar(20),
@temp_prod_code varchar(20),
@temp_prod_desc varchar(20),
@tot_qty int,
@tot_amount int,
@iCnt int,
@tot_sale_qty int,
@net_tot_sale_qty int,
@TableName varchar(10)

select @REP= @rep_in
set @tot_sale_qty=0
set @net_tot_sale_qty=0
set @TableName='#Test2'
--select @TableName='Test'
set @page=1
set @RecsPerPage=100

Declare @SQL VarChar(1000),
@cnt int,
@iCurLine int, @SQLX VarChar(1000)


set @iCurLine=1


set @iCurLine=1

select @Cnt = (SELECT count(*) FROM VW_PART_REP WHERE (REP = @REP))
print "Nos Of Party :"+ convert(char(1),@iCnt)

Create Table #Test2 (ID int identity,prod_desc nvarchar(100),pack nvarchar(100),ntp int)-- ,tot_qty int ,tot_amt int )




while @iCurLine <= @cnt
begin
SELECT @SQL= "ALTER TABLE #Test2 ADD "
SELECT @SQL = @SQL + "party_qty"+convert(char(2),@iCurLine)+" int "
set @iCurLine=@iCurLine+1
Exec (@SQL)
print @sql
end
SELECT @SQL= "ALTER TABLE #Test2 ADD "
SELECT @SQL = @SQL +"tot_qty int , tot_amt int "
SELECT @SQL = @SQL
exec (@SQL)
print @SQL



select * from #Test2

--Create Table #Test1 (ID int identity,prod_desc nvarchar(100),pack nvarchar(100),ntp int,party_qty1 int ,party_qty2 int ,party_qty3 int ,party_qty4 int ,party_qty5 int ,tot_qty int ,tot_amt int )
set @iCurLine=1


--=============================================



--print '=================Product Name========================'
DECLARE PROD_CUR CURSOR FOR
SELECT distinct(prod_code) as prod_code,prod_desc,pack,ntp FROM VW_sales_sum
WHERE (REP = @REP)
group by prod_code,prod_desc,pack,ntp

Declare @SQL_ins VarChar(1000)

OPEN PROD_CUR
FETCH NEXT FROM PROD_CUR
INTO @temp_prod_code,@temp_prod_desc,@temp_pack,@temp_n tp

WHILE @@FETCH_STATUS = 0
BEGIN


--Print @temp_prod_desc+'-'+convert(varchar(10),@temp_pack)+'-'+convert(varchar(10),@temp_ntp)
--print '=================prod_code :'+convert(varchar(10),@temp_prod_code)+'========= ==============='
print "============Insert Statement============"

SET @SQL_ins = "Insert into "+@TableName+" values("
SET @SQL_ins = @SQL_ins +""""+ @temp_prod_desc+""","""+@temp_pack+""","""+convert(char(10),@temp_ntp)+""","


DECLARE Party_CUR1 CURSOR FOR
SELECT party_code,party_name FROM VW_PART_REP
WHERE (REP = @REP) group by party_code,party_name

DECLARE @SQL1 varchar(10),@SQL2 varchar(100)
set @SQL2=''
OPEN Party_CUR1
FETCH NEXT FROM Party_CUR1
INTO @temp_party_code,@temp_party_name



WHILE @@FETCH_STATUS = 0
BEGIN
print "==Party_name :"+ @temp_party_name +"===Party_Code :"+convert(char(3),@temp_party_Code)
set @tot_sale_qty=0
set @tot_sale_qty= (select sum(issuedqty)as tot_qty from vw_sales_sum
where party_code =@temp_party_code
and (REP = @REP)
and (prod_code=@temp_prod_code))
if @tot_sale_qty IS NULL
begin
set @tot_sale_qty=0
print "===Tot Qty :"+convert(varchar(10),@tot_sale_qty)
end
else
begin
print "===Tot Qty :"+convert(varchar(10),@tot_sale_qty)
end
SELECT @SQL1 = @tot_sale_qty
select@net_tot_sale_qty=@net_tot_sale_qty+@tot_sale_qty
--print @SQL1
Select @SQL2 = @SQL2 + @SQL1 +","



FETCH NEXT FROM Party_CUR1
INTO @temp_party_code,@temp_party_name

END

CLOSE Party_CUR1
DEALLOCATE Party_CUR1
print '==============================='
print @SQL2+convert(char(4),@net_tot_sale_qty)


--set @net_tot_sale_qty=@tot_sale_qty+@net_tot_sale_qty
--SET @SQL = @SQL+convert(char(10),@tot_sale_qty)+','
--set @tot_sale_qty=0

Select @SQL_ins = @SQL_ins+@SQL2+convert(char(8),@net_tot_sale_qty)
Select @SQL_ins = @SQL_ins+","
Select @SQL_ins = @SQL_ins+convert(char(12),(@net_tot_sale_qty*@temp _ntp))
Select @SQL_ins = @SQL_ins+")"
print @SQL_ins
Exec (@SQL_ins)

select@net_tot_sale_qty=0
FETCH NEXT FROM PROD_CUR
INTO @temp_prod_code,@temp_prod_desc,@temp_pack,@temp_n tp


END


CLOSE PROD_CUR

DEALLOCATE PROD_CUR



SELECT * FROM #Test2


SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER ON
GO

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

I Can't Create A Temp Table

Jul 20, 2005

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 Related

SQL 2012 :: How Many Records Can Insert Into A Temp Table

Mar 25, 2014

I use code below to insert data into a temp table.

How many records can insert into a temp table?

Select * into #temp from ORDER

View 3 Replies View Related

SQL 2012 :: 1.5 Million Records Into Temp Table

Sep 23, 2014

I come from a web based world were loading 1.5 million records into a temp table is suicide. I’m doing more data warehouse stuff now and I was looking into optimizing a buddies proc and noticed he was loading 1.5 million records into a temp table. We had a discussion about it because being from a web world I was drastically against it. He on the other hand didn’t feel it was an issue being it gets called once maybe twice a day. The tempdb is set to autogrow and it is on a different drive than all the other databases on the box. It has one ldf and mdf. He’s creating an index on the table after load. Why we shouldn’t be loading 1.5 million recs into temp table?

View 5 Replies View Related

Is There Any Way In A Sproc To LOOP Thru The Records Of A Table ?

Sep 21, 2005

Hi. It seems to be very simple, actually, but I don't know if it isfeasible in TSQL. I have a sproc which gathers in one place many callsto different other sprocs, all of them taking a 'StoreGroupe'parameter. I would like to add a case where if the call has NOStoreGroupe parameter, the sproc should LOOP thru all records in tableStoreGroupeTable, read the column StoreCode, and pass that value as aparam to the other sprocs, as in:CREATE PROCEDURE MySproc(@StoreGroupe nvarchar(6) = NULL)ASif (@StoreGroupe is not null)BeginExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............EndElseBeginA 'Group Code' has NOT been specifiedI want to take all the StoreGroups in tableStoreGroupeTable, in turn.I would like to do SOMETHING LIKE THIS:Do While not [StoreGroupeTable].EOFRead [Code] from [StoreGroupeTable]Set @StoreGroupe = The value I just readExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............LoopEndGOIs that feasible in a sproc, or do I have to do this in the client(ADO) ?Thanks a lot.Alex.

View 4 Replies View Related

Random Loop Through Table Without Using The Same Records Over Again.

Jul 31, 2007

Hello,

Anyone have any suggestions on creating a query that will randomly select records from a table, but not use those records again. I have some code that does it, but it uses the same fields over again, and also throws in some blank records that I did not specify in the query. I am creating a test engine that has to randomly ask questions.

View 5 Replies View Related

Using A Variable To Create Temp Table

Mar 3, 2003

Can 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

View 2 Replies View Related

CREATE A Temp Table Via EXEC (@SQL)

Jan 23, 2006

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 Related

Create Temp Table Instead Of View?

Aug 23, 2013

I 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] ....

View 2 Replies View Related

SP5a - Cusor Skipping Records On Temp Table

Dec 14, 1999

Hello,
Just in case anybody has had this problem after applying service pack 5a.
a stored procedure that sets serial numbers on records on a temporary table is skipping every other record.
I ran the code manually on isql and could not replicate the error, however when re-compiled on stored procedure it fails misserably.
when is Microsoft going to release a service pack that actually works?

Did you know that Executive does not recognize 2000 as a leap year?

If anybody has any suggestions on this problem, besides the obvious (using a real table) please write back
thanks.

David E. Pierri
Pseudo data-admin
ACNY

View 1 Replies View Related

Newbie How To Create Temp Table And Populate

May 8, 2006

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

How Can I Create A Temp Table On A Linked Server?

Apr 11, 2007

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

View 6 Replies View Related

Find The Datatypes That Where Used To Create A Temp Table

Feb 18, 2008

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.

View 2 Replies View Related

Create A View Or Temp Table From 2 Tables

Jan 4, 2008



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



View 10 Replies View Related

How Do I Create A Temp Table As The Beginning Of A Process Flow?

Dec 29, 2006

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

View 4 Replies View Related

Best Practice: Use Values In WHERE Clause Or Create And Join Temp Table ?

Dec 28, 2006

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

View 2 Replies View Related

Create Temp Table To Store Data From Database(mssql)

Jun 26, 2007

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 Replies View Related

Transact SQL :: Create A Temp Table On Results Of A Pivot Query?

Jun 17, 2015

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.

View 3 Replies View Related

Transact SQL :: Create Temp Table That Persists While A Front End App Is Open

Sep 30, 2015

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]...

View 10 Replies View Related

SQL Server Admin 2014 :: Create Dynamic Columns In Temp Table?

Jun 9, 2014

I 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

View 5 Replies View Related

Transact SQL :: Create Index On Temp Table To Reduce Run Time Of Update Query

Apr 29, 2015

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] ....

View 7 Replies View Related

Using Foreach Loop To Parse An SQL Table - Lookup And Create Derived Column

Oct 25, 2007

I am looking for the best way in SSIS to do the following. I have an SQL table that for each row in the table I want to take an element from the table do a lookup in a Teredata Table, return information from the teredata source. Use that returned data to do some calculations and create a derived column from my calculations and place the data into the same SQL table that I am parsing through.

Ideas?

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

Create Temp Table On Linked Server From Local Server

Jan 15, 2004

Hi,

I would like to join two tables: one on a local server which I have admin access to and another server which I only have read access. The local table is very small, but the remote table is very large.

If I look at Query Analyzer's execution plan, it appears that the join will be done locally (i.e. the entire table is transferred from the remote server and then joined to my local table). Is there a way to create a temp table using linked servers, transfer my small local table to the remote server and then perform the join on the remote server? In the past, I have been able to use openquery to restrict the data to a small subset that is transferred but the local table is a little too large for that.

I appreciate any advice / guidance anyone can offer me!

View 1 Replies View Related

Create A String Of Records From A Table In A Stored Procedure,

Jul 20, 2005

I have a table tblCustomers in a one-to-many relationship with tabletblProducts.What I want to do is to create a stored procudure that returns a listof each customer in tblCustomers but also creates a field showing astring (separated by commas)of each matching record in tblProducts.So the return would look like:CustID Customer ProductList1 Smith Apples, Oranges, Pears2 Jones Pencils, Pens, Paperetc...Instead of:CustID Customer Product1 Smith Apples1 Smith Oranges1 Smith Pears2 Jones Pencils2 Jones Pens2 Jones PaperWhich is what you get with this:SELECT tblCusomers.CustID, tblCusomers.Customer,tblProducts.ProductFROMtblCusomers INNER JOINtblProducts ONtblCustomers.CustID = tblProducts.CustIDI'd appreciate any help!lq

View 6 Replies View Related

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related







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