-1

I am creating a Script which has more than 700 INSERT statements

INSERT tableName ([A], [B], [D], [E]) VALUES (1, 1, N'a', N'p')
INSERT tableName ([A], [B], [D], [E]) VALUES (2, 1, N'x', N'o')
INSERT tableName ([A], [B], [D], [E]) VALUES (3, 1, N'y', N'u')
INSERT tableName ([A], [B], [D], [E]) VALUES (4, 1, N's', N't')
INSERT tableName ([A], [B], [D], [E]) VALUES (5, 1, N'y', N'w')
INSERT tableName ([A], [B], [D], [E]) VALUES (6, 1, N'z', N'z')
INSERT tableName ([A], [B], [D], [E]) VALUES (7, 1, N'k', N'f')

and so on....

I want to remove the redundant part

INSERT tableName ([A], [B], [D], [E])

Is there any way to achieve this ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IMAGINATION :

some thing like, 

INSERT tableName ([A], [B], [D], [E]) MULTIPLE VALUES
   (1, 1, N'a', N'p')
 , (2, 1, N'x', N'o')
 , (3, 1, N'y', N'u')
 , (4, 1, N's', N't')
 , (5, 1, N'y', N'w')
 , (6, 1, N'z', N'z')
 , (7, 1, N'k', N'f')

please guide ... how can i improve the performance as well , as i have mentioned that i have 700 records to be added... from a single script..

N.K
  • 2,220
  • 1
  • 14
  • 44
  • 1
    Yes this is possible, just remove the `multiple`: https://msdn.microsoft.com/en-us/library/ms174335.aspx See here: http://sqlfiddle.com/#!6/07202/1 –  May 10 '16 at 09:13
  • Possible in SQL Server by removing the Multiple. Not sure about other vendors. – Carra May 10 '16 at 09:14
  • If you supply all the columns in the table you do not need the first part: Let's say you have columns A to E and C should always be null, then do this: INSERT tableName VALUES (1, 1, null, N'a', N'p') INSERT tableName VALUES (2, 1, null, N'x', N'o') INSERT tableName VALUES (3, 1, null, N'y', N'u') INSERT tableName VALUES (4, 1, null, N's', N't') INSERT tableName VALUES (5, 1, null, N'y', N'w') INSERT tableName VALUES (6, 1, null, N'z', N'z') INSERT tableName VALUES (7, 1, null, N'k', N'f') – nivs1978 May 10 '16 at 09:38

1 Answers1

1

You can try like this:

INSERT tableName ([A], [B], [D], [E])  VALUES
   (1, 1, N'a', N'p')
 , (2, 1, N'x', N'o')
 , (3, 1, N'y', N'u')
 , (4, 1, N's', N't')
 , (5, 1, N'y', N'w')
 , (6, 1, N'z', N'z')
 , (7, 1, N'k', N'f')

i.e., simply remove the MULTIPLE from your query which you almost got it yourself.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331