MS SQL Server :: Looping Through Temporary Table To Concatenate A String

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

Table Names In Stored Procedures As String Variables And Temporary Table Question

Apr 10, 2008

How do I use table names stored in variables in stored procedures?

Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000

I receive the error 'must declare table variable '@tablename''

I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.

Code Snippet

if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename

It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.

Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.

Heres the actual SQL query that produces the temporary table error.

Code Snippet
Select * into #temptableabcd from TableA

Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB

TRUNCATE TABLE TableA

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB

The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.

Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?

Thanks for the help.

View 6 Replies View Related

Concatenate String

Oct 25, 2004

Please help me with this if you can.
I have one table with CustomerID and some other data.
In other table i have CustomerID(the link with the first table) and Agent
The relation of the first with the second one is ONE TO MANY.
I want something like this:
Customer,'Agent1,Agent2,Agent3'

Is it possible.
Please help :)

View 3 Replies View Related

How To Concatenate A String To A Int Value

Mar 5, 2007

I hope this is the correct place to ask this...

I would like to concatenate a String value to an Int value using an SQL statement. At the moment it reads like this:

SELECT 'website.com/shop/product.cfm?ProductID=' + Products.ProductID AS Product_URL

But unfortunately I am getting the error:
"Conversion failed when converting the varchar value 'website.com/shop/product.cfm?ProductID=' to data type int."

Any idea how to get around this at all just using an SQL query statement?

View 2 Replies View Related

Concatenate SQL String...

Feb 20, 2008

Hi all I need some help in concatenatng a string in T-SQL. Having used the Command Microsoft Access inside the 'SQL View' window and typed the following it worked perfectly.

Code Snippet

UPDATE tblValidUsers SET blocked_users = blocked_users + 'name_123@hotmail.com;' WHERE userid='Onam'

However attempting the same command in T-SQL I get the following error:

Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals text.

Reason for having this command is I want to be able to add something to the end of the field "blocked_users" without actually overwriting the fields contents.

So for instance if I had the items: "Item1, Item2, Item3" in blocked_users and I updated it with "Item4" then the value "Item4" would be added to the end thus the use of "+" is used to concatenate. Is there a way of doing this in T-SQL?

Thanks for the help, Onam

View 6 Replies View Related

Concatenate String In Query

Apr 22, 2008

Hi,

As I build a record set in an SP I need to add in a string containing a list derived from a second query. I need the results of the sub query to be presented as a single string in the first query, separated by a single space.

I have no idea how to do this in t-sql, and am doing it on the web server at the moment, but becase the dataset is quite large, I'm getting 20 - 40 second processing times which is far too long.

I have found reference to the xp_sprintf function but this is not supported by my host, so not a solution.

I'm no expert in t-sql, so I imagine there's a way somewhere, and would be grateful for any advice available.

regards,

NEIL

Neil

View 5 Replies View Related

Convert String To Hex And Concatenate With

Sep 4, 2014

To start, I am NOT a SQL programmer. I have to do some minimal SQL administration (DB Creation, Backups, Security) on spatial databases that are for the most part managed by a 3rd party program. My experience with T-SQL is mostly simple tasks (i.e. Select and Update statements)..However I have been requested to calculate an ID Field using the values of two other fields. Per the request, I need to convert one field to Hex and concatenate with the second field.

ex. Field 1 + Field 2(hex string) = Field 3
Field 1 = 'FF02324323'
Field 2 = 'Smith Creek'
Field 3 = 'FF02324323536D69746820437265656B'

Field 1 VarChar(10) (Code)
Field 2 VarChar(65) (Common Name)
Field 3 VarChar(max) (ResourceID)

Spent half the day searching and have tried various forms of CAST, CONVERT, fn_varbintohexstr and others but have unable to come up with the correct combination to get what I need.

View 3 Replies View Related

Concatenate A String Problem

Oct 18, 2006

Hi to all:
In my package, I have in OLE DB SOURCE a statement:

DECLARE @CMonth as smalldatetime
SET @CMonth = '11/1/2006'
select day(@CMonth)+month(@CMonth)+year(@CMonth) as ID_Month

and in OLE DB DESTINATION, I have ID_Month column as a char(10).

I want to be result as concatenated string �� 1112006, but I receive 2018 (As a result of calculation)
Can anybody help me? Thank you

View 3 Replies View Related

Turn Hex Into Char String And Concatenate

Feb 16, 2006

I've been trying to return hex data in a way that can be concatenated.
I need the actual hex info (e.g. 0x6E3C070) as displayed
since it contains info about the path to a file.
So I can turn it into D:6E3C7 to get the path to the file.
In searching around I have come across a way to do this but can't figure out how to get it to run through a column and either display or insert into a table multiple results.

-Here's the user function that converts an integer into a hex string-

CREATE FUNCTION udf_hex_string (@i int)
RETURNS varchar(30) AS
BEGIN
DECLARE @vb varbinary(8)
SET @vb = CONVERT(varbinary(8),@i)
DECLARE @hx varchar(30)
EXEC master..xp_varbintohexstr @vb, @hx OUT
RETURN @hx
END
GO

-Using this table-

create table
XPages
(PageStoreId int null,
HexString varchar (30) null,
VolumePath varchar (60) null, )

--'PageStoreId' contains the data that needs to be converted into the editable hex string
--'HexString' is where I'd like it to go so I can parse it later.

--I can run the below select and get the hex string. But am stuck on how to run a select or update that would run through the 'XPages.PagestoreId'
column and insert the hex string into the 'XPages.Hexstring' column. 'XPages.PagestoreId' could have 100's of entries that need to be converted and placed in the relevant the 'XPages.Hexstring' column.

SELECT dbo.udf_hex_string(1234)

Thanks

View 3 Replies View Related

Concatenate String And Pass To FORMSOF?

Jul 23, 2005

The following query works perfectly (returning all words on a list called"Dolch" that do not contain a form of "doing"):SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, "doing")')WHERE (dbo.CombinedLexicons.vchWord IS NULL)However, what I really want to do requires me to piece two strings together,resulting in a word like "doing". Any time I try to concatinate strings toget this parameter, I get an error.For example:SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, "do' + 'ing")')WHERE (dbo.CombinedLexicons.vchWord IS NULL)I have also tried using & (as in "do' & 'ing") and various forms of singleand double quotes. Does anyone know a combination that will work?FYI, in case this query looks goofy because of the unused "CombinedLexicons"table, it is because the end result should be a working form of thefollowing...Figuring out the string concatination is just a step toward this goal:SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')WHERE (dbo.CombinedLexicons.vchWord IS NULL)Thanks!

View 2 Replies View Related

Transact SQL :: Concatenate String Using Conditions?

Sep 19, 2015

I have a SQL table like this

col1 col2 col3
1 0 0
1 0 1
1 1 1
0 1 0

I am expecting output as

col1 col2 col3 NewCol
1 0 0 SL
1 0 1 SL,PL
1 1 1 SL,EL,PL
0 1 0 EL

condition if col>0 then SL else '', if col2>0 EL else '', if col3>0 PL else ''

View 5 Replies View Related

Concatenate String Then Convert To Datetime

Jan 23, 2008

I've been trying to do the following:

Code Snippet
if year(@SomeDateTime) <> @SomeYear

set @SomeDateTime= convert(datetime, @SomeYear+ '1231', 112)

If variable @SomeDateTime evaluates to 20080101 and @SomeYear = 2006, I wish that my variable @SomeDateTime becomes 20061231 (December 31st).

The way it's written now, it doesn't work... @SomeDateTIme evaluates to 1908-11-12.... ?!

View 6 Replies View Related

Transact SQL :: Aggregate 2 Dates And Concatenate Into New String

May 21, 2015

Have this table

ACCOU NAME NAME TODATE ID EDUCAT EXPIRYDATE
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000

Need to get it like

ACCOU NAME NAME TODATE ID EDUCAT EXPIRYDATEstring
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000

In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.

View 11 Replies View Related

How Can I Concatenate A String To A Int Variable? Need Correct Combination Of Quotes!

May 26, 2008

Hi,
I need to concatenate a string with an int variable on a stored procedure; however, i looks like i am lost in single and double quotes. Does any one know the right comination of quotes for this please? My Code is below:
1 @Price int
2
3 DECLARE @SqlPrice varchar(50)
4
5 if (@Price is not null)
6
7 set @sqlPrice = 'AND price' + '' > '' + '' + @Price + ''
8

View 4 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

Possible To Have Temporary Rows In A Non-temporary Table? Lifespan On Rows..

Mar 14, 2004

I have a table that includes the html-output of different parts of my pages. This table grows very big very fast, and rows older than 24 hours are useless.

My question is if it is possible to have temporary rows, whose are automatically deleted after these 24 hours? And then how to accomplish that?

View 2 Replies View Related

Get Value By Looping Through Table Column

Apr 5, 2014

Here are two tables:

TABLE_A
[ac] [dest]
1 A
1
1
2
2
3
3 B
3

(ac=1, dest=A; ac=3, dest=B)

The space between '1' and 'A' isn't showing up correctly.

TABLE_B
[fleet] [ron]
1 A
1 A
1 A
1 A
1 A
2
2
2
2
3 B
3 B

(fleet=1, ron=A; fleet=3, ron=B; etc.etc.)

I would like to fill TABLE_B's column "ron" by referring to TABLE_A's column "dest".ac = fleet, and for each ac, I would like to loop through the 'dest' column in TABLE_A from top to bottom to get the top most value. If there are no values (like with ac = 2), then value is blank.

For 1, the value is A.
For 2, the value is blank.
For 3, the value is B.

Therefore, in TABLE_B, for all 'ron' for fleet=1, the value is filled A.
For all 'ron' for fleet=2, the value is blank.
For all 'ron' for fleet=3, the value is B.

- create variable 'v'
- where ac=fleet, loop through 'dest' from top to bottom to get top-most value. variable 'v' = the value.
- in TABLE_B, for each ac=fleet, insert variable 'v' into the 'ron' column

View 1 Replies View Related

Concatenate Into Column For Same Table

Jan 29, 2008

I want this query to result in a new table that includes all of the information from the existing table, JobFormPressSpecs PLUS an additional Column with concatenated data as called for below. What am I missing?

SELECT (jobformpressspecs.job#) +'-'+

jobformpressspecs.[Form #]

AS Job

FROM jobformpressspecs

Thanks for your help!

David

View 7 Replies View Related

Creating A Common Table Expression--temporary Table--using TSQL???

Jul 23, 2005

Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!

View 11 Replies View Related

Cursors - Looping Through A Table And Do Inserts From It

Dec 5, 2006

I've been looking online and cannot find any help / resources with this so I brought it here :D
I'm looking for help in creating a Cursor (this will be inside a SP) that will loop through the records of a "Table" (Temporary or Retrieved) and for each row that is looped through I can use it's values to do inserts against a few other tables.
Any resources / help would be great! I work best by example.

View 12 Replies View Related

Looping Thru A Table And Updating The Contents

Nov 8, 2007

Hello, Its hard trying to explain this.
I have 3 tables
Table 1 is where the users are stored, each user has a username and a userrank
Table 2 is where the points that decides the userrank are stored
Table 3 contains the available userranks like this

Table 1 (user_list) looks briefly like this:username nvarchar(20),userrank int, -- Reference to Table3 id... alot more fields
Table 2 (settings_profile) looks like this:username nvarchar(20),total_active_points int,... some more fields
Table 3 (data_ranks) looks like this:id int primary key auto inc,rankname nvarchar(20),min_pts int,max_pts int

Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code:
WHILE (SELECT @username = username, @id = id, @temp1 = ap_sentmails, @temp2 = ap_createdthreads, @temp3 = ap_createdanswers, @temp4 = ap_signguestbook, @temp5 = ap_blogcomment, @temp6 = ap_createblogentry, @temp7 = ap_profilefirsttime, @temp8 = ap_profilephoto, @temp9 = ap_activateguestbook, @temp10 = ap_addnewfriend, @temp11 = ap_superguruvote, @temp12 = ap_forumtopicvote, @temp13 = ap_labervote, @temp14 = ap_funstuffitemvote, @temp15 = ap_movievote, @temp16 = ap_actorvote, @temp17 = ap_money_new WHERE (created < Dateadd(dd, -7, @todaysdate))BEGINSET @sum = 0SET @sum = @temp1 + @temp2 + @temp3 + @temp4 + @temp5 + @temp6 + @temp7 + @temp8 + @temp9 + @temp10 + @temp11 + @temp12 + @temp13 + @temp14 + @temp15 + @temp16 + @temp17UPDATE settings_profile SET total_active_points = total_active_points - @sum WHERE (username = @username)DELETE FROM konto_daylist WHERE (id = @id)END
Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.Something like this...
DECLARE @username nvarchar(20)DECLARE @pts int, @rank int
...something that starts a loop thru table A (user_list) and get the username into @username...
SELECT @pts = total_active_points FROM settings_profile WHERE (username = @username)-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranksSELECT @rank = id FROM data_ranks WHERE (pts_min => @pts AND pts_max < @pts)UPDATE user_list SET rank = @rank WHERE (username = @username)
...next persion in the loop...
This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank...
But how do i loop thru all the users? with a cursor?

View 10 Replies View Related

Looping Through A Table Sorting And Updating

Jun 3, 2008

Hello,

I am a little new at T-SQL. and I could use a suggestion on the best way to accomplish this task. I have an application where I must loop through a table in Microsoft SQL Server 2005, find the records that match and break them up into groups where the value of the Thickness field is in groups of ( <=8).

Lets say I have 100 records and the table contains sacks marked A, B, C, and D. Within Sack A is 6 records. Each of the 6 records has a thickness column with a value between 0.5 to 1.0 (my example shows 2) I have a column called bundle that I must update to show a 1 for the first group of (<=8) inches thick a 2 for the second group (<=8) inches thick and so on. Then repeat the process for sacks B, C and D.

The table itself has the following
Order Number |Zipcode |Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |NULL |
2 |19809 |A | 2 |NULL
3 |19809 |A | 2 |NULL
4 |19809 |A | 2 |NULL
5 |19809 |A | 2 |NULL
6 |19809 |A | 2 |NULL |
7 |19721 |B | 4 |NULL |
8 |19721 |B | 3 |NULL |
9 |19721 |B | 2 |NULL |
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |

What I need to do is sum the thickness until it gets to less than 8 and update bundle with a 1. The result would be 4 records and the table would then look like this:

Order Number |Zipcode | Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |NULL
6 |19809 |A | 2 |NULL |
7 |19721 |B | 4 |NULL |
8 |19721 |B | 3 |NULL |
9 |19721 |B | 2 |NULL |
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |

Notice there are two records that are not bundles for sack A. I need to place them in a separate bundle called 2. The result will be as follows
Order Number |Zipcode | Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |2
6 |19809 |A | 2 |2 |
7 |19721 |B | 4 |NULL|
8 |19721 |B | 3 |NULL
9 |19721 |B | 2 |NULL
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |

Now Sack A is complete so I must move on to Sack B. The first two records total to 7 and adding another record would move the thickness past 8 so I must update the bundle for only the first two records with a 1. The next two total to seven also and the following 2 total to four. We must set those bundles to 1, 2, 3 as follows

1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |2
6 |19809 |A | 2 |2 |
7 |19721 |B | 4 |1 |
8 |19721 |B | 3 |1 |
9 |19721 |B | 2 |2 |
10 |19721 |B | 5 |2 |
11 |19721 |B | 2 |3 |
12 |19721 |B | 2 |3|

These scenarios just keep repeating until the last record

Thank you for any help you can give me

View 7 Replies View Related

Updating A Table By Looping Through All The Record

Oct 1, 2004

The process of adding a column with DEFAULT (0) to a table that has 15million records takes a despicable amount of time (too much time) and the transaction log of the database grew to an unacceptable size. I would like to accomplish the same task using this procedure:

·Add the column to the table with null value.
·Loop through the table (500000 records at a time) and SET the value in the newly added column to 0.
·Issue a commit statement after each batch
·Issue a checkpoint statement after each batch.
·Alter the table and SET the column to NOT Null DEFAULT (0)

Here is my Sample script

ALTER TABLE EMPLOYEE ADD EZEVALUE NUMERIC (9,6) NULL
Go

Loop
UPDATE EMPLOYEE SET EZEVALUE = 0
Commit Tan
CHECKPOINT
END (Repeat the loop until the rows in EMPLOYEE have the value 0)

Go

ALTER TABLE EMPLOYEE ALTER COLUMN EZEVALUE NUMERIC (9,6) NOT NULL DEFAULT (0)

My problem is with the loop section of this script. How do I structure the loop section of this script to loop through the employee table and update the EZEVALUE column 500000 rows at a time, issue a Commit Tran and a CHECKPOINT statement until the whole table has been updated. Does anyone out there know how to accomplish this task? Any information would be greatly appreciated.

Thanks in advance

View 5 Replies View Related

Looping Through Table To Exec An SP Many Times

Dec 31, 2006

Hi,
If i have an SP called mySP that accepts one parameter @param
If I have a table of paramaters with only one column like this:
Param1
Param2
..
ParamN

How do I do if I want to execute the SP on all the table fields:
some thing like this:
Exec my SP 'Param1'
Exec mySP 'Param2'
...
Exec mySP 'ParamN'
I want that automatically since the parameters are going to be in a table called myTblParams
Notice that I don t want to pass all the parameters to the SP just once but only one value each time I execute the SP since mySP ccepts only one parameter.

Thanks a lot for guidelines

View 8 Replies View Related

Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.

Feb 13, 2006

We have the following two tables :

Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )

The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.

The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).

We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.

For instance,

Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.

Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.

View 1 Replies View Related

Is It Possible To Insert Data Into A Table From A Temporary Table That Is Inner Join?

Mar 10, 2008

Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789

View 2 Replies View Related

Looping Through A Table And Fetching Values From Rows

Mar 10, 2008

I have a table (temp_Order) wherein I use to insert a temporary order... after inserting the temporary order, when the buyer submits the order,I want to loop through the table and fetch the orders made by the buyer and insert those products on another table (Order)...how can I achieve this? im currently using sqlDataSource...

View 2 Replies View Related

Difference In Creating Temporary Table By #table And ##table

Nov 29, 2006

Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp

both works fine , then what is the difference
waiting for ur reply
Banti"

View 1 Replies View Related

Temporary Table Vs. Table Valued Function

Jun 6, 2006

I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.

Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:

StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...

Or using a table-valued function to return a temp table as the result.

I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.

View 4 Replies View Related

Temporary Table

Feb 1, 2004

Hi

I am using SQL 2000 I have the following code. When saving it, I am getting an error:
There is already an object named '##tbl' in the database

This is although #tbl is dropped.
Is threrea way to avoid this error? the only work around I found was to create a string with the SQL command and call EXEC, but I don't like this solution as it prevents early compilation of the procedure.

declare @x int
set @x=1
IF @x=0
begin
create table #tbl (
abc int
)
drop table #tbl
end

IF @x=1
begin
create table #tbl (
abc int
)
drop table #tbl
end

Than you.

View 5 Replies View Related

Temporary Table

Nov 8, 2005

Hi

I have to create a temporary table for generating a report in VB.
Pls help how to check the temporary table name in database.
I want if exits than drop and create a new one.

thanks

asm

View 3 Replies View Related

Temporary Table

Apr 11, 2006

I have the following fields in table A:

GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
===================================================
1000| Sales | -20,000.00 | 01 | 2005
===================================================
1000| Sales | -10,000.00 | 02 | 2005
===================================================
1001| Cost | 5,000.00 | 01 | 2005
===================================================
1001| Cost | 5,000.00 | 02 | 2005

the fields above have the following datatype:

Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger

The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:

Description | Period 01 | Period 02 | Year to Date
=========================================================
Sales | 20,000.00 | 10,000.00 | 30,000.00
Total Sales | 20,000.00 | 10,000.00 | 30,000.00

Cost | 5,000.00 | 5,000.00 | 10,000.00
Total Cost | 5,000.00 | 5,000.00 | 10,000.00
=========================================================
Profit | 15,000.00 | 5,000.00 | 20,000.00

The above report would list 4 columns, with the last column being a calculated field as a sum of
Period01 + Period02 Amount, sorted by GL_ID and group under a summation row called
Total Sales & Total Cost.There would be a net amount appearing as Profit (Total Sales-Total Cost).

Guys, hope someone out there can help me with the sql command for the above report?

View 2 Replies View Related