I need to push data from a temporary table into a master table in sql server database(Both tables are in the same database).
I need to follow these conditions.
tables used:
1. temporary table: Temp
2. master table: Master
for every record or row in Temp
check if exists(Temp.field1)in Master.Field1 then
update Master with this row.
else Insert into Master this row.
I performed a research through the net, and found various suggestions. like usage of cursors, usage of while loops etc.
I have to use nearly 50,000 to 60, 000 rows minimum or even more.
Time complexity is also to be considered
I request all who visit this thread to place any possible solutions/suggestions how shall i make this task.
Hi all,I just start using SQL Server for my project. I have some questionsrelated to set operations. Suppose I have two tables, Table A andTable B, as following.TableA TableB======= =======--------------- ---------------| ID | DATA | | ID | DATA |--------------- ---------------| 1 | Val-01 | | 1 | Val-01 || 2 | Val-01 | | 2 | Val-02 || 3 | Val-02 | | 3 | Val-02 || 4 | Val-03 | | 4 | Val-03 || 5 | Val-04 | ---------------| 6 | Val-05 |---------------In DB2, I can write SQL statements as followingSQL 1:===========================SELECT DATA FROM TableAEXCEPTSELECT DATA FROM TableBAnd the result will beVal-04Val-05===========================SQL 2:===========================SELECT DATA FROM TableAEXCEPT ALLSELECT DATA FROM TableBAnd the result will beVal-01Val-04Val-05===========================1. How can I handle the EXCEPT (ALL) operator in SQL Server?2. Are there equivalent SQL queries for the above SQL queries?Thank youErwin Leonardi
I'm still a newbie with MS SQL DB. I was asked to perform some tasks within 3 days at maximum as follows:
1- Refine unique names from over than 80,000 students' names (Firstname, Middlename and Lastname) into a unique table so that we take all those name and translate them from local language to English through data entry employees (a dictionary).
2- Replace local names by English names into a new columns.
3- Creating a procedure that auto generates aliases based on Firstname, Middlename and Lastname as follows:
Assume the name John Edward Smith is found 5 times between 80,000 students, to create a unique ID do the following:
I would like to know if there is a way to disable logging within SQL. (SQL7) I set all of the obvious options (select into/bulk copy; truncate on checkpoint...) -- and not using SQL Servers conditions set in books online.
Here is the situation:
We have a VB app that updates about 14million records on a monthly basis. The commands are INSERT, UPDATE.. (no select into OR write text) There is no way to use this app that I know using BCP, or bulk insert since we are updating and not simply importing? So now when this process runs, first the Tempdb fills(problem 1). I shut down SQL and re-initalized the TEmpdb. The data "update" should take ~3 days. Initial run shows the Tempdb filling, then the thread halts. Basically, the application copies records from a user defined "staging" database into a second user database. The second db's transaction log is growing considerable (say 6GB and growing after only a day) (problem2). I am concerned that after resolving the Tempdb from filling (unrestricted size set, and I know Tempdb automatically truncates on checkpoint..) but then we still face the issue to the db that the data is updated will fill out of control!
Ideally, I would like to set a db option that disables logging during this UPDATE statement, in other words operate with no integrety. We backup before so a restore should take care of things in the event. I think this will also aid in performance.
I'm new to SQL Server and relatively new to database design and I have a specific problem I'm trying to resolve. I have a collection of records in a table (let's call them 'tasks') which represent some list of things that needs to be completed. Each task has an associated datetime on which this action is to commence, and my applications is responsible for executing these tasks. My first instinct is to regularly poll the 'tasks' table to determine if there are any tasks which have not been processed and are past their schedule start datetime. But, I'm wondering if there is some sort of database feature that would recognize that a task's starttime has arrived, and could somehow communicate this to my application without my application having to constantly poll the database. Thanks.
Hi,Im trying to write a class that compiles a list of SQLCommands and then executes them all at once. Im trying to reduce the amount of calls to the database.Im also trying just to update the fields which have changed so I cannot use Stored Procedures as that would mean writing way too many stored procedures for every permutation on every table in my database.So Ive decided to build sql commands and then execute them all with one call to the database. When I print the commandtext property of the sqlcommand to the page whilst debugging It shows something like insert into XXX (f1,f2) values (@v1,@v2). Is there any way to see the final sql string, with the @v1 variables replaced by the actual values in the parameters I have added to the sql command?Im building the commands by creating a new sqlCommand. Then I set the commandtext to "insert into XXX (f1,f2) values (@v1,@v2)". Then I Add Parameters to the sqlCommand. My Parameters count is showing the correct value.I want to be sure now that when I go to send these commands to the database as part of one long string that it will work.Thanks,C
i write a following stored procedure create proc prc_BUSINESS_MASTER(@chByMode Char(1), @intLIST_ID int=null,@intLISTBUSINESSID int=null,@dtmDATE datetime=null,@dtmEXPDATE datetime=null,@vchSTATUS varchar(10)=null,@vchOFFICE varchar(100)=null,@numCITY numeric =null,@numCOUNTY numeric=null,@numSTATE numeric=null,@numCOUNTRY numeric=null,@vchSICCODE varchar(50)=null,@numASKINGPRICE numeric =null,@numDOWNPRICE numeric=null,@bitOWNERFINANCE bit=null,@numOWNERFINANCE numeric=null,@numADJ_NETAMOUNT numeric=null,@numSALESAMOUNT numeric=null,@dtmYOE datetime=null,@dtmYRSOWNED datetime=null,@bitBITISACTIVE bit=null,@vchCREATEDBY varchar(50)=null,@dtmCREATEDDATE datetime=null,@vchUPDATEDBY varchar(50)=null,@dtmUPDATEDDATE datetime=null ) as set nocount ondeclare @byIntErrDescOut intdeclare @interrno intdeclare @intdupchk intbegin IF @chByMode NOT IN ('I','U') BEGIN SET @byIntErrDescOut = 1 Return -1 END SET @byIntErrDescOut = 0 /* *************** Insertion Area *************** */
IF @chByMode = 'I' BEGIN IF @vchSTATUS IS NULL OR @vchSTATUS = '' BEGIN SET @byIntErrDescOut = 1 Return -1 END /***** Duplicate checking ****/
SELECT @intDupChk = COUNT(1) FROM BUSINESSMASTER WHERE vchLIST_STATUS=@vchSTATUS And vchLIST_OFFICE=@vchOFFICE IF @intDupChk <> 0 BEGIN SET @byintErrDescOut = 5 Return -1 END
insert into BUSINESSMASTER( intLIST_ID,dtmLIST_DATE,dtmLIST_EXPDATE,vchLIST_STATUS,vchLIST_OFFICE,numLIST_CITY,numLIST_COUNTY,numLIST_STATE,numLIST_COUNTRY,vchLIST_SICCODE,numLIST_ASKINGPRICE,numLIST_DOWNPRICE,bitLIST_OWNERFINANCE,numLIST_OWNERFINACE,numLIST_ADJ_NETAMOUNT,numLIST_SALESAMOUNT,dtmLIST_YOE,dtmLIST_YRSOWNED,bitBITISACTIVE,vchCREATEDBY,dtmCREATEDDATE,vchUPDATEDBY,dtmUPDATEDDATE )values(@intLIST_ID,@dtmDATE,@dtmEXPDATE,@vchSTATUS,@vchOFFICE,@numCITY,@numCOUNTY,@numSTATE,@numCOUNTRY,@vchSICCODE,@numASKINGPRICE,@numDOWNPRICE,@numOWNERFINANCE,@bitOWNERFINANCE,@numADJ_NETAMOUNT,@numSALESAMOUNT,@dtmYOE,@dtmYRSOWNED,@bitBITISACTIVE,@vchCREATEDBY,@dtmCREATEDDATE,@vchUPDATEDBY,@dtmUPDATEDDATE ) SET @intErrNo = @@Error IF (@intErrNo <> 0) BEGIN SET @byIntErrDescOut = 2 Return -1 END /*Insertion Area End*/
/*-------Updation----------*/ /* IF @chbyMode = 'U' BEGIN --IF @intBuyerId IS NULL BEGIN SET @byIntErrDescOut = 1 Return -1 END */ IF (EXISTS (SELECT * FROM BUSINESSMASTER WHERE intLISTBUSINESSID = @intLISTBUSINESSID )) BEGIN
/***** Duplicate checking ****/ SELECT @intDupChk = COUNT(1) FROM BUSINESSMASTER WHERE intLISTBUSINESSID = @intLISTBUSINESSID and vchLIST_STATUS=@vchSTATUS IF @intDupChk <> 0 BEGIN SET @byintErrDescOut = 5 Return -1 END
UPDATE BUSINESSMASTER SET intLIST_ID=@intLIST_ID,dtmLIST_DATE=@dtmDATE,dtmLIST_EXPDATE=@dtmEXPDATE, vchLIST_STATUS=@vchSTATUS,vchLIST_OFFICE=@vchOFFICE,numLIST_CITY=@numCITY,numLIST_COUNTY=@numCOUNTY, numLIST_STATE=@numSTATE,numLIST_COUNTRY=@numCOUNTRY,vchLIST_SICCODE=@vchSICCODE,numLIST_ASKINGPRICE=@numASKINGPRICE,numLIST_DOWNPRICE=@numDOWNPRICE,bitLIST_OWNERFINANCE=@bitOWNERFINANCE,numLIST_OWNERFINACE=@numOWNERFINANCE,numLIST_ADJ_NETAMOUNT=@numADJ_NETAMOUNT,numLIST_SALESAMOUNT=@numSALESAMOUNT,dtmLIST_YOE=@dtmYOE,dtmLIST_YRSOWNED=@dtmYRSOWNED,bitBITISACTIVE=@bitBITISACTIVE,vchCREATEDBY=@vchCREATEDBY,dtmCREATEDDATE=@dtmCREATEDDATE,vchUPDATEDBY=@vchUPDATEDBY,dtmUPDATEDDATE=@dtmUPDATEDDATEWHERE intLISTBUSINESSID = @intLISTBUSINESSID -- AND WhenUpdated = @ptmsUpdated SET @intErrNo = @@Error IF (@intErrNo <> 0) BEGIN SET @byIntErrDescOut = 4 Return -1 END END ELSE BEGIN SET @byIntErrDescOut = 4 Return -1 END END End How can i check this procedure by performing insert ,update operations? ie., how can i passing and updating informations through stored procedures? Explain me in detail....
Every time I mention cursors, I hear a lot of people pointing out how you shouldn't use cursors and how set based operations are much faster.
However, procedural code offers the big advantage that I can embed status/progress type commands into a cursor loop and get accurate data on progress and ETA.
If I execute a single huge UPDATE/INSERT, I am at the whim and mercy of SQL Server. Seemingly simple statements often run for 50 hours+ and I have no clue how far along it is or anything remotely close to an ETA. Often, that is absolutely inacceptable to have mission criticial hardware to be churning through a process for DAYS without the slightest idea if it is actually making progress or not.
Is there any way to get progress/status on such an UPDATE/INSERT command?
Hi, I'm trying to get data from several databses of different kinds (Sql Server and Oracle) and from different servers - is that possible ? If not - is there any way to copy some data from one database to another - like tables and their content (or partial content) ?
I tried to use "select name from <database name>..sysobjects where xtype = 'U'" but I get security error.
I noticed that the current SLQCe driver does not offer support for the APM(Asynchronous Programming Model). Are there any plans to do this in the future? In light of the lack of APM functionality doe anyone have any ideas or thoughts on how async operations could be done, or if they are even needed in the context of applications that use SQL Ce
hi, how can i make mathematical operations with the DateTime format from thw Sql? -- this is the format 9/6/2007 11:09:00 PM -- how can i substract 30 days from that date and know the resulting one? if i have two dates, how can i know what number of hours and minutes are between them (if they are fewer than 24), or what number of days and hours and minutes are (if the difference is grater than 24 hours) please help me, thanks
This statement works fine in T-SQL, but when executed thru ODBC results in a <NULL>. How can I get this to work thru ODBC....? Im guessing I need to use CAST, but not sure how the sytax would be, my attempts have worked but still resulted in a <NULL>...
UPDATE mytable SET switch = left(switch, 3) + '1' + right(switch , len(switch) -4) WHERE blah = blah
Hi there. Does anyone know of a why to perform several operations on a table within only 1 ALTER TABLE statement? I haven't found anything to date and don't even know if it's possible.
We would like to use MOM to monitor every problem in SQL Server. I was trying to find all the MOM rules corresponding to severity 17 to severity 25 errors. But there are a lot of the errors don't have the matching MOM rules, I wonder if someone can help me or direct me to the proper place.
For example,
error 8653, severity 17. Warning: The query processor is unable to produce a plan because the table '%.*ls' is marked OFFLINE
I'm trying to have a SProc that will initialize a database for me. This db is in development (I'm primarily writing SSIS packages, atm), and I constantly need to truncate the tables, then re-add a dummy/unknown row (PK/Identity value = 1). Of course, I need triggers not to fire (got that part working), and FK constraints to be bypassed temporarily -- that's the problem.
Here's where I'm at:
---------------------------------------------------------------------------------- CREATE PROCEDURE [dbo].[_InitializeDB] AS
DECLARE tables CURSOR FOR SELECT [name] FROM [sysobjects] WHERE [type]='U' AND [name]<>'sysdiagrams'
OPEN tables FETCH NEXT FROM tables INTO @name WHILE @@FETCH_STATUS=0 BEGIN SET @sql = 'ALTER TABLE ['+ @name + '] NOCHECK CONSTRAINT ALL' EXEC sp_executeSQL @sql SET @sql = 'DISABLE TRIGGER ALL ON [' + @name + ']' EXEC sp_executeSQL @sql SET @sql = 'TRUNCATE TABLE [' + @name + ']' EXEC sp_executesql @sql BEGIN TRY SET @sql = 'INSERT INTO [' + @name + '] (Active) VALUES (0)' EXEC sp_executeSQL @sql END TRY BEGIN CATCH PRINT @sql + ':' PRINT ERROR_MESSAGE() END CATCH SET @sql = 'ENABLE TRIGGER ALL ON [' + @name + ']' EXEC sp_executeSQL @sql SET @sql = 'ALTER TABLE ['+ @name + '] CHECK CONSTRAINT ALL' EXEC sp_executeSQL @sql FETCH NEXT FROM tables INTO @name END
CLOSE tables DEALLOCATE tables ----------------------------------------------------------------------------------
Running this Sproc produces (for the first ref'd table):
Msg 4712, Level 16, State 1, Line 1 Cannot truncate table 'Person' because it is being referenced by a FOREIGN KEY constraint.
I'm trying to optimize a few batch import procedures we use in our processes.
It currently works like this:
1) Cursor loop cycles through all data to be imported from IMPORT table
2) For every record there is an attempted insert to PROD table in a TRY-CATCH check to see whether the record would pass all the primary key and foreign key constrains in PROD table
3) Only those that pass the TRY-CATCH check gets imported into PROD table
4) Every row gets logged into a separate LOG table, either with a comment like "Import OK" or "Error: foreign key violation in field 'my_id'"
The thing is, the procedure runs fine when I'm importing several thousands of records, but when it comes to hundreds of thousands, the speed becomes an issue, as I currently get 20 records per second and slowing...
There is no other code in that procedure, no queries. Just the Cursor cycle and the try-catch check.
I created a report where collected payments are listed by each collector from month to date.Everything Looks ok on the reporting side until a requirement came up where I am Also supposed to list the total number of payments collected by each collector (the count) and also the (total sum). If you look at the attached excel you would see two tables, I am getting the first table, 2nd is the one I want expected). How do I perform sum and count operations?
ALTER PROC USP_PaymentsByCollector ( @CollectorName AS varchar(20) = NULL, @CollectorCode AS varchar(20) = NULL, @LoanID AS varchar (20) = NULL --@StartDate AS DATETIME = NULL, ----@EndDate AS DATETIME = NULL
Currently studying for 70-229.I'm trying to understand how security for users is managed in SQLServer. I've been using SQL Server for a few years now, but withoutinvestigating the bits that "just work".So, here's the scenario. This is more or less how I create all myapplications (which these days are all ASP.NET).I have a database called "TESTDB" (original, huh?)Now, I want to create a method for users to access this database, so Iopen EM, locate and expand the node for the server containing thedatabase, and open the "Security" node. Click on the "Logins" leaf,and in the pane right-click and select "New Login.."On the "General" tab I enter "TESTDBLOGIN" as the Name.Select "SQL Server Authentication", and put in a password.In the drop-down list of databases select "TESTDBLOGIN"Ignoring the "Server Roles" tab, I go to the "Database Access" tab andscroll down until I can see the "TESTDB" database. Check the "Permit"checkbox, and lo!, the "Database Roles for TESTDB" list is populated,with "public" already ticked. I check the "db_owner" box, and press"OK". It asks me to confirm the password, and once that's done there'sa new login called TESTDBLOGIN. What's more in "Users" leaf in the"TESTDB" database node there's a new user called "TESTDBLOGIN".All well and good. I can now create a connection using this login anddo more or less what I want in the TESTDB database.But.If I click the "Users" leaf in the "TESTDB" node, there are two users -"dbo" and "TESTDBLOGIN". If I right-click the "TESTDBLOGIN" user, Ican see that it has Database Role Memberships for the "public" and the"db_owner" roles. But if I examine the permissions on these two roles(by selecting the row, and then pressing the "Permissions" button) Ifind that there are permissions set for EITHER role - all the checkboxes are blank!So, how is it that I can do SELECT, UPDATE, INSERT and DELETEoperations via this login/user?Sorry it all took so long - I just wanted to get it right.ThanksEdward--The reading group's reading group:http://www.bookgroup.org.uk
I get the below error when i try to process my cubes:
The datasource , 'local - SRDB', contains an ImpersonationMode that that is not supported for processing operations.
I have tried all 4 impersonation modes, but the same error comes from all. i created a new connection , and when i click "test" it says test successful, but the new connection throws the same error. anyone have any ideas?
We use Reporting Services in native mode in our environment and have several data-driven subscriptions on reports. Somehow not all of the subscriptions can be executed, this seems totaly random and doesn't always apply to the same report/schedule. In the event viewer, I can see the error message : "Scheduled operations cannot be created. SQL Server Agent is not running" ....
But the Agent is in running state and there are no logs that the Agent has stopped or started again. A few minutes later, everything looks okay again and the rest of the scheduled subscriptions are executed.
This doesn't happen every day but 3 or 4 times a week. The following version of SQL Server is installed :
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4295.0 (X64) Oct 18 2013 19:22:52 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ).
What causes these problems or where to look to determine the cause of this error?
Hi, I'd like to perform a number of different operations on my Common Table expression but I seem to be limited to only one operation. For example I cannot both delete duplicate rows and then perform a select statement. I can only execute one of the statements referencing the common table expression.
What is wrong with my syntax?
;With OrderedTable
AS
(
select Row_number() OVER (partition BY SSNumber order by Department_Id desc ) AS ROWID,* from Employee
)
delete from OrderedTable where RowId != 1
SELECT COUNT(*),SSNumber FROM OrderedTable group by Department_Id order by count(*) desc
I actually am just looking for some supporting documentation on some facets of SQL Server.As far as I have always known, when anyone does a READ from a SQL Server database (SELCT * from <TABLE>), SQL Server does not create a log record...since there's no data or database structure being modified. A colleague is under the impression READ's are logged operations.
MOM connects to 19 instances without issue and fails on one (1). The failing instance resides on a server with two(2) other instances that do not fail. Security is setup the same on windows and sqlserver.
Receiving error in MOM: "Could not connect using SQLDMO to SQL Instance WEB. However, the instance is currently running."
Presents in Event Viewer as: "18452: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
Troubleshooting so far: Created simple SqlDMO VBScript that uses a trusted connection. This works fine from my desktop to all twenty (20) instances yet fails on the one server that MOM is running on.
We have several database linked via merge replications. Due to business requirements, we need to delete 5M rows in one table, we did it on one subscriber. However, the publisher kept uploading the deletion operations from the subscriber and blocked any downloading operation from publisher to subscriber. How can we acceralte the replications now as this has already operated in 2 days, and will continue 1-2 days? Is it possible to set the publisher take the downloading before uploading? How to speed up large amount data deletion operations in replication environment?
SQL 2005 SP2. Upgraded from 2000 and migrated maintenance plan. I am now getting an error when running the maintenance plan for all System databases:
failed with the following error: "Backup and restore operations are not allowed on database tempdb. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Someone else has the same problem and reported it here:
What is the percentage of FREE disk space that is needed for a backup? I have backups that are failing with no disk space errors. But there is enough disk. Does SQL Server need a percentage of free space all the time?
I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations. How can I set auto logged mechanism and how can I access the logs in c#? Thanks
