Mega Code Archive
Using a dynamic sql statement in Insert select
<![CDATA[
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
]]>