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.
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.
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.
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
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
and I need to find a sequence of values in column "StringVal", for example: A B.I look for a suitable SELECT, that returns (in this case) following result:
HiWe are migrating from Access to SQL server. The code is in VB and usesDAO3.6. I have successfully made the connection to the databases onthe SQL server and done operations with recordsets. I have also used aSELECT INTO command to create new tables. However CREATE TABLE andALTER TABLE commands do not work.Any ideas?The DBA says we have owner privileges on the databases and thereforeshould be able to do what we like.Many thanksSi
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.
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
set @21=(select count(*) from j9a whereIngestion=1) set @22=(select count(*) from j9a whereInhalation/nasal=1) set @23=(select count(*) from j9a whereAspiration=1) set @24=(select count(*) from j9a whereOcular=1) set @25=(select count(*) from j9a whereDermal=1) set @26=(select count(*) from j9a whereBite=1) set @27=(select count(*) from j9a whereParenteral=1) set @28=(select count(*) from j9a whereOtic=1) set @29=(select count(*) from j9a whereRectal=1) set @30=(select count(*) from j9a whereVaginal=1) set @31=(select count(*) from j9a whereOther=1) set @32=(select count(*) from j9a whereUnknown=1)
Create table table9(Route varchar(30),Fatal int) insert into table9 values ('Route_Ingestion',@1,@21) insert into table9 values ('Route_Inhalation',@2,@22) insert into table9 values ('Route_Aspiration',@3,@23) insert into table9 values ('Route_Ocular',@4,@24) insert into table9 values ('Route_Dermal',@5,@25) insert into table9 values ('Route_Bite',@6,@26) insert into table9 values ('Route_Parenteral',@7,@27) insert into table9 values ('Route_Otic',@8,@28) insert into table9 values ('Route_Rectal',@9,@29) insert into table9 values ('Route_Vaginal',@10,@30) insert into table9 values ('Route_Other',@11,@31) insert into table9 values ('Route_Unknown',@12,@32)
select * from table9
The exRoute result is like this 70 Ingestion 71 Inhalation 72 Aspiration 73 Ocular 74 Dermal 75 Bite/sting 76 Parenteral 77 Other 78 Unknown 524 Otic 525 Rectal 526 Vaginal
The above giving the errors Msg 207, Level 16, State 1, Line 19 Invalid column name 'Ingestion'. Msg 207, Level 16, State 1, Line 20 Invalid column name 'Inhalation'.
Hi,I want to create a temporary table and store the logdetails froma.logdetail column.select a.logdetail , b.shmacnocase when b.shmacno is null thenselectcast(substring(a.logdetail,1,charindex('·',a.logde tail)-1) aschar(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 ShmAcnointo ##tblabcendfrom shractivitylog aleft outer joinshrsharemaster bon a.logkey = b.shmrecidThis statement giving me syntax error. Please help me..Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'case'.Server: Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'end'.sample data in a.logdetailBR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00······0234578······· ····· ··········UB··Aqua Duck··Toon Town Central·Punchline Place···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00·····Regards.
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
I am creating a database using 'Execute SQL Task'. Then i have to create few tables in that database. where,I have my create table SQL queries for 5 tables in a .txt file.
I'm using connection strings to SQL Server Express that have the form
"DataSource= .SQLEXPRESS;AttachDbFilename="(path to my development directory)CurrentDevelopmentDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
I'm assuming this works because the DB is already there, created "manually".
How might I build a connection string for a SQLCommand for a "Create DataBase" query?
I'm confused about what the AttachDbFileName would be, or if there would be another variable in its place, since there's no file yet.
I'm having problems executing TOP n queries on a database that was migrated from 6.5 to 7. I can get it to work on the Authors table in pubs, not in my other dbs. Here is an example:
CREATE TABLE dbo.tblsapParentCust ( Parent char (10) NOT NULL , Name varchar (40) NULL, IsSoldTo bit NOT NULL DEFAULT 0, CONSTRAINT PK_tblsapParentCust PRIMARY KEY CLUSTERED (Parent) ) GO
<load in some data>
SELECT TOP 10 * FROM tblsapParentCust
The select statement results in a syntax error:
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '10'.
I can switch over to pubs and change the query to reference the Authors table, and it runs fine.
If anyone can explain this behavior to me, I would appreciate it.
If I were to create a stored procedure that searches a table using (optional) multiple parameters, what would be the best way to do the search. I want to try and avoid using several "IF" statements (like IF @FirstName IS NOT NULL, etc). How would I do it, or would I just be better off using several "IF" statements? Thanks... CREATE PROCEDURE intranet_search_GetEmployeesBySearch ( @FirstName NVarChar(100), @LastName NVarChar(100), @Phone NVarChar(50), @Cell NVarChar(100), @Pager NVarChar(100), @Ext NVarChar(50), @Email NVarChar(100), @Department NVarChar(200), @Position NVarChar(100), @IsManager Bit ) AS BEGIN
I have 6 separate mailing label like reports with a textbox inside a cell. I tried to join them into one report with subreports"but" it does not work since you need the print layout in order to get both columns to show. What is the best way to accomplish this task?
In my database I have a field with the type decimal. In the select query I want to return true if this field is smaller than 1 and false if this field is 1. How can I do this?
I need something like that:
Select id, name, (mydecimalField < 1) from mytable
I wrote a simple select query that counts the number of records I have in certain zip codes. How can I get a total of the "count" column at the bottom of the results? For example, my results may look like this:
ZIP | (no column name for "count") _____________________________________ 89502 | 10 89509 | 15 89521 | 25
What statement would I use to get the total of '50' displayed in the resluts? Thank you in advance
Hi Will somebody please explain how to combine asp.net dropdown lists to write a SQL database select query. I am using VWdeveloper and C Sharp. For example, say I have 3 dropdownlists on my webpage as below, List 1, Cities, London, Rome, Barcelona etc List 2, Restaurants by Type, Italian, chinese, Indian etc List 3, Number of tables/ seats 10-20, 20- 40, 50 -100 I want someone to be able to search for a restaurant by selecting an item from each dropdownlist such as, "Barcelona" "Italian" "50-100" This search query would return all the Italian restaurants in Barcelona with 50-100 tables/seats. I would also like the select query to work even if one of the dropdownlists items is not selected. Hope somebody can clear this up? Also would sql injection attacks be a threat by doing it this way? Thanks all
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT * INTO dupContactKeys FROM Contacts WHERE ContactKey IN ( SELECT ContactKey FROM Contacts GROUP BY ContactKey HAVING COUNT(*) > 1)
SELECT * FROM dupContactKeys WHERE ContactKey IN ( SELECT AddressLine1, Zip FROM Contacts GROUP BY AddressLine1, Zip HAVING COUNT(*) > 1) ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
I'm reporting from a Microsoft SQL database (poorly documented unfortunately) and would like to find a 3rd party application to assist me in rapidly making Select queries. The ability to browse data in a field from the interface would be a plus.
What are the best alternatives for rapidly creating these queries from some sort of builder or wizard?
I am trying to select the max for each Schedule_Number when ProcessDescription = 'Exit Cold Rinse'. In the following table, 2:00 and4:00 should be returned for 12345_001 and 12345_002 respectively. Ihave tried to join the two queries and would like to use the currentSchedule_Number as one of the criteria when determining the max.Below is some code that I've used thus far? Does anyone havesuggestions?*Schedule_Number * Process_Description * TMDT*12345_001 * Exit Cold Rinse * 1/07/08 01:00:00 PM*12345_001 * Enter Cold Rinse * 1/07/08 01:30:00 PM*12345_001 * Exit Cold Rinse * 1/07/08 02:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 02:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 03:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 03:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 04:00:00 PMSelect *From(Select distinct Schedule_NumberFrom dbo.Process_DataWHERE left(Schedule_Number,5) = '12345') as Query1left join(Select *From dbo.Process_DataWhere TMDT =(SELECT Max(TMDT)FROM dbo.Process_DataWHERE Process_Description = 'Exit Cold Rinse' andQuery1.Schedule_Number = Query2.Schedule_Number)) as Query2on Query1.Schedule_Number=Query2.Schedule_Number
I am using a sql server 2000 database to log the results from a monitorthat I have running - essentially every minuite, the table describedbelow has a insert and delete statements similar to the ones below runagaint it.Everything is fine for a few weeks, and then without fail, all accessesto the table start slowing down, to the point where even trying toselect all rows starts timing out.At that point, the only way to make things right that I have found, isto delete the table and recreate it.Am I doing something specific that sql server really doesn't like? Isthere a better solution then deleting and recreating the table?CREATE TABLE [www2] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),[success] [bit] NULL ,[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[level] [int] NULL ,[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOINSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])VALUES ('Error on: <ahref="http://www2.klickit.com/include/asp/system_test.asp">http://www2.klickit.com/include/asp/system_test.asp</a><br><br>The operation timedout<br><br>(Test Activated From: Lynx/2.8.2rel.1libwww-FM/2.14)',0,1,'',getDate())DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (ReportNot Like 'ResetThanks in advance,Simon Withers*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi AllStrange request I know, but could somebody give me pointers on how I can put3 queries into 1 'thing' and then get only the unique entries from this'thing'.To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queriesthemselves aren't that complex and all return the same 2 fieldsets of stockcode and stock desc. Because these separate queries might bring back thesame stock code/description I need to amalgamate the data and then queryagain to bring out only distinct stock values, eg:Query 1 brings back:stock code stock descIVP Invoice PaperSTP Statement PaperKGC Keyboard Coveretc... etc...Query 2 brings back:stock code stock descIVP Invoice PaperBOB Back PackKGC Keyboard Coveretc... etc...Query 3 brings back:stock code stock descKGC Keyboard Cover3.5"D 3.5" Disksetc... etc...I need to produce 1 resultset that shows:stock code stock descIVP Invoice PaperBOB Back Pack3.5"D 3.5" DisksKGC Keyboard CoverSTP Statement Paperetc... etc...(all unique entries)I'm currently just bringing back the 3 query results in Excel, but I'd liketo be able to do the above.In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do?ThanksRobbie
I have 4 sets of select queries under 1 stored proc, now on the report calling the stored proc via dataset. when i run the dataset the only first set of the select query related fields appearing under the dataset.
But on the back end sql server, if i execute the same stored proc, i get 4 resultsets in one single executioln.
i am not seeing the remaingin 3 resultsets, on the reports dataset.
Is it possible on the reports or not.
In the asp.net project i was able to use that kind of stored procedures whcih has multiple select statements in 1 procedure., i use to refer 0,1,2,3 tables under a dataset.