SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INSERT INTO, EXCLUDING ONE COLUMN


INSERT INTO, EXCLUDING ONE COLUMN

Author
Message
hoseam
hoseam
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1222 Visits: 431
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
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73179 Visits: 40960
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!
hoseam
hoseam
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1222 Visits: 431
Without listing the columns??
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73179 Visits: 40960
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!
hoseam
hoseam
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1222 Visits: 431
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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42089 Visits: 20008
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
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73179 Visits: 40960
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!
hoseam
hoseam
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1222 Visits: 431
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.
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42089 Visits: 20008
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
hoseam
hoseam
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1222 Visits: 431
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).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search