-2

I have data from an sql query which i need to update in a table. Im saving the output of this query as a csv file. When i do load this file in sql though using the import export wizard, the data is all jumbled up as the columns have shifted in a lot of cases. So unrelated data is updated in many columns.

What could be the reason for this and please suggest a resolution.

Regards, Priyesh

priyesh a
  • 21
  • 1
  • 2
  • 7
  • 5
    Why not just insert from the query into the table? Going through a CSV file seems needlessly complicated. – Gordon Linoff Mar 06 '19 at 17:03
  • 2
    Tag properly. SQL Server <> MySQL. Which one is this??? – Eric Mar 06 '19 at 17:07
  • Hi Gordon, how do i do that? I cant create a temp table because this needs to be shared with the team and also there are almost 150 columns in my data. – priyesh a Mar 06 '19 at 17:10
  • Hi Eric, modified the tag – priyesh a Mar 06 '19 at 17:11
  • You didn't change the tags, but for [SQL Server & MySQL here is how](https://stackoverflow.com/questions/25969/insert-into-values-select-from) or [here as an UPDATE](https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server) – S3S Mar 06 '19 at 17:14
  • 1
    Usually, this type of issue is a result of not adding a data element correctly when it is NULL or empty. For example, if you were just adding `First Name, Middle Name, Last Name` elements, your output without a middle name should be like "My",,"Name". Sometimes, the concatenation step checks for data before appending it. Since there is no middle name element, this would result in output like "My","Name", missing a comma for an empty data element. To check, count the commas in a problem line. – Laughing Vergil Mar 06 '19 at 17:48

1 Answers1

1

The obvious answer here is to use the insert into select syntax. If you have a query already that has generated data, insert it to where you want directly without saving to a CSV. 

INSERT INTO target_table
SELECT * FROM your_original_query

If the issue is manually writing out most of the column names for a 150 column table, here is a trick. From within SSMS, click on the Columns folder of the table in question, and drag that to a new query window. You will get a list of all the column names for the table. (You could then remove the few that you do not need.)

Robert Sievers
  • 1,277
  • 10
  • 15