How to update a table with list of values in T-SQL?

Question

I have a table that I want to update one-to-many relationship records. The table def is:

CREATE TABLE CT_ProductFailures 
( 
PFID int identity(1,1) PRIMARY KEY NOT NULL, 
Old_Modes varchar(75), 
New_Modes varchar(75) 
)

Now I want to add records for New_Mode 'N1' with Old_Modes 'A', 'B', 'C'. Other than by doing separate insert statements, how can I achieve this?

No, I'd rather just use one insert statement. I have a list of 1000 records and rather than manually type all these records before loading, I'm looking for a more automated solution. — salvationishere, May 01 '11 at 23:00
Please provide an example of how your source data is currently formatted. — Martin Smith, May 01 '11 at 23:45
Above, I described in general terms how it is formatted. It is one contiguous list: "A, B, C, D, ..." which is separated by comma delimiters between each record. So I want to do as little manual work as possible. Is there a way to do this? — salvationishere, May 02 '11 at 00:04

score 2 · Answer 1 · answered May 01 '11 at 23:04

2

If you are using SQL Server 2008 you can do this rather easily using syntax like below

INSERT INTO CT_ProductFaiures (Old_Modes, New_Modes)
VALUES ('N1', 'A'), ('N1', 'B'), ('N1', 'C');

This assumes PFID is auto incrementing.

You can use UNIONS do sort of achieve this in older versions. Check out the link below for great examples.

http://blog.sqlauthority.com/2008/07/02/sql-server-2008-insert-multiple-records-using-one-insert-statement-use-of-row-constructor/

answered May 01 '11 at 23:04

K Richard

1,924
2
22
43

Yes, I have 2008 and yes, PFID is auto incrementing. But I don't want to do it ur way cause then I have to type in each record. Instead I want to copy this list so that all I have to do is add single quotes around each record. And then I want to insert this list, so that each word gets added to a separate record. Make sense? (This list is already typed for me.) – salvationishere May 01 '11 at 23:08
1

If the list in in MS Excel, wouldn't it be easy to write a function in MS Excel to produce the VALUES format? For instance..."('"&A1&"', '"&B1&"')," – K Richard May 02 '11 at 02:04
... or do a simple search and replace, or execute a macro in your favorite editor... – Lieven Keersmaekers May 02 '11 at 06:44

score 1 · Answer 2 · edited May 23 '17 at 12:32

1

INSERT INTO CT_ProductFailures (New_Modes, Old_Modes)
SELECT n.New_Modes, o.Old_Modes
FROM (SELECT 'N1' New_Modes) n
CROSS JOIN (SELECT 'A' Old_Modes
    UNION ALL SELECT 'B'
    UNION ALL SELECT 'C') o

Alternatively, write a split function using newline as the separator:

INSERT INTO CT_ProductFailures (New_Modes, Old_Modes)
SELECT 'N1' New_Modes, o.value Old_Modes
FROM dbo.split(
'A
B
C') o

edited May 23 '17 at 12:32

Community

1
1

answered May 01 '11 at 23:13

Anthony Faull

17,549
5
55
73

Thanks, but is there a way to do this so that I can enter the list with one contiguous flow? Like a way so that I don't need to add the extra "UNION ALL SELECT" between each record? So all I have to do is add single quotes around each record? – salvationishere May 01 '11 at 23:17
1

Considered using BCP or BULK INSERT or SSMS Import or Excel Export. – Anthony Faull May 01 '11 at 23:42

Alex Aza · Answer 3 · 2011-05-02T08:18:04.950

Ryan,

You are saying: 'Instead I want to copy this list so that all I have to do is add single quotes around each record'.
It is not clear how your list is formated.

Let's assume you have the list like this (comma separated values)

N1, A
N1, B
N1, C

What you can do is:

Copy/paste the list to SQL Server Management Studio
Run some replacements using regular expressions:
- hit Ctrl+H to open 'Find and Replace' dialog
- in Find Options select Use 'Regular expressions'
- Find what: [^] Replace with: [('] Hit 'Replace All'
  Don't type square parenthesis in. I used them to make sure you could see the spaces!
- Find what: [$] Replace with: ['),] Hit 'Replace All'
- Find what: [, ] Replace with: [', '] Hit 'Replace All'
- Remove the last redundant comma
You will end up with the list like this:
```
('N1', 'A'),
('N1', 'B'),
('N1', 'C')
```

Then add insert statement at the top of the script :

insert into CT_ProductFaiures (Old_Modes, New_Modes)
values

So in the result you have this

insert into CT_ProductFaiures (Old_Modes, New_Modes)
values 
('N1', 'A'),
('N1', 'B'),
('N1', 'C')

Hope that this is what you needed.

[Edited]
I'm sorry, didn't pay attention.
So list is formatted like this: A, B, C, D.
Then you just replace [,] with ['), ('N1', '] without any regular expressions.
Also, fix starting and ending value manually.

Thanks Alex, I would have responded earlier but my SSMS program at home crashed and wouldn't start. Thank you for your helps! However, can u try running that insert with multi-values in one pass? I get an error if I try to insert more than one record at a time — salvationishere, May 02 '11 at 16:29
What kind of error do you see? Does the table have a trigger on insert? — Alex Aza, May 03 '11 at 00:32

score 0 · Answer 4 · answered May 02 '11 at 00:05

0

Create a table called NewTable with the A B C records with another New_Modes field.

Use this table in your insert command.

answered May 02 '11 at 00:05

JeffO

7,957
3
44
53

How to update a table with list of values in T-SQL?

4 Answers4