Using a dynamic sql statement in 'Insert...select' : Dynamic SQL « Transact SQL « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Transact SQL
3. Dynamic SQL

Using a dynamic sql statement in 'Insert...select'

 

2>
3> CREATE TABLE Orders (
4>      OrderID int IDENTITY (1, 1) NOT NULL ,
5>      CustomerID nchar (5) NULL ,
6>      EmployeeID int NULL ,
7>      OrderDate datetime NULL ,
8>      RequiredDate datetime NULL ,
9>      ShippedDate datetime NULL ,
10>     ShipVia int NULL ,
11>     Freight money NULL DEFAULT (0),
12>     ShipName nvarchar (40) NULL ,
13>     ShipAddress nvarchar (60) NULL ,
14>     ShipCity nvarchar (15) NULL ,
15>     ShipRegion nvarchar (15) NULL ,
16>     ShipPostalCode nvarchar (10) NULL ,
17>     ShipCountry nvarchar (15) NULL
18> )
19> GO
1>
2>
3> DECLARE
4>   @schemaname AS NVARCHAR(128),
5>   @tablename  AS NVARCHAR(128),
6>   @colname    AS NVARCHAR(128),
7>   @sql        AS NVARCHAR(805),
8>   @cnt       AS INT;
9>
10> SET @schemaname = N'dbo';
11> SET @tablename  = N'Orders';
12> SET @colname    = N'CustomerID';
13> SET @sql = N'SELECT COUNT(DISTINCT '
14>   + QUOTENAME(@colname) + N') FROM '
15>   + QUOTENAME(@schemaname)
16>   + N'.'
17>   + QUOTENAME(@tablename)
18>   + N';';
19>
20> CREATE TABLE #T(cnt INT);
21> INSERT INTO #T
22>   EXEC(@sql);
23> SET @cnt = (SELECT cnt FROM #T);
24> SELECT @cnt;
25> GO

-----------
          0
1>
2> DROP TABLE #T;
3> GO
1>
2> drop table orders;
3> GO

 

Related examples in the same category