Can someone help me parse this oracle insert statement into SQL Server please?
insert into notify_template (subject, body, priority)
values
(
'
Budgeted time exceeded for task [18040]
',
'
Actual Time exceeds Budgeted time for the following task'
|| chr(10) || chr(10) ||
' Task Name : [18040]
Root Program Name : [18049]
Actual Days : [2000000100]
Budgeted Days : [2000000101]
Estimated Time : [2000000102]'
|| chr(10) || chr(10) ||
' The following resource(s) in the above task have exceeded the budgeted time:'
|| chr(10) || chr(10) ||
' [2000000784]'
|| chr(10) || chr(10) ||
' Click here : [12865]
',
2)Got it...
+ CAST(CHAR(10) AS VARCHAR) + CAST(CHAR(10) AS VARCHAR) +
for || chr(10) || chr(10) ||
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Monday, March 19, 2012
Saturday, February 25, 2012
Checking User & Expire of Update Possibility (by Trigger), How to?
Hi,
I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:
RateID, Service, Rate, DateTime, User
Want I want is a code (trigger) in the database can do the following:
If user perform an Update request the code will check:
- if this recored inserted by the same user update command will be execute.
- if this recored inserted by other user: update command will not execute and return message.
- if more than 5 minutes passed the update command will not be execute and return message.
Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.
Change the update statement from something like:
update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
to something like
|||update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())
Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?
and what if I want to put this code in Trigger?
thnx again,,,
|||Any help?|||Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.
The code idea below relies upon [User] being captured with the system_user system function (domain/username).
Code Snippet
CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN
DECLARE @.User varchar(50)
IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END
GO
Hi,
I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:
CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN
IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)
END
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
Checking User & Expire of Update Possibility (by Trigger), How to?
Hi,
I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:
RateID, Service, Rate, DateTime, User
Want I want is a code (trigger) in the database can do the following:
If user perform an Update request the code will check:
- if this recored inserted by the same user update command will be execute.
- if this recored inserted by other user: update command will not execute and return message.
- if more than 5 minutes passed the update command will not be execute and return message.
Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.
Change the update statement from something like:
update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
to something like
|||update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())
Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?
and what if I want to put this code in Trigger?
thnx again,,,
|||Any help?|||Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.
The code idea below relies upon [User] being captured with the system_user system function (domain/username).
Code Snippet
CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN
DECLARE @.User varchar(50)
IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END
GO
Hi,
I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:
CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN
IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)
END
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
Friday, February 24, 2012
Checking to see if a record exists and if so update else insert
I've decided to post this as a sticky given the frequency this question is asked.
|||I use OLE DB Command update the records. Thanks!|||
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Thanks Jamie!
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
PhilThis is exactly what I was looking for.
I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.
However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.
Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.
Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.
I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||
This is very helpful. Thanks!
I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?
Thanks!
|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
|||
Sudhir Kesharwani wrote:
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||
Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".
I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split
I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.
So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).
What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.
When the data was updated (second round), SQL was always faster.
Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.
Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?
Leonce
|||Leonce,
First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?
Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.
SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)
Phil
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
I use OLEDB Destination, I'll try with SQL Server Destination.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
Leonce
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.
|||Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.
Checking to see if a record exists and if so update else insert
I've decided to post this as a sticky given the frequency this question is asked.
|||I use OLE DB Command update the records. Thanks!|||
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Thanks Jamie!
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
PhilThis is exactly what I was looking for.
I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.
However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.
Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.
Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.
I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||
This is very helpful. Thanks!
I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?
Thanks!
|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
|||
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||
Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".
I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split
I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.
So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).
What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.
When the data was updated (second round), SQL was always faster.
Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.
Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?
Leonce
|||Leonce,
First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?
Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.
SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)
Phil
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
I use OLEDB Destination, I'll try with SQL Server Destination.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
Leonce
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.
|||Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.
Checking to see if a record exists and if so update else insert
I've decided to post this as a sticky given the frequency this question is asked.
|||I use OLE DB Command update the records. Thanks!|||
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Thanks Jamie!
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
PhilThis is exactly what I was looking for.
I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.
However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.
Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.
Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.
I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||
This is very helpful. Thanks!
I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?
Thanks!
|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
|||
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||
Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".
I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split
I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.
So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).
What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.
When the data was updated (second round), SQL was always faster.
Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.
Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?
Leonce
|||Leonce,
First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?
Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.
SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)
Phil
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
I use OLEDB Destination, I'll try with SQL Server Destination.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
Leonce
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.
|||Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.
Checking to see if a record exists and if so update else insert
I've decided to post this as a sticky given the frequency this question is asked.
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Thanks Jamie!
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
PhilThis is exactly what I was looking for.
I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.
However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.
Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.
Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.
I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.|||
This is very helpful. Thanks!
I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?
Thanks!
|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
|||
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||
Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".
I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split
I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.
So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).
What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.
When the data was updated (second round), SQL was always faster.
Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.
Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?
Leonce
|||Leonce,
First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?
Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.
SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)
Phil
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
I use OLEDB Destination, I'll try with SQL Server Destination.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
Leonce
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.
|||Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.
Checking to see if a record exists and if so update else insert
I've decided to post this as a sticky given the frequency this question is asked.
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Thanks Jamie!
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
PhilThis is exactly what I was looking for.
I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.
However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.
Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.
Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.
I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.|||
This is very helpful. Thanks!
I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?
Thanks!
|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
|||
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||
Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".
I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split
I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.
So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).
What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.
When the data was updated (second round), SQL was always faster.
Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.
Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?
Leonce
|||Leonce,
First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?
Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.
SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)
Phil
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
I use OLEDB Destination, I'll try with SQL Server Destination.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
Leonce
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.
|||Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.
Checking to see if a record exists and if so update else insert
I've decided to post this as a sticky given the frequency this question is asked.
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Thanks Jamie!
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
PhilThis is exactly what I was looking for.
I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.
However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.
Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.
Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.
I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.|||
This is very helpful. Thanks!
I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?
Thanks!
|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
|||
Sudhir Kesharwani wrote:
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||
Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".
I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split
I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.
So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).
What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.
When the data was updated (second round), SQL was always faster.
Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.
Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?
Leonce
|||Leonce,
First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?
Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.
SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)
Phil
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
I use OLEDB Destination, I'll try with SQL Server Destination.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
Leonce
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.
|||Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.
Checking to see if a record exists and if so update else insert
I've decided to post this as a sticky given the frequency this question is asked.
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Thanks Jamie!
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
PhilThis is exactly what I was looking for.
I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.
However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.
Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.
Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.
I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.|||
This is very helpful. Thanks!
I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?
Thanks!
|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
|||
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||
Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".
I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split
I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.
So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).
What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.
When the data was updated (second round), SQL was always faster.
Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.
Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?
Leonce
|||Leonce,
First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?
Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.
SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)
Phil
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
I use OLEDB Destination, I'll try with SQL Server Destination.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
Leonce
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.
|||Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.
Checking to see if a record exists and if so update else insert
I've decided to post this as a sticky given the frequency this question is asked.
|||I use OLE DB Command update the records. Thanks!|||
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Thanks Jamie!
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
PhilThis is exactly what I was looking for.
I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.
However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.
Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.
Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.
I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||
This is very helpful. Thanks!
I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?
Thanks!
|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
|||
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||
Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".
I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split
I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.
So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).
What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.
When the data was updated (second round), SQL was always faster.
Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.
Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?
Leonce
|||Leonce,
First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?
Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.
SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)
Phil
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
I use OLEDB Destination, I'll try with SQL Server Destination.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
Leonce
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.
|||Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.
Checking to see if a record exists and if so update else insert
I've decided to post this as a sticky given the frequency this question is asked.
|||I use OLE DB Command update the records. Thanks!|||
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Thanks Jamie!
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
PhilThis is exactly what I was looking for.
I appreciate that.
Fahad|||It's an awesome article - and I used this method in my 1st SSIS package.
However, if number of records is high (over 1000) the OLE Update Command performs slowly. So in that case you might better off loading source data to staging table and creating Stored Procedure which Updates then Inserts (and Deletes) data in the destination table.
Using Lookup transform in the way described in article is quite efficient (compared to other methods like merge join) - and can be useful in a variety of situations|||TheViewMaster,
Yes, very valid points. Each developer will have to decide based on the pros and cons of each method.
Personally, I wouldn't go to the extreme that you did, but would rather dump just the updates to a staging table and then outside of the data flow use an Execute SQL task to perform the batch update. I don't want to spend the cost to insert the data twice (once into staging table, and then again into the destination table using your idea).|||Can somebody point me to a simple example of this.
I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert.
|||
This is very helpful. Thanks!
I am completely new to the SQL Server Integration Services. I went through your link and understand how you filter out diffrent records. Could you pointting out how you actually update you existing records ?
Thanks!
|||I have cloned the example above and it does not seem to be picking up the new or changed records. Is there any way to troubleshoot this.|||I use OLE DB Command update the records. Thanks!|||
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
|||
Hi phill,
Great article, I loved it.
This is what i had been looking for since long time.
One doubt is there in my mind, my current assignment i have to transform about 40K rows from a database.
Will this method work for me or there is any other better way.. ?
regards
Sudhir Kesharwani
You need to likely send your changed records to a temporary table and then perform a batch update using an Execute SQL Task on the control flow. Using the OLE DB Command transformation isn't very efficient for high numbers of rows.|||
Phil,
As we discussed about updating/inserting techniques a while ago(see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211321&SiteID=1) you told me SSIS can perform this operation much faster than using plain SQL... I put the emphasize on the "much faster".
I have created a performance test package comparing the 2 methods:
1) stored proc with UPDATE and INSERT statements
2) Data flow using look up/conditional split
I have a data flow task to create a data test set with a variable number of rows (I used Jamie's script component source example) with IDs (used as PK) always ranging from 1 to nbrRows. Each row is constituted of 5 int, 4 string (10, 20, 40 and 80 characters) and 1 datetime with values chosen randomly.
So what I do is calling the package with nbrRows=1, 10, 100... The first time 1 new row is added. The second time, id=1 already exists so 1 row is updated, the other 9 are added. And so on... I then perform a second round of tests with the same numbers, the only difference being that the destination table is now full (therefore performing only updates).
What I've found out so far is that SSIS is faster only when inserting a certain number of rows (approx . 1000 to 100000). SSIS performed on avg. 30% better with 50000 rows while at 1 million rows, SQL performed better by 5%.
When the data was updated (second round), SQL was always faster.
Of course, these figures are based on my tests which may not reflect actual usage. I run SQL/SSIS in parallel, the tests are done in batch, .... I thought about using DBCC DROPCLEANBUFFER but it doesn't seem to change much.
Maybe my way of comparing is totally flawed... Have you ever tried comparing the two methods in terms of performance? How did you conclude the SSIS way is much faster?
Leonce
|||Leonce,
First, when using fast load option on the destination, there are two parameters which need to be adjusted based on the rows you are working with. Max Insert Commit Size and Rows Per Batch. What were the values for these set at?
Also note, that when possible, using a SQL Server Destination over the OLE DB Destination would be preferred.
SSIS should be no different than using BULK INSERT (it seems to be just a glorified wrapper to that). Performing updates via an OLE DB Command will always be slower than a batch update using SQL. (Only because the OLE DB Command is executed for every row in the data flow)
Phil
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
I use OLEDB Destination, I'll try with SQL Server Destination.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
Leonce
|||I left the default values for Commit Size and Rows Per Batch ('' and 0). You suggest I should adjust these values depending on the number of rows transferred? What would typical values be for a table with 100000 rows? Does it dependent on the row size?
MICS should be set to whatever you wish the batches to be committed. So if you want to commit in 10,000 row batches, set MICS to 10,000. Rows Per Batch should be set to 100,000 in this case.
I forgot to mention I use your method for updates (dump into table then update). Do you think SSIS can be faster than plain SQL for updates? I would tend to think it's impossible.
True. Using an Execute SQL task will likely have a slight slowness to it compared to straight SQL, but in the end they do the same thing, so they should be REAL close.
|||Let us say that a package uses merge join option to insert new rows and it uses OLE DB Destination with fast load, table lock, and batch size (1000) options. The table lock option on the destination forces us to put a NOLOCK hint in the OLE DB Source query (on the destination table). Otherwise, they run into locking contentions (one is requesting exclusive table lock when the other is requesting shared table lock). This works most of the times but occationally I see that SQL Server does not honour the hint (one scenario: when the target table does not have a clustered index on the join key column) and runs into locking contentions. Have you seen this? Do you rely on NOLOCK hint too? Thanks for your help in advance.
checking success of a request in a stored procedure
Hi,
Say I have a stored procedure which does two INSERT operation.
How can I check if the first INSERT succeeded in order to know if I
should carry on and do the second one ?
Say I have a stored procedure which does two INSERT operation.
How can I check if the first INSERT succeeded in order to know if I
should carry on and do the second one ?
RegardsYou can check @.@.ERROR to see if an error was raised, or @.@.ROWCOUNT to
see how many rows were inserted - in some cases you might consider it
an error if no rows were inserted.
See here for much more information:
http://www.sommarskog.se/error-handling-II.html
Simon
Sunday, February 19, 2012
Checking if String is NULL or EMPTY in SQL
I need to check in my Stored procedure if the information passed is null or empty so I can decided to insert the new value or keep the old. How do I accomplish this please in T-SQL. Thanks in advance.
IFISNULL(@.param)OR @.param =''THEN doSomething...
(Note the two apostrophes ' and ', not a quote mark!)|||
Books online (help files that comes with SQL) is an amzing little app. Below is an example and the Syntax.
USE pubsGOSELECTAVG(ISNULL(price, $10.00))FROM titlesGOISNULL ( check_expression , replacement_value )|||
Create Procedure mySpNameHere
@.InputValueHere VARCHAR(50) = NULL
AS
IF @.InputValue IS NULL OR @.InputValue = ''
/*Keep the old value*/
ELSE
/*Run the update statement here with your new value*/
|||
cheetahtech:
ISNULL ( check_expression , replacement_value )
Sorry. I got a blackout. Of course, ISNULL syntax is to be used in a query where you want to specify an alternative value, if the expression is NULL.
The correct way to check for NULL in a condition is IF @.Param IS NULL as rich freeman points out.
Thursday, February 16, 2012
Checking existence with insert... select OPENXML
Hi,
If I am inserting data into a table and I do not wish to insert if the row is already there I can write:
insert into t1 (c1,c2,c3)
select c1,c2,c3 from t2
where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and c3=t2.c3)
If my source query for the insert uses OPENXML is there any syntactical form that I can use to test for existence of the row in the destination table as I did above. I can always insert into a temp table of course and then use that as the source of the da
ta for the insert and use the first syntax form. But I was just curious whether this can be easily achieved without the use of a temp table.
Cheers
Ken
Yes, it is possible. You just need to add an alias to the OPENXML derived
table. For example:
INSERT INTO YourTable(CustomerID, ContactName)
SELECT CustomerID, ContactName
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20)) AS XM
WHERE NOT EXISTS
(
SELECT 1
FROM YourTable AS x
WHERE x.CustomerID = XM.CustomerID
)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ken Eng" <KenEng@.discussions.microsoft.com> wrote in message
news:C2A7166E-7506-4B51-986A-7013210B63CA@.microsoft.com...
Hi,
If I am inserting data into a table and I do not wish to insert if the row
is already there I can write:
insert into t1 (c1,c2,c3)
select c1,c2,c3 from t2
where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and
c3=t2.c3)
If my source query for the insert uses OPENXML is there any syntactical form
that I can use to test for existence of the row in the destination table as
I did above. I can always insert into a temp table of course and then use
that as the source of the data for the insert and use the first syntax form.
But I was just curious whether this can be easily achieved without the use
of a temp table.
Cheers
Ken
|||Many thanks
Ken
"Narayana Vyas Kondreddi" wrote:
> Yes, it is possible. You just need to add an alias to the OPENXML derived
> table. For example:
> INSERT INTO YourTable(CustomerID, ContactName)
> SELECT CustomerID, ContactName
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20)) AS XM
> WHERE NOT EXISTS
> (
> SELECT 1
> FROM YourTable AS x
> WHERE x.CustomerID = XM.CustomerID
> )
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ken Eng" <KenEng@.discussions.microsoft.com> wrote in message
> news:C2A7166E-7506-4B51-986A-7013210B63CA@.microsoft.com...
> Hi,
> If I am inserting data into a table and I do not wish to insert if the row
> is already there I can write:
>
> insert into t1 (c1,c2,c3)
> select c1,c2,c3 from t2
> where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and
> c3=t2.c3)
> If my source query for the insert uses OPENXML is there any syntactical form
> that I can use to test for existence of the row in the destination table as
> I did above. I can always insert into a temp table of course and then use
> that as the source of the data for the insert and use the first syntax form.
> But I was just curious whether this can be easily achieved without the use
> of a temp table.
> Cheers
> Ken
>
>
If I am inserting data into a table and I do not wish to insert if the row is already there I can write:
insert into t1 (c1,c2,c3)
select c1,c2,c3 from t2
where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and c3=t2.c3)
If my source query for the insert uses OPENXML is there any syntactical form that I can use to test for existence of the row in the destination table as I did above. I can always insert into a temp table of course and then use that as the source of the da
ta for the insert and use the first syntax form. But I was just curious whether this can be easily achieved without the use of a temp table.
Cheers
Ken
Yes, it is possible. You just need to add an alias to the OPENXML derived
table. For example:
INSERT INTO YourTable(CustomerID, ContactName)
SELECT CustomerID, ContactName
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20)) AS XM
WHERE NOT EXISTS
(
SELECT 1
FROM YourTable AS x
WHERE x.CustomerID = XM.CustomerID
)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ken Eng" <KenEng@.discussions.microsoft.com> wrote in message
news:C2A7166E-7506-4B51-986A-7013210B63CA@.microsoft.com...
Hi,
If I am inserting data into a table and I do not wish to insert if the row
is already there I can write:
insert into t1 (c1,c2,c3)
select c1,c2,c3 from t2
where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and
c3=t2.c3)
If my source query for the insert uses OPENXML is there any syntactical form
that I can use to test for existence of the row in the destination table as
I did above. I can always insert into a temp table of course and then use
that as the source of the data for the insert and use the first syntax form.
But I was just curious whether this can be easily achieved without the use
of a temp table.
Cheers
Ken
|||Many thanks
Ken
"Narayana Vyas Kondreddi" wrote:
> Yes, it is possible. You just need to add an alias to the OPENXML derived
> table. For example:
> INSERT INTO YourTable(CustomerID, ContactName)
> SELECT CustomerID, ContactName
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20)) AS XM
> WHERE NOT EXISTS
> (
> SELECT 1
> FROM YourTable AS x
> WHERE x.CustomerID = XM.CustomerID
> )
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ken Eng" <KenEng@.discussions.microsoft.com> wrote in message
> news:C2A7166E-7506-4B51-986A-7013210B63CA@.microsoft.com...
> Hi,
> If I am inserting data into a table and I do not wish to insert if the row
> is already there I can write:
>
> insert into t1 (c1,c2,c3)
> select c1,c2,c3 from t2
> where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and
> c3=t2.c3)
> If my source query for the insert uses OPENXML is there any syntactical form
> that I can use to test for existence of the row in the destination table as
> I did above. I can always insert into a temp table of course and then use
> that as the source of the data for the insert and use the first syntax form.
> But I was just curious whether this can be easily achieved without the use
> of a temp table.
> Cheers
> Ken
>
>
Sunday, February 12, 2012
Check who have INSERT, UPDATE and DELETE privileges?
Hi,
Permissions in properties of each table show who can SELECT, INSERT etc.
Instead of going through each table manual in a db in EM, given a specific
database, is there any way to find out such privileges information in some
kind of summary way as shown below?
table1 insert delete update
user1 user1 user5
user2 user3
table2 user1 user3
etc.
Can the above information be pulled out of some system database? Could it
be done with just queries or I have to code.
Thanks in advance for any ideas or pointers.
Bing
sp_helprotect via Query Analyzer should show you permissions you want
ie
use <your db>
go
sp_helprotect
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing
|||Hi,
All the previlages will be stored in SYSPROTECTS system table of each
database.
As well as have a look into the system stored proc sp_helprotect in books
online. This almost serve your purpose.
Thanks
Hari
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
> specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing
Permissions in properties of each table show who can SELECT, INSERT etc.
Instead of going through each table manual in a db in EM, given a specific
database, is there any way to find out such privileges information in some
kind of summary way as shown below?
table1 insert delete update
user1 user1 user5
user2 user3
table2 user1 user3
etc.
Can the above information be pulled out of some system database? Could it
be done with just queries or I have to code.
Thanks in advance for any ideas or pointers.
Bing
sp_helprotect via Query Analyzer should show you permissions you want
ie
use <your db>
go
sp_helprotect
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing
|||Hi,
All the previlages will be stored in SYSPROTECTS system table of each
database.
As well as have a look into the system stored proc sp_helprotect in books
online. This almost serve your purpose.
Thanks
Hari
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
> specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing
Check who have INSERT, UPDATE and DELETE privileges?
Hi,
Permissions in properties of each table show who can SELECT, INSERT etc.
Instead of going through each table manual in a db in EM, given a specific
database, is there any way to find out such privileges information in some
kind of summary way as shown below?
table1 insert delete update
user1 user1 user5
user2 user3
table2 user1 user3
etc.
Can the above information be pulled out of some system database? Could it
be done with just queries or I have to code.
Thanks in advance for any ideas or pointers.
Bingsp_helprotect via Query Analyzer should show you permissions you want
ie
use <your db>
go
sp_helprotect
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing|||Hi,
All the previlages will be stored in SYSPROTECTS system table of each
database.
As well as have a look into the system stored proc sp_helprotect in books
online. This almost serve your purpose.
Thanks
Hari
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
> specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing
Permissions in properties of each table show who can SELECT, INSERT etc.
Instead of going through each table manual in a db in EM, given a specific
database, is there any way to find out such privileges information in some
kind of summary way as shown below?
table1 insert delete update
user1 user1 user5
user2 user3
table2 user1 user3
etc.
Can the above information be pulled out of some system database? Could it
be done with just queries or I have to code.
Thanks in advance for any ideas or pointers.
Bingsp_helprotect via Query Analyzer should show you permissions you want
ie
use <your db>
go
sp_helprotect
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing|||Hi,
All the previlages will be stored in SYSPROTECTS system table of each
database.
As well as have a look into the system stored proc sp_helprotect in books
online. This almost serve your purpose.
Thanks
Hari
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
> specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing
Check who have INSERT, UPDATE and DELETE privileges?
Hi,
Permissions in properties of each table show who can SELECT, INSERT etc.
Instead of going through each table manual in a db in EM, given a specific
database, is there any way to find out such privileges information in some
kind of summary way as shown below?
table1 insert delete update
user1 user1 user5
user2 user3
table2 user1 user3
etc.
Can the above information be pulled out of some system database? Could it
be done with just queries or I have to code.
Thanks in advance for any ideas or pointers.
Bingsp_helprotect via Query Analyzer should show you permissions you want
ie
use <your db>
go
sp_helprotect
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing|||Hi,
All the previlages will be stored in SYSPROTECTS system table of each
database.
As well as have a look into the system stored proc sp_helprotect in books
online. This almost serve your purpose.
Thanks
Hari
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
> specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing
Permissions in properties of each table show who can SELECT, INSERT etc.
Instead of going through each table manual in a db in EM, given a specific
database, is there any way to find out such privileges information in some
kind of summary way as shown below?
table1 insert delete update
user1 user1 user5
user2 user3
table2 user1 user3
etc.
Can the above information be pulled out of some system database? Could it
be done with just queries or I have to code.
Thanks in advance for any ideas or pointers.
Bingsp_helprotect via Query Analyzer should show you permissions you want
ie
use <your db>
go
sp_helprotect
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing|||Hi,
All the previlages will be stored in SYSPROTECTS system table of each
database.
As well as have a look into the system stored proc sp_helprotect in books
online. This almost serve your purpose.
Thanks
Hari
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:FB2E8CC4-BD99-420F-B37F-A8EA12A8097C@.microsoft.com...
> Hi,
> Permissions in properties of each table show who can SELECT, INSERT etc.
> Instead of going through each table manual in a db in EM, given a
> specific
> database, is there any way to find out such privileges information in some
> kind of summary way as shown below?
> table1 insert delete update
> user1 user1 user5
> user2 user3
> table2 user1 user3
> etc.
> Can the above information be pulled out of some system database? Could it
> be done with just queries or I have to code.
> Thanks in advance for any ideas or pointers.
> Bing
Check to see if a row exists in another table before insert
I have a stored procedure that selects invoices based on the date range
delete from BillingCurrent
insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),
GETDATE(), 112))
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent
delete from Billing30
insert into Billing30(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing3
Now, I need to check to see if the row exists in Billing30, if it exists in Billing 30 then I don't want it to insert into BillingCurrent.
You can use something along the lines of the code listed below.
Chris
IF EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)
BEGIN
--Insert the SQL that you want to execute if a Billing30 record is found
END
ELSE
BEGIN
--Insert the SQL that you want to execute if a Billing30 record is not found
END
|||Ok I understand that and it's very helpful but,
for
IF EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)
BEGIN
--Insert the SQL that you want to execute if a Billing30 record is found
I want it to do Nothing if the row exists but I don't want it to exit because I need to do this for 5 tables
END
|||There's nothing to stop you using multiple IF statements or even nesting them if you desire, see below - note that I've reversed the logic.
Chris
IF NOT EXISTS (SELECT 1 FROM Billing30 WHERE <insert criteria>)
BEGIN
--Insert the SQL that you want to execute if a Billing30 record is not found
END
IF NOT EXISTS (SELECT 1 FROM Table2 WHERE <insert criteria>)
BEGIN
--Insert the SQL that you want to execute if a Table2 record is not found
END
IF NOT EXISTS (SELECT 1 FROM Table3 WHERE <insert criteria>)
etc....
|||ok so I can nest all of the if not exists and then after those just
if exists
end
to make it do nothing if the row already exists?
|||In my previous example none of the code within the BEGIN END blocks will execute if at least one row meeting the relevant criteria exists in each of the tables that you are checking. There's no need to add any additional code to make SQL Server do nothing - if a condition fails then the code within the associated BEGIN END block will not be executed, it's as simple as that. If all of the conditions fail then the batch will complete without executing any of the code within any of the BEGIN END blocks.
It isn't clear from the description of your scenario whether you will need to use nested or multiple IF statements so I can't help any further in that respect without more info.
Chris
|||
Ok, Heres my SP
This prints out (because I use a relation from the billing tables to the InvoiceDetails Table) A Billing statement for each customer, problem is: if a customer has an invoice this month and last month then if prints out two invoices. I'm trying to get it to check each table first billing120 then billing90 then billing60..... So the customer row only gets inserted once.
delete from Billing120
insert into Billing120(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112)Between CONVERT(varchar(15),dateadd (d,-150,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -215, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing120
delete from Billing90
IF NOT EXISTS (SELECT CustomerID FROM Billing120)--WHERE <insert criteria>)
BEGIN
insert into Billing90(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-120,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -90, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing90
END
delete from Billing60
IF NOT EXISTS (SELECT CustomerID FROM Billing90)
BEGIN
insert into Billing60(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-90,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -60, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing60
End
delete from Billing30
IF NOT EXISTS (SELECT CustomerID FROM Billing90)
BEGIN
insert into Billing30(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing30
END
delete from BillingCurrent
IF NOT EXISTS (SELECT CustomerID FROM Billing90)
BEGIN
insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),
GETDATE(), 112))
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent
END
RETURN
|||Maybe you should try a different approach then, see below. This approach allows you to analyze the contents of the tables before performing any INSERTs etc...
Chris
DECLARE @.Billing120Exists BIT
DECLARE @.Billing90Exists BIT
DECLARE @.Billing60Exists BIT
DECLARE @.Billing30Exists BIT
SET @.Billing120Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing120) THEN 1 ELSE 0 END
SET @.Billing90Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing90) THEN 1 ELSE 0 END
SET @.Billing60Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing60) THEN 1 ELSE 0 END
SET @.Billing30Exists = CASE WHEN EXISTS (SELECT CustomerID FROM Billing30) THEN 1 ELSE 0 END
--Insert logic here that examines the values of the @.BillingExists variables and performs the appropriate actions.
--If you want you can declare additional variables to indicate whether or not rows have subsequently been inserted into one of the tables.
|||Actually I ended up doing it this way,
delete from Billing120
insert into Billing120(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip
FROM Invoices INNER JOIN
Customers ON Invoices.CustomerID = Customers.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112)Between CONVERT(varchar(15),dateadd (d,-150,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -215, GETDATE()), 112)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing120
delete from Billing90
BEGIN
insert into Billing90(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip
FROM Invoices INNER JOIN
Customers A ON Invoices.CustomerID = A.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-120,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -90, GETDATE()), 112)
And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing90
END
delete from Billing60
BEGIN
insert into Billing60(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip
FROM Invoices INNER JOIN
Customers A ON Invoices.CustomerID = A.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-90,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -60, GETDATE()), 112)
And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)
And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing60
End
delete from Billing30
BEGIN
insert into Billing30(CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip
FROM Invoices INNER JOIN
Customers A ON Invoices.CustomerID = A.CustomerID
WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-60,GETDATE()), 112)and CONVERT(varchar(15),dateadd (d, -30, GETDATE()), 112)
And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)
And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)
And not exists (select 1 from billing60 D where D.CustomerID = A.CustomerId)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billing30
END
delete from BillingCurrent
BEGIN
insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)
SELECT Distinct A.CustomerID, A.Name,A.Address, A.City, A.State, A.Zip
FROM Invoices INNER JOIN
Customers A ON Invoices.CustomerID = A.CustomerID
WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),
GETDATE(), 112))
And not exists (select 1 from billing120 B where B.CustomerID = A.CustomerId)
And not exists (select 1 from billing90 c where c.CustomerID = A.CustomerId)
And not exists (select 1 from billing60 D where D.CustomerID = A.CustomerId)
And not exists (select 1 from billing30 e where E.CustomerID = A.CustomerId)
select CustomerID,[Name],invoicetotal,invoiceID,[date] from billingCurrent
END
RETURN
Thanks for the Help!
Subscribe to:
Comments (Atom)