0

I often get a list of names I need to update in a table from an Excel list, and I end up creating a SSIS program to reads the file into a staging table and doing it that way. But is there I way I could just copy and past the names into a table from Management Studio directly? Something like this:

create table #temp (personID int, userName varchar(15))
Insert
Into #temp (userName)
values (
'kmcenti1',
'ladams5',
'madams3',
'haguir1',
)

Obviously this doesn't work but I've tried different variations and nothing seems to work.

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
Jimmy Genslinger
  • 557
  • 3
  • 21
  • You are almost there. Need parenthesis around each string. See https://stackoverflow.com/questions/2624713/insert-multiple-rows-without-repeating-the-insert-into-part-of-the-stateme – PM 77-1 Nov 08 '22 at 19:39

3 Answers3

3

Here's an option with less string manipulation. Just paste your values between the single quotes

Declare @List varchar(max) = '
kmcenti1
ladams5
madams3
haguir1
'

Insert into #Temp (userName)
Select username=value
  From  string_split(replace(@List,char(10),''),char(13))
 Where Value <>''

For Multiple Columns Source:

enter image description here

-- This is a copy/paste from Excel --
-- This includes Headers which is optional --
-- There is a TAB between cells --

Declare @List nvarchar(max) = '
Name    Age email
kmcenti1    25  kmcenti1@gmail.com
ladams5 32  ladams5@gmail.com
madams3 18  madams3@gmail.com
haguir1 36  haguir1@gmail.com
'


Select Pos1 = JSON_VALUE(JS,'$[0]')
      ,Pos2 = JSON_VALUE(JS,'$[1]')  -- could try_convert(int)
      ,Pos3 = JSON_VALUE(JS,'$[2]')
 From  string_split(replace(replace(@List,char(10),''),char(9),'||'),char(13)) A
 Cross Apply (values ('["'+replace(string_escape(Value,'json'),'||','","')+'"]') ) B(JS)
 Where Value <>''
   and nullif(JSON_VALUE(JS,'$[0]'),'')<>'Name'

Results

enter image description here

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
1

Is this along the lines you're looking for?

create table #temp (personID int identity(1,1), userName varchar(15))
insert into #temp (userName)
select n from (values 
('kmcenti1'),
('ladams5'),
('madams3'),
('haguir1'))x(n);

This assumes you want the ID generated for you since it's not in your data.

Stu
  • 30,392
  • 6
  • 14
  • 33
  • Thank you!! The ID is in the database, but the lists I receive only supply usernames so I have to upload the usernames and then to an update the temp table with the ID from their personnel record – Jimmy Genslinger Nov 08 '22 at 20:08
0

That SQL statement you have won't work (That's one row). But I have a work around. Build what you need with a formula in Excel.

Assuming user IDs are in column A:

In Cell B2, insert this formula:

="('"&A1&"'),"

And then drag the formula down you list.

Go to SSMS and type in:

 insert into [your table](userName) values

And then paste in column B from Excel and delete the last comma.

KeithL
  • 5,348
  • 3
  • 19
  • 25