inserting using a select
-
Does anyone know an easy way to do an insert using a select statement on a table with an identity field without having to list every field in the insert? To clarify: I have a table in which I want to duplicate a record, but have a new identity for the newly inserted record. I can't use insert [tablename] select * from [tablename] where id = 1 because it tries to insert the same id value. I also don't want to have to list all of the field names in the insert, which I believe would work, as there are around 50 of them. Any ideas? If this is completely confusing please let me know and I'll try and clarify my oh-so-cloudy description.
Thanks
-
I created a table in Northwind named x with an extra Idenitity column and this worked just fine no matter where the extra column was in my table that was identity. Only has an issue if I have the same number of columns in both.
insert into x select * from orders
-
I think you need to list out the fields.
I had an archive process that did this.
And I remember cutting and pasting alot.
You might play around with this, if you're new to variable tables.
declare @myVarTable table
(
au_id varchar (64) ,
au_lname varchar (64) ,
au_fname varchar (64) ,
phone varchar (64) ,
address varchar (64) ,
city varchar (64) ,
state varchar (64) ,
zip varchar (64) ,
contract varchar (64)
)
Insert into @myVarTable
select * from authors
Select * from @myVarTable
I tried dropping the @myVarTable.au_id, without any luck. I stand by my first statement, but letting you know I actually tried something.
-
So far I haven't been able to find a way to do this. It does seem like the field list is required. The best solution I've been able to come up with so far is to use the view builder to get the list of column names and then copy that text for my query. It gets the job done, but isn't vey elegant.
-
If you know the name of the identity column, you can do something like this :
-- drop table tst drop table #tst
go
create table tst
(
i int identity(1,1),
j varchar(10),
k int
)
go
insert tst select 'A',1
go
select * into #tst from tst where i = 1
go
alter table #tst drop column i -- drop identity column from temp table
go
insert tst select * from #tst
drop table #tst
go
select * from tst
-
This is a neat trick:
In QA, Objectbrowser:
Drag and Drop the Columns folder of your table into the query window....
How's that for saving some typing
/rockmoose
You must unlearn what You have learnt -
I think you always want to list the explicit columns in your insert statement. This will make your life easier in the future. Say you add a column to your destination table TableA, if you use "insert into TableA select * from tableB", it will fail immediately. Here is an easy way to get all the column names from any table without identity column.
SELECT c.name + ','
FROM sysobjects o
INNER JOIN syscolumns c On o.ID = c.ID
WHERE o.name ='ctlReportAssetClass'
AND autoval IS NULL
ORDER BY c.colOrder
Just copy the resultset and put it into your select and insert into statement. It's pretty easy to do.
-
Wow! Nice trick, Rockmoose!
If you want to do it for dynamic SQL:
create function Column_CSV (@TableName sysname)
returns varchar(8000)
as
begin
declare @ColList varchar(8000)
SELECT @ColList = isnull(@ColList + ', ', '') + c.name
FROM sysobjects o
JOIN syscolumns c On o.ID = c.ID
WHERE o.name = @TableName
ORDER BY c.colOrder
return @ColList
END
Signature is NULL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply