Insert Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-08-14 : 07:53:36

Hi,

I have a table called TblPracExcl which will be updated time to time by an application developed in Vb.Net.

I want to copy over the original record before it is updated from TblPracExcl to TblPracExclHistory..

For example;

TblPracexcl

PracNo PracName PracStatus
1 AB Active
2 BC Old
3 CD Active

Now, a user from the application level updated PracNo 2 & 3 as shown

PracNo PracName PracStatus
1 ABA Active
2 BCB Old

Therefore, I want to insert the original data prior to the change to TblPracExclHistory as;

PracNo PracName PracStatus
1 AB Active
2 BC Old

I am new to table triggers - please can anyone help me with this...

Thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-14 : 08:08:05

If you are on SQL 2005 or later, the simplest way might be to use the OUTPUT clause. It would be something like this:

UPDATE TblPracExcl  SET
	col1 = 23,
	col2 = 'ABCD'
OUTPUT
	DELETED.* INTO TblPracExclHistory
WHERE
	col3 = 'YourWhereCondition';

I am showing only the simplest possible example above. It is a lot more flexible - see details here on the MSDN page: http://msdn.microsoft.com/en-us/library/ms177564.aspx

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-08-14 : 08:14:59

sunitabeck please could you check my thread again - I wasnt clear earlier..

The update has been coded OK on the application and TblPracExcl will be updated accordingly. I only want to have a record of the data before the update to be inserted to TblPracExclHistory..

Am working with SQL Server 2005.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-14 : 09:02:22

quote:
Originally posted by dr223

sunitabeck please could you check my thread again - I wasnt clear earlier..

The update has been coded OK on the application and TblPracExcl will be updated accordingly. I only want to have a record of the data before the update to be inserted to TblPracExclHistory..

Am working with SQL Server 2005.

Thanks

That is what the code I posted would do. If you modify your update statement to add the output clause, it would update the table just as it did before and simultaneously insert the data as it existed before the update into the TblPracExclHistory table. Test it in a dev environment and you will see what I mean.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-08-14 : 09:13:15

where do u put the code? the output clause ? within the application? thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-14 : 09:16:06

quote:
Originally posted by dr223

where do u put the code? the output clause ? within the application? thanks

In your VB.Net code, or in a stored procedure that the VB.Net code calls, there has to be an update statement. Look for that, and add the output clause to that.

If you like, post the entire update statement to the forum.

Also, please be sure to TEST IN A DEV ENVIRONMENT before you do this for real.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-08-14 : 09:27:36

Ok -

This is my update statement;


   query = "UPDATE dbo.TblPracExclude SET prac_name  ='" & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(1).Value & "' where Prac_No=" & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(0).Value & ""
                cmd = New SqlCommand(query, conn)
                cmd.ExecuteNonQuery()

                query = "UPDATE dbo.TblPracExclude SET Prac_status = '" & _
                          Nz(Replace(DgvPracExcl.Rows(e.RowIndex).Cells(2).Value, "'", "''")) & "' where Prac_No=" & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(0).Value & ""
                cmd = New SqlCommand(query, conn)
                cmd.ExecuteNonQuery()


                query = "UPDATE dbo.TblPracExclude SET Datetime ='" & _
                          DateTime.Now & "' where Prac_No=" & _
                          DgvPracExcl.Rows(e.RowIndex).Cells(0).Value & ""
                cmd = New SqlCommand(query, conn)
                cmd.ExecuteNonQuery()

Note: PracNo is the Primary Key and it doesnt need any update. However, will need to insert to the TblPracExclHistory.

Thank you co much

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-14 : 09:56:21

It looks like you have 3 update statements, each updating the same row(s) in the same table. If you print out the query string before each cmd.ExecuteNonQuery, you would see these 3 sql statements:

UPDATE dbo.TblPracExclude SET prac_name = 'somename' WHERE Prac_no = 1234;
UPDATE dbo.TblPracExclude SET Prac_status = 'somestatus' WHERE Prac_no = 1234;
UPDATE dbo.TblPracExclude SET Datetime = '08/14/2012' WHERE Prac_no = 1234;

What I would suggest is to first, change your VB code to combine them into a single statement as follows, followed by a single call to executenonquery:

UPDATE dbo.TblPracExclude SET
	prac_name = 'somename',
	Prac_status = 'somestatus',
	Datetime = '08/14/2012'
WHERE
	Prac_no = 1234

Once you have that working, make the following change to the query string

UPDATE dbo.TblPracExclude SET
	prac_name = 'somename',
	Prac_status = 'somestatus',
	Datetime = '08/14/2012'
[red]OUTPUT DELETED.Prac_no,DELETED.prac_name,DELETED.Prac_status,DELETED.Datetime
INTO TblPracExclHistory (Prac_no,prac_name,Prac_status,Datetime)[red]
WHERE
	Prac_no = 1234

And that should be it. I am making several assumptions here - for example, if the TblPracExclHistory table has other non-nullable columns you would need to supply those as well.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-08-15 : 05:22:55

Hi,

I got the Update into one statement as shown below - which works fine;

 query = "UPDATE dbo.TblPracExclude SET prac_enabled = @prac_enabled, Notes = @Notes, SysDatetime = @SysDateTime WHERE prac_no = @prac_no"
                cmd = New SqlCommand(query, conn)
                cmd.Parameters.AddWithValue("@prac_enabled", DgvPracExcl.Rows(e.RowIndex).Cells(4).Value)
                cmd.Parameters.AddWithValue("@Notes", DgvPracExcl.Rows(e.RowIndex).Cells(5).Value)
                cmd.Parameters.AddWithValue("@SysDateTime", DateTime.Now)
                cmd.Parameters.AddWithValue("@prac_no", DgvPracExcl.Rows(e.RowIndex).Cells(0).Value)
                cmd.ExecuteNonQuery()

How can incorporate the OUTPUT clause please..

Note am working with Vb.Net 2005 and my backend is SQL Server 2005.

Thanks

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-08-15 : 06:01:51

RESOLVED


query = "UPDATE dbo.TblPracExclude SET prac_enabled = @prac_enabled, Notes = @Notes, SysDatetime = @SysDateTime OUTPUT DELETED.Prac_no,DELETED.GoldPracid,DELETED.Prac_name,DELETED.Prac_status,DELETED.Prac_enabled,DELETED.Notes,DELETED.SysDatetime INTO dbo.TblPracExcludeHistory WHERE prac_no = @prac_no"
                cmd = New SqlCommand(query, conn)
                cmd.Parameters.AddWithValue("@prac_enabled", DgvPracExcl.Rows(e.RowIndex).Cells(4).Value)
                cmd.Parameters.AddWithValue("@Notes", DgvPracExcl.Rows(e.RowIndex).Cells(5).Value)
                cmd.Parameters.AddWithValue("@SysDateTime", DateTime.Now)
                cmd.Parameters.AddWithValue("@prac_no", DgvPracExcl.Rows(e.RowIndex).Cells(0).Value)
                cmd.ExecuteNonQuery()

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-15 : 06:55:19

Change your query as shown in red

UPDATE dbo.TblPracExclude SET prac_enabled = @prac_enabled, Notes = @Notes, SysDatetime = @SysDateTime 
OUTPUT DELETED.prac_enabled, DELETED.Notes, DELETED.SysDatetime,DELETED.prac_no 
INTO TblPracExclHistory (prac_enabled,Notes,SysDatetime,prac_no) 
WHERE prac_no = @prac_no

I am assuming
a) that the column names in the TblPracExclHistory are prac_enabled,Notes,SysDatetime,prac_no and
b) that there are no other non-nullable columns in TblPracExclHistory

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources