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?