February 7, 2014 at 6:25 am
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
February 7, 2014 at 6:31 am
hoseam (2/7/2014)
HiIs 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
February 7, 2014 at 6:34 am
Without listing the columns??
February 7, 2014 at 6:40 am
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
February 7, 2014 at 6:49 am
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
February 7, 2014 at 6:56 am
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.
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
February 7, 2014 at 7:01 am
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
February 7, 2014 at 7:11 am
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.
February 7, 2014 at 7:18 am
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?
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
February 10, 2014 at 12:43 am
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).
February 10, 2014 at 12:55 am
Use the OUTPUT clause on an UPDATE statement. OUTPUT was introduced in SQL Server 2005.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 10, 2014 at 12:58 am
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.
February 10, 2014 at 1:27 am
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 ....
February 10, 2014 at 2:04 am
Can you provide us with a brief description of the end to end process?
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
February 10, 2014 at 2:34 am
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