locked
insert default values dynamically in sql server RRS feed

  • Question

  • Hi I have one question in sql server .
    how to load  dynamically for all tables :default values if 9999 values is not exists in the tables .
    one database have three tables.I want load default 9999 values for unique field 
    if datatypeis varchar then pass NA values.
    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

    go
    INSERT [dbo].[dept] ([deptid], [dname], [loc]) VALUES (1, N'abc', N'hy')
    GO
    INSERT [dbo].[emp] ([eid], [ename], [doj], [sal], [deptid]) VALUES (1, N'ab', CAST(N'1988-10-04' AS Date), 100.0000, 10)
    GO
    INSERT [dbo].[loc] ([locid], [locname]) VALUES (1, N'hyd')
    GO
    INSERT [dbo].[sourceFields] ([tablename], [tablecolumns]) VALUES (N'dept', N'deptid')
    GO
    INSERT [dbo].[sourceFields] ([tablename], [tablecolumns]) VALUES (N'dept', N'dname')
    GO
    INSERT [dbo].[sourceFields] ([tablename], [tablecolumns]) VALUES (N'dept', N'loc')
    GO
    INSERT [dbo].[sourceFields] ([tablename], [tablecolumns]) VALUES (N'emp', N'eid')
    GO
    INSERT [dbo].[sourceFields] ([tablename], [tablecolumns]) VALUES (N'emp', N'ename')
    GO
    INSERT [dbo].[sourceFields] ([tablename], [tablecolumns]) VALUES (N'emp', N'doj')
    GO
    INSERT [dbo].[sourceFields] ([tablename], [tablecolumns]) VALUES (N'emp', N'sal')
    GO
    INSERT [dbo].[sourceFields] ([tablename], [tablecolumns]) VALUES (N'emp', N'deptid')
    GO
    INSERT [dbo].[sourceFields] ([tablename], [tablecolumns]) VALUES (N'loc', N'locid')
    GO
    INSERT [dbo].[sourceFields] ([tablename], [tablecolumns]) VALUES (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 [dbo].[dept] ([deptid], [dname], [loc]) VALUES (9999, 'NA', 'NA')
       END
    for Emp table : 
    IF NOT EXISTS (SELECT * FROM emp WHERE  eid=9999 )
       BEGIN
          INSERT [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 [dbo].[loc] ([locid], [locname]) VALUES (9999, 'NA')
       END

    Here I have written 3 statment for 3 tables checking default values exist or not.
    instead of this 3 statments how to write dynamic query like using cursor concept and table loop iterate values
    is exist or not if not exist then insert .

    tablename : sourceFieldstable 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 modify existing logic. default value should be load remain new tables .
    inside cursor get table names and fields information into one object then insert statement should be change dynamically based on table structure ,use cursor will be work but I am unable to write logic in cursor.

    here tablinfo table have tablesnames and filedinformation  to refer dynamic insert statment

    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


    can you please tell me how to write dynamic query for inserting  default values  in a table.


    Friday, January 3, 2020 6:56 PM

All replies

  • How many tables do you have in total? It's a lot of work to get that going, so if you only have some fifty-odd tables, you are better off crafting the INSERT statements manually. Maybe not if you have 5000 tables, but getting a script like this working flawlessly would take a few days also for an experienced SQL programmer. Who says that the primary key is always a single int column? How do deal with composite keys? How to deal with keys that are strings?

    If you want to pursue this, you will need to make yourself familliar with the catalog views, sys.objects, sys.columns and sys.types.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Naomi N Sunday, January 5, 2020 9:43 PM
    Saturday, January 4, 2020 10:05 AM
  • >> how to load dynamically for all tables ..<<

    So much so wrong! Your attempt at DDL does not involve tables at all. Because the table must have a key and a key cannot be NULL. Identifiers are a new nominal scale, so they can never be numeric. Tables model sets of things, so their names cannot be singular (they have to be collective or plural). Unlike Visual Basic or other earlier programming languages, SQL can take longer meaningful names. The format for a data element name according to ISO 11179 is "<attribute>_<attribute property>". Notice that since an identifier is on a nominal scale it cannot be numeric value. I'm ing to assume that you did careful research on the length of the names of departments and locations and did not just pull a number out of the air.

    Let's start fixing this table by table:

    CREATE TABLE Departments -- note the plural and the lack of NOT NULLs
    (department_id CHAR(5) DEFAULT,'99999' NOT NULL 
     dept_name VARCHAR(20) NOT NULL,
     loc_id CHAR(16) NOT NULL -- this REFERENCES makes the DDL relational
     REFERENCES Locations(loc_id)
    ON UPDATE CASCADE,
    PRIMARY KEY (department_id, loc_id ));

    Please notice the assumption I had to make that departments are differentiated by location. This means the accounting department in Chica is a totally different one from the one in New York City. Your schema and your lack of specs gave us know it, so I had to make this assumption.

    CREATE TABLE Personnel -- note the collective noun
    (emp_id CHAR(10) DEFAULT '9999999999' NOT NULL PRIMARY KEY, -- a key is not an option
     emp_name VARCHAR(20) NOT NULL,
     hire_date DATE NOT NULL --DOJ is "department of justice."
    ); 

    CREATE TABLE Job_Assignments 
    (emp_id CHAR(10) NOT NULL
     REFERENCES Personnel (emp_id),
     department_id CHAR(5) NOT NULL
     REFERENCES Departments( department_id),
     salary_amt DECIMAL(12,2)
      CHECK (salary_amt >= 0.00) 
    PRIMARY KEY (emp_id, department_id));

    You noticed that they had to assume the salary es with a particular position that you're holding in some department? You modeled it is an innate characteristic of an employee.
     
    If you'll do a little research will find out that we don't use the MONEY data types from the original Sybase SQL Server because they don't work! There multiplication division truncates too early and gives bad results which are illegal in both the GAAP and the EU banking standards. But a better question is why is a department ID an attribute of an employee? Is he born with it?? That seems absurd. I would assume you need a table to normalize your schema that has the job assignments.

    CREATE TABLE Locations
    (loc_id CHAR(16) NOT NULL PRIMARY KEY, 
     loc_name VARCHAR(20) NOT NULL) ;

    I'm trying to guess that ''Loc" is your locations (if that's what "LOC means"; why do you like vague names?), And that you're not in that you picked a standard encoding scheme. I happen to like HTM, but use whatever is standard in your industry. Surely, nobody would be crazy enough to create their own numbering system.

    CREATE TABLE Source_Fields 
    (table_name VARCHAR(20) NOT NULL,
     table_columns VARCHAR(20) NOT NULL,
     PRIMARY KEY (table_name, table_columns)) ;

    Besides not being a table because it has no key, this is so incredibly bad! You are mixing data and metadata in violation of every principle of RDBMS and basic data modeling. This is total garbage! 

    Your insertion statements use the old Sybase insert inset of the ANSI/ISO standard insert into syntax and you do it row by row. The reason that Sybase originally did this way was because it was built around the UNIX tape files and had to write one punch card at a time. 

    >> Here I have written 3 statment for 3 tables checking default values exist or not.
    instead of this 3 statments how to write dynamic query like using cursor concept and table loop iterate values is exist or not if not exist then insert . <<

    You have a completely erroneous mindset. Defaults in SQL are declared as a table property; the default value will be inserted when either no value is provided or it can be explicitly inserted as a parameter and insertion statement. Did you read anything in any book on SQL on how the insert into statement works?

    Saying "cursor" to a programmer who works with a declarative language like SQL is much like saying "fried dead babies." To a vegan priest :-) no competent programmer would do it. Again, you don't know what a field is in SQL (it refers to a part of a scalar value in a column which can have generic meaning a particular context, such as the year, month and day fields within a date column).

    In the words of Edgar Dijkstra, "you are doing everything completely wrong." 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, January 5, 2020 2:17 AM
  • Hello CELKO,

    You are giving good advises, however, the way you present is little harsh. With your experience, if you don't hurt the other guy with your answer, people will start admire and follow you.  Those who post here may be relatively new to SQL Server world or specific to that area. Its just a suggestion, but why can not we make them feel better with this forum platform?

    (Just a thought shared)


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    Sunday, January 5, 2020 4:09 AM
  • Hello CELKO,

    You are giving good advises, however, the way you present is little harsh. With your experience, if you don't hurt the other guy with your answer, people will start admire and follow you.  Those who post here may be relatively new to SQL Server world or specific to that area. Its just a suggestion, but why can not we make them feel better with this forum platform?

    (Just a thought shared)


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    We are trying to explain CELKO how to behave in the forums for many years. The owner of the forums contacted him several times and many others tried as well. There are jokes about his abusive and even a blog written once he made the same mistake he probably say to others that it is stupid and show zero understanding (using the word "field" instead of "column"). Sometimes I get a feeling that maybe it helped, but it hold for 1 day/message and then he is back to his abusive of newbies.

    As much as I know he is the number one abuser in the forum. Even the auto spammer tool blocked him few weeks back - seems like the AI learned well. As much I can see/know, he definitely have the highest amount of messages which are marked as abusive - and I am talking about tens times more than anyone else.

    I hope that you will succeed better, but don't count on it and don't feel bad if you fail in this :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, January 5, 2020 11:09 AM
  • Hi balakrishna324,

    Thank you for your posting.

    Is it possible for you to specify the default value when creating table?

    --like this
    create table #temp(
    dname varchar(50) default 'N/A',
    deptid int default 0,
    doj date default '1900-01-01')

    If not, you may manually add your special value in the stored procedure.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, January 6, 2020 8:02 AM
  • Hi balakrishna324,

    I am writing to follow up this thread with you. Have you solved your issue? Please feel free to let us know if you have any other question.

    It's so kind of you to mark helpful replies as answers in order to close this thread. By doing so, it will benefit all community members who are having this similar issue.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, January 7, 2020 8:13 AM