-1

sorry if this has been asked before in some form (I've been looking around for it but haven't found something that fits my question exactly). What I am trying to do is take all of the tables in my database and address all the columns in those tables. What I want to do is have my query automatically change all float columns to nvarchar.

When I import my data, SSMS seems to be randomly assigning my columns with numbers in them as floats or nvarchars. I am running some queries to unionize a lot of tables so I need the columns to have the same types.

Currently, what I have, is that if I manually change all the columns to have the same types, I can unionize everything and cast dates / make certain things uppercase and that all works, but I was wondering if there was a way at the start of my query to make all floats into nvarchar.

Thanks in advance!

Edit: I should add that there are multiple data types per table. For instance, I could be working with a group that has floats, datetimes, and nvarchars. I need to make sure all the datetimes stay in their format while only floats turn into nvarchar.

Edit2: Okay, so the goal of all this is to write a query that will look in a section of related tables (they come from excel, so they are the worksheets of given workbook). The excel version is 97-2003, so it only goes up to 65000 rows per worksheet. Because of this, I have in my database Tables names like the following:

A_1
A_2
A_3
B_1
B_2
C_1
C_2
C_3
C_4

Where each letter corresponds to a different workbook. The end goal is write a query that will basically say something like this: For all columns in Tables A_1 A_2 and A_3, if the column is float, make it varchar. After that, Union A_1 A_2 and A_3. After Unionization, UPPER(Header1), UPPER(Header2), CAST(Header3) AS DATE, etc.

I can already do most of this, but I don't want to manually go through every time and change every column that is type float to type nvarchar. I've already written the part of the query that does everything else.

Scott T
  • 233
  • 2
  • 5
  • 14
  • **How** do you import your data? SSIS ? `BULK INSERT` ? Some other means?? – marc_s Aug 06 '15 at 15:30
  • If there are values corresponding to nvarchar then you cannot change the datatype without data loss – Tharif Aug 06 '15 at 15:30
  • I must be confused here. You're trying to UNION every column of every table? What on earth for? Are you thinking that will be an efficient way to search the whole database? I'm not understanding what your goal is, let alone what you're trying to do. – Bacon Bits Aug 06 '15 at 15:33
  • I am importing my data from excel by right clicking the Database I'm putting it into and clicking Import Data. – Scott T Aug 06 '15 at 15:34
  • @BaconBits I've added a second edit that hopefully explains my process / needs a little better – Scott T Aug 06 '15 at 15:45

2 Answers2

3

Try this

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += 'alter table ' + TABLE_NAME + ' alter column '
               + COLUMN_NAME + ' nvarchar(50) '
FROM   INFORMATION_SCHEMA.columns
WHERE  DATA_TYPE = 'float'

--print @sql
EXEC Sp_executesql @sql
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • what if tables have nvarchar values ? it should show error know – Tharif Aug 06 '15 at 15:31
  • I think this is working for me, I'm going to try it out in a couple different scenarios. If I have any more questions pertaining to this bit, do you mind if I message you again? – Scott T Aug 06 '15 at 16:08
1

Based on your comment that the data is being imported using Management Studio's Insert Data option which opens the Insert Data wizard.

I am importing my data from excel by right clicking the Database I'm putting it into and clicking Import Data.

You should be able to resolve problem using wizard mappings steps. Just put the mapping of float column as varchar or nvarchar.

Sample screenshot

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60