INSERT INTO, EXCLUDING ONE COLUMN

  • Hi

    Is there a way I can:

    INSERT INTO TableA

    SELECT * FROM TableB

    but exclude one column from TableB?

    My TableB has ID which is IDENTITY(1,1), I want to select everything to TableA except the ID

  • hoseam (2/7/2014)


    Hi

    Is there a way I can:

    INSERT INTO TableA

    SELECT * FROM TableB

    but exclude one column from TableB?

    My TableB has ID which is IDENTITY(1,1), I want to select everything to TableA except the ID

    easily, but you have to explicitly name the columns.

    INSERT INTO TableA(Column1,Column2,otherColumns)

    SELECT Column1,Column2,otherColumns

    FROM TableB

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Without listing the columns??

  • hoseam (2/7/2014)


    Without listing the columns??

    nope. it's actually a best practice to name the columns every single time, because as soon as you add a column to either table, any existing code that was not changed in tandem to the new column being added would fail.

    the only exception might be not identifying the destination columns, and that's only if the # of columns match exactly(ignoring identity and calculated columns)

    INSERT INTO TableA

    SELECT Column1,Column2,otherColumns

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I have this code:

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Hosea_tempTable]') AND type in (N'U'))

    DROP TABLE [dbo].[Hosea_tempTable]

    GO

    DECLARE @SQLStatement NVARCHAR(MAX),@TableName NVARCHAR(MAX), @Product_Id VARCHAR(50), @Fund_Id VARCHAR(50),

    @NewFund_Id VARCHAR(50),@NewProduct_Id VARCHAR(50), @InsertSQL NVARCHAR(MAX), @UpdateSQL NVARCHAR(MAX), @InsertBackSQL NVARCHAR(MAX)

    SET @Product_Id = 'AGP1'

    SET @Fund_Id = 'E016'

    SET @NewProduct_Id = 'PCCV'

    SET @NewFund_Id = 'E33333'

    SET @TableName = 'Hosea_tblDef_RETURNS'

    SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tempTable (';

    SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+ CASE

    WHEN st.name LIKE '%CHAR%'

    THEN st.name + '(' + CONVERT(VARCHAR(4),c.max_length) + ')'

    ELSE st.name

    END

    FROM sys.tables t

    JOIN sys.columns c

    ON t.OBJECT_ID = c.OBJECT_ID

    INNER JOIN sys.types st

    ON st.system_type_id = c.system_type_id

    WHERE t.name = @TableName

    AND c.is_identity= 0

    ORDER BY column_id;

    SET @SQLStatement = @SQLStatement + ');';

    SET @SQLStatement = REPLACE(@SQLStatement,'(,','('); -- remove first comma

    EXEC sp_executesql @SQLStatement

    SET @InsertSQL = 'INSERT INTO Hosea_tempTable SELECT * FROM ' + @TableName +' WHERE (Product_Id = '''+@Product_Id+''' or Product_Id = '''') AND (Fund_Id = '''+@Fund_Id+''' or Fund_Id is null)';

    EXEC sp_executeSQL @InsertSQL;

    look at how I create my temptable, then I want to populate it from the table that I will also be parsing as a parameter, I can't name columns in this instance

  • hoseam (2/7/2014)


    Without listing the columns??

    No - as Lowell said, you will need a column list on the SELECT side.

    Once you've been bitten a few times, you will realise it's almost always best to use a column list on both sides.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • is this a subset of a larger script? why the dynamic SQL?

    also, since you drop and recreate the table every time, why not a temp table?

    why create the table in advance? why not just use INSERT ... INTO and build the table on the fly completely?

    SET @InsertSQL = 'SELECT identity(int,1,1) As ID,* INTO Hosea_tempTable FROM '

    + @TableName

    + ' WHERE (Product_Id = '''

    + @Product_Id

    + ''' or Product_Id = '''') AND (Fund_Id = '''

    + @Fund_Id

    + ''' or Fund_Id is null)';

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • here's the idea behind Dynamic SQL:

    The user will be passing table name to make changes to it, as they past table name, I'm creating another table in the same structure as the as the one they passed in, leaving out the ID IDENTITY(1,1), I do that with Dynamic SQL.

    SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tempTable (';

    SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+ CASE

    WHEN st.name LIKE '%CHAR%'

    THEN st.name + '(' + CONVERT(VARCHAR(4),c.max_length) + ')'

    ELSE st.name

    END

    FROM sys.tables t

    JOIN sys.columns c

    ON t.OBJECT_ID = c.OBJECT_ID

    INNER JOIN sys.types st

    ON st.system_type_id = c.system_type_id

    WHERE t.name = @TableName

    AND c.is_identity= 0

    ORDER BY column_id;

    the next step is to load all of the data from the table they passed in as a parameter to the new table I created, but leaving out the ID IDENTITY(1,1). That's where I'm getting an error.

  • hoseam (2/7/2014)


    here's the idea behind Dynamic SQL:

    The user will be passing table name to make changes to it...

    Data changes, or table structure changes?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Data change.

    I have TableA, it has IDENTITY(1,1) on the PK. I need to clone some of the rows in this table, with only two columns changing. what I decided to was to take a row I need to clone into another table, change the two columns I need to change then put that row back into that table.

    i.e TableA has a row(row1) with 6 columns, and I need to clone this row, but changing only two columns. That mean I will have another row(row1) with the same data as row1 except tow columns.

    So I decided to another table(TableB) with the same structure but exclude ID IDENTITY(1,1), then the take row1 from TableA , put it into TableB, update the two columns of row1 that has to change then put it back to TableA, then it becomes row2, the clone of row1, with two columns changed though. but TableA has IDENTITY(1,1) on the PK, which led me run into errors, saying I'm violating IDENTITY(1,1).

    So I decided to create another table as TableA without IDENTITY(1,1), load the row I want from TableA, update then and so forth.

    Dynamic SQL was the option I thought will be suitable.

    The error I'm getting now is with the loading part.

    SET @InsertSQL = 'INSERT INTO Hosea_tempTable SELECT * FROM ' + @TableName +' WHERE (Product_Id = '''+@Product_Id+''' or Product_Id = '''') AND (Fund_Id = '''+@Fund_Id+''' or Fund_Id is null)';

    ERROR: "Insert Error: Column name or number of supplied values does not match table definition" because TableB doesn't have ID IDENTITY(1,1), which makes it to have 4 columns if TableA had 5 columns including ID IDENTITY(1,1).

  • Use the OUTPUT clause on an UPDATE statement. OUTPUT was introduced in SQL Server 2005.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • On this case, RETURNS_ID is a PK with IDENTITY(1,1). I could do this:

    SELECT * INTO #TempTable

    FROM Hosea_tblDef_RETURNS

    WHERE Product_Id = 'AGP1'

    AND (Fund_Id = 'E016' or Fund_Id is null)

    ALTER TABLE #TempTable

    DROP COLUMN RETURNS_ID

    SELECT * FROM #TempTable

    DROP TABLE #TempTable

    but the problem is that I will never know if the table has a PK with IDENTITY(1,1) or not since the user will be inputting the name of the table.I want a generic solution that will cater for any table. the solution I have now works fine for tables without PK. I tested with a table having PK IDENTITY(1,1) then I ran into this problem.

  • Another solution is to create a view of your table without the identity column

    CREATE TABLE TEST(

    CODE_test int IDENTITY(1,1) NOT NULL,

    shortname varchar(14) NULL,

    Name] varchar(38) NOT NULL,

    CONSTRAINT [PK_TEST] PRIMARY KEY (CODE_test)

    )

    go

    create view vtest as select shotname, name from TEST

    go

    You can do some insert into the view

    like

    -> insert into test values ('Test', 'what a test !')

    -> insert into test select .... from bigtable ....

  • Can you provide us with a brief description of the end to end process?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • CREATE TABLE TEST(

    Code_test int IDENTITY(1,1) NOT NULL,

    shortname varchar(14) NULL,

    Name varchar(38) NOT NULL,

    CONSTRAINT [PK_TEST] PRIMARY KEY (Code_test)

    )

    go

    --Create a view without the identity column

    create view vtest

    as

    select shortname, name from TEST

    go

    --You can do some insert into the view like

    insert into vtest values ('Test1', 'what a test !')

    insert into vtest values ('Test2', 'what a test !')

    insert into vtest values ('Test3', 'what a test !')

    --------------------------------------

    select * from test

    --it returns the 3 rows

    --------------------------------------

    -- you can else do insert directly with the table

    insert into test values ('Test 100', 'what a test !')

    insert into test values ('Test 101', 'what a test !')

    insert into test values ('Test 102', 'what a test !')

    select * from test

    --it returns the 6 rows

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply