Hi.
I have an insert query which inserts record that are returned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).
How can I solve this ?
Hi. I have an insert query which inserts record that are rturned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint). How can I solve this ?
I have a query where I need to select a bunch of rows from one table, hypothetically we'll call them ssn, first name, last name, and I need to select a subquery which coalesces a bunch of rows together (in no case will there be only one row returned from that subquery).
Anyone know how I could go about this? I'll give you an example of what I've tried, but it does not work currently.
delcare @path varchar(255) select e.ssn,
e.firstname, e.lastname, ( @path = select coalesce(@path + ', ', '') from space s1 inner join space s2
on s1.lft BETWEEN s2.lft AND s2.rgt and s1.rgt BETWEEN s2.lft AND s2.rgt where s1.spaceID = 133225 select @path) from employees e where e.id = 5
Using that spaceID is guaranteed to give me four rows, and I need them coalesced together, but I can't just use a function (too slow on the scale it would be used), any thoughts?
I am working with parent child tables and want to populate the primary key on insert so that the user does not have to enter this for each record. Here is my codeInsertCommand="INSERT INTO [Awards] ([UFID], [DateAwarded], [Amount], [AwardingAgency]) Select UFID, @DateAwarded, @Amount, @AwardingAgency from master where GatorlinkName = @LoginName" <InsertParameters><asp:Parameter Name="LoginName" Type="String" /> <asp:Parameter Name="strusername" Type="String" /> <asp:Parameter Name="UFID" Type="String" /> <asp:Parameter Name="DateAwarded" Type="DateTime" /> <asp:Parameter Name="Amount" Type="Decimal" /> <asp:Parameter Name="AwardingAgency" Type="String" /> </InsertParameters> The UFID field is the only field that should be populated from SQL data the others are coming from a form view insert form. When I run an insert I get no error but the insert does not happen. I know that the @LoginName works since I am using this same logic in my select statement. Thanks in advance for your help,Ken
I have a Bit field in my database ,but when i try to insert the bit in database it inserted succesfully but while accessing it returns -1,what would be the problem? i used integer while inserting same as get the value from database?
Hi,I have a SQL query:select products.name, products.notes, products.purchase_date,products.serial_no, products.total_value, products.product_code,products.quantity, products.product_group, products.lhs1_name,lhs1.department, lhs1.address1, lhs1.city, lhs1.countryfrom products, lhs1where products.lhs1_id = lhs1.idI want to insert the results of this query into a table called 'temp' in thedatabase. I used to copy and paste this into excel then import it but itdoesn't always work.Is there a way to do it all in a SQL script. Please be aware that myknowledge of SQL is fairly basic so please explain things clearly.Thanks,Darren
hi my self avii want to copy data from one table to other table,by giving certaincondition and i want o use insert statement .in this i want to pass somevalue directly and some value from select statement , if i try i ll geterror i.e all column of destination table (i.e in which i want to insertdata) should match with all columns in values column some thing likethis.plz give me some helpful suggetion on this
I need to insert data into a table based on the results returned by a select statement. Basically, the select statement below gives me a list of all the work orders created in the last hour.
select worknumber from worksorderhdr where date_created > DATEADD(HOUR, -1, GETDATE())
This might return anywhere between 5 and 50 records each time. What I then need to do is use each of the work numbers returned to create a record in the spec_checklist_remind table. The other details in the insert statement will be the same for each insert, it's just the worknumber from the select statement that needs to be added to the insert where the ?? are below:
hey there, i'm trying to move one record from one table to the next,so i'm doing this by doing an insert into table, then delete from theprevious table. Only thin g is on the insert i want to include aparameter. Can't work how to do this. Do you think i need to doanother update query and insert the parameter that way, after theinsert is done?ALTER PROCEDURE dbo.ReturnLoan(@strBarcode varchar(100),@strLibrary varchar(100),@dateReturned datetime)ASINSERT INTO Loan_History(Barcode,UserID,Date_Borrowed,Library)Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHEREBarcode = @strBarcodeAND Library = @strLibrary;DELETE FROM LoansWHERE Barcode = @strBarcodeAND Library = @strLibrary ;RETURN
This is my very first post to this forum. How to use the INSERT INTO... SELECT FROM query to export MS SQL 2000 data into FoxPro 2.6 DBF file? I want to write a VB 6.0 program, and already connected to MS SQL 2000 Database by MS SQL ODBC connection. Now I want to Export this MS SQL data to FoxPro 2.6 DBF. I found ROWSET option, but don't know how to use it for FoxPro. Please help me.
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
I have two tables. Customers and Orders. Customers table contains customer id, and customer name columns. Orders table contain order id,product id,customer id. So orders table contains products bought for each order by a customer.
I want to write a query to retrieve all order details (products for each order and customer id), where product with id 5 is bought. Can I write this sql without using a subquery.
Hi.I'm new in SqlServer programing.Is it possible to do something like this ( It is common construction in oracle )Select X from(select a+1 as X from tab1)without creating view containig subquery ?thx. MD
select t1.a, (select t2.b from t2 where t1.c = t2.c) b from t1 I need to write that kind of sql to return me single value a and multiple values b on each of lines, like a b ---------------------------- tom small,big,hugh But if I execute that sql, I would get error like 'select Subquery returned more than 1 value'. Please help me find a solution, thanks!
1) Getting this error: Msg 156, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10 Incorrect syntax near the keyword 'in'. Msg 102, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10 Incorrect syntax near ','. Msg 512, Level 16, State 1, Procedure SP_ImportHotels_Step4, Line 7 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. 2) I also coild not use subquery as myname... It did not allow to specify "as" for some reason... Here is my procedure.
alter procedure SP_ImportHotels_Step4 As BEGIN INSERT INTO classifieds_Photos ( AdId, IsMainPreview, DateCreated, createdOn, createdBy, modifiedOn, modifiedBy, URL, ThumbnailURL ) SELECT classifieds_Ads.Id, [i.ID] in (Select Min(Images.ID) from Images group by HotelID), GetDate() AS Expr2, GetDate() AS Expr3, 'admin' AS Expr4, GetDate() AS Expr5, GetDate() AS Expr6, i.URL, i.ThumbnailURLFROM (classifieds_Ads INNER JOIN classifieds_Address ON classifieds_Ads.LocationId = classifieds_Address.addressID) INNER JOIN Images as i ON classifieds_Address.tempIONHotelID = i.HotelID; END go execute SP_ImportHotels_Step4
hi all how are you today i am not good because i can't set this variable in this query please read this thanks
create table aaa1 (id_no int, name varchar(20)) go create table aaa2 (id_no int, name varchar(20)) go
insert into aaa1 values (1,'rahmi') insert into aaa1 values (2,'atilganer') insert into aaa1 values (3,'hasan')
insert into aaa2 values (4,'rahmi') insert into aaa2 values (5,'atilganer') insert into aaa2 values (6,'hasan')
/* declaring any numeric variable*/ declare @id_no_var int /* and set variable to max table name's (aaa2 table in this example) id_ no column
note :insqlhelp and insqlhelp2 is an alias for tablename query (recommended from sql help */
set @id_no_var = (select max(id_no) from (select max(name) insqlhelp from sysobjects where name like 'aa%') insqlhelp2 )
this query return:
Server: Msg 207, Level 16, State 3, Line 3 Invalid column name 'id_no'.
this error returned because max(id_no) column is absent
if you are run select * from (select max(name) insqlhelp from sysobjects where name like 'aa%') insqlhelp2
this query return that
insqlhelp --------------- aaa2
(1 row(s) affected)
this mean that my table name query returned table name but i cant use this name in any other query (i think because of daclaring alias "insqlhelp, insqlhelp2")
other way is
set to any other text variable to table name, and concetanate to query, and execute with exec
table1 member_name legacy_id team_name ----------------------------------------- Bill 1234 nationals Bill 1234 nationals Tom 3456 nationals Tom 3456 orioles
I wish I could restructure the data or normalize it but this is unfortunately what I have to deal with.
I need a query that returns the team name and the number of times it appears in the table excluding duplicates for each person. I have duplicates all over the place in this tables. Bill could have nationals listed a couple hundred times.
My query should return
team_name count ----------------- nationals 2 - because it occurs for bill, and tom orioles 1 - because it occurs for tom
If I do something like:
select distinct(team_name), count(team_name) from table1 group by team_name
I get back:
team_name count ------------------- nationals 3 - because it occurs for bill twice, and tom once orioles 1 - because it occurs for tom once
I've tried something like:
select team_name, count(team_name) from table1 where legacy_id in ( select distinct legacy_id from table1 )
I get a syntax error. Regardless, I'm not sure this will give me what I need.
I've tried over a dozen variations of select distinct, joins, etc but with no luck.
Any of you sql gurus know how to solve this problem? I've been banging my head against it for a couple days and boy does my head hurt.
SELECT Field1, (SELECT Field2 FROM Table2 WHERE Key=1) AS Field2 FROM Table1 SELECT Field1, (SELECT Count(Field2) FROM Table2 ) AS Field2 FROM Table1
But when I try execute it with SQL Server Everywhere it says "Token in error = SELECT".
Is there some kind of limitations to do this with SQL Everywhere? SQL Everywhere seems to be nice compared with Access and JET but for my project it's useless if I can't use subquerys.
I have 2 tables, Jobs and Categories.Each job belongs to a category. At present, I am returning all categories as follows:SELECT categoryID, categoryName FROM TCCI_CategoriesWhat I'm trying to do, is also return the number of jobs assigned to each category, so in my web page display, it would show something like this:Engineering(5)Mechanical(10) etc.My db currently has 5 categories, with only one job assigned to a category. I tried the following sub-query, but instead of returning all the categories with their job counts, it just returns the category that has a job assigned to it:SELECT c.categoryID, c.categoryName, COUNT(j.jobID)FROM TCCI_Categories c, (SELECT jobID, categoryID FROM TCCI_Jobs) jWHERE j.categoryID = c.categoryIDGROUP BY c.categoryID, c.categoryName, j.jobIDThis is the output when I run the query:categoryID categoryName Column1 ---------------- ---------------------- ------------------------------32 Engineering 1 How would I fix this?
I am pulling back records from the DB in this case to get Wheel information. I am pulling back based on user input, but also need to query a second table that contains the Price and model number from another table based on a field being pulled back in the original select.
I am not sure if this makes sense, here is a working copy of the SQL I have , but it's not pretty. There must be another way of stating this statement that i am missing, can anyone give me some suggestiosn?
SELECT tblMacPak2.*, (SELECT ListPrice FROM tblMacPakPrices WHERE WheelId = OEMWheel) AS ListPrice, (SELECT PartNumber FROM tblMacPakPrices WHERE WheelId = OEMWheel) AS PartNumber FROM tblMacPak2 WHERE (Make = N'honda') AND (Model = N'civic') AND (SubModel = N'standard') AND (YearRange = N'2006') AND (Factory_Wheel_Diameter = N'15')
Ok I have the following SQL, I have a subquery in the SELECT part but also the same subquery in the WHERE part as well.What I'm trying to do is get all parents that have children OR get all parents with no children OR just get all parents regardless (@HasResponses is a BIT that can be 1, 0 or null). At the same time I want to count the total number of children in the select list, but I'm having to copy the same subquery in the SELECT and WHERE parts which doesn't seem terribly optimal to me (maybe it is, that's why I'm asking). I've tried referencing the column alias in the select list (AS [Responses]) for the where part (@HasResponses = 0 AND [Responses] = 0), but it doesn't seem to work.Is this the most optimal way to do it? Is there a better way? I'm working with SQL Server 2005.SELECTf.FeedbackText AS [Feedback Comment],u.Name AS [Feedback Author],f.CreatedDate AS [Created On],(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) AS [Responses]FROM Feedback fINNER JOIN [User] u ON f.StaffID = u.StaffIDWHERE f.CreatedDate >= @DateFromAND f.CreatedDate <= @DateToAND(@HasResponses IS NULLOR(@HasResponses = 1 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) > 0)OR(@HasResponses = 0 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) = 0))
Hi there, I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results. Here is my basic structure:
I'm trying to select from a table with three columns. I want these columns to be spread out among multiple columns based on the values. I hope someone can shed some light on this. I might be able to use pivot, but don't know how the syntax would roll for this.
Here is the example of dummy values and the output I am trying to obtain.
drop table table1
create table table1
(Category int, Place int, Value int)
insert into table1 values
(1, 1, 20)
insert into table1 values
(1,2, 12)
insert into table1 values
(1,3, 30)
insert into table1 values
(2,1, 34)
insert into table1 values
(2,2, 15)
insert into table1 values
(2,3, 78)
select Category,
(select top 1 value from table1 where place = 1 and Category = t1.Category) as place1,
(select top 1 value from table1 where place = 2 and Category = t1.Category) as place2,
(select top 1 value from table1 where place = 3 and Category = t1.Category) as place3
Please try the following, see that trigger is getting invoked, even if there is no row inserted into trigger table.
drop TABLE X GO CREATE TABLE X ( x1 bigint NOT NULL , x2 nvarchar(40) ) GO drop TABLE Y CREATE TABLE Y ( y1 bigint NOT NULL , y2 nvarchar(40) ) GO CREATE TRIGGER trg1 ON X FOR INSERT AS BEGIN DECLARE @prfirststatus INTEGER, @newcontextid NCHAR SELECT @newcontextid = x2, @prfirststatus = x1 FROM inserted PRINT 'See the trigger getting invoked without even a single ' + ' row being inserted in table X and values passed to this ' + ' triggers are inserted.x2 = ' + @newcontextid insert into Y values (@prfirststatus,@newcontextid) END GO insert into X SELECT y1, 'x' from Y where y2 = 'DOESNTEXIST'
Hi, When i try to save my stored procedure.. i am getting the above error and this is my sproc 1 INSERT INTO Statement..ClientSources 2 ( 3 ClientId, 4 ClientSourceId, 5 SourceName 6 ) 7 Select Distinct 8 @ClientId, 9 SOURCE_NUM, 10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME 11 WHEN 2 Then SRC2NAME 12 WHEN 3 THEN SRC3NAME 13 WHEN 4 THEN SRC4NAME 14 WHEN 5 THEN SRC5NAME 15 WHEN 6 THEN SRC6NAME 16 WHEN 7 THEN SRC7NAME 17 WHEN 8 THEN SRC8NAME 18 WHEN 9 THEN SRC9NAME 19 WHEN 10 THEN SRC10NAME 20 WHEN 11 THEN SRC11NAME 21 WHEN 12 THEN SRC12NAME 22 WHEN 13 THEN SRC13NAME 23 WHEN 14 THEN SRC14NAME 24 WHEN 15 THEN SRC15NAME 25 END 26 FROM 27 PlanDBF p 28 Where 29 p.PLAN_NUM = s.PLAN_NUM 30 ) as SourceName 31 FROM 32 SourceDBF s 33 Where 34 SOURCE_NUM NOT IN ( 35 SELECT DISTINCT 36 ClientSourceId 37 --SourceName 38 FROM 39 Statement..ClientSources 40 Where 41 ClientId = @ClientId 42 )
I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name i get this error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Any help will be appreciated. Regards Karen
I am getting error [[Msg 116, Level 16, State 1, Line 7 .Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.]] for the below script.
