Transact SQL :: How To Add Columns To Temporary Table In Loop
Aug 27, 2015How to add columns to temporary table in loop with sample code.
View 12 Replies
ADVERTISEMENT
Temporary Table And Sparse Columns
Jun 27, 2015I have recently converted a table with many columns that are now using sparse columns.However I have now found that a lot of my queries are not working.It seems to be due to the fact that I am using a lot of #Temp tables in my queries along with the SELECT... INTO... method to get data into the temporary table.This worked previously fine, however the sparse columns are not coming across to the #Temp table.Here is an example of what previously working fine:-
(This obviously just grabbed everything in the Client table)
Select *
INTO #temptable1
from ClientI have tried the following without any success (Knowing I now need to specify the column names)
Select ClientId, Val1, Val2, Val3, Val4, Val5, Val6
INTO #temptable1
from ClientVal3, Val4, Val6 and Val6 are sparse columns.
The query runs fine, however when I try and do a select of any of the sparse columns from #temptable1, it just says they do not exist.After reading up. It would seem I can not use the SELECT... INTO... however I have not found any other alternatives that I can convert my code to?I have a lot of quite complex queries that use this method and I am looking for something that I can convert them all over to.
Variable Number Of Columns In A Temporary Table
Nov 8, 1999How do I create a temporary table in a stored procedure with differeent number of columns?
That is: sometimes ten columns, sometimes 24 etc.
How To Create Dynamic Columns In A Temporary Table
Sep 5, 2007Hi 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.
Transact SQL :: Temporary Table Created In Linked Server
Sep 20, 2015I'm executing a sp name [uspGeneral_Getinfo]. It return a global temporary table's name and I want to get data from that table.
[192.168.2.11] = Linked Server
Declare@LinkedServer varchar(40)='[192.168.2.11].DBPharm.dbo.',@OutPutTableName varchar(50)=' ',@SQL nvarchar(max)Set@LinkedServer=Rtrim(Ltrim(@LinkedServer))Set@SQL=' Declare @OutPutTableName varchar(50),@SQL nvarchar(max) EXEC '+@LinkedServer+'[uspGeneral_GetDomainDataNew] 9,null, @OutPutTableName OutPut '+' Set @SQL='''+'Select * from '''+'+ @OutPutTableName'+' Exec sp_executesql @SQL 'Select@SQL
Above code are executing from another server [192.168.2.10].But getting an error that .I can't able to search where the temp table created in linked serve tempdb or ...?
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Msg 208, Level 16, State 0, Line 38
Invalid object name '##table1FD1B81Bx4EAFx4FFDx9F6Fx15B77B6445F'.
Transact SQL :: Usage Of OUTPUT Clause Depending On Temporary Table
Jul 14, 2015Suppose we have the following table in our database;
CREATE TABLE [dbo].[PERMISSION](
[ID] [int] IDENTITY(1,1) NOT NULL,
[USERID] [int] NOT NULL,
[STARTTIME] [smalldatetime] NOT NULL,
[ENDTIME] [smalldatetime] NOT NULL,
[REASON] [nvarchar](250) NULL,
[PERMISSIONTYPEID] [int] NOT NULL,
[code]....
This code works pretty well. But I don't want to do this with "select" since there is OUTPUT clause in T-SQL. So the CommandText property will be changed into this;
command.CommandText = @"insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @outID, @outCREATETIME
values(2, getdate(), getdate(), 'sdfg', 1, DEFAULT);";
well, not only this statement gives an error while executing; but also, no such usage defined in the
documentation of OUTPUT Clause. Actually the documentation tell us to use Temporary Tables for that. So I have to change CommandText into this;
command.CommandText = @"DECLARE @MyTableVar table(ID int, CREATETIME smalldatetime);
insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @MyTableVar
code]....
No temporary tables required; thus, no "type spesific" things required. This way is easier to create dynamic queries though. Only,the RETURNING INTO clause.So, I was wondering; why MS-SQL (T-SQL) forces users to "declare a temporary table (type specific)" and "execute select on the temporary table in order to assign values to output parameters" while using "the OUTPUT Clause". Comparing to the Oracle's sample I'm just using "the RETURNING INTO Clause in order to assign values to output parameters" and that's it; very easy and dynamic. To Summarize, Oracle's RETURNING INTO Clause is better than MS-SQL's OUTPUT Clause, but in fact they're doing the same job.
Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?
Apr 29, 2015I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
Transact SQL :: How To Update Table Without Using While Loop
Nov 15, 2015I have two tables i have to update table2 using table1 without using while loop.
example given below.
Table1
rid
id
amt
firdate
lastdate
1
1
500
[code]....
Loop Through A Recordset To Populate Columns In A Temp Table
Jul 23, 2005I 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 RelatedIntegration Services :: Using Foreach Loop Container To Elaborate A Table With Two Columns - SSIS 2012
May 6, 2015In order to update an Oracle table target from a SQL Server table source I need to use a Foreach Loop Container, so I can loop on the rows of the SQL Server table source. This source table has two columns: the old identifier to update and the new identifier to apply. I must use the value of the old identifier to filter the Oracle rows to update, while the new identifier is the new value to assign to the filtered old identifier.
I already know how to use the Foreach Loop Container when it is necessary to loop on an unique column of a table/view (using an object variable, using a Foreach ADO enumerator, etc.), but I need to loop on two columns.
Transact SQL :: Find Max Of 2 Columns In One Table?
Jul 23, 2015I am having trouble trying to find the max of 2 columns in one table. I've tried using a common table expression and a subquery, but can't seem to get the correct results. I want to get the max from refnum, then the max "number" associated with that max refnum along with the date and decision
Table
IDCustomerRefnumnumberdate decision
16511114/17/2015Approved
16521125/1/2015Declined
16531216/10/2015Approved
16542116/15/2015Tentative
Expected
Customer 1 had a max of refnum of 2 and 1st one on number
IDCustomerRefnumnumberdate decision
16531216/10/2015Approved
16542116/15/2015Tentative
Transact SQL :: Table Name Followed By Columns Names In A Single Row?
May 12, 2015I am able to get a list of columns in a table from the query I have written shown below:
select sc.name ColumnNames,st.name TableName from sys.columns sc inner join sys.tables st on sc.object_id=st.object_id
order by st.name
But I am looking for the resultset with the format below:
TableName Columns
employee employeeid,employeename,employeesalary
order orderid,address,price
Transact SQL :: How To Get Distinct Count Of All Columns Of A Table
Dec 3, 2015I have a table called Employee which have 6 columns. This table are having 1000 records. Now I want to know the distinct value count of all these 6 columns as well as normal count. like this:
ColumnName DistinctCount NormalCount
Id 1000 1000
Name 1000 1000
Phone 600 600
PINCode 200 1000
City 400 1000
Gender 2 1000
Transact SQL :: Join Same Table And Insert Values In Different Columns
Aug 7, 2015I would like to compare values in the same table and get the single record with different values in the multiple columns.For table tab1, ID is my key column. If type1 is active (A) then i need to update X else blank on Code1 column and if type2 is active (A) then i need to update X else blank on code2 column. Both type1 and type2 comes from same table for same ID..Below is the example to understand my scenario clearly....
declare @tab1 table (ID varchar(20), dt date, status varchar(1), type varchar(10))
insert into @tab1 values ('55A', '2015-07-30', 'A', 'type1')
insert into @tab1 values ('55A', '2015-07-30', 'C', 'type2')
insert into @tab1 values ('55B', '2015-07-30', 'C', 'type1')
insert into @tab1 values ('55B', '2015-07-30', 'A', 'type2')
[code]....
Transact SQL :: How To Return Content Of Table As Columns In Query
Oct 1, 2015I have
Customer table, tblCust: ID; Name
1 Peter2 Mary
Product table, tblProduct: ID; Name
1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
Name Banana Orange Apple
Peter 0 20 0
Transact SQL :: How To Count Number Of Specific Columns Of Table
Jul 11, 2015I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.
i.e. EmployeeID: 001
week1: 40
week2: 24
week3: 24
week4: 39
This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?
Transact SQL :: How To Convert Table Columns Into Rows In 2005
Aug 27, 2015i have following table columns and i want to convert these all columns into row
SELECT [CASHINHAND]
,[CASHATBANK]
,[DEPOSITS]
,[ACCRECEVABLE]
,[SUNDRYDEBTORS]
,[LOANANDADVANCES]
[Code] ....
required output looks like
CASHINHAND 118950
CASHATBANK 200
DEPOSITS 3000
ACCRECEIVABLE
25000
Transact SQL :: Generate A Report Based Of Columns In Each Table
Oct 31, 2015I have 5 different tables with same structure. Each table has an account column and another column count. I am trying to generate a report based of the columns in each table. Account is same in all tables with same account numbers but count will be different.
Eg:
Table 1 has Account Count
Table 2 has Account Count
Table 3 has Account Count
Table 4 has Account Count
Table 5 has Account Count
I want output as:
Account Count Account Count Account Count Account Count Account Count
Transact SQL :: Adding New Columns To Server 2012 Table
Apr 28, 2015I am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, can you tell me why one location is better than another location?
View 12 Replies View RelatedTransact SQL :: Split String As Columns And Insert Into Table
Aug 20, 2015I have a string ,want to split the values after every space as column value and insert them into a table
1306453 0 0 0 0 0
col1 col2 col3 col4 col5 col6
1306453 0 0 0 0 0
Transact SQL :: Multiple Inserts Different Columns And Tables Into One Table
Oct 12, 2015I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL
Drop table Bezeichnungen;
GO
create table Bezeichnungen
(
Artikelnummer nvarchar(18),
Artikelbezeichnung nvarchar(80),
Artikelgruppe nvarchar(13),
[code]...
Transact SQL :: Unpivot With Dynamic Columns Or Redesign Relational Table?
Sep 3, 2015I want to use column name to be join another tables.
I have invoice table to store detail of invoice and post some column ' s record to another table .
Invoice table
Invoice_Name | Invoice_Amount | Invoice_Vat | Invoice_Total
Inv001 | 1000 | 70 | 1070
Account_table
Account_No | Account_Number | Data_Source
JV001 | 1111 | Invoice_Amount ---->1000
JV001 | 1112 | Invoice_Vat ----> 70
JV001 | 1113 | Invoice_Total ----> 1070
I want to join Invoice table to Account_table
ON Invoice_Amount , Invoice_Vat , Invoice_Total with Data_Source
The way i got so far I unpivot Invoice table column into row and join with Account_table .
The problem is , if the column in Invoice_table are created , I must used dynamic columns to do this in sql query.
Transact SQL :: Select Multiple Columns From Table But Group By One Column
Jun 17, 2015I have a SQL query like this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
As per this query I got the result like this
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 1 1
AED BNT 12 1
AED SCN 1 1
AED SNT 1 3
[Code] ....
But I wish to grt result as
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 13 2
AED SCN 1 1
AED SNT 11 7
AFN BPC 8 6
[Code] ....
I also tried this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
Transact SQL :: Function To Return Comma Delimited Values From Table Columns
May 12, 2015I've to write a function to return a comma delimited values from a table columns
If a table has Tab1 ( Col1,Col2,Col3).
E.g. as below ( the columnName content I want to use as columns for my pivot table
CREATE FUNCTION [RPT].[GetListOfCol]
(
@vCat NVARCHAR(4000)
)
RETURNS @PList
AS
BEGIN
SELECT @PList += N', [' + [ColumnName] +']'
FROM [ETL].[TableDef]
WHERE [IsActive] = 1
AND [Category] = @vCat
RETURN;
END;
I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....
Transact SQL :: How To Bulk Insert Rows From Text File Into A Wide Table Which Has 1400 Columns
Feb 3, 2010we can easily load a file into db tables. However, my main concern here is the number of columns in the file. A text file TEXT_1400.txt has 1400 columns. I am unable to load data to my db table using BCP or BULK INSERT commands, as maximum of 1024 columns are allowed per table in SQL Server 2008.
We can still go ahead and create ‘Wide Table’ (a special table that holds up to 30,000 columns. The maximum size of a wide table row is 8,019 bytes.). But when operating on wide table, BCP/BULK INSERT commands still fail. After few hours of scratching my head over BCP and BULK INSERT, I observed that while inserting BCP/BULK INSERT commands are unable to identify SPARSE columns and skip these columns, which disturbs column mapping and results in data conversion and trancation errors.
Is there any proper way to load this kind of files into the db table?
Transact SQL :: Find Unmatching Columns Between Two Tables While Applying A Change Column Prefix Table
Nov 16, 2015For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
1, View1, Column4
2, View2, Column4
2, View2, Column5
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4
View2, Column4
View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
[Code] ....
Loop Though Table Using RowID, Not Cursor (was Loop)
Feb 22, 2006I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure
Aug 29, 2007which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View RelatedPossible To Have Temporary Rows In A Non-temporary Table? Lifespan On Rows..
Mar 14, 2004I 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?
PL-SQL LOOP Equivilant In TRANSACT-SQL
Jul 20, 2005Hi,I need to convert this anonymous pl-sql block into transact-sql to runin Microsoft SQL Server Query Analyser.Does anyone know what the conversion syntax would be?I know that to declare variables to use the @.PRINT for printing the output.No idea for the LOOP.Any suggestions greatly appreciated.Many thanks.Thiko!____________________________________________SET SERVEROUTPUT ONDECLARErecordcount NUMBER;BEGINDBMS_OUTPUT.ENABLE(5000000); -- Sets buffer size.recordcount := 0;LOOPINSERT INTO RecoverTest VALUES (SYSDATE - (recordcount / 4));COMMIT; -- frees up rollback segment;recordcount := recordcount + 1;DBMS_OUTPUT.PUT_LINE('INSERT: ' || recordcount || ' - 45000 ');EXIT WHEN recordcount > 45000;END LOOP;END;/
View 1 Replies View RelatedLoop Through All Tables And Columns
Oct 26, 2005Is there a way to change the collation against all the tables and columns within a database?
View 2 Replies View RelatedTable Names In Stored Procedures As String Variables And Temporary Table Question
Apr 10, 2008How 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.
Transact SQL :: Pass Filenames To A Loop
Aug 27, 2015I’m working on building a file list from a directory I have and wish to loop through the filenames. I’ve worked out I can get the list using
EXEC
xp_dirtree @path, 10, 1
I’m just not sure out I pass this to my loop 1 element at a time.
E.g. Let’s say there are 10 files in my folder, I wish to loop through each name using my @Filename parameter
DECLARE @Path
varchar(50), @FileName
varchar(20)
SET @Path
= 'C:myFiles'
EXEC
xp_dirtree @path, 10, 1
[Code] ....