Unique problem

• SQLPain

  Hall of Fame

  Points: 3522

  More actions

  October 15, 2015 at 2:13 pm

  #399192

  I am trying to make the following update: All the columns are fine except for the 'name' column. datatype for 'name' column in the target table is varchar(30) and the datatype for 'name' in the sourcetable is varchar(40), I cannot change the datatype of the column 'name' to varchar(40) because I am told it may affect performance. what I want to do is just update the first 'name' column of the target table by the first 30 characters of the source table column 'name'

  I am using the following query, can somebody let me know if it is possible to do it or are there any other ways I can update the column without changing the datatype?

  MERGE INTO .[dbo].[AF_Copy] AS TargetTable

  USING (SELECT source_code, name, addr1, city, zip FROM

  [D].[D_TEST].[dbo].[SO_Copy]) AS SourceTable

  ON ([TargetTable].[Code] = [SourceTable].[source_code])

  WHEN MATCHED

  THEN

  UPDATE

  SET --[TargetTable].[Name] = [SourceTable].[name],

  [TargetTable].[AddLine1] = [SourceTable].[addr1],

  [TargetTable].[City] = [SourceTable].[city],

  [TargetTable].[Zip] = [SourceTable].[zip];

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  October 15, 2015 at 2:42 pm

  #1833624

  SQLPain (10/15/2015)

  ---

  I am trying to make the following update: All the columns are fine except for the 'name' column. datatype for 'name' column in the target table is varchar(30) and the datatype for 'name' in the sourcetable is varchar(40), I cannot change the datatype of the column 'name' to varchar(40) because I am told it may affect performance. what I want to do is just update the first 'name' column of the target table by the first 30 characters of the source table column 'name'

  I am using the following query, can somebody let me know if it is possible to do it or are there any other ways I can update the column without changing the datatype?

  MERGE INTO .[dbo].[AF_Copy] AS TargetTable

  USING (SELECT source_code, name, addr1, city, zip FROM

  [D].[D_TEST].[dbo].[SO_Copy]) AS SourceTable

  ON ([TargetTable].[Code] = [SourceTable].[source_code])

  WHEN MATCHED

  THEN

  UPDATE

  SET --[TargetTable].[Name] = [SourceTable].[name],

  [TargetTable].[AddLine1] = [SourceTable].[addr1],

  [TargetTable].[City] = [SourceTable].[city],

  [TargetTable].[Zip] = [SourceTable].[zip];

  Should be as simple as this.

  LEFT([SourceTable].[name], 30)

  I have to ask, why are you using MERGE for a basic update?

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• SQLPain

  Hall of Fame

  Points: 3522

  More actions

  October 15, 2015 at 2:53 pm

  #1833631

  Thanks !!! its working

  I read it somewhere merge statement has a less complicated code to write. which would be better in my case, merge or update? merge is getting me the desired result.

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  October 15, 2015 at 3:00 pm

  #1833637

  SQLPain (10/15/2015)

  ---

  Thanks !!! its working

  I read it somewhere merge statement has a less complicated code to write. which would be better in my case, merge or update? merge is getting me the desired result.

  MERGE works but is more complicated than a basic update statement. When all is said and done it should be the same execution plan.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/