Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

INSERT INTO, EXCLUDING ONE COLUMN Expand / Collapse
Author
Message
Posted Friday, February 7, 2014 6:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:20 AM
Points: 171, Visits: 273
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
Post #1539132
Posted Friday, February 7, 2014 6:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 12,906, Visits: 31,984
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1539135
Posted Friday, February 7, 2014 6:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:20 AM
Points: 171, Visits: 273
Without listing the columns??
Post #1539137
Posted Friday, February 7, 2014 6:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 12,906, Visits: 31,984
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1539142
Posted Friday, February 7, 2014 6:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:20 AM
Points: 171, Visits: 273
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
Post #1539147
Posted Friday, February 7, 2014 6:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 7,191, Visits: 13,645
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1539149
Posted Friday, February 7, 2014 7:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 12,906, Visits: 31,984
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1539150
Posted Friday, February 7, 2014 7:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:20 AM
Points: 171, Visits: 273
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.
Post #1539158
Posted Friday, February 7, 2014 7:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 7,191, Visits: 13,645
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1539166
Posted Monday, February 10, 2014 12:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 4:20 AM
Points: 171, Visits: 273
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).
Post #1539642
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse