Conditional Insert Without Using Exec
Dec 27, 2007
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')
INSERT TestTable
(Col1, Col2, Fred)
VALUES
(1,'test', 'fredvalue')
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.
Any help would be appreciated
View 3 Replies
ADVERTISEMENT
Jan 21, 2004
I need to insert the results into a temp table and i recieve the "MSDTC on server 'servername' is unavailable error".
declare @thestringall varchar(1000)
set @thestringall = 'select statment here'
insert into #temptable exec (@thestringall)
Thanks
Wooanaz
View 2 Replies
View Related
Apr 19, 2007
I need get the o/p of a system sp into a table. I am doing the following,
insert #repl_monitor
exec [distribution].sys.sp_replmonitorhelpsubscription @publisher =
N'FGRWA0508', @publisher_db = N'DB_Name', @publication = N'publication'
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
View 8 Replies
View Related
Oct 23, 2007
Hi,
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.
Thanks all,
Anand
View 11 Replies
View Related
Nov 9, 2007
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.
help if you can.
View 7 Replies
View Related
Oct 29, 2006
Hi,
Originally had 2 tables, fullsource and ssotarget. I did the following extract on fullsource due to its irregular schema and inserted into ssotarget:
INSERT SSOTARGET (pin, address1, address2, address3, MemberNo, Tel1, Tel2, Tel3, Tel4, DOB, Email, IDNumber, Title, Initials, Firstname, Surname, STATUS)
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:
SELECT [PIN]
,[SYSTEM]
,[Header]
,[Property]
,[Value]
,[Status]
,[ID_Header]
,[ID_Property]
FROM [fullsource]
View 1 Replies
View Related
Oct 22, 2004
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.
Thanks...
View 1 Replies
View Related
Aug 8, 2006
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$]
thanks people
View 8 Replies
View Related
May 25, 1999
Hi,
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>
View 2 Replies
View Related
Jul 6, 2004
Hi all,
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?
Sp code
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/************************************************** *******************
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
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- 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
GO
View 5 Replies
View Related
Jul 24, 2006
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 .
View 1 Replies
View Related
Sep 19, 2007
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
View 1 Replies
View Related
Nov 9, 2006
Hi,
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.
Is it possible to resolve it..
View 5 Replies
View Related
Jun 16, 2006
Hi All,
I am having a problem with nested insert exec. Say for example I have three stored procedure procA, procB and procC.
I am executing procedure procC in procB; I am storing the values returned by procC in a tempTable. The code is
INSERT INTO #Temp
EXEC procC
And in procA, I am executing procB, and the values returned by procB are stored in another temp table. The code is
INSERT INTO #TempOne
EXEC procC
When I execute the procA, I am getting error as
An INSERT EXEC statement cannot be nested.
My requirement is like this, please give me a solution.
Thanks
View 5 Replies
View Related
Apr 12, 2006
i have a 3 or 4 cursors, and in the inner cursor i am inserting into a table from a sproc. i keep getting the error
An INSERT EXEC statement cannot be nested.
heres the actual insert code:
set @SQLString = 'EXEC ScoresGetlines '+cast(@customerID as char(10))+',' + cast(@programId as char(10))+',' + '"'+ @period +'",NULL,NULL,0'
INSERT INTO reportData
exec (@sqlString)
ive tried just a simple :
insert into reportdata
exec scoreGetLines @customerId,@programID...........
that still doesnt work. same error. how can this be sorted
View 13 Replies
View Related
Feb 7, 2008
I am using the following statement in a SP.
EXECUTE (' INSERT INTO #OutPut
EXEC h_DailyDividend
@TickerTable = '+@TickerTableName+',
@DateTable = '+@DateTableName+',
@Units = '+@Units
)
AND IN h_DailyDividend I am using the following statement.
EXECUTE (' INSERT INTO #TickerTable
EXEC h_SecMstr_SecMap_TQAExch_Info
@IDList = '+@TickerTable+',
@IsTable = 1,
@Type = 0,
@OutPutFormat = 0,
@VenType = 14 ')
And i am getting the following error.
Message: An INSERT EXEC statement cannot be nested.
Can any body help me out how to solve this problem.
Regards
Sulaman
View 4 Replies
View Related
Jun 4, 2008
Good Afternoon,
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!
Thanks,
Bryan
View 9 Replies
View Related
Mar 14, 2006
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
TIA
ICW
View 9 Replies
View Related
Jan 21, 2006
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
View 9 Replies
View Related
Sep 19, 2007
Hi
I am doing bulk insert as follows. The @lastUpdate, @filePath, @formatFile comes as a parametes to stored proc
INSERT INTO Categories
SELECT CategoryId, @LastUpdate FROM OPENROWSET
(
BULK @filePath ,
FORMATFILE = @formatFile,
FIRSTROW =2
)
AS a
This works fine for me.
But my new requirement is that i shouldn't insert the CategoryId if it exists
How can we have conditional bulk insert? i am using Bulk insert as the file might have millions of category Ids.
Please provide your inputs that executes much faster
Best Regards,
~Mohan Babu
View 1 Replies
View Related
Sep 14, 2004
Hi all,
I have to insert records using transact iin the stored procedure. I have some thing like:
DECLARE @Err varchar(100)
DECLARE insertQ varchar(1000)
SET @Err = 'Insertion data'
SET @insertQ =('INSERT INTO dbo.T_ERRORLOG (ERROR_DESCR) VALUES(' + @Err + ')')
EXEC insertQ
But it don't work. Can you help me to solve the problem plz?
Thanks a lot
TT
View 4 Replies
View Related
May 20, 2008
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:
o INSERT INTO #TMP_F_B (
o Field1 ,
o Field2,
o Field3 ,
o …,
o )
o EXEC dbo.ps_a5s_rpt_charge700 @util, @annee , @perimetre , @secteur , @poste , @fournisseur , @err , @lib_err
Many thanks
Sincerly
View 2 Replies
View Related
May 3, 2004
HI,
WELL WE HAVE BEEN TRYING TO AUTOMATE A PROCEDURE OUT HERE,AND WE ARE TRYING TO CONVERT MOST OF THE THINGS INTO PROCEDURES.
BUT WE ARE GETTING A FEW HICCUPS. PLS HELP
THIS IS HOW IT GOES :-
CREATE PROCEDURE MY_PROC1
AS
BEGIN
ST1 .........;
ST2..........;
END
CREATE PROCEDURE MY_PROC2
AS
BEGIN
CREATE TABLE #TMP2
(COL1 DATATYPE
COL2 DATATYPE)
INSERT INTO #TMP2
EXEC MY_PROC1
ST1 .........;
ST2..........;
END
THIS PROCEDURE TOO RUNS WELL ,AFTER TAKING THE DATA FROM THE FIRST PROC IT MANIPUATES THE DATA ACCORDING TO THE CRITERIA SPECIFIED
NO PROBLEM TILL NOW.......
BUT,
CREATE PROCEDURE MY_PROC3
AS
BEGIN
CREATE TABLE #TMP3
(COL1 DATATYPE
COL2 DATATYPE)
INSERT INTO #TMP3
EXEC MY_PROC2
ST1 .........;
ST2..........;
END
THEN IT GIVES AN ERROR AS :-
"An INSERT EXEC statement cannot be nested."
CAN'T WE , FROM A PROCEDURE CALL A PROCEDURE WHICH CALLS A PROCEDURE........
WHAT IS THE NESTING LEVEL OF A PROCEDURE ?
IS THERE ANY WAY AROUND IT OR CAN IT BE DONE BY CHANGING SOME SETTINGS ?
PLS HELP ME OUT IN THIS
THANKS
View 13 Replies
View Related
Aug 17, 2006
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?
Any help is much appreciated.. thanks!
View 8 Replies
View Related
Aug 15, 2005
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
View 1 Replies
View Related
Nov 28, 2005
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
View 2 Replies
View Related
Oct 17, 2005
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?
View 1 Replies
View Related
Aug 7, 2007
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?
View 1 Replies
View Related
Mar 12, 2008
Hello all,
I just wanted to know whether is it possible to use Bulk Insert and Conditional Split together for one transformation.
Regards,
Kapadia Shalin P.
View 3 Replies
View Related
Feb 17, 2004
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.
Create Proc test
as
Select Top 5 first_name, last_name from people
GO
--code ran on Servers B & C:
create table #tmptbl (nm varchar(100), nm2 varchar(100))
insert into #tmptbl
Exec ServerA.db1.dbo.test
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.
If anyone has any ideas, I'd like to hear them.
Tim.
View 3 Replies
View Related
Dec 15, 2005
Hi!
I have a table called DB1 that contains this:
MID
IIN
NUM_EVENTS
DATE
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?
View 6 Replies
View Related
Apr 4, 2014
In this selection when there is particular segment found in the selection then need to add 2 records per segment otherwise just selected results
--Drop table #list
CREATE TABLE #LIST ( email varchar(20),Segment varchar(20), Addr1 varchar(20), City varchar(20),ST varchar(20), Code int)
--Drop table #subject
CREATE TABLE #Subject (Segment varchar(20), Fname varchar(20), LName varchar(20))
[Code] ....
---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
[Code] .....
View 3 Replies
View Related
Dec 15, 2005
Hi!
I have a table called DB1 that contains this:
MID
IIN
NUM_EVENTS
DATE
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?
$3.99/yr .COM!
http://www.greatdomains4less.com
View 3 Replies
View Related
