Select From Table - Xml File
Nov 8, 2005Hi. How I can do a query on datatype ntext (xml file) in "SQL Query Analyzer" when I need all data? When I get select from this table, I get back
only first 256 bytes (chars)??? Thanks
ADVERTISEMENT
SQL Server 2012 :: SELECT INTO - Importing A Text File Into A New Table?
Jun 6, 2015how to import a text file with a list of NI numbers into a new table with a column to list all the NI numbers? I think I use the Select INTO clause, but not sure how to do this?
View 1 Replies View RelatedIs There A Method To Convert Select * From Table To Select Field1,field2,...fieldn From Table ?
Nov 29, 2007Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
Thanks
SQL Server 2012 :: Select Statement That Take Upper Table And Select Lower Table
Jul 31, 2014I need to write a select statement that take the upper table and select the lower table.
View 3 Replies View RelatedDeclaring A Table Variable Within A Select Table Joined To Other Select Tables In Query
Oct 15, 2007Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
Select * From Table Is Processed Much Faster Than Select MyField From Table ¿?¿?
Oct 1, 2007
I have a query that has the following structure
Select *
From Table
Where Condition And ... (some 'Exists' conditions)
When I run the query using field names the query gets much slower, and I cannot understand Why!
Select MyField
From Table
Where Condition And ... (some 'Exists' conditions)
I'm talking about three times slower using the Select MyField sintax.
Any ideas???
DB Engine :: Unable To Select Data From A Table Even After Providing Select Access
Aug 28, 2015I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
Default Table Owner Using CREATE TABLE, INSERT, SELECT && DROP TABLE
Nov 21, 2006For 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 RelatedHow To: Create A SELECT To Select Records From A Table Based On The First Letter.......
Aug 16, 2007Dear 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
Generate A Separate Txt File For Each Account In A Table, Need To Join Tables To Get Details, And Specify Output File Name?
May 16, 2008Hey,
I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?
I have table1 contain account numbers and output-filename for each account,
I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.
Here is my code using bcp (bulk copy)
DECLARE @RowCnt int,
@TotalRows int,
@AccountNumber char(11),
@sql varchar(8000),
@date char(10),
@ArchPath varchar(500)
SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT @TotalRows = count(*) FROM table1
--select @ArchPath
WHILE (@RowCnt <= @TotalRows)
BEGIN
SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
--PRINT @AccountNumber --test
SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = '''
+ @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
--PRINT @sql
EXEC master..xp_cmdshell @sql
SELECT @RowCnt = @RowCnt + 1
END
Select * From Table Does Not Select All Records
Aug 17, 2005I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
Any suggestions?
Thanks!
GordonG
SQL 2012 :: How To Do Selective Read Of File Stored In File Table
Jul 2, 2015I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.
Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.
I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.
Need Help W/ Postback To 'Customers' Table On Form Using Select Query From 'Parameters' Table
Dec 20, 2007I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other' populate the drop down list for 'Type').
The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.
HOW TO SELECT ROWS IN THE MASTER TABLE WITH NO RELATIONED DATA IN DETAILS TABLE
Dec 7, 2007I have the following data
MASTER
id
name
DETAIL
id
master_id
name
I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.
How can I do that???
Using Result Of A Select Statement From One Table To Add Data To A Different Table.
Mar 17, 2008I 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 RelatedSelect Columns In 1 Table With Provision In 2nd Table
Jan 20, 2014I have two tables with the common column-name/ The first one include such columns as the name of football team, its country and budget. The second one include the team name, victories (quantity of it), lost games, and season. So I have the task to select such column as name, victories, lost games in 2nd table but only that ones that has budget over 1 mln? How to build select statement SELECT name, victories, lost g/ from TABLE2 WHERE budget >1000000 from TABLE1? Or should I equates the table1.name=table2.name??
View 6 Replies View RelatedSelect Column From Table 2 If Not Exist On Table 1
Oct 16, 2014I am trying to pull a min value of a date column from table 1, if table 1 does not have any record I need pull a date column from table 2. Table 2 will always have a unique record ID (No duplicate ID's).
Table 1
ID date
1 1/1/2014
1 1/5/2014
Table 2
ID date
1 1/5/2014
2 10/15/2014
Here is the desired result when running for ID = 1 (select Min(date) where ID = 1, I should be getting 1/1/2014 from Table 1.
If I am running the same query where ID=2 then I should be getting 10/15/2014 from Table 2. So basically I need to check if a value exists on Table 1 first, if there is no value on Table 1 and then to grab the value from Table 2.
Select ROWS In Table A That Aren't In Table B
Jan 28, 2008I've been scratching my head over this one.
It should be pretty easy to do, but I keep going over the same problem.
I have 3 tables (Suppliers, Categories & Suppliers_To_Categories).
Suppliers_To_Categories contains which suppliers are related to which categories.
The set-up is so (simplified):
Suppliers
id
1 Supplier 1
2 Supplier 2
3 Supplier 3
4 Supplier 4
Categories
id
1 Category A
2 Category B
3 Category C
4 Category D
Suppliers_To_Categories
id supplierId categoryId
1 1 1
1 1 2
1 1 3
2 2 3
3 3 1
3 3 3
What I am after is an SQL statement which tells me for a particular category, which suppliers ARE NOT related to it, so they can be assigned.
E.g.
Category D has Suppliers 1-4 unassigned
Category C has Supplier 4 unassigned
Category B has 2, 3, 4 unassigned
Category A has Supplier 2, 4 unassigned etc...
I've got the other side of the SQL statement which tells me which suppliers are assigned to a category (see below):
SELECT Suppliers.*
FROM CatToSupplier INNER JOIN
Suppliers ON CatToSupplier.supplierId = Suppliers.supplierId
WHERE (CatToSupplier.CatId = @CatId)
This is probably really easy, but I've become unstuck!
How To Select From Tables To A File?
Jul 20, 1999I need to select data from within a stored procedure into a file, then FTP the file. Do I need to select it into a temporary table and bcp it or is there a better way?
View 1 Replies View RelatedTransact SQL :: Verify Inserted Values From One Table (in CSV File) With Another Table (in Database)
Aug 4, 2015I am looking for a Sql query to verify the inserted values from one table(in CSV file) with another table(in sql database)
For example: I have below Values column that is present in once CSV file, after my data migration the values get stored in Results table under Message column.
I need to very whether values(1X,1Y) are inserted in Message record "successfully inserted value 1X"
Values (CSV)
1X
1Y
Results Table(SQL)
CreatedDate Message
2015-08-04 08:45:29.203 successfully inserted value 1X
2015-08-04 08:44:29.103 TEst pass
2015-08-04 08:43:29.103 successfully inserted value 1X
2015-08-04 08:42:29.203 test point
2015-08-04 08:35:29.203 successfully inserted value 1Y
2015-08-04 08:30:29.203 Test Pass
2015-08-04 08:28:29.203 successfully inserted value 1Y
If all values are inserted:
Output:
All values from values table are inserted successfully
Total count of values inserted: 2
If only few values are inserted, example only 1X from Values table is inserted in Message
Example:
Results Table CreatedDate Message
2015-08-04 08:45:29.203 successfully inserted value 1X
2015-08-04 08:44:29.103 TEst pass
2015-08-04 08:43:29.103 successfully inserted value 1X
2015-08-04 08:42:29.203 test point
Output:
All values from values are not inserted successfully in result table.
Total count of values inserted: 1
Missing Values not inserted in results table are: 1Y
Using An Exec Query To Insert Pdf, .doc File Into Table From A Dir Path Which Is A Field In Another Table
Aug 5, 2007I have the following query in sql 2005:
PROCEDURE [dbo].[uspInsert_Blob] (
@fName varchar(60),
@fType char(5),
@fID numeric(18, 0),
@bID char(3),
@fPath nvarchar(60)
)
as
DECLARE @QUERY VARCHAR(2000)
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
Any ideas how to do this?
Exporting Select Statements To Txt File...
Mar 28, 2008Hi All,
Having trouble getting this to work. Been using a bunch of code from sites (including this one), but just can't get what I want. It seems so simple, but just can't get it.
So, instead of showing what I have, I'd rather see what someone usually uses.
I'm using Windows Auth, am going to use a 'SELECT * FROM DB.dbo.Table', and I want overwrite the outputed file each time it runs. And if the file doesn't exist - create it. I would also like it to be TAB delimited, and use quoted identifiers (""). And nulls will output as null (will be imported to another SQL Server by means I don't know of - and can't find out). They'll need to come back in as Null on the other side.
Also, I'd like to see errors if there are any.
I do appreciate any help.
Thanks ahead of time,
Denvas
Batch File - Read From One Table And Write To Another Database And Table
Nov 17, 2011Any easy way for a batch file or automated process to read from one db and table and what ever entry is missing out of another database + table it writes those missing entries to.
This is a simple table in one db that is filled with usernames, I want to see if there are missing usernames in another db and table and write those entries.
db1.usr_table.usr_name = jdoenew
If jdoenew is missing in the 2nd db I will need to write entries like:
db1.usr_table.usr_name = jdoenew
db1.usr_table.password = tmppassword
db1.usr_table.active = 1
Only Bringing In Select Columns From A Text File
Dec 2, 2004Hi all,
I have some bad data that i'm trying to do a workaround for. I have a comma delimited text file with a column which contains a few records that has, gasp, a comma in it. Thus creating an extra column and pushing out NULL values in an empty column that isn't recognized by my DTS package.
Can i fix this?
And if I'm correct in my assumption that I can't, how do I import only the fields preceding the bad data field? When i attempt to do that, I get the following message
'Too many columns found in the current row; non white-spaced characters found after the last defined column's data.'
Blindman, you probably know exactly what to do, huh?
Output Select Results To A Text File.
Jul 23, 2005I have a dynamic database that will be periodically queried to selectthe data from a blob field. This blob data field is text of a variablelength. The data will be selected using an id field and a date range.There will be multiple blob fields returned that I would like to outputinto a txt file in a local folder.I have the blob fields showing up as text in the field and not areferring link. Can someone point me to an output to text solution?Thanks
View 1 Replies View RelatedSelect Records And Insert Into Text File?
Sep 14, 2007Greetings,A novice needs some advice:I am able to bulk insert data from a text file into a table. Is itpossible to run a query and insert the results into a text file on thelocal or network hard drive?Thanks For Reading,Aaron
View 2 Replies View RelatedSend Results Of SELECT FOR XML Query To A File
Feb 9, 2008
I have posted this on previous forums, but the below code is a VB.net CLR stored proc that will accepts two parameters, a SELECT.... FOR XML query and a file path, and saves the resulting xml result into a file specified by the parameter. After compiling the VB code into a .dll than the T-SQL code to import the assembly, create the CLR proc and test it is included underneath. Hope some will find this useful
Code Snippet
'Requires the Trial or Release version of Visual Studio .NET 2005 Professional (or greater).
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Xml
Partial Public Class outputxml
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub outputxml(ByVal XmlData As SqlXml, ByVal Filename As SqlString)
'Create the variables to hold the values that are supplied by the parameters
'input by the stored procedure
Dim xmlDoc As New XmlDocument()
Dim output As SqlPipe = SqlContext.Pipe()
Try
'Load the result set into the XmlDoc Variable and then save the results in the
'path provided by the stored procedure. The values are provided by the
'input parameters of the stored procedure
xmlDoc.LoadXml(XmlData.Value)
xmlDoc.Save(Filename.Value)
Catch ex As Exception
'If an error occurs catch the message and pipe it back to SQL
output.Send(ex.Message.ToString)
End Try
End Sub
End Class
T-SQL CODE BEGINS HERE
/*Alter the database to set trustworthy on in order
to allow the ability to set the assembly to external
access*/
ALTER DATABASE AdventureWorks SET trustworthy ON
--Import the assembly into SQL
CREATE ASSEMBLY outputxml
from 'C:outputxml.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
-- Create the proc from the imported dll
CREATE PROCEDURE output
@xmldata XML,
@filename nvarchar(1024)
AS
EXTERNAL NAME outputxml.[outputxml.outputxml]
.outputxml
-- Test managed stored procedure
DECLARE @output xml
SET @output = (SELECT ProductID, Name, ListPrice
FROM Production.Product Product
FOR XML AUTO, ROOT('Catalog'), TYPE)
EXEC dbo.outputxml @output, 'c:Output.xml'
GO
Using SqlDataSource To Execute A Select Statement In The Code File???
Nov 27, 2006Hi you all,
In abc.aspx, I use a GridView and a SqlDataSource with a SelectCommand. The GridView's DataSourceID is the SqlDataSource.
In abc.aspx.cs, I would like to use an IF statement in which if a criterion is not satistied then I will use the SqlDataSource with another SelectCommand string. Unfortunately, I have yet to know how to write code lines in order to do that with the SqlDataSource. Plz help me out!
Setting Select Parameter Based On Config File
Jun 17, 2008Hi everybody,
Is there a way to set SelectParameter for SQLDataSource in ASPX file using System.Configuration.ConfigurationManager.AppSettings["SiteID"]) ?
Thanks a lot in advance.
SQL Server Admin 2014 :: Select Certain Column To CSV File
Jun 25, 2014I have a database it is 50 gb with hundreds of columns. I would like to choose a certain column and convert the data in it to .csv or excel file. How can I do that I am very new to MSSQL...
View 1 Replies View RelatedHow To Select Value From Table B To Replace Value In Table A
May 29, 2001I have an automatic data collection system. The large majority of the time, all data is electronically gathered. On occasion, the need arises to substitute a manually entered value into the data when a valuable piece of information cannot be electronically secured. When a piece of data is open to substitution, it arrives with a value of -9999 (a value not naturally occuring) in Table A. I want to be able to use a trigger on table A to get its substitution value from a corresponding column (Same name) in Table B. Tables A & B will have an identical schema. A is raw data, B is the manually updated table. Any value in the raw table with the content of -9999 gets its replacement from Table B.
I'm open to any ideas!!
RC =:((
SELECT Id From Table A Where Matching Id Is Not In Table B
Feb 23, 2007Hi
Trying to figure out a fairly simple SQL query.
Basically i have two tables which have both a matching ID column. How ever, i need to find out which ID's are NOT present in BOTH the tables.
for example
table a -
id
12
13
14
15
table b -
id
12
14
15
Table 'a' has an id of 13 which is not in present in table 'b' and this is the value i need to select!
thanks in advance
SQL Statement Select From One Table Where Not In Another Table
Jul 20, 2005I have two tables with a 1-many relationship. I want to write aselect statement that looks in the table w/many records and comparesit to the records in the primary table to see if there are any recordsthat do not match based on a certain field.Here is how my tables are setup:Task Code TableTCode,TCodeFName,ActiveTracking Table(multiple records)ID,User,ProjectCode,TCode,Hours,DateI want to look at the tracking table and see if there are any TCodelisted here that are not listed in the Task Code table.How do I write a SQL statement to do this?
View 6 Replies View Related