Rearraigning Table Data In Query Output.

Apr 18, 2008

I am creating queries and reports in an Access front end (let the groans begin)...it is what they gave me to work with!

This particular query is pulling information from several different tables and manipulating it the way I want. However, there is one table that I am trying to pull info from that has the data arranged differently than all the others.

e.g.:

Name Drive Capacity Free_Space
Computer A C 40 10
Computer A D 200 175
Computer B C 80 55
Computer B D 500 445
Computer B E 500 365

I want that to look like:

Computer A C 40 10 D 200 10
Computer B C 80 55 D 500 445 E 500 365

I would prefer to do this within the existing query, without creating a new (permanent) table because the info needs to be real-time.

Any ideas/tips/leads?

View 7 Replies

Create Table From Query Output

May 5, 2008

I have a query that is spliting up FullName into FistName and LastName columns. Need help writing a query that dump that output into a new table.

Thanks
Kevin

View 6 Replies View Related

Create Table From Query Output

Oct 25, 2013

I have the following sql query in vb.net and ms access , how do I create a table from the query result ?

SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1

I have tried but it does not work

create table tble10 as SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1

View 14 Replies View Related

Query Output As Text Rather Than A Temp Table..

Dec 3, 2006

Hi Guys,

I'm trying to figure out how to output a query as text instead of a tempory table...

I thought perhaps i could use this:

SELECT PRINT CustomerID FROM ORDER_TABLE

But that doesn't work

PRINT CustomerID FROM ORDER_TABLE

Doesn't work either...

I need this so i can print a customised invoice in SQL. That is, unless their is a better way of tackling this problem?

thx for reading :)

--Philkills

View 5 Replies View Related

How To Output Data To A New Table

Jul 10, 2006

Hi

I run a SQL query to select a few colums of data using a select statement, I want the output to be stored in the new table which can be defined in the SQL statement how can I do it?

Your helpwill bw highly appreciated.

View 5 Replies View Related

Getting Data (rows) And Output Params From A Query

May 15, 2005

I have a stored procedure that I use to return Purchase Orders from our PO system. It returns the data rows for PO's that match the criteria passed in (including the page to show etc.) + it returns two output params, Number of rows and Number of Pages.
Using query analyzer I can confirm the query works exactly as we want. I cannot however seem to get the data out to our ASP.net app.
Here is a function that I use in one of my classes:
<code>
Function fnListPOsByCoordinatorIDPaged(ByVal strCoordinatorID As String, ByVal intPOStatusID As Int16, _
ByVal intUserTypeID As Int16, ByVal intArchived As Int16, _ByVal intPageNum As Int32, ByVal intPerPage As Int32, _ByVal strConn As String) As SqlClient.SqlDataReader
Dim dr As SqlClient.SqlDataReader
SqlConnection1.ConnectionString = strConn
prListPOByCoordinatorPaged.Parameters("@CoordinatorID").Value = strCoordinatorIDprListPOByCoordinatorPaged.Parameters("@POStatusID").Value = intPOStatusIDprListPOByCoordinatorPaged.Parameters("@UserTypeID").Value = intUserTypeIDprListPOByCoordinatorPaged.Parameters("@Archived").Value = intArchivedprListPOByCoordinatorPaged.Parameters("@PageNum").Value = intPageNumprListPOByCoordinatorPaged.Parameters("@PerPage").Value = intPerPage
SqlConnection1.Open()dr = prListPOByCoordinatorPaged.ExecuteReader(CommandBehavior.CloseConnection)
Me.Pages = prListPOByCoordinatorPaged.Parameters("@Pages").ValueMe.Rows = prListPOByCoordinatorPaged.Parameters("@Rows").Value
If Me.Rows / intPerPage > Me.Pages Then Me.Pages = Me.Pages + 1End If
Return dr
prListPOByCoordinatorPaged.Dispose()SqlConnection1.Close()SqlConnection1.Dispose()
End Function
</code>
It does not crash, it returns my data reader with the correct records. Unfortunately my property values are returned as 0. They should have values.
Anyone know how to do this?
Thanks.

View 1 Replies View Related

Output Query Data To Text File

Apr 17, 2012

I am running SQL Server 2000 and need to output query data to a text file. If I run the following query (Below) using XP_CMDSHELL and BCP, it runs fine and creates a text file with the data output.

However, I need to change the WHERE Field1=10 to a string value WHERE Field1='abc'. When I try to do this I get a general error on the Field1='xyz'. I tried to change the quotes, etc but I am still getting the error.

Command:
Exec master..xp_cmdshell 'bcp "SELECT Field1 FROM Table WHERE Field1=10" queryout c:filename.txt -U UserName -P Password /S SQLServerNam /c'

View 3 Replies View Related

Insert Record In Table - Select Output Of Query

Oct 14, 2013

I had a query and i need to insert record in table and also want to select output of query.

Actually I need to insert and show data in same query.

;with cte as (
select id , status
from tbl_main
where id = 15555
)
insert into testinsert (id , status)
select * from cte

View 3 Replies View Related

WMI Data Reader With Output To Table

Aug 31, 2007

Hi,

maybe you can help me with a little SSIS task. I want to fetch some wmi infos from serveral servers with the wmi reader. to read something is no problem, but whats the best way to bring them to a table or the control flow? Read from the generated file cant be a way. I also tried the variable. How do I get them to data flow. I tried a script task but i cant read the resultset. What format has the variable? ado.net, ado?
Whats the best way to get the results into a table?

regards
andreas

View 4 Replies View Related

SQL Server 2008 :: Query To Show XML Output For Hierarchical Data?

Mar 10, 2015

selecting table data in hierarchical XML .

Here is the sample table DDL and data

Declare @continents Table
(
id int identity (1,1)
,continent_id int
,continent_Name varchar(100)
,continent_surface_area varchar(100)
,country_id int

[code]....

View 8 Replies View Related

SQL Server 2008 :: Storing Dynamic Query Output In Temp Table

Apr 6, 2015

I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.

Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!

View 3 Replies View Related

Stored Procedure | Selects Some Data |query The Results It Created And Output

Dec 2, 2005

I can create 1 view of a database then use that views results to query

but i want do this with a stored procedure
pass some data to it, so it selects some data

query the results it created and output this data.

ie

pass 2 values to the procedure
(
@FirstValue int,
@secondValue int output

)

select from a database with the firstpassed value

Select *
From TableName
Where ID = @FirstValue

Then
using the results from the above select

Select *
From theResultsOfAbove
Where ID = @SecondValue

any ideas would be fabo !

View 6 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!

But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

Output And Error Output Write The Same Table At The Same Time, Stall The Process.

Aug 30, 2006

Hi

I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....

The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.

What can I do to avoid such situation?

Thanks in advance,

JD

View 4 Replies View Related

Query Produces Jumbled Output / Output Not In Sequence

Jul 23, 2005

Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn

View 5 Replies View Related

SQL Server 2012 :: Select Query To XLS Output - Export Data In Columns To Separate Tabs In Excel

Apr 21, 2015

Using below script to export the select statement result to .xls

declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.

how to export the data in columns to separate tabs in excel.

View 2 Replies View Related

SQL Server 2012 :: Rollover Prior Quarter Data To New Quarter In A Table - Output Identity Values

Jun 5, 2014

I have a process to rollover prior quarter data to new quarter in a table.

For example, i have a table with (col1, col2, year, qtr) with data like ( Note: col1 is identity(1,1) )

1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1

Now when i run my process, above 3 records will be rolled over new quarter 2014 Q2 and the table will be like

1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
4,'today',2014,2
5,'tomorrow,2014,2
6,'friday',2014,2

Row 1 with identity 1 has rolled over to new quarter row 4 with identity 4 ( qtr fields are changed )
Row 2 with identity 2 has rolled over to new quarter row 5 with identity 5. Same with last row as well.

Here, i have another table called "ident_map" with columns like (old identity, new identity ) and during rollover i am supposed to load ident_map table with old and new identity. So after rollover is complete, ident_map table should look like

1,4
2,5
3,6

I know using output clause I can capture the new identity values. 4,5,6 in this case. But is there any way to capture both old identity and new identity during rollover so that i can load the ident_map table with old and new identity.

View 9 Replies View Related

Query To Return Records Where One Table Is Void Of Linked Data In Another Table

Feb 13, 2008

I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.

Thanks in advance,

Mark

View 5 Replies View Related

Query To Move Certian Data From Sql Table To Access Table

Dec 14, 2007

hi all,
is there any query to move certain data from a sql data to access table through query. i am having a requirement where i have to fetch the records from a sql table that falls within a specified range to a ms access table. is this possible through a query.

thanks

View 5 Replies View Related

Update Query Containg Static Data And Data From Another Table.

Sep 28, 2006

Hi,First post so apologies if this sounds a bit confusing!!I'm trying to run the following update. On a weekly basis i want toinsert all the active users ids from a users table into a timesheetstable along with the last day of the week and a submitted flag set to0. I plan then on creating a schduled job so the script runs weekly.The 3 queries i plan to use are below.Insert statement:INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)VALUES ('user ids', 'week end date', '0')Get User Ids:SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'Get last date of the weekSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)I'm having trouble combing them as i'm pretty new to this. Is the bestapproach to use a cursor?If you need anymore info let me know. Thanks in advance.

View 4 Replies View Related

Change Data Table From System.data Into Sql Query

Apr 4, 2007

i someone had teach me how to write a query in datatable. however i need to get the data out from my database rather than the data table. can someone teach me how should i do it?esp at the first like.... like DataTable dt = GetFilledTable() since i already have set of data in my preset table i should be getting data from SqlDataSource1 right ( however i am writing this in my background code or within <script></script> so can anyone help me? protected void lnkRadius_Click(object sender, EventArgs e) { DataTable dt = GetFilledTable(); double radius = Convert.ToDouble(txtRadius.Text); decimal checkX = (decimal)dt.Rows[0]["Latitude"]; decimal checkY = (decimal)dt.Rows[0]["Longitude"]; // expect dt[0] to pass - as this is our check point // We use for rather than fopreach because the later does not allow DELETE during loop execution for(int index=0; index < dt.Rows.Count; index++) { DataRow dr = dt.Rows[index]; decimal testX = (decimal)dr["Latitude"]; decimal testY = (decimal)dr["Longitude"]; double testXzeroed = Convert.ToDouble(testX -= checkX); double testYzeroed = Convert.ToDouble(testY -= checkY); double distance = Math.Sqrt((testXzeroed * testXzeroed) + (testYzeroed * testYzeroed)); // mark for delete (not allowed in a foreach - so we use "for") if (distance > radius) dr.Delete(); } // accept deletes dt.AcceptChanges(); GridView1.DataSource = dt.DefaultView; GridView1.DataBind(); }

View 2 Replies View Related

Query Table Without Data

Mar 13, 2006

I'm writting a stored proc that has to query 2 tables. One table is a table of "jobs" and the other table contains jobs that have been invoiced (2 tables are jobs and invoicedJobs). The invoiced table only contains records for jobs that have an invoice and not jobs that do not have an invoice.
My dilemma is that I need to write a query that can retrieve all un-invoiced jobs in my stored proc. You can't rightly join a table that does not have a relationship with another table (can you?). So in my query for jobs with an invoice, I simply join my jobs table and invoice table based on a job id that both tables contain. But how could I perform a query for jobs that do not exist in my invoice table inside my stored proc? Any help would be greatly appreciated.

View 2 Replies View Related

Query To Get Data From Same Table

Nov 19, 2013

I have table with 4 columns Environmnet, LocationName, SettingName, Settingvalue

Table looks like

Environmnet, LocationName, SettingName, Value

values are
Prod, All, abc, abcdefrr
Test, All, gfd, rerwe
All, Newyork, fgsdf, dfgs
Demo, DC, erger, stgt
All, virgenia, fgaer, sdfasd
production, Virgenia, dfgd, drtert

I want query that displays for the condition where environment = all or production and locationname =all or virgenia

Environmnet, LocationName, SettingName, Value,

Production, All, abc, abcdefrr
All, virgenia, fgaer, sdfasd
production, Virgenia, dfgd, drtert

I want to display data in above result .

View 6 Replies View Related

Query On Data In Another Table

Mar 14, 2008

query on data in another table.
I have two tables.
Table 1
EventIDEventName
01234Jackpot

Table 2
EventNameAmount
Jackpot 123.00
EmptyCanister14.00

I would like to query Table2 based on the events in
the Table1. How do I do this ?
Not using select * from Table2 where EventName = 'Jackpot'

View 4 Replies View Related

Query With Data In Another Table...

Dec 12, 2007

Hi,

I have the following problem... For these two tables I have to query, all "nrtelefone" from PABX where tronco is not between tronco ini and tronco fim....and tronco is not in there clause "troncos_cliente", the the user will provide to us...
Let's suppose that the troncos_cliente has the value "NOT BETWEEN 32 and 55", and in tronco ini there is "10" and in tronco fim there is "20" so I need to query all nrtelefone that is not between 32 and 55 and not between 10 and 20, but the problem is with cursor this process will take to much time cause htere is too many clients..

Code Block
CREATE TABLE [LOGIN] (
[COD_CLIENTE] [varchar] (50),
[TRONCO_INI] [int] NULL ,
[TRONCO_FIM] [int] NULL ,
[TRONCOS_CLIENTE] [nvarchar] (4000)
)

Code Block
CREATE TABLE [PABX] (
[COD_CLIENTE] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[NRTELEFONE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[TRONCO] [int] NULL CONSTRAINT [DF_PABX_TRONCO] DEFAULT (9999),
)

So is there a way to make this query without using cursors ? (NOTE: I'm using cursor cause I need to build the T-SQL at runtime just because of the TRONCOS_CLIENTE that the user will provide the SQL clause)

Thanks

View 4 Replies View Related

Output Data Is 2 TIMES The Input Data.SSIS.Newbie

Apr 10, 2008

Hello All,
I am migrating data from one database to another. I am using Multicast to seperate (legal street,legal mail and legal city) and (mail_street,mail_state,mail_zip,mail_city) also later after UNION of the above I am doing two lookups as I had to get contact ID and Customer ID from other two tables. In UNION i am matching (Mail street legal street) and so on.

I am getting double the data in the output. my input data is 1000000 and im gettin 2000000.

What could be the reasons. Please help me out.

Thank You

View 5 Replies View Related

SQL Query - Insert Data From One Table Into The Other

Oct 12, 2007

OK, this one should be easy for you folks but my mind is drawing a blank.
Can someone give me an example query to copy data from one table to another? Both tables already exist and both tables have the same table definition. tblA has data tblB is completely empty. I just want to copy from tblA into tblB.

I dont want to use the GUI, nor do I want to do a "Select * Into..."

Thanks

View 1 Replies View Related

SQL Query To Insert Data To A Table

May 21, 2007

Hi all...Can anyone give clue in doing below?Let say i have a table A with the following 3 fields. 1)Date2)Title 3)Status.i need to insert into table B (also have the same 3 fields) from thistable A with the condition where Title is "Alarm" and Status is "ON".This can be done by a simple "INSERT" query.However, there's a case for table A in like below:Date Title Status------ ------ ----------5/7/07 1:05:23am ALARM ON5/7/07 1:05:24am ALARM ON5/7/07 1:05:25am ALARM ACK5/7/07 1:05:25am ALARM ONBased on the table A above, i only need to insert from table A intotable B the first 2 ALARM(ON)s. The third ALARM(ON) which has the sameDate with ALARM(ACK) needs not to be inserted into table B.How can i write a simple SQL query which can insert all ALARM(ON)swhich doesnt have same date with ALARM(ACK)?Thanks. Pls help.Albert

View 1 Replies View Related

Query Data Type Of Column In DB Table

Aug 14, 2006

Hi All
I want to retrieve the data type of table column and validate the input data whether same as data type of table column before insert into database. Any suggestion?
I use asp.net + msde

Thank you.

View 6 Replies View Related

Query To Filter Duplicate Data From Table.

Apr 8, 2004

i am having 3000 records in table. now i want take out the duplicate from that table.

for example: i want to find out the duplicates of 'CompanyNames'.

help needed to write query for this operation.

View 2 Replies View Related

Query For Find Data Last Inserted In A Table

Apr 9, 2015

I need to find the history in SQl server.

I need to find the data, When was data last inserted/updated/deleted in a specific table?. is it possible?

Table name: Employee

View 1 Replies View Related

Editing Table Data In Query Analyzer

Jul 23, 2005

Hi All !I open a table in Query Analyzer by right clicking on it andselecting 'Open' and when the data is displayed in the view pane Iwould like to be able to edit it. It seems however that tables markedas read only (as shown on the top bar e.g. SQL Query Analyzer - [OpenTable ServerName.DBName.dbo.TableName(read-only)] ) can not be editedwhereas the one that do not have that attribute infront of the name Ican edit. Is there a way I can change the read only attribute of atable so that I can edit data directly in the view pane?Please let me know if there is !Thanks a heap ! :)Harkirat