CREATE TABLE [dbo].[TEMP_DATA](
[CITY_NAME] nvarchar NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CITY_DATA](
[CITY_NAME] nvarchar NULL
) ON [PRIMARY]
GO
INSERT INTO TEMP_DATA
SELECT 'London'
INSERT INTO TEMP_DATA
SELECT 'London45555'
As this is just a temp table that is based on some excel coming in and loading to this, before it writes to the main Table i.e CITY_DATA which only allows 12.
I want to read the meta data
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TEMP_DATA'
I can see this is 12 for the character_maximum_length
Is there a way to read each record and also go get the 12 and then do is len(CITY_DATA) =
...schema value?
because you did not use the 3 tick marks (before and after your DDL) --> ` <-- we cannot tell the length of the CITY_NAME column
CREATE TABLE [dbo].[TEMP_DATA](
[CITY_NAME] nvarchar(12) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CITY_DATA](
[CITY_NAME] nvarchar(12) NULL
) ON [PRIMARY]
GO
you have
CREATE TABLE [dbo].[TEMP_DATA](
[CITY_NAME] nvarchar NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CITY_DATA](
[CITY_NAME] nvarchar NULL
) ON [PRIMARY]
GO
The value is in temp table is say 20.
This goes into CITY but it users put in 20 this will fail.
So i am trying to dynamically do this so it shows an error that if over x.
The table TEMP will have loads of columns.
I will check numeric, null etc by reading metadata as a pre validation script
If 20 it will always be 20. Are you trying to see if the data inside it has a length of x? Or the data type has length of x?
Select * from [TEMP_DATA] where Len([CITY_NAME] ) > x
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CITY_DATA'
This will be 12.
Read TEMP_DATA then
If the len(city_name) > then what returned from above
Print a message...
DECLARE @CITY_NAME_LEN smallint
SELECT @CITY_NAME_LEN = c.max_length
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.CITY_DATA') AND c.name = 'CITY_NAME'
INSERT INTO dbo.CITY_DATA ( CITY_NAME, ... )
SELECT LEFT(CITY_NAME, @CITY_NAME_LEN), ...
FROM dbo.TEMP_DATA
Sys.columns ..ok that will be good. Thanks.
Yes. Don't use the INFORMATION_SCHEMA views in SQL Server. They are slower and do not have many of the columns added to the standard MS sys. views.