Create Temporary Table Through Select Statement
Jul 20, 2005
Hi,
I want to create a temporary table and store the logdetails from
a.logdetail column.
select a.logdetail , b.shmacno
case when b.shmacno is null then
select
cast(substring(a.logdetail,1,charindex('·',a.logde tail)-1) as
char(2)) as ShmCoy,
cast(substring(a.logdetail,charindex('·',a.logdeta il)+1,charindex('·',a.logdetail,charindex('·',a.lo gdetail)+1)-(charindex('·',a.logdetail)+1))
as char(10)) as ShmAcno
into ##tblabc
end
from shractivitylog a
left outer join
shrsharemaster b
on a.logkey = b.shmrecid
This statement giving me syntax error. Please help me..
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'case'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'end'.
sample data in a.logdetail
BR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep
2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00·····
·0234578······· ····· ··········
UB··Aqua Duck··Toon Town Central·Punchline Place···02 Sep
2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00·····
Regards.
View 2 Replies
ADVERTISEMENT
Jan 17, 2006
Hi guys,
anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.
so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" .... "week 5".
so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?
regards
terence chua
View 4 Replies
View Related
Jul 27, 2004
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?
Example:
I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.
I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.
BTW - this would be a stored procedure...
Any suggestions would be greatly appreciated.
View 1 Replies
View Related
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
Sep 5, 2007
Hi there,
i have a requirement that a temporary table contains dynamic columns depending on where condition.
my actual table is like
Key
Value
X1
x
X3
x
X5
x
Y1
y
Y2
y
when user select x, the input variable passed to stored proc and the result is shown like
column names
X1 X3 X5 as column headers.
the select query is from temporary table.
these out put is based on the user selection. so the temporary table created with columns dynamically.
please help me out.
please let me know if you didn't understand.
thanks
Praveen.
View 7 Replies
View Related
Jul 20, 2005
HiDose any body know why a temporary table gets deleted after querying it thefirst time (using SELECT INTO)?When I run the code bellow I'm getting an error message when open the temptable for the second time.Error Type:Microsoft OLE DB Provider for SQL Server (0x80040E37)Invalid object name '#testtable'.-------------------------------------------------------------------------------------------cnn.Execute("SELECT category, product INTO #testtable FROM properties")'---creating temporary TestTable and populate it with values from anothertableSET rst_testt = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTableSET rst_testt2 = cnn.Execute("SELECT * from #testtable") '----- ERRORopening the temporary TestTable for the second time (that where the erroroccurred)rst_testt2.Close '---- closing table connectionSET rst_testt2 = nothingrst_testt.Close '----- closing table connectionSET rst_testt = nothingcnn.Execute("DROP TABLE #testtable") '------ dropping the temporaryTestTable'-----------------------------------------------------------------------------------------But when I create the temp table first and then INSERT INTO that table somevalues then it is working fine.'-----------------------------------------------------------------------------------------cnn.Execute("CREATE TABLE #testtable (category VARCHAR(3), productVARCHAR(3))")cnn.Execute("INSERT INTO #testtable VALUES('5','4')")SET rst_testt = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTableSET rst_testt2 = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTable for the second timerst_testt2.Close '----- closing table connectionSET rst_testt2 = nothingrst_testt.Close '----- closing table connectionSET rst_testt = nothingcnn.Execute("DROP TABLE #testtable") '------ dropping the temporaryTestTable'-----------------------------------------------------------------------------------------Does any body know why the first code (SELECT INTO) is not working where thesecond code it working?regards,goznal
View 4 Replies
View Related
Jun 10, 2014
I have created a stored procedure for retrieving column name, as shown below
CM_id, CM_Name, [Transaction_Month], [Transaction_Year], [Invoice raised date],[Payment Received date],[Payout date],[Payroll lock date]
Now I am trying to create a temporary table using the above generated coluimns from Stored Procedure with datatype.
View 3 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 31, 2014
I need to write a select statement that take the upper table and select the lower table.
View 3 Replies
View Related
May 28, 2014
I am trying to create a new column 'COL_4' and, in the same step, create a case statement off of the new col. I know that the code below will not execute. I realize that I could get ride of COL_4 in my code below and concatonate but I need to keep Col_4 in the output.
SELECT
COL_1
,COL_2
,COL_3
,COL_4 = COL_1 + COL_2
,COL_5 = CASE
WHEN COL_1
THEN 'SOMETHING'
END
FROM TABLE_1
;
View 1 Replies
View Related
Jan 30, 2008
Hi!
I have a select question that look like this:
DECLARE @ID uniqueidentifier;
SET @ID = '40bd3052-60f4-414a-99df-ca882c128493';
SELECT
rp.ID AS ReportPackId
,rp.SupplierPartyIdentifier AS SupplierPartyIdentifier
,rpap.AdditionalPartyIdentifier AS AdditionalPartyIdentifier
FROM
ReportPack rp
INNER JOIN ReportPackAdditionalParty rpap
ON rpap.fk_ReportPack = rp.ID
WHERE rp.ID = @ID
The result a get when running the select question is:
ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier
40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1
40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId2
My problem is that the result I want is the following:
ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier
AdditionalPartyIdentifier
40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1
addPartyId2
I always know that the ReportPackId and SupplierPartyIdentifier will be identical for all rows because of the Where condition, and therefore I want all AdditionalPartyIdentifiers to be in columns instead of a new row. Is this possible?
View 5 Replies
View Related
Dec 19, 2003
Example:
Select icount + 1 as icount from table
or
Select counter() as icount from table
The above is wrong ...just a sample to show what I am trying to accomplish.
Is there a function in SQL statement? Thanks.
View 2 Replies
View Related
Apr 24, 2007
What I'm trying to do is this;
I have a table with Year , Account and Amount as fields. I want to
SELECT Year, Account, sum(Amount) AS Amt
FROM GLTable
WHERE Year <= varYear
varYear being a variable which is each year from a query
SELECT Distinct Year FROM GLTable
My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.
What I have done is to defined two variables as follows
Name: varYear (this will hold the year)
Scope: Package
Data type: String
Name:vSQL (This will hold a SQL statement using the varYear)
Scope: Package
Data type: String
Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]
I've created a SQL Task as follows
Result set: Full Result Set
Connection Type: OLE DB
SQL Statement: SELECT DISTINCT Year FROM GLTable
Result Name: 0
Variable Name: User::varYear
Next I created a For Each Loop container with the following parameters
Enumerator: Foreach ADO Enumerator
ADO Object source Variable: User::varYear
Enumeration Mode: Rows in First Table
I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows
Data Access Mode: SQL Command from Variable
Variable Name: User::varYear
However this returns a couple of errors "Statement(s) could not be prepared."
and "Incorrect syntax near '='.".
I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.
Any help would be most appreciated.
Regards,
Bill
View 5 Replies
View Related
Aug 16, 2007
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
View 3 Replies
View Related
Oct 21, 2015
I have the below script to create directories based on my select statement.
create table students
(
f_name varchar(100),
l_name varchar(100)
);
insert into students values ('John','Smith'),
[Code] ...
The result I am getting is truncating the directory names as in the below:
View 4 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Jul 20, 2005
Is it possible to have part of a table name used in a CREATE statementcontained in a variable? Here's what I'd like to do, althoughobviously the syntax of this isn't quite right or I wouldn't be hereasking:DECLARE @TblPrefix char (3)SET @TblPrefix = 'tst'CREATE TABLE @TblPrefix + TestTable (col1 int)The point there is to have a table named tstTestTableThe reason I need to do this is that my ISP will only give me onedatabase to work with and I'd like to have two copies of theapplication I'm developing running at the same time. So I'd like torun the sql script that creates the tables with TblPrefix set to "dev"and then run it again with TblPrefix set to "liv"thankseric
View 3 Replies
View Related
Jan 4, 2007
If we don t have the right to create an SP, can we create a dynamic in memory kind of SP
what s the syntax pls?
Thanks a lot
View 8 Replies
View Related
Sep 24, 2007
Hi guys,
I have a question regarding a locking scheme in MSSQL I hope you guys can help. In Sybase, I am able to specify datarow locking in DDL (ex. create table, alter table). Can I do the same in MSSQL or is there an equivalent option in CREATE TABLE statement in MSSQL? I came across a few articles in MSDN about datarow locking and it seems to me that MSSQL only allows locking through DML... Is that true? Thanks.
View 2 Replies
View Related
Mar 17, 2008
I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?
View 3 Replies
View Related
Sep 3, 1998
When I use comands insert and update with VB5 and ODBC, one temporary stored procedure is created in database tempdb to each command executed.
These stored procedures are deleted only when the connection is closed.
My program use comands insert and update inside a loop, and a lot of temporary stored procedure are generated and full the database tempdb. When it occur, others systems are afecteds.
My questions:
Why it occur ?
Wich have created this stored procedure ?
How to avoid it occur ?
The versions are:
SQL Server 6.5
Visual Basic 5.0
SQL Server ODBC Driver 2.65.0240
View 4 Replies
View Related
Nov 27, 2001
We use sql server 6.5 with service pack 5A.
What are the differences between "select * into" and "Create TABLE". I know
that select * into creates the table in the background and populates the
data from the old table. What I want to know is
1.Are there any benefits in doing a certain way?
2. Are there any risks?
We have a "select * into" query in our code that created problems INTERMITTENTLY
and we are replacing it with "create table" command. We are not sure if
that is going to fix the problem.
View 3 Replies
View Related
Dec 14, 2005
SQLServer2k doesn't let me do a:
CREATE TABLE myNewTbl
AS
select fld1 from myOldTbl
In oracle it works.
Thanks,
Carl
View 6 Replies
View Related
Dec 21, 2006
Hi,
I need to create a table which has the columns from the select statement result.
I tried in this way
drop table j9a
SELECT er.* into j9a
FROM caCase c
LEFT OUTER JOIN paPatient pp ON c.caCaseID=pp.caCaseID
Left Outer JOIN paManagementSite pm ON pp.paManagementSiteID=pm.paManagementSiteID
Left Join exexposure ee ON ee.cacaseID=c.caCaseID
LEFT OUTER JOIN exExposureRoute eer ON eer.caCaseID=c.caCaseID
LEFT OUTER JOIN exRoute er ON er.exRouteID=eer.exRouteID
WHERE c.caCallTypeID =0
AND c.Startdate between '1/1/2006' and '12/1/2006'
AND (ee.exMedicalOutcomeID=4 OR ee.exMedicalOutcomeID=10)
AND pp.paSpeciesID=1
AND c.PublicID_adOrganization_secondary is null
declare @1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,
@9 int,@10 int,@11 int,@12 int,@21 int,@22 int,@23 int,@24 int,@25 int,
@26 int,@27 int,@28 int,@29 int,@30 int,@31 int,@32 int
set @21=(select count(*) from j9a whereRoute_Ingestion=1)
set @22=(select count(*) from j9a whereRoute_Inhalation=1)
set @23=(select count(*) from j9a whereRoute_Aspiration=1)
set @24=(select count(*) from j9a whereRoute_Ocular=1)
set @25=(select count(*) from j9a whereRoute_Dermal=1)
set @26=(select count(*) from j9a whereRoute_Bite=1)
set @27=(select count(*) from j9a whereRoute_Parenteral=1)
set @28=(select count(*) from j9a whereRoute_Otic=1)
set @29=(select count(*) from j9a whereRoute_Rectal=1)
set @30=(select count(*) from j9a whereRoute_Vaginal=1)
set @31=(select count(*) from j9a whereRoute_Other=1)
set @32=(select count(*) from j9a whereRoute_Unknown=1)
The exRoute result is like this
70Ingestion
71Inhalation/nasal
72Aspiration (with ingestion)
73Ocular
74Dermal
75Bite/sting
76Parenteral
77Other
78Unknown
524Otic
525Rectal
526Vaginal
The above giving the errors Msg 207, Level 16, State 1, Line 19
Invalid column name 'Route_Ingestion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'Route_Inhalation'
How to create table j9a.j9a has the columns from select
Thanks in advance
View 8 Replies
View Related
May 6, 2008
I'm trying to get the <Title> node value returned in the select statement below, but cant quite get it to work. I have the <ID> node being returned in the statement, but not the title. Any help is apprecited.
param ids = '<Collection><Content><ID>1</ID><Title>Document 1</Title></Content>< Content><ID>2</ID><Title>Document 2</Title></Content></Collection>'
CREATE PROCEDURE [GetDownloads](@Ids xml) AS
DECLARE @ResearchDocuments TABLE (ID int)
INSERT INTO @ResearchDocuments (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @Ids.nodes('/Collection/Content/ID') as ParamValues(ID)
SELECT * FROM research_downloads INNER JOIN @ResearchDocuments rd ON research_downloads.doc_id = rd.ID Where research_downloads.post_sf = 0
View 5 Replies
View Related
May 7, 2008
I'm trying to get the <Title> node value returned in the select statement below, but cant quite get it to work. I have the <ID> node being returned in the statement, but not the title. Any help is apprecited.
param ids = '<Collection><Content><ID>1</ID><Title>Document 1</Title></Content>< Content><ID>2</ID><Title>Document 2</Title></Content></Collection>'
CREATE PROCEDURE [GetDownloads](@Ids xml) AS
DECLARE @ResearchDocuments TABLE (ID int)
INSERT INTO @ResearchDocuments (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @Ids.nodes('/Collection/Content/ID') as ParamValues(ID)
SELECT * FROM research_downloads INNER JOIN @ResearchDocuments rd ON research_downloads.doc_id = rd.ID Where research_downloads.post_sf = 0
View 6 Replies
View Related
Jul 28, 2000
it seems like i am able to create it. But when i try to access that
temporary table, i get an error "Invalid object".
this is happening only when i try to create local temporary table.
Global temporary table works fine inside the dynamic SQL.
Any Help appreciated.
View 1 Replies
View Related
Oct 16, 2007
Hi,
I'd like to create a temporary table (# or ## table) and have it available in DataFlow to work with it. I see I can create it using "Execute SQL Task" component in ControlFlow; but it's not available in DataFlow. It seems it should be created by a DataFlow component to be seen in the same DataFlow.
Any Idea on this?
View 2 Replies
View Related
Dec 10, 2007
*Groan* Sorry. One more.
ERD above is what my database looks like (ignore it's in Access, database is in SQL 2005)
I have this code:
SELECT orderID, orderAmount = SUM(customerID)/customerID FROM orders
GROUP BY orderID
-- When Sum of the customer ID (and then) divided by the customer ID > 1
HAVING orderAmount > 1
which doesn't work because I never did find out how you make a column in the results to output your maths in.
orderAmount doesn't exist as a column in the database, but for this query it should show only those customers who have ordered more than once with the company.
Thanks again for any replies.
View 4 Replies
View Related
Apr 7, 2007
Dear All,
This is my first post to this forum.
I would like to know if there is any way to restrict users from creating temp tables.
Problem: I am facing problems with lots of temporary objects getting created in my database. The users have read-only access to the database for adhoc-querying purpose through QA. Yet they are able to create temporary tables in tempdb database taking lot of resources on tempdb disk causing abnormally high growth of tempdb.
Thanks in advance.
Best Regards,
Chetan Jain
View 6 Replies
View Related
Sep 6, 2006
I m trying to create a global temporary table whose data will be deleted after the end of the session..
the DDL in oracle is
CREATE GLOBAL TEMPORARY TABLE STUDENT
(
name CHAR(20),
) ON COMMIT DELETE ROWS ;
I want to know the corresponding DDL in SQL Server..
when i try the following
CREATE TABLE ##STUDENT
(
name CHAR(20),
)
the table gets deleted at the end of the session..help needed
View 4 Replies
View Related
Apr 10, 2002
In oracle we can create table like
CREATE TBALE AS SELECt * FROM TAB1
How can I do this in SQL Server. I want to craete table on basis of following staement.
select * from t1 inner join t2 on i1.id=t2.id
Thanks in advance.
View 1 Replies
View Related
Jan 31, 2006
I'm currently working on a web frontend which was designed for mysql but is being moved over to mssql.
The current system uses a select query to create a table:
Code:
CREATE TABLE newtable AS SELECT afield FROM atable
This doesn't seem to work in mssql.
Is there an equivilant in mssql which can be used?
View 2 Replies
View Related
