SQLTeam.com | Weblogs | Forums

How to load default values dynamically in sql server

I have one question about SQL Server: how to dynamically load default values for all tables if 9999 values does not exist in the tables?

The database has three tables. I want load default 9999 values for unique column

if datatype is varchar then pass NA value
if datatype is int then pass 0 value
if datatype is date then pass 1900-01-01 value
Here sourceFiedls table is maintain all table related fields information.

CREATE TABLE [dbo].[dept]
(
[deptid] [INT] NULL,
[dname] VARCHAR NULL,
[loc] VARCHAR NULL
)

CREATE TABLE [dbo].[emp]
(
[eid] [INT] NULL,
[ename] VARCHAR NULL,
[doj] [DATE] NULL,
[sal] [MONEY] NULL,
[deptid] [INT] NULL
)

CREATE TABLE [dbo].[loc]
(
[locid] [INT] NULL,
[locname] VARCHAR NULL
)

CREATE TABLE [dbo].[sourceFields]
(
[tablename] VARCHAR NULL,
[tablecolumns] VARCHAR NULL
)

INSERT INTO [dbo].[dept] ([deptid], [dname], [loc])
VALUES (1, N'abc', N'hy')
GO

INSERT INTO [dbo].[emp] ([eid], [ename], [doj], [sal], [deptid])
VALUES (1, N'ab', CAST(N'1988-10-04' AS Date), 100.0000, 10)
GO

INSERT INTO [dbo].[loc] ([locid], [locname]) VALUES (1, N'hyd')
GO

INSERT INTO [dbo].[sourceFields] ([tablename], [tablecolumns])
VALUES (N'dept', N'deptid'), (N'dept', N'dname'), (N'dept', N'loc'),
(N'emp', N'eid'), (N'emp', N'ename'), (N'emp', N'doj'),
(N'emp', N'sal'), (N'emp', N'deptid'), (N'loc', N'locid'),
(N'loc', N'locname')
GO
Based on above table I want check the data if 9999 values exist or not.

For dept table :

IF NOT EXISTS (SELECT * FROM dept WHERE deptid=9999 )
BEGIN
INSERT INTO [dbo].[dept] ([deptid], [dname], [loc])
VALUES (9999, 'NA', 'NA')
END
For Emp table :

IF NOT EXISTS (SELECT * FROM emp WHERE eid=9999 )
BEGIN
INSERT INTO [dbo].[emp] ([eid], [ename], [doj], [sal], [deptid])
VALUES (9999, 'NA', CAST('1900-01-01' AS Date), 0, 0)
END
For Loc table :

IF NOT EXISTS (SELECT * FROM loc WHERE locid=9999 )
BEGIN
INSERT INTO [dbo].[loc] ([locid], [locname])
VALUES (9999, 'NA')
END
Here I have written 3 statements for 3 tables checking default values exist or not. Instead of these 3 statements, how to write a dynamic query like using cursor concept and table loop iterate values - if exist or not if not exist then insert.
this table maintain tablenames and filed names to generate dynamic insert statments and values.

below table maintain tablename and columnsinfo.to create dynamicy script for inserct script
CREATE TABLE [dbo].[Tableinfo](
[TableName] varchar NULL,
[ColumnsList] varchar NULL
)

INSERT [dbo].[Tableinfo] ([TableName], [ColumnsList]) VALUES (N'dept',N'deptid,dname,loc')
INSERT [dbo].[Tableinfo] ([TableName], [ColumnsList]) VALUES (N'emp', N'eid,ename,doj,sal,deptid')
INSERT [dbo].[Tableinfo] ([TableName], [ColumnsList]) VALUES (N'loc', N'locid,locname')

Can you please tell me how to write dynamic query for inserting default values into a table?

sourceFields table have table name and fields information.based on that we need to insert default values for all table.suppose few more tables are added in the same database .then we cannot modifiy existing logic. default value should be load remain new tables .
inside cursor get table names and fieldsinformaton into one object then insert statement should be change dynamically based on table ,use cursor will be work but I am unable to write logic in cursor.

hi

i am working on it !!!
its a little work ... ( involves DYNAMIC sql )

Once I am done i will post
:slight_smile: :slight_smile:

If you want DEFAULT values in your tables, CREATE them (or ALTER) with DEFAULT values:

CREATE TABLE dbo.dept(
	deptid	INT	NOT NULL PRIMARY KEY,
	dname	VARCHAR(50) NULL DEFAULT ('NA'),
	loc	VARCHAR(50) NULL DEFAULT ('NA')
)

CREATE TABLE dbo.emp(
	eid	INT	NOT NULL PRIMARY KEY,
	ename	VARCHAR(50)	NULL DEFAULT ('NA'),
	doj	DATE	NULL DEFAULT '190101',
	sal	MONEY	NULL,
	deptid	INT	NOT NULL	DEFAULT 0
)

INSERT INTO dbo.dept (deptid) VALUES (0);
INSERT INTO dbo.dept (deptid, dname) VALUES (1, 'Sales');
SELECT * from dbo.dept

INSERT INTO dbo.emp(eid) VALUES(0)
INSERT INTO dbo.emp(eid, ename) VALUES(1, 'John Doe')
SELECT * from dbo.emp

DROP TABLE IF EXISTS dbo.dept
DROP TABLE IF EXISTS dbo.emp

On the other hand, PRIMARY KEYs that are NULLable, using VARCHAR and not VARCHAR(<a_length>), ... makes it hard to know what you're really after.

1 Like

hi Wim

Don't
think thats what he is looking for

Anyhow i could be wrong
I understood something .. i am working on it ... Could be my understanding is wrong!!!

:slight_smile: :slight_smile:

What I now think he wants, is a script that generates dynamic SQL scripts based on the meta data in the systemtables that describe the tables and the data types of their columns.
And I bet that's what you are doing now. :slight_smile:

Here is an alternative:

SQL for sample tables and data
DROP TABLE IF EXISTS dbo.dept
DROP TABLE IF EXISTS dbo.emp
DROP TABLE IF EXISTS dbo.sourceFields
GO
CREATE TABLE dbo.dept(
	deptid	INT	NOT NULL PRIMARY KEY,
	dname	VARCHAR(50) NULL,
	loc	VARCHAR(50) NULL
)

INSERT INTO dbo.dept (deptid) VALUES (0);
INSERT INTO dbo.dept (deptid, dname) VALUES (1, 'Sales');
SELECT * from dbo.dept

CREATE TABLE dbo.emp(
	eid	INT	NOT NULL PRIMARY KEY,
	ename	VARCHAR(50)	NULL,
	doj	DATE	NULL,
	sal	MONEY	NULL,
	deptid	INT	NULL
)

INSERT INTO dbo.emp(eid) VALUES(0)
INSERT INTO dbo.emp(eid, ename) VALUES(1, 'John Doe')
SELECT * from dbo.emp

CREATE TABLE dbo.sourceFields(
	tableschema	VARCHAR(255) NOT NULL,
	tablename VARCHAR(255)	NOT NULL,
	tablecolumn VARCHAR(255) NOT NULL
)

INSERT INTO dbo.sourceFields(tableschema, tablename, tablecolumn) VALUES
('dbo', 'dept', 'dname'),
('dbo', 'dept', 'loc'),
('dbo', 'emp', 'ename'),
('dbo', 'emp', 'doj'),
--('dbo', 'emp', 'sal'),
('dbo', 'emp', 'deptid')
SELECT * from dbo.sourceFields
GO

Generator of UPDATE SQL scripts:

;WITH CTE_tables AS(
SELECT DISTINCT SF.tableSchema, SF.tablename, SF.tableSchema + '.' + SF.tableName as FTableName, OBJECT_ID(SF.tableSchema + '.' + SF.tableName) AS TableObjectId
FROM DBO.sourceFields as SF
)
,CTE_columns AS(
SELECT T.tableSchema, T.tablename, T.TableObjectId, C.name as ColumnName, TP.name as ColumnType, ROW_NUMBER() OVER (PARTITION BY T.tablename ORDER BY C.column_id) as ColumnNr
FROM CTE_tables as T
	INNER JOIN dbo.sourceFields as SF
		ON T.tableschema = SF.tableschema
		AND T.tablename = SF.tableName
	INNER JOIN sys.columns AS C
		ON T.TableObjectId = C.object_id
		AND SF.tablecolumn = C.name
	INNER JOIN sys.types AS TP 
		ON c.user_type_id = TP.user_type_id  
)
, pre AS(
SELECT T.TableObjectId
	,1 as SequenceNr
	,0 as ColumnrNr
	,'UPDATE ' + T.FTableName as SQL_
FROM CTE_tables as T
	UNION ALL
SELECT C.TableObjectId
	,2 as SequenceNr
	,C.ColumnNr
	,CASE C.ColumnNr WHEN 1 THEN 'SET '
		ELSE '	,'
	END +
	+ C.ColumnName + ' = COALESCE(' + C.ColumnName + ', ' +
	CASE C.ColumnType 
		WHEN 'varchar' THEN '''NA'''
		WHEN 'int' THEN '0'
		WHEN 'date' THEN '''19000101'''
		ELSE ' ** ERROR Uncovered data type "' + C.ColumnType + '"! **'
	END +
	')'
	as SQL_
FROM CTE_columns as C
	UNION ALL
SELECT C.TableObjectId
	,3 as SequenceNr
	,C.ColumnNr
	,CASE C.ColumnNr WHEN 1 THEN 'WHERE '
		ELSE '	OR '
	END +
	+ C.ColumnName + ' IS NULL'	as SQL_
FROM CTE_columns as C
	UNION ALL
SELECT  T.TableObjectId
	, 4 as SequenceNr
	, 0 as ColumnrNr
	, 'GO
'as SQL_
FROM CTE_tables as T
)
SELECT SQL_
FROM pre
ORDER BY TableObjectId, SequenceNr, ColumnrNr

Generated UPDATE scripts:

UPDATE dbo.dept
SET dname = COALESCE(dname, 'NA')
	,loc = COALESCE(loc, 'NA')
WHERE dname IS NULL
	OR loc IS NULL
GO

UPDATE dbo.emp
SET ename = COALESCE(ename, 'NA')
	,doj = COALESCE(doj, '19000101')
	,deptid = COALESCE(deptid, 0)
WHERE ename IS NULL
	OR doj IS NULL
	OR deptid IS NULL
GO
1 Like

Nice one Wim

No reply or any communication from poster

Hi Wim, here I need insert statment to insert default values and need to check default values exist or not before insert .could you please help on it

hi srinivas

I tried to do this !!
Please see if its what you want ????
I tried using cursor !!!

please click arrow to the left for DROP Create DATA ...
drop table [dept]
go 

drop table [emp]
go 

drop table [loc]
go 

drop table [sourceFields]
go 

CREATE TABLE [dbo].[dept]
(
[deptid] int  NULL,
[dname] VARCHAR(100) NULL,
[loc] VARCHAR(100) NULL
)
go 

CREATE TABLE [dbo].[emp]
(
[eid] VARCHAR(100)  NULL,
[ename] VARCHAR(100) NULL,
[doj] [DATE] NULL,
[sal] [MONEY] NULL,
[deptid] [INT] NULL
)
go 

CREATE TABLE [dbo].[loc]
(
[locid] VARCHAR(100)  NULL,
[locname] VARCHAR(100) NULL
)
go 

CREATE TABLE [dbo].[sourceFields]
(
[tablename] VARCHAR(100) NULL,
[tablecolumns] VARCHAR(100) NULL
)
go 

INSERT INTO [dbo].[dept] ([deptid], [dname], [loc])
VALUES (N'1', N'abc', N'hy')
GO

INSERT INTO [dbo].[emp] ([eid], [ename], [doj], [sal], [deptid])
VALUES (N'1', N'ab', CAST(N'1988-10-04' AS Date), 100.0000, 10)
GO

INSERT INTO [dbo].[loc] ([locid], [locname]) VALUES (N'1', N'hyd')
GO

INSERT INTO [dbo].[sourceFields] ([tablename], [tablecolumns])
VALUES (N'dept', N'deptid'), (N'dept', N'dname'), (N'dept', N'loc'),
(N'emp', N'eid'), (N'emp', N'ename'), (N'emp', N'doj'),
(N'emp', N'sal'), (N'emp', N'deptid'), (N'loc', N'locid'),
(N'loc', N'locname')
GO

please click arrow to the left for CURSOR
DECLARE 
          @tablename varchar(100) 
		 ,@tablecolumn varchar(100) 
		 ,@sql nvarchar(max) 
		 ,@sql1 nvarchar(max) 
		 ,@columns int 

DECLARE cursor_sourcefields CURSOR FOR SELECT tablename,tablecolumns  FROM sourceFields

OPEN cursor_sourcefields

FETCH NEXT FROM cursor_sourcefields INTO @tablename,     @tablecolumn;

WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @tablename, @tablecolumn

		SET @sql = ''
		SET @sql1 = '' 
		SET @columns = ''

		SELECT @columns = count(column_name)
        FROM INFORMATION_SCHEMA.COLUMNS where table_name = @tablename
            
		DECLARE @i int = 0
		DECLARE @ch varchar(100)
		SET @ch = 'NULL,'
			
		SET @sql1 =  @ch 
		WHILE @i < @columns-1
			BEGIN
				SET @i = @i + 1
				SET @SQL1 = @SQL1 +  @ch 					
			END
        SET @SQL1 = LEFT(@SQL1,len(@SQL1)-1)
        SET @SQL1 = @SQL1   

		SET @sql = 'IF NOT EXISTS (SELECT * FROM '+@tablename+' WHERE '+@tablecolumn+'=''9999'' )
		  BEGIN INSERT INTO [dbo].['+@tablename+'] SELECT '+ @sql1+'
           UPDATE [dbo].['+@tablename+'] set '+@tablecolumn+' =''9999''  WHERE '+@tablecolumn +' IS NULL END'

        PRINT (@SQL)
        EXEC (@SQL)

    
        FETCH NEXT FROM cursor_sourcefields INTO @tablename,     @tablecolumn;
    END;
 
CLOSE cursor_sourcefields; 
DEALLOCATE cursor_sourcefields;

image

OK, now I see. You want to scan a number of tables in your database and check each of them if they have a record with ID = 9999 (9999 is a bad idea, use 0 or a negative number for this). For all tables that don't have such a record, insert a record with default values for all the columns.

It seems Harish has already solved it for you.

hi Hrisha, we are getting extra values in each tables.every table we need only one 9999
values.suppose we take emp: here only 9999 NA 9999-01-01 00 0
no need this values
9999 9999 9999-01-01 9999.00 9999
NULL 9999 9999-01-01 9999.00 9999
NULL NULL 9999-01-01 9999.00 9999
NULL NULL NULL 9999.00 9999
NULL NULL NULL NULL 9999
similar to dept :need :9999 NA
no need extrac value in dept : NULL 9999
similar to loc : need:9999 NA NA
no need this values NULL 9999 9999
NULL NULL 9999
could you please help on it.

I will surely help

If I am not able to
Please excuse me .. it may take 1 or 2 days
I have urgent health related stuff

Wim or other experts watching
Can help you

It's not complicated
Simple to fix

:+1:

hi srinivas

I am trying to understand what you want !!

its not clear at all

Please explain nicely and neatly
... how to explain to others in nice neat ways so that they
can understand easily and help ... is ANOTHER subject

I am having head ache trying to understand !!!
Sorry if i am not being nice !
Please excuse me ...


hi Hrisha,

we are getting extra values in each tables.

every table we need only one 9999
values.

suppose we take emp: here only
9999 NA 9999-01-01 00 0

from
seeing source field table .... columns
How this is done ??? :+1: