SELECT output formation

• kevv

  SSC Enthusiast

  Points: 191

  More actions

  April 4, 2017 at 5:27 am

  #104378

  Hi

  I'm very very noob at SQL so please forgive me if I'm missing something really obvious or seem like I have no idea what I'm talking about (because I probably dont).
  Anyway..

  I've fetched about 30k rows using this:

  SELECT id,
      Column1,
      Column2,
      Column3,
      Column4,
      Column5,
      Column6
FROM example_table
WHERE id BETWEEN 1 AND 29095;

  I need all 30k rows to look like this upon using "Export grid rows":

  UPDATE example_table SET Column1 = "VALUE", Column2 = "VALUE", etc etc.. WHERE id = "VALUE";

  Any ideas?

  Help is much appreciated!

  Thanks, Kev.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  April 4, 2017 at 5:39 am

  #1936488

  Kev

  It looks like you're on the right lines.  What is the value of "VALUE" for id that you want to update for?

  John

• kevv

  SSC Enthusiast

  Points: 191

  More actions

  April 4, 2017 at 5:51 am

  #1936491

  Thanks for the answer John!

  The value varies on all of the 30,000 rows.

  I forgot to mention that what I want is the rows to look like :
  UPDATE example_table SET Column1 = "VALUE", Column2 = "VALUE", etc etc.. WHERE id = "VALUE";
  upon using "Export grid rows".

  Right now the rows will look like this:
  REPLACE INTO `example_template` (`id`, `column1`, `column2`, `column3`, `column4`, `column5`, `column6`) VALUES (1, 1, 1, 1, 1, 1, 1);

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  April 4, 2017 at 5:58 am

  #1936495

  Kev

  I'm afraid I'm still confused.  Please will you post a CREATE TABLE statement to show what your table looks like, and some INSERT statements so that we can see exactly what kind of data you have?  Please also show us exactly how you want those rows to look after the update.

  Thanks
  John

• Thom A

  SSC Guru

  Points: 99126

  More actions

  April 4, 2017 at 6:05 am

  #1936499

  I could be missing the point entirely here, but do you mean something like this?
  USE DevTestDB;
GO

  CREATE TABLE example
  (id int IDENTITY,
  Column1 varchar(20),
  Column2 varchar(20),
  Column3 varchar(20),
  Column4 varchar(20),
  Column5 varchar(20),
  Column6 varchar(20));
GO

  INSERT INTO example
VALUES
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f'),
  ('a','b','c','d','e','f');

  SELECT 'UPDATE example ' +
'SET Column1 = ''' + Column1 + ''', '+
'SET Column2 = ''' + Column2 + ''', '+
'SET Column3 = ''' + Column3 + ''', '+
'SET Column4 = ''' + Column4 + ''', '+
'SET Column5 = ''' + Column5 + ''', '+
'SET Column6 = ''' + Column6 + ''', '+
'WHERE id = ' + CAST(id as varchar(10)) + ';'
FROM example;
GO

  DROP TABLE example;


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• kevv

  SSC Enthusiast

  Points: 191

  More actions

  April 4, 2017 at 6:13 am

  #1936503

  I could do that but It would be a lot of code and I dont think it's necessary. I'm unsure of how to explain what I want but I'll give it another go:

  In my old database I have some tables with some columns. I want to export these columns and their values into a .SQL file so that I can update the table in my new database which uses the same structure.

  I thought the easiest way to do this would be via a SELECT statement but I could be very wrong.

• Thom A

  SSC Guru

  Points: 99126

  More actions

  April 4, 2017 at 6:18 am

  #1936504

  kevv - Tuesday, April 4, 2017 6:13 AM

  I could do that but It would be a lot of code and I dont think it's necessary. I'm unsure of how to explain what I want but I'll give it another go:

  In my old database I have some tables with some columns. I want to export these columns and their values into a .SQL file so that I can update the table in my new database which uses the same structure.

  I thought the easiest way to do this would be via a SELECT statement but I could be very wrong.

  How would you deal with values that are missing at either end? Your version simply does an update, what if an I'd is in your source, but not your destination (it won't get inserted using an update statement). What about IDs that are in your destination but not your source? Are these ok to be left alone, or should they be deleted, something else?

  Edit: I hate posting on a phone...

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  April 4, 2017 at 6:53 am

  #1936511

  kevv - Tuesday, April 4, 2017 6:13 AM

  I could do that but It would be a lot of code and I dont think it's necessary. I'm unsure of how to explain what I want but I'll give it another go:

  In my old database I have some tables with some columns. I want to export these columns and their values into a .SQL file so that I can update the table in my new database which uses the same structure.

  I thought the easiest way to do this would be via a SELECT statement but I could be very wrong.

  Kev

  The trouble with not going to the trouble of writing a little bit of code (it's not really a lot - you only need something that's representative) is that a lot of people have to scratch their heads wondering what you mean, then post solutions that don't meet your requirement.  Don't forget we can't see your screen.

  That said, it sounds as if you need to use Import/Export in SSMS.  Right-click on a database, then choose Tasks and Import Data or Export Data and follow the wizard.  If the servers can't see each other, use bcp (or Export in SSMS) to export to a csv file and then bcp again (or Import in SSMS) to import into the new database from the text file.  If you're updating existing data in the new database instead of doing a straight insert, import into a staging table and do your manipulations from there.

  John

• Thomas Rushton

  SSC-Insane

  Points: 22648

  More actions

  April 4, 2017 at 7:57 am

  #1936539

  John Mitchell-245523 - Tuesday, April 4, 2017 6:53 AM

  kevv - Tuesday, April 4, 2017 6:13 AM

  I could do that but It would be a lot of code and I dont think it's necessary. I'm unsure of how to explain what I want but I'll give it another go:

  In my old database I have some tables with some columns. I want to export these columns and their values into a .SQL file so that I can update the table in my new database which uses the same structure.

  I thought the easiest way to do this would be via a SELECT statement but I could be very wrong.

  Kev

  The trouble with not going to the trouble of writing a little bit of code (it's not really a lot - you only need something that's representative) is that a lot of people have to scratch their heads wondering what you mean, then post solutions that don't meet your requirement.  Don't forget we can't see your screen.

  That said, it sounds as if you need to use Import/Export in SSMS.  Right-click on a database, then choose Tasks and Import Data or Export Data and follow the wizard.  If the servers can't see each other, use bcp (or Export in SSMS) to export to a csv file and then bcp again (or Import in SSMS) to import into the new database from the text file.  If you're updating existing data in the new database instead of doing a straight insert, import into a staging table and do your manipulations from there.

  John

  In addition to what m'learned colleague says, it could be that the T-SQL MERGE statement is what you need to do some of the manipulation, conceptually anyway.  Just be advised that it's not necessarily the fastest way of doing things...

  Thomas Rushton
  blog: https://thelonedba.wordpress.com

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  April 4, 2017 at 8:07 am

  #1936544

  kevv - Tuesday, April 4, 2017 6:13 AM

  I want to export these columns and their values into a .SQL file so that I can update the table in my new database which uses the same structure.

  Use BCP to output to an SQL "Native" file of the desired table and import it to a staging table at the other end.  Then, do your merge.

  Since "BCP" is an abbreviation for many things, here's the Google search for more info to make it easier on a newbie that may not know what to look for.
  https://www.google.com/?gws_rd=ssl#q=bcp+sql+server&*&spf=1

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• kevv

  SSC Enthusiast

  Points: 191

  More actions

  April 4, 2017 at 10:54 am

  #1936589

  Big thanks to everyone for your help, I think I got it!