Insert Record Into Temporary Table From A Select Statement
Jan 17, 2006
Hi guys,
anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.
so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" .... "week 5".
so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?
regards
terence chua
View 4 Replies
ADVERTISEMENT
Jul 20, 2005
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.
View 2 Replies
View Related
Apr 9, 2015
Sometimes I want to quickly to edit a record in a table instead of using an insert statement.
Sometimes there are auditing columns like DateCreated, and CreatedBy,
I know it can be made as default. for DateCreated to be sysdatetime, and createdby to be system user.
But I just curious to know if there is a way to manually enter today's date and the user in the cell?
View 9 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
Jan 8, 2008
Hey gang,
I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query')
I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling.
Here's my current Query this works fine:
Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum
From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo
Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#')
What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note.
I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No)
Any advice would be greatly appreciated.
View 2 Replies
View Related
Feb 12, 2014
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
View 6 Replies
View Related
Mar 10, 2008
Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789
View 2 Replies
View Related
Jul 20, 2005
HiDose any body know why a temporary table gets deleted after querying it thefirst time (using SELECT INTO)?When I run the code bellow I'm getting an error message when open the temptable for the second time.Error Type:Microsoft OLE DB Provider for SQL Server (0x80040E37)Invalid object name '#testtable'.-------------------------------------------------------------------------------------------cnn.Execute("SELECT category, product INTO #testtable FROM properties")'---creating temporary TestTable and populate it with values from anothertableSET rst_testt = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTableSET rst_testt2 = cnn.Execute("SELECT * from #testtable") '----- ERRORopening the temporary TestTable for the second time (that where the erroroccurred)rst_testt2.Close '---- closing table connectionSET rst_testt2 = nothingrst_testt.Close '----- closing table connectionSET rst_testt = nothingcnn.Execute("DROP TABLE #testtable") '------ dropping the temporaryTestTable'-----------------------------------------------------------------------------------------But when I create the temp table first and then INSERT INTO that table somevalues then it is working fine.'-----------------------------------------------------------------------------------------cnn.Execute("CREATE TABLE #testtable (category VARCHAR(3), productVARCHAR(3))")cnn.Execute("INSERT INTO #testtable VALUES('5','4')")SET rst_testt = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTableSET rst_testt2 = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTable for the second timerst_testt2.Close '----- closing table connectionSET rst_testt2 = nothingrst_testt.Close '----- closing table connectionSET rst_testt = nothingcnn.Execute("DROP TABLE #testtable") '------ dropping the temporaryTestTable'-----------------------------------------------------------------------------------------Does any body know why the first code (SELECT INTO) is not working where thesecond code it working?regards,goznal
View 4 Replies
View Related
Sep 1, 2006
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
View 6 Replies
View Related
Mar 21, 2006
I'm trying to insert the results of a stored procedure call into a temporary table, which is not working. It does work if I use a non-temporary table. Can anyone tell me if this is supported or what I am doing wrong.
Here is an example:
-- DROP PROCEDURE testProc
CREATE PROCEDURE testProc AS
BEGIN
SELECT '1111' as col1, '2222' as col2
END
-- this call will fail with message Invalid object name '#tmpTable'.
INSERT INTO #tmpTable EXEC testProc
--- DROP TABLE testTable
CREATE TABLE testTable (col1 varchar(5), col2 varchar(5))
-- this call will succeed
INSERT INTO testTable EXEC testProc
View 5 Replies
View Related
Apr 11, 2007
Hi,
I have a series of queries which have doubled in the amount of time they take to execute since moving to SQL Server 2005. The queries being performance tested are utilising hardware that is very similar to that of the comparison SQL Server 2000 server. They have 6 CPUs exactly the same and we have swapped RAM around to eliminate that difference.
There are 4 parts to the query suffering performance degredation.
1. Create temporary results table
2. Create (using SELECT INTO) and populate temporary working table 1 - 212,263 rows
3. Create (using SELECT INTO) and populate temporary working table 2 - 5,102 rows
4. Insert into temp results table matches found in temp work table 1 and temp work table 2 - 382 rows
On 2000, the queries take a total of 15 secs. On 2005, they take 30 seconds. Part four of the query takes approx 17 secs on its initial run. However, if i truncate the temp results table and re-run just the last query it only takes 1 sec. Query Plan caching?
I have reviewed the forum for a solution to the problem but with no luck. Many of the solutions presented appear to relate to permanant user tables. Solutions such as UPDATE STATISTICS and recompiling stored procedures have no positive effect. When reviewing the query plans, there are very little differences. Some expected between versions right?
The following code snippet is the query from part 4.
Code Snippet
INSERT #MatchingResults
(Table1IDNo, Table2IDNo, MatchRunNo)
SELECT DISTINCT #Table2.IDNo AS Table2IDNo,
#Table1.IDNo AS Table1IDNo,
1 AS MatchRunNo
FROM #Table1
INNER JOIN #Table2
ON ( #Table2.LastName = #Table1.LastName )
AND ( #Table2.AddressDetails = #Table1.AddressDetails )
AND ( #Table2.Country = #Table1.Country )
AND ( ( #Table2.FirstName = #Table1.FirstName) OR ( #Table1.FirstName = '' ) )
AND ( ( #Table2.Title = #Table1.Title ) OR ( #Table1.Title = '' ) )
The query plan shows a hash join on both servers. I have tried removing the distinct statement and forcing a Loop Join (query hint).
I have also run SQL Profiler. The only differences there appear to be with the "SELECT StatMan" statements.
On 2000, part of the query duration is 1719 and is as follows:
Code SnippetSELECT statman([AddressDetails],[LastName],[FirstName],[Title],[Country],@PSTATMAN)
FROM (SELECT TOP 100 PERCENT [AddressDetails],[LastName],[FirstName],[Title],[Country] FROM [dbo].[#TMCT04042007101009_________________________________________________________________________________________________000000000096] WITH(READUNCOMMITTED,SAMPLE 3.675520e+001 PERCENT) ORDER BY [AddressDetails],[LastName],[FirstName],[Title],[Country]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)
On 2005, part of the query duration is 5188 and is as follows:
Code Snippet
SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [AddressDetails] AS [SC0] FROM [dbo].[#TMCT04042007101009_________________________________________________________________________________________________00000000000E] WITH (READUNCOMMITTED,SAMPLE 7.946877e+001 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)
Its clear that the sampling rate is higher. I assume this could have something to do with it. Can this be modified?
Thank-you for your help in advance..
Cheers
Tim
View 9 Replies
View Related
Apr 21, 1999
I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?
Thanks,
Sam
View 1 Replies
View Related
Jan 5, 2001
I HAVE A SELECT STATEMENT WITH TEACHERS AND STUDENTS AND SOMETHING ELSE TOO.
FOR EACH TEACHER I ONLY NEED ONE(FIRST ONE) STUDENT.
HOW THE STATEMENT SHOULD BE?
SELECT DISTINCT .... TID, SID, SOMETHING ???????
View 3 Replies
View Related
Jul 23, 2005
Ok,I have a data warehouse that I am pulling records from using OracleSQL. I have a select statement that looks like the one below. Now whatI need to do is where the astrics are **** create a case statement orwhatever it is in Oracle to say that for this record if a 1/19/2005record exists then End_Date needs to be=1/19/2005 else getEnd_Date=12/31/9999. Keep in mind that a record could have both a1/19/2005 and 12/31/9999 instance of that account record. If 1/19exists that takes presedent if it doesnt then 12/31/9999. The problemis that the fields I pull from the table where the end_date is inquestion change based on which date I pull(12/31/9999 being the mostrecient which in some cases as you see I dont want.) so they are notidentical. This is tricky.Please let me know if you can help.SELECTCOLLECTOR_RESULTS.USER_ID,COLLECTOR_RESULTS.LETTER_CODE,COLLECTOR_RESULTS.ACCT_NUM AS ACCT_NUM,COLLECTOR_RESULTS.ACTIVITY_DATE,COLLECTOR_RESULTS.BEGIN_DATE,COLLECTOR_RESULTS.COLLECTION_ACTIVITY_CODE,COLLECTOR_RESULTS.PLACE_CALLED,COLLECTOR_RESULTS.PARTY_CONTACTED_CODE,COLLECTOR_RESULTS.ORIG_FUNC_AREA,COLLECTOR_RESULTS.ORIG_STATE_NUMBER,COLLECTOR_RESULTS.CACS_FUNCTION_CODE,COLLECTOR_RESULTS.CACS_STATE_NUMBER,COLLECTOR_RESULTS.STATE_POSITION,COLLECTOR_RESULTS.TIME_OBTAINED,COLLECTOR_RESULTS.TIME_RELEASED,COLLECT_ACCT_SYS_DATA.DAYS_DELINQUENT_NUM,sum(WMB.COLLECT_ACCT_SYS_DATA.PRINCIPAL_AMT)As PBal,FROMCOLLECTOR_RESULTS,COLLECT_ACCT_SYS_DATA,COLLECT_ACCOUNTWHERECOLLECT_ACCOUNT.ACCT_NUM=COLLECT_ACCT_SYS_DATA.ACC T_NUM(+)ANDCOLLECT_ACCOUNT.LOCATION_CODE=COLLECT_ACCT_SYS_DAT A.LOCATION_CODE(+)AND COLLECT_ACCOUNT.ACCT_NUM=COLLECTOR_RESULTS.ACCT_NU M(+)AND COLLECT_ACCOUNT.LOCATION_CODE=COLLECTOR_RESULTS.LO CATION_CODE(+)AND COLLECTOR_RESULTS.ACTIVITY_DATE =to_date(''01/19/2005'',''mm/dd/yyyy'')AND COLLECT_ACCOUNT.END_DATE = to_date(''12/31/9999'',''mm/dd/yyyy'')AND COLLECT_ACCT_SYS_DATA.END_DATE = *****************
View 1 Replies
View Related
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
View Related
Jul 31, 2014
I need to write a select statement that take the upper table and select the lower table.
View 3 Replies
View Related
Jul 24, 2015
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
View 4 Replies
View Related
Jan 17, 2007
Greeting.
I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.
I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.
Have this problem solution? May be, I must use another method to get my data?
P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.
P.S.S. Sorry for bad english.
View 1 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 23, 2008
Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER - using SELECT stmt
2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output).
3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4. I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing. And I do not see error in my package either. It is not failing.
Thanks in advance!
View 6 Replies
View Related
Apr 21, 2008
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
View 7 Replies
View Related
Jun 25, 2015
I have a table in different databases with the same name. My goal is to compare the two tables, and insert a record into the second table where there is not a match in the first table.  So far, my query looks like the following:
SELECT [metal] FROM [ProductionDatabase].[dbo].[Metalurgy]
EXCEPT
SELECT [metal] FROM [TestDatabase].[dbo].[Metalurgy]
This gives me a list of records from [Production].[dbo].[Metalurgy] which do not reside in [TestDatabase].[dbo].[Metalurgy]. Now, I need to use that list to insert missing records into [TestDatabase].[dbo].[Metalurgy]. How can I modify the above code to INSERT the missing records?
View 4 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
