Click here to Skip to main content
15,867,488 members
Home / Discussions / Database
   

Database

 
GeneralRe: Reading csv file data using select statement Pin
indian14311-Apr-18 9:18
indian14311-Apr-18 9:18 
Questionwriting complex queries in Sql Pin
emilyz788-Apr-18 8:42
emilyz788-Apr-18 8:42 
Questionwriting complex queries in Sql Pin
emilyz788-Apr-18 8:42
emilyz788-Apr-18 8:42 
AnswerRe: writing complex queries in Sql Pin
Mycroft Holmes8-Apr-18 12:57
professionalMycroft Holmes8-Apr-18 12:57 
AnswerRe: writing complex queries in Sql Pin
CHill608-Apr-18 23:33
mveCHill608-Apr-18 23:33 
AnswerRe: writing complex queries in Sql Pin
Eddy Vluggen9-Apr-18 2:16
professionalEddy Vluggen9-Apr-18 2:16 
QuestionConfiguring multiple properties or values in SSIS Package using Package Configuration file Pin
indian1432-Apr-18 14:18
indian1432-Apr-18 14:18 
QuestionTrying to insert values into Table Variable in Dynamic Sql Pin
indian1432-Apr-18 13:17
indian1432-Apr-18 13:17 
Hi all,

I am trying to insert values into Table variable within Dynamic Sql, I am able to select values from Table variable but not able to insert values in to the Table variable.
Here is how I am selecting values from Table variable
CREATE TYPE IntegerTableType AS TABLE (ID INT);
go

DECLARE @TempVehicles IntegerTableType; 

INSERT  @TempVehicles
values (1);

DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='SELECT * 
           FROM @TempVehicles;';

EXECUTE SP_EXECUTESQL @SQL,N'@TempVehicles IntegerTableType READONLY',
@TempVehicles;

IF EXISTS (SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name ='IntegerTableType') 
    DROP TYPE [dbo].IntegerTableType;
GO
But when I am trying to insert values as below, I am not able to insert.
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name ='Results') 
    CREATE TYPE Results AS TABLE (TableName nvarchar(500), ColumnName nvarchar(500))
GO

Declare @Tables table (Id int identity(1,1), TableName nvarchar(500))
declare @Columns table (Id int identity(1,1), ColumnName nvarchar(500))

declare @Results AS Results

declare @IdMin1 int, @IdMax1 int, @IdMin2 int, @IdMax2 int, @col varchar(500), @cmd varchar(max), @TableName nvarchar(500)

insert into @Tables(TableName)
SELECT t.name 
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = N'dbo'
  order by t.name

select @IdMin1=min(Id), @IdMax1=max(Id) from @Tables
while(@IdMin1<=@IdMax1)
begin
    set @TableName = (select top 1 TableName from @Tables where Id = @IdMin1)

<pre>
delete from @Columns
insert into @Columns(ColumnName)
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = @TableName
order by c.name

--insert into @Results (TableName) values(@TableName)

select @IdMin2=min(Id), @IdMax2=max(Id) from @Columns

while(@IdMin2<=@IdMax2)
begin
set @col=''
select @col=ColumnName from @Columns where Id=@IdMin2

SELECT @cmd = 'IF EXISTS (SELECT top 1 * FROM ' + @TableName + ' WHERE [' + @col +
'] IS NULL) BEGIN insert into @Results (TableName, ColumnName) values( ''' + @TableName +''',''' + @col + ','') end';

begin try
--select @cmd
--EXEC(@cmd)
EXECUTE SP_EXECUTESQL @cmd, N'@Results Results READONLY', @Results;
end try
begin catch
SELECT @col=@col + ', Error Message: ' + ERROR_MESSAGE()

end catch

--insert into #Results (ColumnName) values(@col)

set @IdMin2+=1
end

set @IdMin1+=1
end

select * from @Results where TableName is not null order by TableName, ColumnName

IF EXISTS (SELECT 1 FROM sys.types WHERE is_table_type = 1 AND name ='Results')
DROP TYPE [dbo].Results;
GO


Can anybody please help me how can I finish this and let me know why is it not inserting into Table variable using Dynamic Sql, is there any way to perform that action. Any help would be much helpful, thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."
AnswerRe: Trying to insert values into Table Variable in Dynamic Sql Pin
Mycroft Holmes2-Apr-18 14:22
professionalMycroft Holmes2-Apr-18 14:22 
GeneralRe: Trying to insert values into Table Variable in Dynamic Sql Pin
indian1433-Apr-18 7:49
indian1433-Apr-18 7:49 
AnswerRe: Trying to insert values into Table Variable in Dynamic Sql Pin
Richard Deeming3-Apr-18 8:44
mveRichard Deeming3-Apr-18 8:44 
GeneralRe: Trying to insert values into Table Variable in Dynamic Sql Pin
indian1434-Apr-18 12:35
indian1434-Apr-18 12:35 
GeneralRe: Trying to insert values into Table Variable in Dynamic Sql Pin
Richard Deeming5-Apr-18 8:06
mveRichard Deeming5-Apr-18 8:06 
QuestionMysql database problem Pin
Member 1375522730-Mar-18 18:12
Member 1375522730-Mar-18 18:12 
AnswerRe: Mysql database problem Pin
A_Griffin30-Mar-18 21:41
A_Griffin30-Mar-18 21:41 
GeneralRe: Mysql database problem Pin
Member 1375522730-Mar-18 21:55
Member 1375522730-Mar-18 21:55 
GeneralRe: Mysql database problem Pin
A_Griffin30-Mar-18 22:12
A_Griffin30-Mar-18 22:12 
GeneralRe: Mysql database problem Pin
Victor Nijegorodov30-Mar-18 22:45
Victor Nijegorodov30-Mar-18 22:45 
GeneralRe: Mysql database problem Pin
jschell31-Mar-18 7:29
jschell31-Mar-18 7:29 
GeneralRe: Mysql database problem Pin
Victor Nijegorodov31-Mar-18 20:56
Victor Nijegorodov31-Mar-18 20:56 
AnswerRe: Mysql database problem Pin
jschell31-Mar-18 7:34
jschell31-Mar-18 7:34 
QuestionSSIS package and package in a SQL server agent Job Pin
VK1927-Mar-18 8:27
VK1927-Mar-18 8:27 
GeneralRe: SSIS package and package in a SQL server agent Job Pin
CHill6027-Mar-18 22:09
mveCHill6027-Mar-18 22:09 
GeneralRe: SSIS package and package in a SQL server agent Job Pin
VK1928-Mar-18 2:06
VK1928-Mar-18 2:06 
GeneralRe: SSIS package and package in a SQL server agent Job Pin
CHill6029-Mar-18 12:27
mveCHill6029-Mar-18 12:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.