Sql-server – Create SP for inserting data from a table to a table

insertsql serverstored-procedurestemporary-tables

I have a table that has 1 record where one field may have 2 values separated by commas (in that cases I have to create 2 records with those values separated in 2 row and the other field values are the same). To do these I had to make some selects but how can I do the stored procedure to do that in a easier way??? When there are 2 telephone number separated with ',' with the first SELECT I insert to table3 the row with the phone number before the ',':

SELECT DISTINCT SUBSTRING(Telefono, 1, CHARINDEX(',', Telefono, 0) - 1) AS Telefono
    ,Nome,Indirizzo,CAP,Comune,Prov
INTO table3
FROM #table1
WHERE Telefono LIKE '%,%'

INSERT INTO table3
--the record with the phone number after the [,]
SELECT DISTINCT SUBSTRING(Telefono, CHARINDEX(',', Telefono, 0) + 1, LEN(Telefono)) AS Telefono
    ,Nome,Indirizzo,CAP,Comune,Prov,Fax
FROM #table1
WHERE Telefono LIKE '%,%'

INSERT INTO table3
--insert all the remaining rows
SELECT DISTINCT Telefono
    ,Nome,Indirizzo,CAP,Comune,Prov,fax
FROM #table1
WHERE Telefono NOT LIKE '%,%'

Best Answer

Before you start thinking about a solution, try to evaluate if your requirements make sense.

Is it really the best solution to create duplicate rows if multiple phone numbers exist or would it be better to create a new table PhoneNumbers with a foreign key?

And splitting data can be done more elegantly in the tool that loads the data into the database. If you have to do it in SQL, the solution depends on the database system you use. For instance, here is a solution for SQL Server: https://stackoverflow.com/q/5493510/1595345

Related Solutions

Sql-server – Looking for a Trigger to change inserting strings

if you want to use triggers thats your call, i am just suggesting a different approach

you can use the Default Value or Binding feature of a table

e.g in your table1, you can make the default value of Name column = 'Sample Name' by below script

ALTER TABLE table1 ADD CONSTRAINT
    DF_Table1_Name DEFAULT N'Sample Name' FOR Name
GO

Look at image below Table Default value of Column

This code below adds a trigger to your table that works after insert, it updates the name added and replaces the 'x' with '*'

CREATE TRIGGER ReplacingTrigger 
ON  table1  
AFTER INSERT 
AS  
BEGIN 
    UPDATE table1
    SET table1.name = replace(table1.name,'x','*')
    FROM table1
    INNER JOIN inserted i on i.name = table1.name
END

i suggest if you have an ID in the table to use it in the joins to find the exact record inserted to make it accurate like below

CREATE TRIGGER ReplacingTrigger 
ON  table1  
AFTER INSERT 
AS  
BEGIN 
    UPDATE table1
    SET table1.name = replace(table1.name,'x','*')
    FROM table1
    INNER JOIN inserted i on i.id= table1.id
END

Oracle Trigger – Insert Values Based on Existence with Check Constraint

If you query the table you're inserting into inside a trigger on that same table, you are likely to get "ORA-04091: table name is mutating, trigger/function may not see it." error. Check constraints cannot contain subqueries. Therefore your best option is to encapsulate logic for inserting data into a stored procedure and use that procedure instead of direct inserts.

I can assist you with writing the procedure, however, it seems to me that you have a logical flaw in your data model. Your example data shows that my_id is not a unique key for the table but rather that many rows may share the same value for my_id. So if one row with my_id = 1 has field_2 set to some value and for some other row field_2 is null, what is to be done? Don't you have a primary key in your table?

Anyways, if you want literally what you wrote in your post, you could do with something like

create or replace procedure my_practice_table_insert(p_my_id in number, p_field_2 in varchar2, p_field_3 in varchar2)
is
    l_cnt number;
begin
    if p_field_2 is null then
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is not null;
    else
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is null;
    end if;

    if l_cnt = 0 then
        insert into my_practice_table(my_id, field_2, field_3) values(p_my_id, p_field_2, p_field_3);
    end if;
end;

I didn't actually try out the procedure so it may have slight errors, but that's the general form it could take.

Best Answer

Related Solutions

Sql-server – Looking for a Trigger to change inserting strings

Oracle Trigger – Insert Values Based on Existence with Check Constraint

Related Question