Insert Target Table Dynamically Based On schema

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert Target Table Dynamically Based On schema

Author

Topic

pradeepmanne
Starting Member

31 Posts

Posted - 2011-12-30 : 08:45:32

HI,
Insert Target Table Dynamically Based On Source table schema
I Have got a situation where i need to insert some values into a table
based on that schema of table.
can we insert values into table using a store procedure,based on table schema at runtime and also when schema changes .
is it possible???

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-30 : 09:15:59

It is possible. SQL Server is flexible enough to let you do pretty much anything you can imagine. However, hard to tell anything more without seeing the details of the problem.

More often than not, when it seems like one has to dynamically change table names, columns etc., there may be better design choices that would avoid the need for such measures. If you post the details, there are many people on this forum who have in-depth knowledge and sense about good design and they will be able to offer suggestions.

Many of those people who are real experts tend to spend no more than a minute or two looking at any one posting/question, so it would help if you posted the question that is easily understood. Take a look at Brett's blog for some guidance on posting: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

pradeepmanne
Starting Member

31 Posts

Posted - 2011-12-30 : 10:10:50

quote:
Originally posted by pradeepmanne

HI ,
thanks for the reply
below SP will be my table i need insert SP for that and also if u feel
any correction in my SP plz suggest me

Create proc [dbo].[altertemp1](@Value_c varchar)
AS
BEGIN

if(@Value_c<2)

create table ##Mytemp(col1 binary(50),col2 binary(50))

else

DECLARE @counter INT
DECLARE @ColName nvarchar(100)
DECLARE @DynamicSQL nvarchar(250)

SET @counter = 3
WHILE (@counter <= @Value_c ) BEGIN
SET @ColName= 'Col' + cast(@counter as nvarchar(100))
SET @DynamicSQL = 'ALTER TABLE ##Mytemp ADD ['+ @ColName +'] binary(100) NULL'
EXEC(@DynamicSQL)
SET @counter = @counter + 1

end

thanks inadvance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-30 : 13:37:06

Reading through your code, I have the following comments:

1. If the very first time you call it, if the parameter @Value_c is 2 or greater, the stored proc would fail with an error message because you would be trying to alter the temp table which does not exist.

2. If you call the stored proc twice it would fail because it would try to create columns with names that already exist in the table.

Apart from all that, the concept of a stored procedure to dynamically create/alter a global temp table doesn't seem like a good idea. Isn't there another way to accomplish it? For example, create the table with the maximum number of columns that you are likely to need. Do that at the very beginning of whatever you are trying to do. Or may be something else.

I know I am being vague here, but the concept and the design doesn't seem robust to me.

pradeepmanne
Starting Member

31 Posts

Posted - 2011-12-31 : 06:55:39

hi,
i have created table with 2 cols thats not a problem for me
creation of temp table is working perfectly but i need
a sp to insert into temp table based on the Temp table schema

creating temp as other logic in my C#.net code

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-31 : 08:45:57

You can find out how many columns there by querying the sys.columns system table. Is that what you are asking?

DECLARE @current_column_count INT;

SELECT   @current_column_count = COUNT(*) 
FROM     tempdb.sys.[columns] 
WHERE    [object_id] = OBJECT_ID('tempdb..##Mytemp')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources