Insert Query With A Select Subquery
Apr 1, 2008
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 ?
View 6 Replies
ADVERTISEMENT
Apr 1, 2008
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 ?
View 4 Replies
View Related
Apr 30, 2008
Hey guys, I have a brain buster for you today:
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?
View 8 Replies
View Related
Apr 10, 2008
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
View 3 Replies
View Related
Jun 12, 2006
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?
View 1 Replies
View Related
Sep 20, 2005
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
View 4 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Feb 11, 2015
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:
INSERT INTO spec_checklist_remind (form, record_type, linked_to_worknumber, spec_checklist_id) values (5, 0, '??',52)"
View 2 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
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
Jun 19, 2014
I want to convert xml data with select query and insert into table.
Xml sample like this :
<ROOT>
<ROW>
<NAME>JHON</NAME>
<ADDRESS>
<ADDRESS1>
<CITY>LKO</CITY>
<STATE>UP</STATE>
[code]....
And data should be like this with select query :
NAME ADDRESS1CITY ADDRESS1STATE ADDRESS2CITY ADDRESS2STATE
JHON LKO UP DLI DELHI
YASH AAA HYR NULL NULL
I want simple query in form of above format.
View 8 Replies
View Related
Nov 28, 2006
Hi all,
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.
Thanks in Advance.
Regards,
Rajeev Vandakar
View 5 Replies
View Related
Sep 6, 2005
hello..
is there any way to use insert into and subquert together ?
I am trying that
insert into X (a)values((select (max(id) from Y))
but it says
Subqueries are not allowed in this context. Only scalar expressions are allowed.
View 1 Replies
View Related
Apr 26, 2008
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...
View 1 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
Apr 3, 2008
Hi,
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.
Thanks,
Chamal.
View 6 Replies
View Related
Jul 23, 2005
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
View 2 Replies
View Related
Dec 7, 2000
Hi Everyone,
I want to insert a row in a table with 2 fields.
The data for the first column comes from a variable
and the data for the second column comes from a select query
can i insert the record in one sql statement like
************************************************** *************
declare @var1 int
set @var1 = 5
insert into mytable1 (field1,field2)
values
(@var1,select field1 from mytable2 where field2 = 10)
************************************************** ***************
where field2 in mytable2 is a primary key.
Awaiting a reply!
Thanks,
saad.
View 1 Replies
View Related
Dec 24, 2007
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!
View 4 Replies
View Related
Mar 22, 2008
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
View 15 Replies
View Related
Feb 19, 2001
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*/
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 in second
View 1 Replies
View Related
Feb 19, 2001
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
but in this way you cant set to my int variable
please help me thanks for all
hra
View 1 Replies
View Related
Jul 4, 2006
I the following table:
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.
View 4 Replies
View Related
Nov 19, 2006
Hello!
I can use querys like these in Access:
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.
-Teemu
View 3 Replies
View Related
Oct 28, 2006
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?
View 2 Replies
View Related
Nov 28, 2006
Hi there,
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')
3 selects in one statement...that can't be right.
Thanks,
View 5 Replies
View Related
Apr 11, 2008
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))
View 2 Replies
View Related
Jun 26, 2006
I have a select such as this:
select COLUMNS from TABLE where WHERE_CLAUSE1
union
select COLUMNS from TABLE where WHERE_CLAUSE2
Now, I want to order the result set by COLUMNS. When I try the following query, the SQL fails.
select * from
(
select COLUMNS from TABLE where WHERE_CLAUSE1
union
select COLUMNS from TABLE where WHERE_CLAUSE2
) as T
order by COLUMNS
Any idea how this can be done?
Thanks
View 4 Replies
View Related
Mar 6, 2008
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:
Table: Profiles
Fields: Id, ProfileName
Table: Groups
Fields: Id, GroupName, ProfileId
I then need to return something like this:
ProfileName,Groups
"Joe Soap","Group1, Group2, Group3"
Does anyone know how this can be done?
Thanks!
View 7 Replies
View Related
Apr 12, 2008
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
from Table1 t1
group by Category
Thanks for the help.
View 5 Replies
View Related
Sep 20, 2007
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'
View 2 Replies
View Related
Nov 7, 2007
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
View 4 Replies
View Related
Apr 28, 2015
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.
==============================================
Declare @mSql2 Nvarchar(max)
SET @mSql2= (select * from Tablename)
================================================
View 2 Replies
View Related
