January 4, 2020 at 4:13 am
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](50) NULL,
[loc] [VARCHAR](50) NULL
)
CREATE TABLE [dbo].[emp]
(
[eid] [INT] NULL,
[ename] [VARCHAR](50) NULL,
[doj] [DATE] NULL,
[sal] [MONEY] NULL,
[deptid] [INT] NULL
)
CREATE TABLE [dbo].[loc]
(
[locid] [INT] NULL,
[locname] [VARCHAR](50) NULL
)
CREATE TABLE [dbo].[sourceFields]
(
[tablename] [VARCHAR](50) NULL,
[tablecolumns] [VARCHAR](50) NULL
)
CREATE TABLE [dbo].[Tableinfo](
[TableName] [varchar](50) NULL,
[ColumnsList] [varchar](1000) NULL
)
GO
INSERT [dbo].[Tableinfo] ([TableName], [ColumnsList]) VALUES (N'dept', N'deptid,dname,loc')
GO
INSERT [dbo].[Tableinfo] ([TableName], [ColumnsList]) VALUES (N'emp', N'eid,ename,doj,sal,deptid')
GO
INSERT [dbo].[Tableinfo] ([TableName], [ColumnsList]) VALUES (N'loc', N'locid,locname')
GO
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.
tableinfo table have tablesnames and fields info.
Can you please tell me how to write dynamic query for inserting default values into a table?
January 4, 2020 at 9:32 am
You have to specify defaults when you create the table using the DEFAULT keyword. Then if you don't specify a value for that column when you insert a new record, that value is used. No dynamic SQL required - just a bit more planning. If changing the tables isn't an option, you'd have to do the same thing with a stored procedures to add values to each table - specify default values for input parameter values.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply