0

Let's say we wanted to put the same data into multiple rows like so.

Column names for this example:

Monday, Tuesday, Wednesday, Thursday, Friday 

INSERT INTO sample 
VALUES ('long string')

This is an example of what I mean so the same value is in each column

Monday      |  Tuesday    | Wednesday   | Thursday    | Friday 
long string | long string | long string | long string | long string

I want that long string within all columns, is there a way to do this without using multiple insert statements? OR is there a way to create a CTE that could automate this to make it easier?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bartholomew Allen
  • 21
  • 5
  • I did its C-treeACE – Bartholomew Allen Jul 13 '18 at 14:52
  • do you have access to a scripting utility (bash, Perl, many other choices ...) that can generate the multiple INSERT statements for you? sometimes a semi-automated solution is better than a fully automated one, particularly for one-off tasks; even for a repeating task, "glueing" things together with scripts can provide a nice, pragmatic solution – landru27 Jul 13 '18 at 15:09
  • would powershell be good for scripting this? landru27 – Bartholomew Allen Jul 13 '18 at 15:10
  • I've just read up on c-treeACE, which is something new to me; sounds like it's an embedded database, not a stand-alone database where a utility script would be more applicable; as an embedded database, can you just have your application programmatically generate / run the multiple INSERT statements (such as within a loop)? the answers others have provided also look like good ways to shorten the task a bit – landru27 Jul 13 '18 at 15:29
  • First I want to thank you, for doing your best to understand the situation and then getting familiar with what I am talking about. To answer your question I am free to do what I can to get this to work. I actually want to use powershell to create a script that will work with this – Bartholomew Allen Jul 13 '18 at 16:00

3 Answers3

1

You have to list all the values:

insert into sample (Monday, Tuesday, Wednesday, Thursday, Friday)
    values ('long string', 'long string', 'long string', 'long string', 'long string');

You don't specify the database, but you could use a subquery to reduce the size of the query:

insert into sample (Monday, Tuesday, Wednesday, Thursday, Friday)
    select val, val, val, val, val
    from (select 'long string' as val) x;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You could expand Gordon's example like this:

DECLARE @valuesToInsert TABLE (val NVARCHAR(MAX))
INSERT INTO @valuesToInsert
VALUES 
('This is a long sentence'), 
('This is another long sentence'), 
('This is yet another long sentence')

INSERT INTO sample (Monday, Tuesday, Wednesday, Thursday, Friday)
SELECT  val, val, val, val, val
FROM    @valuesToInsert

@valuesToInsert is your datasource that you want to use (I've created a table variable just to show multiple data rows

twoleggedhorse
  • 4,938
  • 4
  • 23
  • 38
-1

Try

insert into sample (Monday, Tuesday, Wednesday, Thursday, Friday) values select 'long string'

Jay Ganesan
  • 113
  • 6