locked
INSERT commands with variables RRS feed

  • Question

  • --Main Database
    drop table #1
    create table #1 (ecode char(6), edescen char(50),edescfr char(50))
    insert into #1 values ('101','Electro Design','Electro Design')
    insert into #1 values ('102','Hydro Design Ltd','Hydro Design Ltd')
    insert into #1 values ('103','Electro Design Co.','Electro Design Co.')
    insert into #1 values ('104','Pacific Co.','Pacific Co.')
    insert into #1 values ('105','Kinetic Co.','Kinetic Co.')

    Now I need to create INSERT command for ecode-101, 103 and 104.
    Normally I create manually INSERT command, as showing below. I take edescen and edescfr columns info from #1 to complete the script. Then I run the following script/commands in another server.

    How I can pick the edescen and edescfr columns info from #1 table for 101, 103, 104 . How I Can define or use the parameter to get the following results.
    Any help on this please.

    INSERT INTO #1 (ecode, edescen, edescfr)
       SELECT '101', 'Electro Design', 'Electro Design' 
          WHERE NOT EXISTS(SELECT 1 FROM #1 WHERE ecode = '101')

    INSERT INTO #1 (ecode, edescen, edescfr)
       SELECT '103', 'Electro Design', 'Electro Design' 
          WHERE NOT EXISTS(SELECT 1 FROM #1 WHERE ecode = '103')

    INSERT INTO #1 (ecode, edescen, edescfr)
       SELECT '104', 'Pacific Co.', 'Pacific Co.' 
          WHERE NOT EXISTS(SELECT 1 FROM #1 WHERE ecode = '104')

    Thanks.

    Thursday, June 16, 2011 6:53 PM

Answers

  • I am not sure I am following you.

    > Normally I create manually INSERT command, as showing below. I take edescen and edescfr columns
    > info from #1 to complete the script. Then I run the following script/commands in another server.

    Do you want to create those insert statements on the fly?

    create table #1 (ecode char(6), edescen char(50),edescfr char(50))
    insert into #1 values ('101','Electro Design','Electro Design')
    insert into #1 values ('102','Hydro Design Ltd','Hydro Design Ltd')
    insert into #1 values ('103','Electro Design Co.','Electro Design Co.')
    insert into #1 values ('104','Pacific Co.','Pacific Co.')
    insert into #1 values ('105','Kinetic Co.','Kinetic Co.')
    
    SELECT
    	'INSERT INTO #1 (ecode, edescen, edescfr) SELECT ' + 
    	QUOTENAME(RTRIM(ecode), '''') + ',' + QUOTENAME(RTRIM(edescen), '''') + ',' + QUOTENAME(RTRIM(edescfr), '''') + 
    	' WHERE NOT EXISTS(SELECT 1 FROM #1 WHERE ecode = ' + QUOTENAME(RTRIM(ecode), '''') + ');'
    FROM
    	#1
    WHERE
    	ecode IN ('101', '103', '104')
    GO
    DROP TABLE #1;
    GO
    

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Khan_K Friday, June 17, 2011 2:06 AM
    Thursday, June 16, 2011 7:23 PM

All replies

  • If you can pass them as COMMA separated values, check this out:

    create table myTable (ecode char(6), edescen char(50),edescfr char(50))
    insert into myTable values ('101','Electro Design','Electro Design')
    insert into myTable values ('102','Hydro Design Ltd','Hydro Design Ltd')
    insert into myTable values ('103','Electro Design Co.','Electro Design Co.')
    insert into myTable values ('104','Pacific Co.','Pacific Co.')
    insert into myTable values ('105','Kinetic Co.','Kinetic Co.')
    go
    
    Alter Function SplitString (@List Varchar(Max), @Delimiter Char(1))
    Returns @Items Table (Item Varchar(100))
    As
    Begin
            Declare @Item Varchar(12), @Pos TinyInt
            While Len(@List) > 0
            Begin
            Set @Pos = CharIndex(@Delimiter, @List)
            If @Pos = 0 Set @Pos = Len(@List) + 1
            Set @Item = Left(@List, @Pos - 1)
            
            Insert @Items
            Select Ltrim(Rtrim(@Item))
            
            Set @List = SubString(@List, @Pos + Len(@Delimiter), Len(@List))
            End
            Return
    End
    
    go
    Alter Procedure ReadData(@ecodes Varchar(1000))
    As
    Select
    	* 
    From 
    	myTable 
    	Inner Join (Select Item From SplitString(@ecodes, ',')) As myItems 
    		On myTable.ecode = myItems.Item 
    	
    Go
    
    Execute ReadData '101, 103, 104'
    
    Go
    
    Drop table myTable 
    

     


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Thursday, June 16, 2011 7:04 PM
  • I am not sure I am following you.

    > Normally I create manually INSERT command, as showing below. I take edescen and edescfr columns
    > info from #1 to complete the script. Then I run the following script/commands in another server.

    Do you want to create those insert statements on the fly?

    create table #1 (ecode char(6), edescen char(50),edescfr char(50))
    insert into #1 values ('101','Electro Design','Electro Design')
    insert into #1 values ('102','Hydro Design Ltd','Hydro Design Ltd')
    insert into #1 values ('103','Electro Design Co.','Electro Design Co.')
    insert into #1 values ('104','Pacific Co.','Pacific Co.')
    insert into #1 values ('105','Kinetic Co.','Kinetic Co.')
    
    SELECT
    	'INSERT INTO #1 (ecode, edescen, edescfr) SELECT ' + 
    	QUOTENAME(RTRIM(ecode), '''') + ',' + QUOTENAME(RTRIM(edescen), '''') + ',' + QUOTENAME(RTRIM(edescfr), '''') + 
    	' WHERE NOT EXISTS(SELECT 1 FROM #1 WHERE ecode = ' + QUOTENAME(RTRIM(ecode), '''') + ');'
    FROM
    	#1
    WHERE
    	ecode IN ('101', '103', '104')
    GO
    DROP TABLE #1;
    GO
    

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Khan_K Friday, June 17, 2011 2:06 AM
    Thursday, June 16, 2011 7:23 PM
  • I ran the script and geto the error But how I can get the following desired output/results format which is INSERT command.

    --Output
    INSERT INTO #1 (ecode, edescen, edescfr)
       SELECT '101', 'Electro Design', 'Electro Design'
          WHERE NOT EXISTS(SELECT 1 FROM #1 WHERE ecode = '101')

    INSERT INTO #1 (ecode, edescen, edescfr)
       SELECT '103', 'Electro Design', 'Electro Design'
          WHERE NOT EXISTS(SELECT 1 FROM #1 WHERE ecode = '103')

    INSERT INTO #1 (ecode, edescen, edescfr)
       SELECT '104', 'Pacific Co.', 'Pacific Co.'
          WHERE NOT EXISTS(SELECT 1 FROM #1 WHERE ecode = '104')


    --Error
    Invalid object name 'SplitString'.
    Invalid object name 'ReadData'.
    Could not find stored procedure 'ReadData'.

    Thursday, June 16, 2011 7:36 PM
  • Sultana_K,

    Instead of "ALTER FUNCTION" and "ALTER PROCEDURE', use "CREATE FUNCTION" and "CREATE PROCEDURE'.

    Not sure, If I got what you asked, If you want to dynamic INSERT statement then I do not think this the best approach to do.


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Thursday, June 16, 2011 7:41 PM
  • Did you check my suggestion?

     


    AMB

    Some guidelines for posting questions...

    Thursday, June 16, 2011 9:56 PM
  • People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use iso-11179 rules for the data element names, avoid needless dialect and use iso-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

    You really used #1 as a table name!  If I did that in one of my books, please would think I was being absurd. Can I guess that this is a list of companies with their names in English and French? It would nice to be able to read the data, so let's try this:


    CREATE TABLE Companies
    (company_code CHAR(6) NOT NULL PRIMARY KEY,
     company_desc_en CHAR(50) NOT NULL,
     company_desc_fr CHAR(50) NOT NULL);

    You might want to learn to use the row constructor syntax for the VALUES clause:

    INSERT INTO Alpha
    VALUES  ('101','Electro Design','Electro Design'),
            ('102','Hydro Design Ltd','Hydro Design Ltd'),
            ('103','Electro Design Co.','Electro Design Co.'),
            ('104','Pacific Co.','Pacific Co.'),
            ('105','Kinetic Co.','Kinetic Co.');

    >> Now I need to create INSERT command for company_code = {'101', '103', '104'}
    Normally I create manually INSERT command, as showing below. I take company_desc_en and company_desc_fr columns info from Alpha to complete the script. Then I run the following script/commands in another server.

    How I can pick the company_desc_en and company_desc_fr columns info from Alpha table for 101, 103, 104 .

    SELECT company_desc_en, company_desc_fr
      FROM Alpha
     WHERE company_code IN ('101', '103', '104');

    The rest of the code in this posting made no sense at all.

    --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
    Thursday, June 16, 2011 10:10 PM
  • It's perfect. Thanks a lot.
    Friday, June 17, 2011 2:09 AM