Adding Row in existing table (SQL Server 2005)

Question

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?

Robert Columbia · Accepted Answer · 2016-08-12 13:30:20Z

1

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')

answered Aug 12, 2016 at 13:30

Robert Columbia

6,45115 gold badges34 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

S3S Over a year ago

@David the only think I'll add to Robert's statement, which I'm up voting as correct, is if there are more records than the 5 you have listed and you want to ensure you aren't inserting a duplicate row you need to check if it exists already.

Fandango68 · Accepted Answer · 2016-08-15 01:59:23Z

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.

Collectives™ on Stack Overflow

Adding Row in existing table (SQL Server 2005)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related