CODEWITHSUNDEEP
sql-server-2005row
David
David

Reputation: 51

Adding Row in existing table (SQL Server 2005)

I want to add another row in my existing table and I'm a bit hesitant if I'm doing the right thing because it might skew the database. I have my script below and would like to hear your thoughts about it.

I want to add another row for 'Jane' in the table, which will be 'SKATING" in the ACT column.

Table: [Emp_table].[ACT].[LIST_EMP]

enter image description here

My script is:

INSERT INTO [Emp_table].[ACT].[LIST_EMP]
([ENTITY],[TYPE],[EMP_COD],[DATE],[LINE_NO],[ACT],[NAME])
VALUES
('REG','EMP','45233','2016-06-20 00:00:00:00','2','SKATING','JANE')

Will this do the trick?

Upvotes: 1

Views: 469

Answers (2)

Fandango68
Fandango68

Reputation: 4898

Inserting records into a database has always been the most common reason why I've lost a lot of my hairs on my head!

SQL is great when it comes to SELECT or even UPDATEs but when it comes to INSERTs it's like someone from another planet came into the SQL standards commitee and managed to get their way of doing it implemented into the final SQL standard!

If your table does not have an automatic primary key that automatically gets generated on every insert, then you have to code it yourself to manage avoiding duplicates.

Start by writing a normal SELECT to see if the record(s) you're going to add don't already exist. But as Robert implied, your table may not have a primary key because it looks like a LOG table to me. So insert away! If it does require to have a unique record everytime, then I strongly suggest you create a primary key for the table, either an auto generated one or a combination of your existing columns.

Assuming the first five combined columns make a unique key, this select will determine if your data you're inserting does not already exist...

SELECT COUNT(*) AS FoundRec FROM [Emp_table].[ACT].[LIST_EMP]
WHERE [ENTITY] = wsEntity AND [TYPE] = wsType AND [EMP_COD] = wsEmpCod AND [DATE] = wsDate AND [LINE_NO] = wsLineno

The wsXXX declarations, you will have to replace them with direct values or have them DECLAREd earlier in your script.

If you ran this alone and recieved a value of 1 or more, then the data exists already in your table, at least those 5 first columns. A true duplicate test will require you to test EVERY column in your table, but it should give you an idea.

In the INSERT, to do it all as one statement, you can do this ...

INSERT INTO [Emp_table].[ACT].[LIST_EMP]
([ENTITY],[TYPE],[EMP_COD],[DATE],[LINE_NO],[ACT],[NAME])
VALUES
('REG','EMP','45233','2016-06-20 00:00:00:00','2','SKATING','JANE')
WHERE (SELECT COUNT(*) AS FoundRec FROM [Emp_table].[ACT].[LIST_EMP]
       WHERE [ENTITY] = wsEntity AND [TYPE] = wsType AND 
             [EMP_COD] = wsEmpCod AND [DATE] = wsDate AND 
             [LINE_NO] = wsLineno) = 0

Just replace the wsXXX variables with the values you want to insert.

I hope that made sense.

Upvotes: 0

Robert Columbia
Robert Columbia

Reputation: 6418

Your statement looks ok. If the database has a problem with it (for example, due to a foreign key constraint violation), it will reject the statement.

If any of the fields in your table are numeric (and not varchar or char), just remove the quotes around the corresponding field. For example, if emp_cod and line_no are int, insert the following values instead:

('REG','EMP',45233,'2016-06-20 00:00:00:00',2,'SKATING','JANE')

Upvotes: 1

Related Questions