noob with one more thing for the week

[sparks]

I have about 100 columns in a table that I am inserting into another
table.

Most of them require reformatting of the destination.

something like
alter table dbo.ClientData
ALTER COLUMN [CurrentWorkStatus] nvarchar(50) NULL

but I have 50 that need to be updated
with a list of all of them is there a way to automate all 50 at once?

another 50 that need to be changed from tinyint to nvarchar(20)

things like that.

Why?
Heck if I know that is what they want to do.
(they have this super new database that is in production and they want
to import Everything from 2 others into it)
square peg in round hole type of thing

[Bob Barrows]

Post by sparks
I have about 100 columns in a table that I am inserting into another
table.
Most of them require reformatting of the destination.
something like
alter table dbo.ClientData
ALTER COLUMN [CurrentWorkStatus] nvarchar(50) NULL
but I have 50 that need to be updated
with a list of all of them is there a way to automate all 50 at once?
another 50 that need to be changed from tinyint to nvarchar(20)
things like that.
Why?
Heck if I know that is what they want to do.
(they have this super new database that is in production and they want
to import Everything from 2 others into it)
square peg in round hole type of thing

Wait a minute ... I thought you said the destination had the "approved"
schema ... why do you need to modify the destination schema?
You've just confused me.

Anyways, you can insert your list of column names and desired datatypes into
a table.
CREATE TABLE ColumnChanges (
ColName varchar(250), DataTypeChg varchar(50));

INSERT ColumnChanges
VALUES
('CurrentWorkStatus', 'nvarchar(50) NULL'),
('Col2', 'nvarchar(50) NULL'),
...
('ColN', 'nvarchar(50) NULL')


You can use Excel to help generate those row constructors (that's what the
value pairs in the parentheses are called).
Once the column names and datatype changes are inserted, you can use this
query to generate the statements:

SELECT 'ALTER TABLE dbo.ClientData ALTER COLUMN ' +
ColName + ' ' + DataTypeChg + ';' As Statements FROM ColumnChanges

Run the query, copy the statements from the result window into a new query
window and run them.

[sparks]

well they are conserned with the data that is being imported.
They only seem to be interesed in the schema ( at least that is all I
hear)
exact words.
this data is from 2 other databases, merge it and then insert it into
the 3rd database.

Well I can't insert reals into ints
ok then change the destination to hold our values.
BUT DO NOT CHANGE THE SCHEMA
I really am not sure why they keep sying that in such a stern way.

I had them faint when I said how do I remove your data without
changing the keys and then putting them back.
UH there are ways.
They seem to know everything but when I ask how you do something they
just say are you done yet

yep all I lack is finishing up LOL

I did what you said and then just cut and paste the names into excel
then put it all together in textpad
alter table dbo.ClientData
alter table dbo.ClientData ALTER COLUMN [CurrentWorkStatus]
nvarchar(50) NULL (one line...sorry this post is word wrapped funny)
one line for each column.
sure its 50 lines long but it works very well...

thank you all for the tip






On Fri, 30 Mar 2012 17:06:05 -0400, "Bob Barrows"

Post by Bob Barrows

Post by sparks
I have about 100 columns in a table that I am inserting into another
table.
Most of them require reformatting of the destination.
something like
alter table dbo.ClientData
ALTER COLUMN [CurrentWorkStatus] nvarchar(50) NULL
but I have 50 that need to be updated
with a list of all of them is there a way to automate all 50 at once?
another 50 that need to be changed from tinyint to nvarchar(20)
things like that.
Why?
Heck if I know that is what they want to do.
(they have this super new database that is in production and they want
to import Everything from 2 others into it)
square peg in round hole type of thing

Wait a minute ... I thought you said the destination had the "approved"
schema ... why do you need to modify the destination schema?
You've just confused me.
Anyways, you can insert your list of column names and desired datatypes into
a table.
CREATE TABLE ColumnChanges (
ColName varchar(250), DataTypeChg varchar(50));
INSERT ColumnChanges
VALUES
('CurrentWorkStatus', 'nvarchar(50) NULL'),
('Col2', 'nvarchar(50) NULL'),
...
('ColN', 'nvarchar(50) NULL')
You can use Excel to help generate those row constructors (that's what the
value pairs in the parentheses are called).
Once the column names and datatype changes are inserted, you can use this
SELECT 'ALTER TABLE dbo.ClientData ALTER COLUMN ' +
ColName + ' ' + DataTypeChg + ';' As Statements FROM ColumnChanges
Run the query, copy the statements from the result window into a new query
window and run them.

[Bob Barrows]

Post by sparks
well they are conserned with the data that is being imported.
They only seem to be interesed in the schema ( at least that is all I
hear)
exact words.
this data is from 2 other databases, merge it and then insert it into
the 3rd database.
Well I can't insert reals into ints

You're right, but you can cast the reals into integers before inserting
them, _without changing the destination schema_. The decimal portion will be
lost, so you need to verify that this is what is intended. it is likely that
they don't care about the decimal portions. If they do, then there is a
problem and you need to make them aware of it. Show them this thread if you
have to.

Post by sparks
ok then change the destination to hold our values.

Why? you can cast the data to the correct datatypes without changing the
destination schema.

Post by sparks
BUT DO NOT CHANGE THE SCHEMA
I really am not sure why they keep sying that in such a stern way.

Obviously they have processes that depend on the schema being unchanged.
That's not really hard to understand, is it?

If "they" are instructing you to insert the data without modifying either
the data or the destination schema, they have given you an impossible task
and it is time to ask for another assignment..

Post by sparks
I had them faint when I said how do I remove your data without
changing the keys and then putting them back.
UH there are ways.
They seem to know everything but when I ask how you do something they
just say are you done yet
yep all I lack is finishing up LOL
I did what you said and then just cut and paste the names into excel
then put it all together in textpad
alter table dbo.ClientData
alter table dbo.ClientData ALTER COLUMN [CurrentWorkStatus]
nvarchar(50) NULL (one line...sorry this post is word wrapped funny)
one line for each column.
sure its 50 lines long but it works very well...

I've got a bad feeling about this.

[Erland Sommarskog]

Post by sparks
I have about 100 columns in a table that I am inserting into another
table.
Most of them require reformatting of the destination.
something like
alter table dbo.ClientData
ALTER COLUMN [CurrentWorkStatus] nvarchar(50) NULL
but I have 50 that need to be updated
with a list of all of them is there a way to automate all 50 at once?
another 50 that need to be changed from tinyint to nvarchar(20)
things like that.

Get a list of the columns to change. Then take that list with one
row per column. Then in SSMS use Find/Replace and check "Use regular
expression" and change ^ to "ALTER TABLE dbo.ClientData ALTER COLUMN "
(don't miss the trailing space). Then change $ to " nvarchar(20) NULL".

To be honest, I don't recall whether SSMS 2008 uses regular expression,
because I would typically run this in a separate editor like EditPlus.

Many of these kind of exercise requires a methodology to do things in
a simple way. Sometimes Regexps are not necessary - clever copy-paste
may very well do it.

[Bob Barrows]

Post by sparks
I have about 100 columns in a table that I am inserting into another
table.
Most of them require reformatting of the destination.
something like
alter table dbo.ClientData
ALTER COLUMN [CurrentWorkStatus] nvarchar(50) NULL
but I have 50 that need to be updated
with a list of all of them is there a way to automate all 50 at once?
another 50 that need to be changed from tinyint to nvarchar(20)
things like that.
Why?
Heck if I know that is what they want to do.
(they have this super new database that is in production and they want
to import Everything from 2 others into it)
square peg in round hole type of thing

Actually, it just dawned on me that you can do the entire script-generation
process in Excel. In column A, put the list of column names. In column B,
the list of datatype changes (similar to what I had you insert into a table
in my prior reply). Then in the first row of column C, enter this formula:
="ALTER TABLE dbo.ClientData ALTER COLUMN [" & A1 & " " & B1 & ";"

Then copy the formula into the rest of the rows in column C. Then copy the
statements into SSMS and run them.

I'm still not clear about why you are altering the destination columns
rather than converting the data in the the correct datatype, lengths, etc.
during the insert process...