-1

Is there a way to change every column data type to the same data type at once (e.g. varchar(MAX)) when importing a flat file into SQL Server?

My data set has 500+ columns. I could ALTER the data types by a script, however, the auto assigned data types are incorrect and won't allow me to upload. So the goal would be to change them all at once in the upload screen.

Also, is there a way to tick all the 'Allow Nulls' at once?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
d789w
  • 357
  • 5
  • 19
  • What are you using to import? Exactly what "auto assigned" the data types? Are you talking about SSIS? There are no data types in a flat file but there are in a SQL Server table. Regardless it's not that hard to generate a script to change all the data types. Lets see how long it takes me to google one.... – Nick.Mc Nov 20 '19 at 05:29
  • Here's something that can be modified to alter a bunch of columns. Just make sure the table is empty. https://dba.stackexchange.com/questions/157771/alter-all-data-types-to-different-data-type (it took me 1 minute to find this) – Nick.Mc Nov 20 '19 at 05:31
  • As I mentioned, there’s no point for a SQL script because I can’t upload the data as the data types are incorrect. Going through 500+ columns to fix the individual columns takes longer than changing it to varchar(MAX). I want to change it when uploading, not by a script. – d789w Nov 20 '19 at 06:02
  • How you are uploading. What is "auto creating" the table? What is this "upload screen"? – Nick.Mc Nov 20 '19 at 06:29

1 Answers1

1

Unfortunately no.

I'd be scripting it rather than fighting with the tools.

Greg Low
  • 1,526
  • 1
  • 4
  • 4