Can I do a bulk insert into a table from Microsoft SQL Server Management Studio with a copy and past-CodePudding

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.

CodePudding user response：

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:

-- 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  [email protected]
ladams5 32  [email protected]
madams3 18  [email protected]
haguir1 36  [email protected]
'


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

CodePudding user response：

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.

CodePudding user response：

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.