Click here to Skip to main content
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 16: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 OriginalGriff 381
1 Sergey Alexandrovich Kryukov 265
2 Praneet Nadkar 237
3 Marcin Kozub 225
4 /\jmot 189
0 OriginalGriff 8,284
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,614
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 22 Feb 2013
Copyright © CodeProject, 1999-2014
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