How To Use Select Statement Inside Insert Statement
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
ADVERTISEMENT
Jul 4, 2006
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
View 7 Replies
View Related
Jul 26, 2007
iam trying to rerieve a certain value from one table
and i want to use that vaue inside a UDF
iam usinf a table valued function as i have to retireve no of values
Can i do something like this to retrieve the value
SET @Value=Select Value from Table WHERE xyz='some no.'
as this value is being calculated by some other fucntion and now this funcation has to use this at runtime.
View 4 Replies
View Related
Sep 20, 2007
How can I do this
Select id
set @temp = id
from tblexample
I want to do something, on each row output, with @temp.
View 8 Replies
View Related
Oct 19, 2007
Hi All,
Can we use the while loop inside a select statement? Meaning, something like this:
Code Block
SELECT DATE,
WHILE (SELECT TOP 1 DATEPART(HH,DATE) FROM SC_DATEDIMENSION_TABLE) <= 23
(SELECT DATEADD(HH,6,SC_DATEDIMENSION_TABLE.DATE) )
FROM SC_DATEDIMENSION_TABLE
What I want to do here is I have a table which has all the dates but with time only representing 00 hrs. I want to display this column and along side, I want to have another column, which displays the date split at 6 hours. So, one left column, there will 4 columns on the right.
Hope the question is clear.
Thanks a lot.
Mannu.
View 4 Replies
View Related
Nov 14, 2007
Hi, can you add a counter inside a select statement to get a unique id line of the rows?
In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order.
This is the normal way:
SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @topicID) ... some more where/order by statements
This is what i want:
DECLARE @tempCounter bigintSET @tempCounter = 0SELECT @tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @topicID)... some more where/order by statements and at the end.. (SELECT @tempCounter = @tempCounter + 1)
Anyone know if this can be done?
View 2 Replies
View Related
Nov 12, 2003
I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query.
So! Here's the statement that works:
======================
DECLARE @fixeddate datetime
DECLARE @CVG_ELECT char(1)
DECLARE @Effdt datetime
DECLARE EFFDTS CURSOR FOR
SELECT Z.EFFDT, COVERAGE_ELECT
FROM PS_LIFE_ADD_BEN Z
WHERE Z.EMPLID = '1000'
AND Z.EFFDT <=
GETDATE()
AND Z.PLAN_TYPE = '20'
ORDER BY Z.EFFDT DESC
OPEN EFFDTS
FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT
WHILE @@FETCH_STATUS = 0
BEGIN
if @CVG_ELECT <> 'E'
break
ELSE
SET @fixeddate = @Effdt
FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT
END
CLOSE EFFDTS
DEALLOCATE EFFDTS
PRINT @fixeddate
======================
If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea).
Is it possible to encapsulate this inside a select statement?
View 6 Replies
View Related
Aug 23, 1999
Can I write a function inside a Select statement in sql server 7.0
If so HOW ?
Manish Mehta
View 2 Replies
View Related
Mar 24, 2015
I would like to UPDATE a column form my SELECT statement below but not sure how to go about this?!
The column I need to update is: courses.active = N
SELECT schools.id, schools.name, schools.cactus_name, schools.cactus_name_english, schools.address1, schools.address2, schools.city, schools.county, schools.postcode, schools.country, schools.active, schools.latitude, schools.longitude, schools.contact, schools.website, schools.email, schools.telephone, schools.fax, schools.dos, schools.other_contacts, schools.school_commission, schools.bo_notes, courses.name, courses.domains,
[Code] ....
View 7 Replies
View Related
Jun 25, 2015
In C# .NET I have the possible to create some validations of my data, with regulary expressions.Β Do SQL have the same feature? I will like to do an data validation of all my insert statement inside the sql-server. Is that possible?
View 5 Replies
View Related
Mar 13, 2003
I have following codes :
insert into table1 (lastname,firstname)
select ##Newrows.LAST, #Newrows.FIRST, from ##newrows
if @@error > 0
begin
select @ReturnError = 91001
end
Will this return @@error = 0 if insert fails but select is successful?
Thanks in advance.
-jfk
View 1 Replies
View Related
May 27, 2008
I have a select statemnet that I want to insert new rows if the data is not found from table 1 into table 2. How can I add onto this statement? What is the sql code neede?
Select *
From tbl_Data_OpenOrders
WHERE EXISTS (Select *
From tbl_TestData
WHERE tbl_Data_OpenOrders.oabl = tbl_TestData.oabl AND
tbl_Data_OpenOrders.ODLOTSEQ = tbl_TestData.ODLOTSEQ AND
tbl_Data_OpenOrders.OACUSTPO = tbl_TestData.OACUSTPO AND
tbl_Data_OpenOrders.OABLDATE = tbl_TestData.OABLDATE)
Lisa Jefferson
View 7 Replies
View Related
Feb 22, 2007
Hi,I have a very simple issue: for simplicity lets say I have 2 tables, A andB.- Table A contains 5 fields. Amongst these there is a 'id'-field whichis but a reference to table B.- Table B contains 2 fields: 'id' and 'text'In order to post data to table A I thus (from a known text value that shouldmatch 1 value in B.text) have to get the value of B.text before performingthe UPDATE/INSERT statement.How is this possible?I would have thought something likeINSERT INTO A (val1, val2, val3, ID, val4)VALUES ('x1','x2','x3', SELECT id FROM B WHERE [SOME TEXT VALUE] = B.text,'x4')however this is not possible, so I'm lost - not experienced in the arts ofSQL:-)Hope someone can help.Best Regards,Daniel
View 6 Replies
View Related
Feb 24, 2007
Hello All,
INSERT INTO [SPIResConv5].[dbo].[Batch]
([BATCH_NO],
[OPENDT]
,[USERID]
,[MODULE]
,[USERBATCH]
,[RESORT_ID]
)
Select [BATCH_NO],
[OPENDT] = getdate(),
'Hwells' as [USER_ID],
'1 CASH RECEIPT' as [MODULE],
[USERBATCH],
Resort_ID = Case Resort_ID
when 2 then 'Ell'
when 3 then 'CSI'
when 12 then 'Ell2'
when 13 then 'ATR'
end
from TransactionTempToTransaction
where Resort_ID = 3 or Resort_ID = 2 or Resort_ID = 12 or Resort_ID = 13
needing help in modifing the statement above
The first would be to grap from the batch table the last [BATCH_NO] and add one number to it-and insert --just that number for all records imported to Batch Table. Example: last record was 50 then 51 is the batch for all recordes imported.
Second: [USERBATCH] is a varchar, but need to insert based on current date: Say for example -today 2/23/2007 but need to insert in format = '2/23 LBX'
Last and again thanks for the help-
Resort_ID-For the Batch table only need to import how many rows,
Based on how many different resorts or in the table TransactionTempToTransaction
For example-- if there are two diffrent resorts only import two rows
With the last field Resort_id being the only difrrent field showing the resort_id. If six different resorts or in tabe TransactionTempToTransaction then six rows.
Thanks for your
JK
View 1 Replies
View Related
Apr 14, 2005
Hi,
I am trying to run the following insert statement, but am gettng an error. The table I want to insert to
(DimensionMonthTime_hold) has an
Identity column defined for its key.
The part I cannot figure out is that when I run this insert statement without the order by statement, the insert is successful. If I attempt to run this insert statement with the Order By statement, I get an error saying that I have to provide a value for the identity value in the insert statement (which i don't want to do.) I need to have the data sorted, hence the reason for the order by.
I've tried to specify the column names on the insert line, but haven't figure that out.
Any suggestions?
Thanks
Jim
----------------------------
Insert into DimensionMonthTime_hold
select distinct substring(period,1,4) + substring(period,6,2),
substring(period,1,4) ,
case substring(period,6,2)
when '01' then '1'
when '02' then '1'
when '03' then '1'
when '04' then '2'
when '05' then '2'
when '06' then '2'
when '07' then '3'
when '08' then '3'
when '09' then '3'
when '10' then '4'
when '11' then '4'
when '12' then '4'
else 1
end,
substring(period,6,2),
case substring(period,6,2)
when '01' then 'First Quarter'
when '02' then 'First Quarter'
when '03' then 'First Quarter'
when '04' then 'Second Quarter '
when '05' then 'Second Quarter'
when '06' then 'Second Quarter'
when '07' then 'Third Quarter '
when '08' then 'Third Quarter '
when '09' then 'Third Quarter '
when '10' then 'Fourth Quarter'
when '11' then 'Fourth Quarter'
when '12' then 'Fourth Quarter'
end,
case substring(period,6,2)
when '01' then 'January'
when '02' then 'February'
when '03' then 'March'
when '04' then 'April '
when '05' then 'May'
when '06' then 'June'
when '07' then 'July '
when '08' then 'August '
when '09' then 'September '
when '10' then 'October'
when '11' then 'November'
when '12' then 'December'
end,
1,
getdate()
from transaction
where Account_type ='A'
------------------------
View 6 Replies
View Related
Jan 14, 2005
Hey,
I am not sure how to really explain this, but I'll give it a try.
I am looking to use a select statement in a way that I can tell it which rows to insert in depending on when only one result is returned. For example, if I run this statement:
SELECT Column1, Column2, Column3
FROM #Temp1
The result set is:
Column1---Column2---Column3
99--------6756756---55555
44--------55---------NULL
Column3 as only the one returned value, so I do not want it associated with any of the other rows, so I need this:
Column1---Column2---Column3
NULL------NULL------55555
99--------6756756---NULL
44--------55---------NULL
Another example:
The returned result now is:
Column1---Column2---Column3---Column4
99---------6756756---55555-----NULL
42---------55---------NULL------12345
So I need:
Column1---Column2----Column3----Column4
NULL-------NULL-------55555------NULL
NULL-------NULL-------NULL-------12345
99---------6756756----NULL-------NULL
44---------55----------NULL-------NULL
Does this make sense, and/or is it even possible?
I know it could be more of a presentation thing, but I would like to know how to do it in the code behind.
Thanks
View 2 Replies
View Related
Jun 9, 2007
is there anyway i can merge select statement and insert statement together?
what i want to do is select few attributes from a table and then directly insert the values to another table without another trigger.
for example, select * from product and with the values from product, insert into new_product (name, type, date) values (the values from select statment)
View 3 Replies
View Related
May 30, 2006
Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo
View 1 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
Jan 2, 2008
I have several tables in a database which I always want to update with information from one table with new records (containing contact and demographical information). The setup is something like this:
NewRecordsTable: fn, ln, streetadd, city, emailadd, phonenumber, gender, birthdate
ContactTable: ID(primarykey), fn, ln, streetadd, city, state, zip, phonenumber, email
DemographicTable: ID(linked to primary key ID in Contact table), birthdate, gender
I want to update the ContactTable and DemographicTable with information from the NewRecords Table. What I have done so far is set the identity insert for the ContactTable to on, then inserted the fn, ln, streetadd, email, etc. from the NewTable. This works fine.
I then try to insert ID, birthdate and gender into the DemographicTable where NewRecordsTable.fn=ContactTable.fn AND NRT.ln=CT.ln AND NRT.streetadd=CT.streetadd AND NRT.emailadd=CT.emailadd - This mostly works, but the records which have NULL values any of those fields don't get inserted.
What I really want is to insert the records that have matching email addresses OR matching fn, ln, streetadd combos, but I can't figure out how to get that SELECT/WHERE statement to work.
The problem that underlies this is that I want to insert the ID values from the ContactTable into the DemographicTable, but the only way I can see to make them match properly is by matching the email addresses or fn, ln, streetadd combos from the NewRecordsTable to the ContactTable (all of the email addresses in our NewRecordsTable are unique, unless the person doesn't have an email address, in which case we make sure they have a unique fn, ln, streetadd combo)
Any help would be appreciated,
Thank you!!
View 3 Replies
View Related
Aug 20, 2007
hello everybody
I want to ask for your help in an issue i am having with SQL Server 2005 Developer Edition . here is the issue:
We have 2 servers called: c10 and cweb. In both, we manually installed SQL server 2005 Dev Edition with no problems.
I created a linked server on c10 to access data on cweb. That is working fine with no problem when executing Select or Insert T-SQL statments like these ones from c10:
select * from cweb.DBNAME.dbo.TableNAME
Or
insert into cweb.DBNAME.dbo.TableNAME (f1, f2, f3)
select f1,f2,f3 from c10.DBNAME.dbo.TableNAME
All works fine up to here. But then there is a new server we setup called c7. This time we created an image of c10 and restore that image on this new server c7. That way, we didnt need to install all software needed in this new server. All software seemed to work ok..but then SQL server 2005 on that new server started failing when doing SELECT t-sql statements.
So Now if i am on c7 and i try to execute this: SELECT * from C7.DNAME.dbo.TableName, it fails
C7 in this case is the local server and it should work. however the error it gives me is that :"linked server not recognize"...it shouldnt need a linked server since it is trying to access the local server. Even with that, i tried to create a linked server to the own local server and now that Select t-sql isntruction worked with no problem..But now here is the othe issue i am having: INSERT t-sql statements are not working. When doing this:
insert into c7.DBNAME.dbo.TableNAME (f1, f2, f3)
select f1,f2,f3 from c7.DBNAME.dbo.TableNAME2
It fails with the following 2 error messages:
"OLE DB provider "SQLNCLI" for linked server "c7" returned message "Multiple-Step OLE DB operation generated errors. Check each OLE DB status, if available. No work was done
The OLE DB provider SQLNCLI for linked server citrix7 could not insert into table c7.DBNAMe.dbo.TableNAme because of column intID. the data value violated the integrity constraints for the column."
I checked that the SELECT part of the INSERT T-sql statement is not retrieving any invalid data for column intID.
I tried restoring the BD on c10 server and tried the same INSERT statement and it worked ok..which mean the data to be inserted is valid.
So i think it is related to some mis-configuration on the linked server or something in SQL server got broken when restoring c10 server image into the new c7 server
So in summary the problem is this:
1. i can not make SELECT T-sql statements using fully qualified names on the local sql server without having a linked server to the local server (which is strange)
2. I can not make INSERT T-sql statements in the local server. This errors happens when doing it
"OLE DB provider "SQLNCLI" for linked server "c7" returned message "Multiple-Step OLE DB operation generated errors. Check each OLE DB status, if available. No work was done
The OLE DB provider SQLNCLI for linked server citrix7 could not insert into table c7.DBNAMe.dbo.TableNAme because of column intID. the data value violated the integrity constraints for the column."
I have been searching thru google and forums but havent found any solutions yet.
Hope you can help me with this..i guess my only option right now is just uninstall and re-install sql server..but maybe there is any other solution to this_?
thanks a lot
Helkyn
View 1 Replies
View Related
Nov 15, 2007
Hello,
I have a trigger on a table named Store. The trigger updates the longitude and latitude on store based on the zip. Simple right? Well, I'm trying to import data and of course the trigger is not updating the data as triggers are not on a row by row basis with multi row inserts.
Here is the error message I'm receiving:
Msg 512, Level 16, State 1, Procedure SetLongLat, Line 17
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How would one go about resolving this issue? I've includes my snippets below:
Trigger:
Code Block
ALTER TRIGGER [dbo].[SetLongLat]
ON [dbo].[Store]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Long float
DECLARE @Lat float
SELECT @Long = LONGITUDE, @Lat = LATITUDE
FROM Zipcode..ZipcodeLite
WHERE ZIP_CODE = (SELECT Zip FROM Inserted)
UPDATE Store SET Longitude = @Long, Latitude = @Lat
FROM Store INNER JOIN Inserted ON Store.Id = Inserted.Id
IF UPDATE(Zip)
BEGIN
UPDATE Store SET Longitude = @Long, Latitude = @Lat
FROM Store INNER JOIN Inserted ON Store.Id = Inserted.Id
END
END
Thanks for your help!
Nathan
View 5 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
Aug 29, 2006
hi
first of all is it possible? if so, what am i doing wrong with this
INSERT into TB2
(
ClientCode,
EngagementCode,
EngagementDescription
)
SELECT
(SELECT dbo.tarCustomer.CustID
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),
NULL,
SELECT
(SELECT dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)
the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.
this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks
tibor
View 5 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
Sep 18, 2005
Trying to convert the following SELECT statement into a INSERT statement and having trouble. No doubt this will be a piece of cake to someone. To eventually get this to a trigger stage would be nice, but for the moment I'd settle for just plain SQL. Using MS SQL 2000. The database name is reporting. The table name is CallLog. I'm trying to convert seperate date (RecvdDate) and time (RecvdTime) columns into a single DateTime column. I've scoured a lot of web pages but I'm still lost.
==============
use reporting
go
SELECT RecvdDate + RecvdTime FROM [dbo].[CallLog]
===============
Any help much appreciated.
View 7 Replies
View Related
Mar 31, 2008
Hi
We have a t-sql statement in a SP that generates on average between 50 60 rows of data, pretty small! The statement references a View, some tables and temporary # table which has been created in the SP.
Everything works a treat and runs sub second until you put a Insert Into in front of the above statement scenario. The SP then takes a about a minute to run which happens to be about the same amount of time to generate all the data in the View.
I have not attached T-Sql statement at this stage as it runs ok without the Insert Into but would be happy to post it if need be.
Anybody else ever had this problem?
We are using SQL Server 2005 SP2 64 bit.
Art99
View 7 Replies
View Related
Mar 9, 2008
The following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005:
"INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;"
Compact 3.5 doesnt like the SELECT statement claiming that:
There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ]
Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement?
I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.
View 9 Replies
View Related
Nov 5, 2015
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 20, 2005
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
