Click here to Skip to main content
11,577,258 members (54,050 online)
Rate this: bad
good
Please Sign up or sign in to vote.
Hi, experts,
I am using SQL Server 2008.
If I want to insert a row in a table, then I simply use the following query
insert into Table1 (Col1, Col2) values  ('EDJ','LP')

And if I wanna check before inserting then I follow
If not exists (select 1 from Table1 where Col1='EDJ' and  Col2='LP) 
begin 
insert into Table1 (Col1, Col2) values  ('EDJ','LP') 
end 
else 
begin 
Print 'Data already exist.' 
end

Now, for multirow inserting in a single query, I follow
insert into Table1 (Col1, Col2) values  ('EDJ', 'LP'), ('AGE', 'LP'), ('DJR', 'VG')
But if I wanna check for multirow insertion in a single batch, then what would be the query ???

Thanks in advance.
Posted 21-Feb-13 15:20pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

1. Insert the value in a table variable first.
2. Check the existence.
3. Insert or show message.

DECLARE @TmpTbl TABLE (Col1 VARCHAR(100), col2 VARCHAR(100))
 
INSERT INTO @TmpTbl(Col1,Col2)VALUES('EDJ','LP'), ('AGE', 'LP'), ('DJR', 'VG')
  
  IF NOT EXISTS(
         SELECT 1
         FROM   Table1 t1
                INNER JOIN @TmpTbl t2
                     ON  t.Col1 = t2.Col1
                     AND t1.Col2 = t2.Col2
     )
  BEGIN
      INSERT INTO Table1(Col1,Col2)
      SELECT Col1,Col2
      FROM   @TmpTbl tt
  END
  ELSE
  BEGIN
      PRINT 'Data already exist.'
  END   
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

My suggestion is you can explore tsql Merge statement if it is sql server specific problem.
Code sample
CREATE TABLE Target(FirstName varchar(10), LastName varchar(10));
GO
 
CREATE TABLE #Source(FirstName varchar(10), LastName varchar(10));
INSERT INTO #Source(FirstName, LastName) VALUES('A','B'), ('C','D'), ('E','F');
 
Merge [Target] AS T
Using (SELECT * FROM #Source) AS S
ON (T.FirstName = S.FirstName AND T.LastName = S.LastName)
WHEN NOT MATCHED BY Target THEN
	INSERT (FirstName, LastName) VALUES(S.FirstName, S.LastName);
--If you want to do something like insert/update/delete then you can use following statement
--WHEN MATCHED THEN
  --UPDATE SET T.FirstName = S.FirstName, T.LastName = S.LastName;

DROP TABLE #Source;

Please visit the links
Link2
Link1
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi,

Try like this

INSERT INTO table1(Col1,Col2)
SELECT D.Col1, D.Col2
FROM (SELECT 'EDJ' Col1, 'LP' Col2
      UNION ALL SELECT 'AGE', 'LP'
	 UNION ALL SELECT 'DJR', 'VG') D 
WHERE NOT EXISTS (SELECT 1 FROM Table1 WHERE Col1=D.Col1 AND Col2=D.Col2 )


In Case of Direct Insert like "INSERT INTO Table_Name (Column_Name) VALUES()..." Go for INSERT with SELECT Statement.


Regards,
GVPrabu
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 DamithSL 155
1 Afzaal Ahmad Zeeshan 125
2 OriginalGriff 115
3 Richard MacCutchan 100
4 Abhinav S 90
0 OriginalGriff 820
1 Sergey Alexandrovich Kryukov 676
2 Abhinav S 528
3 F-ES Sitecore 420
4 Suvendu Shekhar Giri 375


Advertise | Privacy | Mobile
Web03 | 2.8.150603.1 | Last Updated 22 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100