Trim all user tables with nvarchar datatype
-
We are working with a vendor that recently supplied us with a new copy of their database. They changed 70-100 user tables field datatypes from char to nvarchar. In doing so, our data has padding or trailing spaces. A couple of the fields also have preceeding spaces...
Can someone help me with a script that will:
Loop through all user tables with nvarchar datatype and perform a ltrim(rtrim(field))?
-
This will take a few modifications but the basic is there :
for the server objects
IF Object_id('ListTableColumns_NotNulls') > 0
DROP FUNCTION dbo.ListTableColumns_NotNulls
GO
IF Object_id('TablesColumnsNulls') > 0
DROP TABLE dbo.TablesColumnsNulls
GO
IF Object_id('TablesColumnsNulls_Insert') > 0
DROP PROCEDURE dbo.TablesColumnsNulls_Insert
GO
CREATE FUNCTION dbo.ListTableColumns_NotNulls (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + 'COUNT([' + C.Name + ']) [' + C.Name + '],'
FROMdbo.SysColumns C
WHEREC.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
AND C.IsNullAble = 1
AND XType NOT IN (34,35,36,99)
ORDER BYC.Name
SET @Items = LEFT(@Items, ABS(LEN(@Items) - 1))
RETURN @Items
END
GO
CREATE TABLE dbo.TablesColumnsNulls
(
TableName sysname not null,
DateLog datetime not null default (GetDate()),
ColumnName sysname not null,
QtyNonNulls int not null,
primary key clustered (DateLog, TableName, ColumnName)
)
GO
CREATE PROCEDURE dbo.TablesColumnsNulls_Insert @TableName as sysname, @ColumnName as sysname, @QtyNonNulls as int
AS
SET NOCOUNT ON
Insert into dbo.TablesColumnsNulls (TableName, ColumnName, QtyNonNulls) values (@TableName, @ColumnName, @QtyNonNulls)
SET NOCOUNT OFF
GO
The dts activeX script :
Function Main()
Dim MyRsCols
Dim MyRsCount
Dim MyCn
Dim OColumns
Dim OTable
Dim oField
Dim sTable
Dim iCount
SET MyCn = CreateObject ("ADODB.Connection")
SET MyRsCols = CreateObject ("ADODB.RecordSet")
SET MyRsCount = CreateObject ("ADODB.RecordSet")
MyCn.Open ("Provider=sqloledb;Data Source=SERVEUR4;Initial Catalog=Ideal;Integrated Security=SSPI;")
MyRsCols.Open "Select O.Name, NULLIF(dbo.ListTableColumns_NotNulls(O.id), '') as NullColumns from dbo.SysObjects O WHERE O.XType = 'U' and LEN(dbo.ListTableColumns_NotNulls(O.id)) < 7000 order by O.Name", MyCn, 1, 3
if not MyRsCols.EOF THEN
SET OTable = MyRsCols.Fields ("Name")
SET OColumns = MyRsCols.Fields ("NullColumns")
WHILE NOT MyRsCols.EOF
if not ISNULL(OColumns.value) THEN
MyRsCount.Open "Select '[" & OTable.Value & "]', " & OColumns.Value & " FROM dbo.[" & OTable.value & "]", MyCn, 1, 3
sTable = ""
iCount = 0
For each oField in MyRsCount.Fields
if iCount = 0 then
sTable = MyRsCount.Fields(0).Value
else
MyCn.Execute "EXEC dbo.TablesColumnsNulls_Insert '" & sTable & "', '" & OField.Name & "', " & oField.Value
end if
iCount = iCount + 1
next
MyRsCount.Close
END IF
MyRsCols.MoveNext
WEND
END IF
MyRsCols.Close
MyCn.Close
SET MyRsCount = NOTHING
SET MyRsCols = NOTHING
SET MyCn = NOTHING
Main = DTSTaskExecResult_Success
End Function
-
here's a script to find all columns by datatype that might help:
select sysobjects.name as TblName,
syscolumns.name as ColName,
type_name(syscolumns.xtype) As DataType
--into #FKFinder
from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
where sysobjects.xtype='U'
and type_name(syscolumns.xtype)='datetime'
--datatypes
'bit','char','datetime','float','image','int','money','nvarchar','real','smallint','text','varchar')--and syscolumns.name like '%TBLKEY'
order by TblName,colname
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
There are actually a couple of ways to do this. I use the folowing:
declare TextColumnCursor cursor for select name from syscolumns
where id in (select id from sysobjects where name = N'TblPrepTemplate')
and xtype = 231 --nVarchar
open TextColumnCursor
declare @ColName varchar (50), @SQLText nvarchar (4000)
fetch next from TextColumnCursor into @ColName
while @@fetch_status = 0
begin
-- Trims all Field
set @SQLText = 'update TblPrepTemplate set ['
set @SQLText = @SQLText + @ColName + '] = ltrim(rtrim(['
set @SQLText = @SQLText + @ColName + ']))'
Exec (@SQLText)
fetch next from TextColumnCursor into @ColName
end
close TextColumnCursor
deallocate TextColumnCursor
<hr noshade size='1' width='250' color='#BBC8E5'>Kindest Regards,
Roelof
<a href='http://' class='authorlink' target='_blank'></a>--There are only 10 types of people in the world. Those who understand binary, and those who don't.
-
That's the simple way... the problem is that if you have a table with 100 text columns and a few million rows, it'll take hours/day to finish this. While my version does a single pass and updates all the columns for the whole table in one shot.
-
I like Roelof's solution
But instead of building and executing an update statement for each text column of the table, it's probably faster to use the loop to build a single update statement that updates all text columns of the table, and then execute this after the loop. This keeps the code simple and I don't think you can do it much faster...I have better mention that such an update statement can also be generated without using a cursor, otherwise Remi will tell me that cursors are bad (because of performance) and can almost always be avoided
In this case, however, I don't think it is worth doing it... -
See any cursor in my code?
How much do you wanna bet that my code finishes WAAAYYY before Roelof's solution??
-
I think my solution is faster than Roelof's solution... The cursor part of my solution will take less than 1 second, and after that there is "only" a single update statement to be executed. I don't think this can be done any faster, but I could be wrong...
-
You're basically doig the same thing as I am, however I don't have a limit of 8000 characters to work with (providing moving the function's work in the dts loop)
. -
I think I can live with the 8000 character limit
Especially if it means that my code is easier to read... I still haven't completely figured out how your code works
- but that's not the first time... -
Here's why I moved this code to vbs... and why I would have to put the columns' listing in vbs as well :
CREATE TABLE [Chaudières] (
[no] [int] IDENTITY (1, 1) NOT NULL ,
[CNUM] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Nom du client] [nvarchar] (50) COLLATE French_CI_AS NOT NULL ,
[Succursale] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Numéro] [int] NULL ,
[Rue] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Ville] [nvarchar] (50) COLLATE French_CI_AS NOT NULL ,
[Responsable] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[N° téléphone] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Appareil] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Date de dernière mise à jour] [smalldatetime] NULL ,
[Appareil #] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Eau Chaude] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Eau Chaude] DEFAULT (0),
[Vapeur] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Vapeur] DEFAULT (0),
[Huile Thermique] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Huile Thermique] DEFAULT (0),
[Huile#2] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Huile#2] DEFAULT (0),
[Huile#6] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Huile#6] DEFAULT (0),
[Huile usée] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Huile usée] DEFAULT (0),
[Gaz Naturel] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Gaz Naturel] DEFAULT (0),
[Gaz Propane] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Gaz Propane] DEFAULT (0),
[Gaz Butane] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Gaz Butane] DEFAULT (0),
[Électrique] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Électrique] DEFAULT (0),
[Autre1] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Autre1] DEFAULT (0),
[Autre] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Pression de gaz] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Série] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[commande] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Capacité de la chaudière] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Capacité de la chaudière2] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Crn] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Surface de chauffe] [real] NULL ,
[dappareil usagé] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Trou de main qtée] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[trou de main dim] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Trou de main qtée2] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[trou de main dim2] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[trou dhomme qtée] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[trou dhomme dim] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Tubes Qtée] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Tubes Diamètre] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Tubes Longueur 1] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Tubes Quantité 2] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Tubes Diamètre 2] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Tubes Longueur 2] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Gauge Glass Ø] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Gauge Glass longueur] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Pression de conception] [int] NULL ,
[Pression dopération] [int] NULL ,
[Brûleur intégral] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Brûleur intégral] DEFAULT (0),
[Brûleur Marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Brûleur Modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Brûleur Série] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Moteur HP] [nvarchar] (255) COLLATE French_CI_AS NULL ,
[Moteur Volts] [nvarchar] (255) COLLATE French_CI_AS NULL ,
[Moteur RPM] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Moteur Frame] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Nozzle débit] [numeric](18, 2) NULL ,
[Nozzle degré] [int] NULL ,
[Nozzle type] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Electrodes] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Transformateur dallumage marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Transformateur dallumage modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Type de ventilateur] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Dimension du ventilateur] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Sens de rotation du ventilateur] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Régulateur marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Régulateur modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Régulateur orifice] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Régulateur spring] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve principale #1 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve principale #1 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve #1 actuateur] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve principale #2 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve principale #2 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve #2 actuateur] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Vent valve marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Vent valve modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve modulante marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve modulante modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Actuateur modulant marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Actuateur modulant modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Lgps marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Lgps modele] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Hgps marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Hgps modele] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve lubrifiée principale marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve lubrifiée principale modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Régulateur de veilleuse marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Régulateur de veilleuse modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Régulateur de veilleuse orifice] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Régulateur de veilleuse spring] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve veilleuse #1 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve veilleuse #1 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve veilleuse #2 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve veilleuse #2 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Vent valve de veilleuse marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Vent valve de veilleuse modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve lubrifiée de veilleuse marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve lubrifiée de veilleuse modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve principale dhuile #1 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve principale dhuile #1 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve principale dhuile #2 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve principale dhuile #2 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve modulante dhuile marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve modulante dhuile modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Actuateur modulant dhuile marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Actuateur modulant dhuile modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de retour marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de retour modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de purge marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de purge modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Réchaud dhuile électrique marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Réchaud dhuile électrique modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Réchaud dhuile à vapeur marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Réchaud dhuile à vapeur modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Contrôle temp dhuile marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Contrôle temp dhuile modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Carte de sortie du contrôle marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Carte de sortie du contrôle modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Compresseur à air marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Compresseur à air modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Filtre à air marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Filtre à air modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Filtre à huile marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Filtre à huile modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Courroie] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Moteur compresseur HP] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Moteur compresseur Volts] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Moteur compresseur RPM] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Moteur compresseur Frame] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Contrôle de bas niveau deau marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Contrôle de bas niveau deau modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Contrôle de très bas niveau deau marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Contrôle de très bas niveau deau modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Contrôle de haut niveau deau marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Contrôle de haut niveau deau modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Haute limite marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Haute limite modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Opérateur marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Opérateur modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Contrôle modulation marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Contrôle modulation modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Interrupteur de pression dair marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Interrupteur de pression dair modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Superviseur de flamme marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Superviseur de flamme modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Programmeur marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Programmeur modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Amplificateur marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Amplificateur modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Détecteur de flamme marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Détecteur de flamme modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #1 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #1 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #1 pression] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #1 capacité] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #1 dimension] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #2 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #2 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #2 pression] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #2 capacité] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #2 dimension] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #3 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #3 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #3 pression] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #3 capacité] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #3 dimension] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #4 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #4 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #4 pression] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #4 capacité] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #4 dimension] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #5 marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #5 modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #5 pression] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #5 capacité] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Valve de sureté #5 dimension] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Fusibles du moteur du ventilateur] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Fusibles de la metering pump] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Fusibles du réchaud électrique] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Fusibles du transformateur de contrôle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Démarreur du moteur du ventilateur marque] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Démarreur du moteur du ventilateur modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[O/L modèle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[O/L Range] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[Transformateur de contrôle] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[DateInspection] [datetime] NULL ,
[Ouverte] [bit] NOT NULL CONSTRAINT [DF_Chaudières_Ouverte] DEFAULT (0),
[DateNettoyage] [datetime] NULL ,
[InspecteurEstFaxe] [bit] NOT NULL CONSTRAINT [DF_Chaudières_InspecteurEstFaxe] DEFAULT (0),
[FkContrat] [varchar] (10) COLLATE French_CI_AS NULL ,
[ANettoyer] [bit] NOT NULL CONSTRAINT [DF_Chaudières_ANettoyer] DEFAULT (0),
[AnneeInspection] [int] NULL ,
[TsAccess] [timestamp] NOT NULL ,
CONSTRAINT [PK_Chaudières] PRIMARY KEY NONCLUSTERED
(
[no]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_Chaudières_FkContrat] FOREIGN KEY
(
[FkContrat]
) REFERENCES [PD et SOUM] (
[# Prix donné]
)
) ON [PRIMARY]
GO
Count it all you want, but 8000 characters is not long enough
. -
Got me there
You are from Quebec, I guess (changing the subject
)? -
Yup.
btw I DID NOT build this ?table??? if anyone's wondering
. -
Been to Montreal twice, nice city
This should deal with your table
create table Lines
(
Seq int,
Line nvarchar(4000)
)
go
declare @Query nvarchar(4000)
declare @add nvarchar(2)
declare @Seq int
declare @Query1 nvarchar(4000)
declare @Query2 nvarchar(4000)
declare @Query3 nvarchar(4000)
declare @Query4 nvarchar(4000)
declare @Query5 nvarchar(4000)
declare @Query6 nvarchar(4000)
declare @Query7 nvarchar(4000)
declare @Query8 nvarchar(4000)
declare @Query9 nvarchar(4000)
declare @Query10 nvarchar(4000)
set @Query = ''
set @Query1 = ''
set @Query2 = ''
set @Query3 = ''
set @Query4 = ''
set @Query5 = ''
set @Query6 = ''
set @Query7 = ''
set @Query8 = ''
set @Query9 = ''
set @Query10 = ''
set @add = ''
set @Seq = 1
declare TextColumnCursor cursor for select name from syscolumns
where id in (select id from sysobjects where name = N'Chaudières')
and xtype = 231 --nVarchar
open TextColumnCursor
declare @ColName varchar (50), @SQLText nvarchar (4000)
fetch next from TextColumnCursor into @ColName
while @@fetch_status = 0
begin
set @Query = @Query + @add + ' [' + @ColName + '] = ltrim(rtrim([' + @ColName + ']))'
set @add = ', '
if len(@Query) > 3000
begin
insert into Lines select @Seq, @Query
set @Query = ''
set @Seq = @Seq + 1
end
fetch next from TextColumnCursor into @ColName
end
close TextColumnCursor
deallocate TextColumnCursor
select @Query1 = Line from Lines where Seq = 1
select @Query2 = Line from Lines where Seq = 2
select @Query3 = Line from Lines where Seq = 3
select @Query4 = Line from Lines where Seq = 4
select @Query5 = Line from Lines where Seq = 5
select @Query6 = Line from Lines where Seq = 6
select @Query7 = Line from Lines where Seq = 7
select @Query8 = Line from Lines where Seq = 8
select @Query9 = Line from Lines where Seq = 9
select @Query10 = Line from Lines where Seq = 10
exec('update Chaudières set ' + @Query1 + @Query2 + @Query3 + @Query4 + @Query5 + @Query6 + @Query7 + @Query8 + @Query9 + @Query10)
drop table Lines
go
-
I'll keep using my dts task... far less complicated
.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply