I've got a case where I am bringing older data into a brand new DB. Unfortunately, there are several versions of the old DB and I won't know ahead of time which one I will be running against. The data's basicaly the same, the table names are the same, but the column schema is NOT.
Basiclly, I want to use a conditional on the Insert.
Code Block
CREATE TABLE TestTable
(Col1 int NULL,
Col2 varchar(50) NULL)
GO
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG= 'TestDB'
AND TABLE_NAME ='TestTable'
AND COLUMN_NAME= 'Fred')
This results in an "Unknown column" error.
What am I missing here and how can I accomplish it.
I know that I COULD build a SQL string and Exec it, but that is such a PITA and not very elegant.
Code is really not important. Any sys SP can replace the above code.
I am getting the following error Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80 An INSERT EXEC statement cannot be nested.
I have seen the following link which discuss this issue, http://www.sommarskog.se/share_data.html But there is no solution there.
I tried with sp_executesql and EXEC(), but unable to get the result. Can anyone put some light?
------------------------ I think, therefore I am - Rene Descartes
I have a stored procedure that transfers data from one database to another. I do a lot of manipulation to the data in between because although both databases are for the same purpose, the database designs are different. The two databases exist on two different servers. Till now my transfer procedure used a linked server to pull the data from one server to the other. This process takes a ton of time with complex queries across linked servers. I was researching on a faster method to transfer data and the only other working method seemed to be the insert..exec statement with which I can call a remote stored procedure which would run and return the results of the complex queries which I can then use locally as I wish.
My main question is, can I rely on insert..exec to work consistently and/or is there another method to achieve what I need that I'm totally ignorant of? I already know of inline/multi table value functions (they wont work across servers); openquery/openrowset (I need to be able to pass parameters); creating permanent tables to be used and then destroyed (not very appealing). I read an excellent article about these online by Erland Sommarskog (http://www.sommarskog.se/share_data.html) but I'm hoping someone can explain a little further.
There must be some workaround for this. I need to grab the data from a stored proc on another server and place it into a temp table. I do not have the option to change the proc.
SELECT PIN, MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 1' THEN VALUE ELSE NULL END) AS address1, MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 2' THEN VALUE ELSE NULL END) AS address2, MAX(CASE WHEN HEADER = 'ADDRESS DETAILS' AND PROPERTY = 'LINE 3' THEN VALUE ELSE NULL END) AS address3, MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN VALUE ELSE NULL END) AS MemberNo, MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Preferred method of contact*' THEN VALUE ELSE NULL END) AS Tel1, MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Tel number (o/h) e.g. 011 2690000' THEN VALUE ELSE NULL END) AS Tel2, MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Mobile number e.g. 0821234567' THEN VALUE ELSE NULL END) AS Tel3, MAX(CASE WHEN HEADER = 'CONTACT DETAILS' AND PROPERTY = 'Fax number e.g. 011 2691000' THEN VALUE ELSE NULL END) AS Tel4, MAX(CASE WHEN HEADER = 'Date Of Birth' AND PROPERTY = 'eg. 04 Jan 1965' THEN VALUE ELSE NULL END) AS DOB, MAX(CASE WHEN HEADER = 'Email address' AND PROPERTY = 'Email Address' THEN VALUE ELSE NULL END) AS Email, MAX(CASE WHEN HEADER = 'ID Number' AND PROPERTY = 'ID Number' THEN VALUE ELSE NULL END) AS IDNumber, MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Title' THEN VALUE ELSE NULL END) AS Title, MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Initials' THEN VALUE ELSE NULL END) AS Initials, MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Firstname' THEN VALUE ELSE NULL END) AS Firstname, MAX(CASE WHEN HEADER = 'Member Information' AND PROPERTY = 'Surname' THEN VALUE ELSE NULL END) AS Surname, MAX(CASE WHEN HEADER = 'ACOMPANY' AND PROPERTY = 'Membership Number' THEN STATUS ELSE NULL END) AS STATUS
FROM
FULLSOURCE
GROUP BY PIN
Now how can I reverse this to insert all the values back to tbl FULLSOURCE from maybe an updated SSOTARGET Tbl. FULLSOURCE looks like this:
I need to do a conditional insert. This is what I have tried, it does not work. What am I doing incorrectly?
IF (SELECT COUNT(*) FROM TBL1 INNER JOIN TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID INNER JOIN TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE INNER JOIN TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC) > 0
INSERT INTO TBL5 SELECT TBL1.PRO_SITE, TBL1.MODEL_ID, TBL1.NUM_SLOTS, TBL1.TARGET_DAYS, GETDATE() AS Expr1, TBL3.TYPE_ID, NULL AS Expr2, TBL1.NUM_SLOTS AS Expr3, NULL AS Expr4, NULL AS Expr5, 0 AS RAMP FROM TBL1 INNER JOIN TBL2 ON TBL1.MODEL_ID = TBL2.MODEL_ID INNER JOIN TBL3 ON TBL1.PRO_TYPE = TBL3.TYPE INNER JOIN TBL4 ON TBL1.PRO_SITE = TBL4.WHDESC
UPDATE TBL5 SET TEAMS=0 GO UPDATE TBL5 SET TEAMS = (SELECT NUM_SLOTS FROM TBL6 R1 WHERE (R1.TEAM_ID = TBL5.TEAM_ID) ) WHERE (TEAM_ID = (SELECT TEAM_ID FROM TBL6 R3 WHERE (R3.TEAM_ID = TBL5.TEAM_ID))) GO UPDATE TBL5 SET TOTALTEAMS = TEAMS + RAMP GO
I need to do a conditional because sometimes the select that returns data contains no records.
Hello all. I am inserting data into a table from a linked server (.XLS file). One of the columns deals with dates. In the .XLS file some ofthe dates are invalid (either wrong format or wrond data). What i am looking for is how to insert only the rows where the dates are correct. My basic structure is:
INSERT INTO PENSIONS(EMPLOY_REF, CONTRIB_CHANGE) SELECT CAST(EMPLOY_REF AS VARCHAR(10)), CAST(CONTRIB_CHANGE AS DATETIME) FROM mockdownload_dd...[Pensions$]
has anyone come across deadlocks on sysindexes in tempdb where the insert/exec combination is used.
eg
create table #fred (IntColumn int)
insert into #fred exec ProcThatSelectsAnIntColumn
This is being done in a stored procedure, and is deadlocking with other procs which are doing vanilla #table work - creating, inserting into, updatind, selecting from, etc.
I have noticed similar deadlocks where a #table is created inside an explict transaction, and I wondered whether there is an implicit transaction created, but @@Nestlevel is not changing either before or after the insert/exec.
I can't find any references in knowledgebase.
Any pointers appreciated.
Cheers Simon ________________________ Simon Davis Bankers Trust Australia Limited Asset Management Technology Ph: 61 2 9259 9137 <mailto:Simon.Davis@Bankerstrust.com.au>
I have an sp that sends cdomail which requires 4 variables. I want an after insert trigger that fills in the values for the sp from the record just submitted, how can i do that?
This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary. References to the CDOSYS objects are at the following MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
************************************************** *********************/ AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare @description varchar(500) Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************ EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ****************** -- This is to configure a remote SMTP server. -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' -- This is to configure the Server Name or IP address. -- Replace MailServerName by the name or IP of your SMTP Server. EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.bbeyond.nl'
-- Save the configurations to the message object. EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling. IF @hr <>0 select @hr BEGIN EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END END
-- Do some error handling after each step if you have to. -- Clean up the objects created. EXEC @hr = sp_OADestroy @iMsg
Hello,I want to share my experiences about using insert into exec which mayhelp others .Using SQL Server 2000, SP3 .Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicittransactions .I have defined a # table in Parent SP and calling a Child SP like thisinsert into #temp exec childsp ......Child SP has Select * from local # temp table ( local to child SP) as the last statement .When number of records are less ( around 1000 - 5000) Parent SPexecutes but slow .When the Child SP returns higher number of rows ( 1,00,000 or more )the SP will be running for hours with out completion .Although executing the child SP , with exec ChildSP .... with sameparameters it is completed in 2 mins for 3,00,000 rows .Resolution : - Define a temp table (say #tempChild ) in the Parent SP..In the Child SP instead of select * replace with insert into#tempChild select * from ...Also note that this problem is not noticed in SQL 2000 Server with SP4..This may be due to SP executing in implicit transactions .
I try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure, When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.". In Fire bird /Interbase store procedure we can nested. Below are the code; declare @dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20)) Insert Into @dtReturnData Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES' Select doccode, docdate, debtoraccount From @dtReturnData Inside the GetPickList It will do like this, but most of the code I not included; ALTER PROCEDURE GETPICKINGLIST @doctype nvarchar(2), @datefrom datetime, @dateto datetime, @includegrn char(1), @includesa char(1), @includedata nvarchar(5) AS BEGIN declare @dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20)) IF (@DOCTYPE = 'SI') BEGIN Insert Into @dtSALESINVOICEREGISTER Exec SALESINVOICEREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData END ELSE BEGIN Insert Into @dtDELIVERYORDERREGISTER Exec DELIVERYORDERREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData END Select doccode,docdate,debtoraccount From @dtReturnData END
So how can I select a nested store procedure? can someone help me
I have written a master proc which calls another proc (say proc1). This proc1 has insert-exec statements, for eg insert into #temp exec proc1. i.e. multiple times the proc would be nested.
This the err thrown : An INSERT EXEC statement cannot be nested.
I am new to MSSQL and am trying to write a complicated SQL statement that I'm having trouble with. Any help that anyone can offer is much appreciated!
Here is the problem I am tackling:
I have a list of about 5,000 members of our organization stored in the MemberList MSSQL table. I have a separate MSSQL table (CityList) that has approximately 500,000 resident of a city.
I am trying to find matches between MemberList and CityList for the purposes of figuring out which of our members are registered voters.
The tricky part of this problem, is that there is no unique ID (such as a social security number) that is present in each list. Accordingly, I have decided to created several types of matches:
1. NameDOBMatch: Where the FirstName, LastName & DOB fields in MemberList table match the same fields in CityList table.
2. NameAddressMatch: Where the LastName, FirstName & Address fields in the MemberList table match the same fields in the CityList table.
3. DoubleMatch: A combination of the first two matches (i.e. where the LastName, FirstName, DOB, & Address fields in the MemberList table match the same fields in the CityList table).
My goal is to "loop" through the MemberList and CityList tables and to add a new row to a third MSSQL table (MemberMatch) each time one of the aforementioned matches is found. The MemberMatch table has the following fields: 1. MatchID (key) 2. MemberID (Unique ID of member from MemberList table) 3. ResidentID (Unqiue ID of matching member from CityList table). 4. MatchType (value of NameDOBMatch, NameAddressMatch or DoubleMatch, depending on match type).
If anyone could help me create an SQL statement that would accomplish that, I would very much appreciate it!
Is there any way to create a "Conditional Insert Trigger" My Scenario is this; When a user adds an email address to the database, I want to look to see if the email address is like '%@acme-holdings%' and if it is then to change the value to 'Not allowed', otherwise to leave it alone and go ahead with inserting the original email address
I need to dynamic select a column in which insert a vale based on aparameter value, I have this code, but it throws an incorrect syntaxerror.How do I dinamically select a column to insert based on a parameter?Create PROCEDURE dbo.UpdateDetalleOT (@eotId int,)insert into OT (select Casewhen @eotId = 1 THEN OTFechaBorradorwhen @eotId = 2 THEN OTFechaAAsignarend) values ....Best RegardsFabio Cavassinihttp://www.pldsa.com
Hi When i use my insert-exec in my proc, say it Y, and the inner proc is X (don't contain an other insert-exec). when i call the Y proc in sql query analyzer the call is executed and it get me back results.
But when I call the Y proc from an ASP page, the execution is aborted, and I don’t know why, I have tested many thinks but it doesn’t work (for exp: the use commit transaction, set ....). Please could you tell me if i miss some things i should add to my procs for the IIS web server could have right to execute my Y proc
Configuration of my application
- IIS Web server 6.0
- Sql Server 2000 SP 2.0
- TSQL as a sql language (of course)
- ASP as web porgramming language
- The call of the insert exec proc is as following:
I'd *like* to execute a sProc within another sProc, because this other one (let's call it dataProc) will provide me with results I need that are calculated based off execing other procs within that proc itself.
I'd like to use this data in a new sProc (we'll call it newProc), but I don't need NEARly all of the columns. The dataProc returns 1 row with 42 columns. I need about 4-5 of those for my newProc.
The dataProc does not have any output variables, and I do not want to change the signature, because several pages/apps use this existing dataProc.
My question: Is there a way to INSERT INTO table EXEC dataProc *Without* making a temp table that takes in every single column the dataProc puts out?
Hi,I have a sproc with 5 params that takes about 40 seconds to return.But when I Create a Temp table and do aInsert Into #tempExec sproc param1, param2, param3, param4, param5it never returns...any ideas?Thanks,Bill
Hi,all,When I use following sql, an error occurs:insert into #tmprepEXECUTE proc_stat @start,@endThere is a "select * from #tmp " in stored procedure proc_stat, and theerror message is :Server: Msg 8164, Level 16, State 1, Procedure proc_stat, Line 42An INSERT EXEC statement cannot be nested.What's the metter? Any help is greatly appreciated. Thanks
I need to do a conditional insert, and I am afraid that if I do it in 2 steps it will create bugs...I have a table called customers and a table called lockscustomers has 3 fields CustomerID, FirstName, and LastNamelocks has fields LockID, CustomerID, UserID, SessionID, and TimeStampI need to check if there are any records in Locks for a CustomerID and if there are none, I need to insert One.This is going to tell my application that this Customer record is locked so no one else can edit it, until the Lock record is deleted.I just don't want to make 2 trips, One to check if the Lock exists, then One to insert the Lock, since this could allow 2 locks to be created if the timing is correct.Any ideas or comments?
I have several places where I need to get the id (primary key) of a resource, inserting a row if the resource does not exist (i.e. an artificial key to be used as an FK for another table). I should probably change this varchar key lookup to use a hash index, but that is beside the point.
So the table is essentially like: CREATE TABLE MyLookup(id int identity primary key nonclustered, mykey varchar(256)); CREATE CLUSTERED INDEX mylookup_cidx_mykey ON MyLookup(mykey);
I see two main approaches for how I can do my get-id-with-insert-if-needed.
(Approach 1) DECLARE @id INT; SELECT @id = id FROM MyLookup WHERE mykey = 'some key value'; IF (@id is null) BEGIN
INSERT MyLookup ('some key value'); SET @id = SCOPE_IDENTITY(); END
(Approach 2) DECLARE @id INT; INSERT MyLookup SELECT 'some key value' WHERE NOT EXISTS (SELECT id FROM MyLookup WHERE mykey = 'some key value'); IF (@@ROWCOUNT = 0)
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value'; ELSE
SET @id = SCOPE_IDENTITY();
From some quick tests in profiler, approach 2 seems to be a bit faster and have lower resource utilization. But I'm not sure if it maybe takes some more aggressive locks even in the unnecessary case where the mykey row value of 'some key value' already exists. Approach 2 also looks cleaner to me, but I don't mind a bit of extra code if it gives me better scalability through less lock contention.
Any tip on what is considered the best practice for a conditional insert like this, or a tip on how to get detailed lock info for a query? The lock info for profiler was all greek to me, it just had a hex value with each lock acquired/released, so I have no idea what it was telling me. Is my only solution to just run exhaustive tests and look at the perf numbers from the black box?
I have 3 development SQL Servers A, B & C, all running SQL 2000 sp3 and Windows 2003. Servers B & C have a linked server pointing to A, and A has one pointing to B & C. The linkedservers all have RPC , RPC out enabled. I have a stored procedure called test on server A.
When the Insert....Exec code above is ran from server B it works fine, however when I run it from Server C, I get error 7391 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]
But regular linked server calls (directly to tables) and openquery calls work fine from either server...
Eg insert into #tmp Select top 5 first_name, last_name from ServerA.db1.dbo.people
--and this works also
insert into #tmp Select * from openquery(ServerA, 'Exec db1.dbo.test')
Both servers (B & C) appear to be configured the same, and I have reconfigured MSDTC on all three boxes through control panel and component manager, have tried using SET xact_abort, SET implicit_transactions, registry hacks (TurnoffRPCsecurity), basically everything listed on Microsoft, and everything I've been able to find in these groups.
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
---Selection results SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expectedresult FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
--Now here what i am trying to get, union the below two record to the above "selection Results" for only segment ='VEC_BAL' or segment = 'PPC_BAL'
--2 records SELECT l.segment , l.email , Addr1, City, ST, 999 as Code,'' fname, ''lname from #LIST l where email ='mmm@m.com' SELECT l.segment , l.email , Addr1, City, ST, 888 as Code, '' fname, ''lname from #LIST l where email ='lrk@m.com'
--- expected results please execute below block ( per each segment 2 records will insert if there is segment ='VEC_BAL' or 'PPC_BAL' ). If there is no Vec_Bal or PPC BAL then no additional inserts, if there is only VEC_BAL no PPC_BAL then only 2 records insert SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expctresults1 FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment union
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
