conditionally inserting a bunch of rows into a tab | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

conditionally inserting a bunch of rows into a tab

have a bunch of rows (the result of a query) which i want to insert into a table called TableA.. i want to loop through all of them and only insert those not found in TableB..
I think i have the inserting part but my problem is the looping bit..
thanks..i dont want to do this with a cursor..
————————————– if not exists (select uniquekey from TableA whereuniquekey = @keyfrom_my_view)
BEGIN
INSERT INTO TableA select * from my_view where keyfrom_my_view = @keyfrom_my_view
END ————————– The "not exists" part is important to avoid inserting duplicates and saves me having to do error handling..
thanks again.
you can do this without a loop insert into tableA
(field1, field2, field3)
select
field1, field2, field3
from
tableB b
left join tableA a ON (a.field1 = b.field1 and a.field2 = b.field2)
where
a.field1 IS NULL
i have several rows in each record to insert..it couldget very messy..
any suggestions for an INSERT INTO SELECT * FROM solution?
thx
Can you be clearer? I dont understand
the structure of both tables are identical..so how can i cut out writing all the field names? i have about 20 fields in each table..
thx
you cant. using an insert..select you must quote each field name.
but you could select name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ‘MyTable’ and then cut and paste and reformat
thx,
in your answer, you put "a.field1 IS NULL".
i dont have any null field..
can you pls explain this as i see this a lot in left outer join examples..
Do u have any primary key on your table TABLEA & TABLEB ?
With the Help of That PK ( Primary Key ) , u can identify the rows which has not been inserted from TABLEB to TABLEA
Insert Into TABLEA
SELECT B.*
FROM
TABLEB B Left Join TABLEA A on
B.PKID=A.PKID Or U can Use ‘IN’ Clause Also Insert Into TABLEA
SELECT * FROM TABLEB Where
PKID NOT IN ( SELECT PKID FROM TABLEA) PKID Refers 2 ur Primary Key/s of that tables. Thanks,
Sandy
Sorry in the First Query i forgot to put one more condition. that is follows Insert Into TABLEA
SELECT B.*
FROM
TABLEB B Left Outer Join TABLEA A on
B.PKID=A.PKID AND
A.PKID Is NULL Thnx
Sandy
i have a primary key on the table i want to insert into..
no primary key on the table i am inserting from.. i also have duplicates in the table i am inserting from..i want to insert the first and prevent the others..
thx
When u dont have Primary Key on TABLEB,Then How do u identify the records which have been transfered?
&
If Primary Key Is There on TABLEA ( the Table Inserting INTO ) and There Is no PK On TABLEB . so your data must be in tables somewhat like as follows.. TABLEA
——
ID,NAME
1,x
2,y
3,z TABLEB
——
ID,NAME
1,x
2,y
2,y
3,z
4,a
4,a
4,a
5,B
In This case You Can Try It. Insert Into TABLEA
SELECT DISTINCT B.*
FROM
TABLEB B Left Outer Join TABLEA A on
B.PKID=A.PKID AND
A.PKID Is NULL Or
Insert Into TABLEA
SELECT Distinct * FROM TABLEB Where
PKID NOT IN ( SELECT PKID FROM TABLEA) Thnx
Sandy
the tables are identical in structure..
the difference is that in tableB i have a primary key on field A
in tableA,i have no primary key on that field..
thx
In This case You Can Try It. Insert Into TABLEA
SELECT DISTINCT B.*
FROM
TABLEB B Left Outer Join TABLEA A on
B.PKID=A.PKID AND
A.PKID Is NULL Or
Insert Into TABLEA
SELECT Distinct * FROM TABLEB Where
PKID NOT IN ( SELECT PKID FROM TABLEA) Or If ur problem doest get solved then Please give some data with the Example.
Thnx
Sandy
thx sandy,
you have been very patient and helpful but i have 2 more questions 4 u.. in your second statement Insert Into TABLEA
SELECT Distinct * FROM TABLEB Where
PKID NOT IN ( SELECT PKID FROM TABLEA) i am basing my distinct nature on 2 fields (phonenumber and transactype) other fields maybe different..i guess the SQL above may change..any help pls? my next question is , performance wise how does NOT IN far compared to a JOIN?
thx
In Answer of your first question. If Your records are distinct on 2 fields only , then which value u want to take of other columns ( last one or first one or … with any specific condition? ). on that base your query will get change. U can use Max Or Min clause to get the Values with the group by of those 2 fields. And in Answer of your second question. Joins would be always better than ‘NOT IN ‘ Clause. For better understanding , u go through the following links , i hope this will help u . http://www.sql-server-performance.com/transact_sql.asp
http://www.sql-server-performance.com/optimizing_indexes.asp
http://www.sql-server-performance.com/query_analysis_tuning.asp Sandy (DB Developer)
my conditions for duplicates in tableA are transactype=N and fonenumber..
once the fonenumber are the same and transactype=N , then i consider it a duplicate..all the other fields (about 20 others) could be the same or different..the most important thing are those 2 fields..
i guess i should have created a composite index on those fields..

If Other Fields are not so important then u can use transfer just only that two fields. Or U can Use the following Querry Insert Into TABLEA
SELECT B.*
FROM
(Select TransactType,fonenumber,max(Fld1),max(Fld2)…,max(Fld20)
From TABLEB Group By TransactType,fonenumber)B
Left Outer Join TABLEA A on
B.TransactType=A.TransactType AND
B.fonenumber=A.fonenumber AND
Where
A.TransactType Is NULL And
A.fonenumber Is NULL Thnx
Sandy
]]>